How to insert only new records using Linq-to-SQL?

All you have to do is create a new instance of your class and then call InsertOnSumbit() on the table: var foo = new MyFoo { Name = "foo1" }; var dc = new MyDataContext(); dc.Foos. InsertOnSubmit(foo); dc.SubmitChanges() The other thing you need to be sure of is how you're incrementing your ID column. In general, I always make sure to use the IDENTITY(1,1) setting on my ID columns.

This is declared on your LINQ entity's id column like so: Column(AutoSync = AutoSync. OnInsert, IsPrimaryKey = true, IsDbGenerated = true) public Int32 Id { get; set; } To avoid duplicates, what you really need is what we call in my shop an "append" functionality. IMHO, this is most easily accomplished with a stored procedure - we even have a template we use for it: USE GO CREATE PROCEDURE .

__append ( @id INT OUTPUT, @ ) AS BEGIN SELECT @id = id FROM . S (NOLOCK) WHERE = @ IF @id IS NULL BEGIN INSERT INTO . S () OUTPUT INSERTED.Id INTO @inserted_ids VALUES (@) SELECT TOP 1 @id = id FROM @inserted_ids; END ELSE BEGIN UPDATE .

S SET = @ WHERE id = @id END END GO It is possible to do it in linq though, just query for a list of existing IDs (or whatever column you're keying off of): var dc = new MyDataContext(); var existingFoos = dc.Foos.ToList(); var newFoos = new List(); foreach(var bar in whateverYoureIterating) { // logic to add to newFoos } var foosToInsert = newFoos. Where(newFoo =>!existingFoos. Any(existingFoo => newFoo.

Id == existingFoo. Id)); dc.Foos. InsertAllOnSubmit(foosToInsert); dc.SubmitChanges(); // use the next line if you plan on re-using existingFoos.

If that's the case I'd wrap dc.SubmitChanges() in a try-catch as well. ExistingFoos. AddRange(foosToInsert).

All you have to do is create a new instance of your class and then call InsertOnSumbit() on the table: var foo = new MyFoo { Name = "foo1" }; var dc = new MyDataContext(); dc.Foos. InsertOnSubmit(foo); dc.SubmitChanges(); The other thing you need to be sure of is how you're incrementing your ID column. In general, I always make sure to use the IDENTITY(1,1) setting on my ID columns.

This is declared on your LINQ entity's id column like so: Column(AutoSync = AutoSync. OnInsert, IsPrimaryKey = true, IsDbGenerated = true) public Int32 Id { get; set; } To avoid duplicates, what you really need is what we call in my shop an "append" functionality. IMHO, this is most easily accomplished with a stored procedure - we even have a template we use for it: USE GO CREATE PROCEDURE .

__append ( @id INT OUTPUT, @ ) AS BEGIN SELECT @id = id FROM . S (NOLOCK) WHERE = @ IF @id IS NULL BEGIN INSERT INTO . S () OUTPUT INSERTED.Id INTO @inserted_ids VALUES (@) SELECT TOP 1 @id = id FROM @inserted_ids; END ELSE BEGIN UPDATE .

S SET = @ WHERE id = @id END END GO It is possible to do it in linq though, just query for a list of existing IDs (or whatever column you're keying off of): var dc = new MyDataContext(); var existingFoos = dc.Foos.ToList(); var newFoos = new List(); foreach(var bar in whateverYoureIterating) { // logic to add to newFoos } var foosToInsert = newFoos. Where(newFoo =>!existingFoos. Any(existingFoo => newFoo.

Id == existingFoo. Id)); dc.Foos. InsertAllOnSubmit(foosToInsert); dc.SubmitChanges(); // use the next line if you plan on re-using existingFoos.

If that's the case I'd wrap dc.SubmitChanges() in a try-catch as well. ExistingFoos. AddRange(foosToInsert).

Waiting for your edit... – Jader Dias Apr 7 '09 at 13:39 The LINQ solution seems pretty slow. Querying a giant table a loading the contents in memory seems unfeasible for me. Maybe I should stick with the SP approach.

– Jader Dias Apr 7 '09 at 13:51 1 That's what I figured... Though, you could speed the LINQ solution up by only selecting the properties you really need... for example, just the ID column. Also, if you can re-use the "existing" query for multiple runs, that'll help as well. – Daniel Schaffer Apr 7 '09 at 14:35.

Unfortunately, there's no way around it as Linq to SQL does not check the database before it performs the insert. The only way to do this is to query the database first to determine if the duplicate record exists and then add the record if it does not. Ideally Linq to SQL would support the Ignore Duplicate Keys property on a SQL column.

But unfortunately it does not at the moment.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.