Search code examples
linqlinq-to-sqllinqpad

Insert with PrimaryKey=ForeignKey

I have two tables:

File
|- Id (Primary Key)
|- Name
|- ...

SpecificFile
|- FileId (Primary Key & Foreign Key to File.Id)
|- SpecificProperty1
|- ...

I have the following Linq2Sql code (LINQPad):

 var sfiles = from f in File
              where f.Name LIKE 'Specific%'
              select new SpecificFile { FileId = f.Id, SpecificProperty1 = "Foo" };
SpecificFiles.InsertAllOnSubmit(sfiles);

However, the insert fails with:

SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_SpecificFile_File". The conflict occurred in database "MyDatabase", table "dbo.File", column 'Id'. The statement has been terminated.

The reason is clear if I debug and look at the Linq2Sql-generated INSERT statement:

INSERT INTO [SpecificFile]([SpecificProperty1])
VALUES (@p0)

How to inform Linq2Sql that it has to use a specific ID for insertion?

Note: setting the File object reference to f in the new-expression doesn't help either.

Solution

  • Found the answer myself...unfortunately, SpecificFile.FileId was set to IDENTITY(1,1). The exception message didn't help much ...