Linq-to-sql foreign key id not added automatically

Question

I have a service-based file database. With two table: Client and Visitation. There is a One-to-Many relationship between tables: one client -> many visitations

Tables:

CREATE TABLE [dbo].[Clients] (
    [Id]          INT            IDENTITY (1, 1) NOT NULL,
    [Surname]     NVARCHAR (MAX) NULL,
    [Firstname]   NVARCHAR (MAX) NULL,
    [Birthday]    DATETIME       NULL,
    [Email]       NVARCHAR (MAX) NULL,
    [PhoneNumber] NVARCHAR (MAX) NULL,
    [Job]         NVARCHAR (MAX) NULL,
    [Note]        NVARCHAR (MAX) NULL,
    [Birthplace]  NVARCHAR (MAX) NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

CREATE TABLE [dbo].[Visitationdb] (
    [Id]        INT      IDENTITY (1, 1) NOT NULL,
    [StartTime] DATETIME NULL,
    [ClientId]  INT      NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_Visitationdb_ToClients] FOREIGN KEY ([ClientId]) REFERENCES [dbo].[Clients] ([Id])
);

I created the required ClientId column and foreign key constraint.

Linq-to-SQL

Added new Linq to SQL classes (dbml) file to the project and created the tables' classes with the help of the designer. (Here I have to modify one-to-one relationship to one-to-many)

Problem

DataContext MdpContext = new DataContext(Settings.Default.mdcdbConnectionString); //creating the context
var c = new Client //creating new client
    {
        Birthday = patientold.Birthday,
        Email = patientold.Email,
        Firstname = patientold.FirstName,
        Job = patientold.Job,
        PhoneNumber = patientold.PhoneNumber,
        Surname = patientold.SurName,
    };
clients.InsertOnSubmit(c);
MdpContext.SubmitChanges(); //now I will have my auto generated client id set by database       
var visit = new Visitationdb {StartTime = visitold.StartTime};
c.Visitationdb.Add(visit);
MdpContext.SubmitChanges();

EXCEPTION THROWN: Cannot insert the value NULL into column 'ClientId', table 'MDCDB.MDF.dbo.Visitationdb'; column does not allow nulls. INSERT fails.

Questions

Why L2S not handling inserting the ClientId automatically at line c.Visitationdb.Add(visit)? Why do I have to SubmitChanges() every time I need an auto generated Id, is there any solution to handle all the new rows Ids and foreign keys by the framework after I added all clients and visitation?

Thank you for reading my question. Have a good coding!

David Molnar David Molnar · Accepted Answer · 2014-09-09T16:40:06

I have found the problem!

I thought that L2S handle the relationship (association) and correctly generate the entity after drag and drop into the designer.

Problem In L2S instead of foreign key (ClientId) the Visitaitondb.Id was set in the Association.

Solution Click on the Relation (Association) in the designer and at Properties / Participating Properties set the correct database fields. In my case: Client.Id -> Visitationdb.ClientId

Don't have to call SubmitChanges every time you need a generated Id or added a new entity. At the end all ids will be set correctly by L2S!

Linq-to-sql foreign key id not added automatically

1 Answers