This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
I have this tables => Pupil and Document. Both have an Id type/method (Int32/Identity Column) set on the Entity itself. The tables have no Id created by me. Now I have the Junction table PupilDocument. There are no properties set like PupilId or DocumentId because you wrote they are generated automatically. Well not on my side and I would like to know when/how are they generated? I have read this blog post and now comes my last question -in this post...- how can I create a junction table with only 2 Ids(composite) not 3 Ids(composite/surrogate) to make a query fully working. does not work for me. I think I miss something. |
|
|
The first question is why you want to avoid the surrogate key. Unless you are working with a legacy schema, it's much easier just to have the surrogate key. Assuming that you do have an existing schema that you don't want to change, the blog post you linked should work fine. What isn't working? Can you post your model and the schema (CREATE TABLE) statements of the existing database? |
|
|
To answer this I guess you should check this:
http://www.mindscapehq.com/forums/Thread.aspx?ThreadID=2371
I have a database but without tables because I want to do update database... see thread above.
|
|
|
Looking at the screenshot you posted in another thread, one of your problems is that you have created PupilId and DocumentId as normal properties. Notice in the linked blog post that the composite primary key/foreign keys are in the IDENTITY section of the EmployeeProject entity. At the moment, your PupilDocument entity is defined to have a surrogate key, plus explicit PupilId and DocumentId properties, plus possibly PupilId and DocumentId properties that are implicit in the Pupil and Document associations. |
|
|
-- Add table Pupil to the database /* ERROR: Pupil: Add table Pupil to the database - Can't create identity/autoincrement columns on this database */; -- Add table Document to the database /* ERROR: Document: Add table Document to the database - Can't create identity/autoincrement columns on this database */; -- Add table Period to the database /* ERROR: Period: Add table Period to the database - Can't create identity/autoincrement columns on this database */; -- Add table Report to the database /* ERROR: Report: Add table Report to the database - Can't create identity/autoincrement columns on this database */; -- Add table PupilDocument to the database CREATE TABLE [PupilDocument] (Id INT NOT NULL PRIMARY KEY , [PupilId] INT NOT NULL DEFAULT 0, [DocumentId] INT NOT NULL DEFAULT 0, FOREIGN KEY (PupilId) REFERENCES [Pupil](Id), FOREIGN KEY (DocumentId) REFERENCES [Document](Id)); -- Add table PeriodDocument to the database CREATE TABLE [PeriodDocument] (Id INT NOT NULL PRIMARY KEY , [DocumentId] INT NOT NULL DEFAULT 0, [PeriodId] INT NOT NULL DEFAULT 0, FOREIGN KEY (DocumentId) REFERENCES [Document](Id), FOREIGN KEY (PeriodId) REFERENCES [Period](Id)); -- Add table ReportDocument to the database CREATE TABLE [ReportDocument] (Id INT NOT NULL PRIMARY KEY , [DocumentId] INT NOT NULL DEFAULT 0, [ReportId] INT NOT NULL DEFAULT 0, FOREIGN KEY (DocumentId) REFERENCES [Document](Id), FOREIGN KEY (ReportId) REFERENCES [Report](Id)); -- Change identity column from Int64 to Int32 /* ERROR: SchoolclassCode: Change identity column from Int64 to Int32 - LightSpeed cannot automatically apply this change to the database - please update manually */; |
|
|
sorry Ivan for this mess but I followed the instructions of John-Daniel he gave in this thread: http://www.mindscapehq.com/forums/Thread.aspx?ThreadID=3947
I used html view with the pre tags and the Model code within then I posted it but no code is visible :/ What you see above is the log sql script from the Update Database command that failed. |
|
|
I post it extra without code/pre tags:
[Serializable] [System.CodeDom.Compiler.GeneratedCode("LightSpeedModelGenerator", "1.0.0.0")] [System.ComponentModel.DataObject] [Table("SchoolclassCode", IdColumnName="Id", IdentityMethod=IdentityMethod.IdentityColumn)] public partial class SchoolclassCode : Entity<int> { #region Fields
private string _schoolclassCodeName;
#endregion
#region Field attribute and view names
/// <summary>Identifies the SchoolclassCodeName entity attribute.</summary> public const string SchoolclassCodeNameField = "SchoolclassCodeName";
#endregion
#region Relationships
[ReverseAssociation("SchoolclassCode")] private readonly EntityCollection<Pupil> _pupils = new EntityCollection<Pupil>();
#endregion
#region Properties
public EntityCollection<Pupil> Pupils { get { return Get(_pupils); } }
public string SchoolclassCodeName { get { return Get(ref _schoolclassCodeName, "SchoolclassCodeName"); } set { Set(ref _schoolclassCodeName, value, "SchoolclassCodeName"); } }
#endregion }
[Serializable] [System.CodeDom.Compiler.GeneratedCode("LightSpeedModelGenerator", "1.0.0.0")] [System.ComponentModel.DataObject] [Table(IdColumnName="Id", IdentityMethod=IdentityMethod.IdentityColumn)] public partial class Pupil : Entity<int> { #region Fields
private string _firstName; private string _lastName; private string _gender; private string _street; private string _city; private string _postal; private string _photo; private string _email; private string _phone; private string _extraInformation; private int _schoolclassCodeId;
#endregion
#region Field attribute and view names
/// <summary>Identifies the FirstName entity attribute.</summary> public const string FirstNameField = "FirstName"; /// <summary>Identifies the LastName entity attribute.</summary> public const string LastNameField = "LastName"; /// <summary>Identifies the Gender entity attribute.</summary> public const string GenderField = "Gender"; /// <summary>Identifies the Street entity attribute.</summary> public const string StreetField = "Street"; /// <summary>Identifies the City entity attribute.</summary> public const string CityField = "City"; /// <summary>Identifies the Postal entity attribute.</summary> public const string PostalField = "Postal"; /// <summary>Identifies the Photo entity attribute.</summary> public const string PhotoField = "Photo"; /// <summary>Identifies the Email entity attribute.</summary> public const string EmailField = "Email"; /// <summary>Identifies the Phone entity attribute.</summary> public const string PhoneField = "Phone"; /// <summary>Identifies the ExtraInformation entity attribute.</summary> public const string ExtraInformationField = "ExtraInformation"; /// <summary>Identifies the SchoolclassCodeId entity attribute.</summary> public const string SchoolclassCodeIdField = "SchoolclassCodeId";
#endregion
#region Relationships
[ReverseAssociation("Pupil")] private readonly EntityCollection<PupilDocument> _pupilDocuments = new EntityCollection<PupilDocument>(); [ReverseAssociation("Pupils")] private readonly EntityHolder<SchoolclassCode> _schoolclassCode = new EntityHolder<SchoolclassCode>();
private ThroughAssociation<PupilDocument, Document> _documents;
#endregion
#region Properties
public EntityCollection<PupilDocument> PupilDocuments { get { return Get(_pupilDocuments); } }
public SchoolclassCode SchoolclassCode { get { return Get(_schoolclassCode); } set { Set(_schoolclassCode, value); } }
public ThroughAssociation<PupilDocument, Document> Documents { get { if (_documents == null) { _documents = new ThroughAssociation<PupilDocument, Document>(_pupilDocuments); } return Get(_documents); } }
public string FirstName { get { return Get(ref _firstName, "FirstName"); } set { Set(ref _firstName, value, "FirstName"); } }
public string LastName { get { return Get(ref _lastName, "LastName"); } set { Set(ref _lastName, value, "LastName"); } }
public string Gender { get { return Get(ref _gender, "Gender"); } set { Set(ref _gender, value, "Gender"); } }
public string Street { get { return Get(ref _street, "Street"); } set { Set(ref _street, value, "Street"); } }
public string City { get { return Get(ref _city, "City"); } set { Set(ref _city, value, "City"); } }
public string Postal { get { return Get(ref _postal, "Postal"); } set { Set(ref _postal, value, "Postal"); } }
public string Photo { get { return Get(ref _photo, "Photo"); } set { Set(ref _photo, value, "Photo"); } }
public string Email { get { return Get(ref _email, "Email"); } set { Set(ref _email, value, "Email"); } }
public string Phone { get { return Get(ref _phone, "Phone"); } set { Set(ref _phone, value, "Phone"); } }
public string ExtraInformation { get { return Get(ref _extraInformation, "ExtraInformation"); } set { Set(ref _extraInformation, value, "ExtraInformation"); } }
/// <summary>Gets or sets the ID for the <see cref="SchoolclassCode" /> property.</summary> public int SchoolclassCodeId { get { return Get(ref _schoolclassCodeId, "SchoolclassCodeId"); } set { Set(ref _schoolclassCodeId, value, "SchoolclassCodeId"); } }
#endregion }
[Serializable] [System.CodeDom.Compiler.GeneratedCode("LightSpeedModelGenerator", "1.0.0.0")] [System.ComponentModel.DataObject] public partial class ReportDocument : Entity<int> { #region Fields
private int _documentId; private int _reportId;
#endregion
#region Field attribute and view names
/// <summary>Identifies the DocumentId entity attribute.</summary> public const string DocumentIdField = "DocumentId"; /// <summary>Identifies the ReportId entity attribute.</summary> public const string ReportIdField = "ReportId";
#endregion
#region Relationships
[ReverseAssociation("ReportDocuments")] private readonly EntityHolder<Document> _document = new EntityHolder<Document>(); [ReverseAssociation("ReportDocuments")] private readonly EntityHolder<Report> _report = new EntityHolder<Report>();
#endregion
#region Properties
public Document Document { get { return Get(_document); } set { Set(_document, value); } }
public Report Report { get { return Get(_report); } set { Set(_report, value); } }
/// <summary>Gets or sets the ID for the <see cref="Document" /> property.</summary> public int DocumentId { get { return Get(ref _documentId, "DocumentId"); } set { Set(ref _documentId, value, "DocumentId"); } }
/// <summary>Gets or sets the ID for the <see cref="Report" /> property.</summary> public int ReportId { get { return Get(ref _reportId, "ReportId"); } set { Set(ref _reportId, value, "ReportId"); } }
#endregion }
[Serializable] [System.CodeDom.Compiler.GeneratedCode("LightSpeedModelGenerator", "1.0.0.0")] [System.ComponentModel.DataObject] public partial class PeriodDocument : Entity<int> { #region Fields
private int _documentId; private int _periodId;
#endregion
#region Field attribute and view names
/// <summary>Identifies the DocumentId entity attribute.</summary> public const string DocumentIdField = "DocumentId"; /// <summary>Identifies the PeriodId entity attribute.</summary> public const string PeriodIdField = "PeriodId";
#endregion
#region Relationships
[ReverseAssociation("PeriodDocuments")] private readonly EntityHolder<Document> _document = new EntityHolder<Document>(); [ReverseAssociation("PeriodDocuments")] private readonly EntityHolder<Period> _period = new EntityHolder<Period>();
#endregion
#region Properties
public Document Document { get { return Get(_document); } set { Set(_document, value); } }
public Period Period { get { return Get(_period); } set { Set(_period, value); } }
/// <summary>Gets or sets the ID for the <see cref="Document" /> property.</summary> public int DocumentId { get { return Get(ref _documentId, "DocumentId"); } set { Set(ref _documentId, value, "DocumentId"); } }
/// <summary>Gets or sets the ID for the <see cref="Period" /> property.</summary> public int PeriodId { get { return Get(ref _periodId, "PeriodId"); } set { Set(ref _periodId, value, "PeriodId"); } }
#endregion }
[Serializable] [System.CodeDom.Compiler.GeneratedCode("LightSpeedModelGenerator", "1.0.0.0")] [System.ComponentModel.DataObject] public partial class PupilDocument : Entity<int> { #region Fields
private int _pupilId; private int _documentId;
#endregion
#region Field attribute and view names
/// <summary>Identifies the PupilId entity attribute.</summary> public const string PupilIdField = "PupilId"; /// <summary>Identifies the DocumentId entity attribute.</summary> public const string DocumentIdField = "DocumentId";
#endregion
#region Relationships
[ReverseAssociation("PupilDocuments")] private readonly EntityHolder<Pupil> _pupil = new EntityHolder<Pupil>(); [ReverseAssociation("PupilDocuments")] private readonly EntityHolder<Document> _document = new EntityHolder<Document>();
#endregion
#region Properties
public Pupil Pupil { get { return Get(_pupil); } set { Set(_pupil, value); } }
public Document Document { get { return Get(_document); } set { Set(_document, value); } }
/// <summary>Gets or sets the ID for the <see cref="Pupil" /> property.</summary> public int PupilId { get { return Get(ref _pupilId, "PupilId"); } set { Set(ref _pupilId, value, "PupilId"); } }
/// <summary>Gets or sets the ID for the <see cref="Document" /> property.</summary> public int DocumentId { get { return Get(ref _documentId, "DocumentId"); } set { Set(ref _documentId, value, "DocumentId"); } }
#endregion }
[Serializable] [System.CodeDom.Compiler.GeneratedCode("LightSpeedModelGenerator", "1.0.0.0")] [System.ComponentModel.DataObject] [Table(IdColumnName="Id", IdentityMethod=IdentityMethod.IdentityColumn)] public partial class Document : Entity<int> { #region Fields
private string _tags; private string _documentName; private byte[] _documentData;
#endregion
#region Field attribute and view names
/// <summary>Identifies the Tags entity attribute.</summary> public const string TagsField = "Tags"; /// <summary>Identifies the DocumentName entity attribute.</summary> public const string DocumentNameField = "DocumentName"; /// <summary>Identifies the DocumentData entity attribute.</summary> public const string DocumentDataField = "DocumentData";
#endregion
#region Relationships
[ReverseAssociation("Document")] private readonly EntityCollection<PupilDocument> _pupilDocuments = new EntityCollection<PupilDocument>(); [ReverseAssociation("Document")] private readonly EntityCollection<ReportDocument> _reportDocuments = new EntityCollection<ReportDocument>(); [ReverseAssociation("Document")] private readonly EntityCollection<PeriodDocument> _periodDocuments = new EntityCollection<PeriodDocument>();
private ThroughAssociation<ReportDocument, Report> _reports; private ThroughAssociation<PeriodDocument, Period> _periods; private ThroughAssociation<PupilDocument, Pupil> _pupils;
#endregion
#region Properties
public EntityCollection<PupilDocument> PupilDocuments { get { return Get(_pupilDocuments); } }
public EntityCollection<ReportDocument> ReportDocuments { get { return Get(_reportDocuments); } }
public EntityCollection<PeriodDocument> PeriodDocuments { get { return Get(_periodDocuments); } }
public ThroughAssociation<ReportDocument, Report> Reports { get { if (_reports == null) { _reports = new ThroughAssociation<ReportDocument, Report>(_reportDocuments); } return Get(_reports); } }
public ThroughAssociation<PeriodDocument, Period> Periods { get { if (_periods == null) { _periods = new ThroughAssociation<PeriodDocument, Period>(_periodDocuments); } return Get(_periods); } }
public ThroughAssociation<PupilDocument, Pupil> Pupils { get { if (_pupils == null) { _pupils = new ThroughAssociation<PupilDocument, Pupil>(_pupilDocuments); } return Get(_pupils); } }
public string Tags { get { return Get(ref _tags, "Tags"); } set { Set(ref _tags, value, "Tags"); } }
public string DocumentName { get { return Get(ref _documentName, "DocumentName"); } set { Set(ref _documentName, value, "DocumentName"); } }
public byte[] DocumentData { get { return Get(ref _documentData, "DocumentData"); } set { Set(ref _documentData, value, "DocumentData"); } }
#endregion }
[Serializable] [System.CodeDom.Compiler.GeneratedCode("LightSpeedModelGenerator", "1.0.0.0")] [System.ComponentModel.DataObject] [Table("Period", IdColumnName="Id", IdentityMethod=IdentityMethod.IdentityColumn)] public partial class Period : Entity<int> { #region Fields
private string _periodNumber; private string _schoolclassCodeName; private string _homework; private string _content; private string _date;
#endregion
#region Field attribute and view names
/// <summary>Identifies the PeriodNumber entity attribute.</summary> public const string PeriodNumberField = "PeriodNumber"; /// <summary>Identifies the SchoolclassCodeName entity attribute.</summary> public const string SchoolclassCodeNameField = "SchoolclassCodeName"; /// <summary>Identifies the Homework entity attribute.</summary> public const string HomeworkField = "Homework"; /// <summary>Identifies the Content entity attribute.</summary> public const string ContentField = "Content"; /// <summary>Identifies the Date entity attribute.</summary> public const string DateField = "Date";
#endregion
#region Relationships
[ReverseAssociation("Period")] private readonly EntityCollection<PeriodDocument> _periodDocuments = new EntityCollection<PeriodDocument>();
private ThroughAssociation<PeriodDocument, Document> _documents;
#endregion
#region Properties
public EntityCollection<PeriodDocument> PeriodDocuments { get { return Get(_periodDocuments); } }
public ThroughAssociation<PeriodDocument, Document> Documents { get { if (_documents == null) { _documents = new ThroughAssociation<PeriodDocument, Document>(_periodDocuments); } return Get(_documents); } }
public string PeriodNumber { get { return Get(ref _periodNumber, "PeriodNumber"); } set { Set(ref _periodNumber, value, "PeriodNumber"); } }
public string SchoolclassCodeName { get { return Get(ref _schoolclassCodeName, "SchoolclassCodeName"); } set { Set(ref _schoolclassCodeName, value, "SchoolclassCodeName"); } }
public string Homework { get { return Get(ref _homework, "Homework"); } set { Set(ref _homework, value, "Homework"); } }
public string Content { get { return Get(ref _content, "Content"); } set { Set(ref _content, value, "Content"); } }
public string Date { get { return Get(ref _date, "Date"); } set { Set(ref _date, value, "Date"); } }
#endregion }
[Serializable] [System.CodeDom.Compiler.GeneratedCode("LightSpeedModelGenerator", "1.0.0.0")] [System.ComponentModel.DataObject] [Table(IdColumnName="Id", IdentityMethod=IdentityMethod.IdentityColumn)] public partial class Report : Entity<int> { #region Fields
private string _type; private string _date; private string _teacherName; private string _reportDetails; private string _actionTaken; private string _furtherAction; private string _homeContact; private string _tutorGroup;
#endregion
#region Field attribute and view names
/// <summary>Identifies the Type entity attribute.</summary> public const string TypeField = "Type"; /// <summary>Identifies the Date entity attribute.</summary> public const string DateField = "Date"; /// <summary>Identifies the TeacherName entity attribute.</summary> public const string TeacherNameField = "TeacherName"; /// <summary>Identifies the ReportDetails entity attribute.</summary> public const string ReportDetailsField = "ReportDetails"; /// <summary>Identifies the ActionTaken entity attribute.</summary> public const string ActionTakenField = "ActionTaken"; /// <summary>Identifies the FurtherAction entity attribute.</summary> public const string FurtherActionField = "FurtherAction"; /// <summary>Identifies the HomeContact entity attribute.</summary> public const string HomeContactField = "HomeContact"; /// <summary>Identifies the TutorGroup entity attribute.</summary> public const string TutorGroupField = "TutorGroup";
#endregion
#region Relationships
[ReverseAssociation("Report")] private readonly EntityCollection<ReportDocument> _reportDocuments = new EntityCollection<ReportDocument>();
private ThroughAssociation<ReportDocument, Document> _documents;
#endregion
#region Properties
public EntityCollection<ReportDocument> ReportDocuments { get { return Get(_reportDocuments); } }
public ThroughAssociation<ReportDocument, Document> Documents { get { if (_documents == null) { _documents = new ThroughAssociation<ReportDocument, Document>(_reportDocuments); } return Get(_documents); } }
public string Type { get { return Get(ref _type, "Type"); } set { Set(ref _type, value, "Type"); } }
public string Date { get { return Get(ref _date, "Date"); } set { Set(ref _date, value, "Date"); } }
public string TeacherName { get { return Get(ref _teacherName, "TeacherName"); } set { Set(ref _teacherName, value, "TeacherName"); } }
public string ReportDetails { get { return Get(ref _reportDetails, "ReportDetails"); } set { Set(ref _reportDetails, value, "ReportDetails"); } }
public string ActionTaken { get { return Get(ref _actionTaken, "ActionTaken"); } set { Set(ref _actionTaken, value, "ActionTaken"); } }
public string FurtherAction { get { return Get(ref _furtherAction, "FurtherAction"); } set { Set(ref _furtherAction, value, "FurtherAction"); } }
public string HomeContact { get { return Get(ref _homeContact, "HomeContact"); } set { Set(ref _homeContact, value, "HomeContact"); } }
public string TutorGroup { get { return Get(ref _tutorGroup, "TutorGroup"); } set { Set(ref _tutorGroup, value, "TutorGroup"); } }
#endregion }
/// <summary> /// Provides a strong-typed unit of work for working with the SchoolModel model. /// </summary> [System.CodeDom.Compiler.GeneratedCode("LightSpeedModelGenerator", "1.0.0.0")] public partial class SchoolModelUnitOfWork : Mindscape.LightSpeed.UnitOfWork {
public System.Linq.IQueryable<SchoolclassCode> SchoolclassCodes { get { return this.Query<SchoolclassCode>(); } }
public System.Linq.IQueryable<Pupil> Pupils { get { return this.Query<Pupil>(); } }
public System.Linq.IQueryable<ReportDocument> ReportDocuments { get { return this.Query<ReportDocument>(); } }
public System.Linq.IQueryable<PeriodDocument> PeriodDocuments { get { return this.Query<PeriodDocument>(); } }
public System.Linq.IQueryable<PupilDocument> PupilDocuments { get { return this.Query<PupilDocument>(); } }
public System.Linq.IQueryable<Document> Documents { get { return this.Query<Document>(); } }
public System.Linq.IQueryable<Period> Periods { get { return this.Query<Period>(); } }
public System.Linq.IQueryable<Report> Reports { get { return this.Query<Report>(); } }
}
|
|
|
What you've got is using a surrogate key (and looks correct in that regard), and the SQL that you posted shows no errors in terms of creating the PupilDocument table (though again of course it has a surrogate key). So what you have should actually work fine. If you really want to get rid of the surrogate key, you need to create identity properties on PupilDocument. (See the post you linked earlier for how this will look -- notice how EmployeeId and ProjectId display in the Identity section rather than the Properties section.) Creating identity properties model-first is a bit fiddly, because we really only support composite/natural keys for compatibility for existing databases. When you've done that, you can map the associations to those identity properties using Key Property Reference (Id.Xxx): this will suppress the autogenerated PupilId and DocumentId properties, and configure the associations to use the identity properties as foreign keys instead. Again, our recommendation would be to stick with the surrogate key unless you have a compelling reason for getting rid of it. It really will make working with the many-to-many association a lot easier! |
|
|
ok good tip I wil consider that!
btw. the problem I HAD is because SQLite does not support Identity Column therefore the tables were never created. I had to use sequence... then the table was create but I could not insert anything, because the messagebox said sqlite3 does not provide sequence number identity generation. Ok ok maybe time to wait for SQL CE4 in spring 2011... |
|
|
Take a look at KeyTable identity generation. That is LightSpeed's default and it works across all databases and is nearly as efficient as sequences (and more efficient than identity columns!). You will need to create the key table but there is a SQL script in the install_dir/Providers directory which will take care of that. Hope this helps! |
|
|
Why does the KeyTable`s Id always increment by 10 from 1 ,11,21,31 with 4 Inserts ? It seems the Keytable`s Id is always the leading Id above all other table`s Id column, right? |
|
|
FYI - SQLCE 4 RTM'd a couple of weeks ago...
|
|