Geek Answers Handbook

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

I need to periodically insert some data into a SQL Server database. But the channels where I read the data repeat some of the data that was inserted before. When I use Linq-to-SQL to insert into the database, either some data is duplicated, or a primary key violation is thrown depending on the primary key.

How to insert data without duplication and without exceptions? I do not want to avoid an exception with try-catch, because as soon as the exception is raised, the rest of the data will not be inserted.

update I also found my own solution: I wrote a stored procedure for deleting duplicate records, which starts immediately after InsertAllOnSubmit + SubmitChanges

+5
source share
2 answers

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();

Another thing you need to make sure of is how you grow your identity column. In general, I always try to use the IDENTITY (1,1) parameter in my identity columns. This is declared in the identifier column of the LINQ object as follows:

[Column(AutoSync = AutoSync.OnInsert, IsPrimaryKey = true, IsDbGenerated = true)]
public Int32 Id { get; set; }

To avoid duplicates, what you really need is what we call add functionality in my store. IMHO, this is easiest to accomplish using a stored procedure - we even have a template that we use for it:

USE [<Database_Name, sysobject, Database_Name>]
GO

CREATE PROCEDURE [<Schema, sysobject, dbo>].[<Table_Name, sysobject, Table_Name>__append]
(
    @id INT OUTPUT,
    @<Key_Param, sysobject, Key_Param> <Key_Param_Type, sysobject, VARCHAR(50)>
)
AS
BEGIN

        SELECT @id = [id] FROM [<Schema, sysobject, dbo>].[<Table_Name, sysobject, Table_Name>s] (NOLOCK) WHERE [<Key_Param, sysobject, Key_Param>] = @<Key_Param, sysobject, Key_Param>

IF @id IS NULL  
BEGIN       
    INSERT INTO [<Schema, sysobject, dbo>].[<Table_Name, sysobject, Table_Name>s] ([<Key_Param, sysobject, Key_Param>]) 
    OUTPUT INSERTED.[id] INTO @inserted_ids
    VALUES (@<Key_Param, sysobject, Key_Param>)

    SELECT TOP 1 @id = [id] FROM @inserted_ids;
END
ELSE
BEGIN
    UPDATE [<Schema, sysobject, dbo>].[<Table_Name, sysobject, Table_Name>s]
    SET
        [<Key_Param, sysobject, Key_Param>] = @<Key_Param, sysobject, Key_Param>
    WHERE [id] = @id
END
END
GO

linq, ( , ):

var dc = new MyDataContext();
var existingFoos = dc.Foos.ToList();
var newFoos = new List<Foo>();
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 the case I'd wrap  dc.SubmitChanges() in a try-catch as well.
existingFoos.AddRange(foosToInsert);
+6

, , Linq to SQL , . - , , , , .

Linq to SQL Ignore Duplicate Keys SQL. , , .

+1

More articles:


All Articles