How do I handle Id-Field in ManyToMany-Link-Table?

Hello,

I have three tables:
- User
- Source
- SourceDetails

Virtually, Source is a link-table between User and SourceDetails. I.e. Every User can have many SourceDetails and every SourceDetail many Users. However, there will never be a direct access from User to SourceDetails (i.e. by means of ThroughAssociations), since Source has an additional Field "PasswordKnown", which shall be read as well.

Below you find the table-class definitions:
********** User *************
public partial class User : Entity<int>
{
    [ValidatePresence]
    private string _Name;
    private string _Password;

    //OneToMany
    [EagerLoad(AggregateName = "Sources")]
    private readonly EntityCollection<Source> _Sources = new EntityCollection<Source>();
}

********** Source *************
public partial class Source : Entity<int>
{
    private int _UserId;
    [Dependent(ValidatePresence = true)]
    private int? _SourceDetailsId;
    private bool _PasswordKnown;

    //ManyToMany        
    [ReverseAssociation("Sources")]
    public EntityHolder<User> _User = new EntityHolder<User>();

    //OneToMany
    [EagerLoad]
    public EntityHolder<SourceDetails> _SourceDetails = new EntityHolder<SourceDetails>();
}

********** SourceDetails *************
public partial class SourceDetails : Entity<int>
{
    [ValidatePresence]
    private string _SourceIdentity;
    private string _Password;

    //OneToMany
    private readonly EntityCollection<Source> _Sources = new EntityCollection<Source>();
}

The table fields are:
********** User *************
- Id
- Name
- Password

********** Source *************
- (?? with or without Id?)
- UserId
- SourceDetailsId
- PasswordKnown

********** SourceDetails *************
- Id
- SourceIdentity
- Password

My problem:
Whenever I write a User (with linked Source and SourceDetails) to the database the corresponding data is written to the database without problems. All relations are taken into account as expected. However, when I want to read the data together with relations (by means of EagerLoad-Attribute "Sources", see User), I receive a Lightspeed-exception informing me about a missing "Id"-field in table "Source". If I add the missing Id-field and fill in manually the id-numbers, the data can be read without problems. However, if I write data to the newly designed database (i.e. with id-field in table "Source") the Id-field is NOT automatically filled by Lightspeed. What do I have to do?

Further question:
In case an additional Id-field is required, how can I assure that the key-pair, consisting of UserId and SourceDetailsId, is unique in table each row?

Thanks a lot for your answer in advance,
Henning

Every entity in LightSpeed must have an Id; therefore your Source table must have an Id column.

I am not sure why you would have problems adding items to the redesigned Source table, but my guess is that you have specified the IdentityMethod as IdentityColumn, but your Id column is not marked as autoincrement / autogenerate / identity in the database.  IdentityColumn means LightSpeed does not send the Id to the database; if the database is not expecting to fill in the Id for itself then this will cause a database error.

LightSpeed doesn't provide a built-in way of guaranteeing uniqueness of the UserId/SourceDetailsId pair.  You could do this through application logic, maybe with a custom validation, or by creating a uniqueness constraint at the database level and catching the exception.