如何使用Linq-to-SQL只插入新记录?

I have to insert some data periodically in my SQL Server database. But the feeds where I read the data repeats some data that was inserted before. When I use Linq-to-SQL to insert into the DB either some data is duplicated, or a primary key violation exception is raised, depending on the primary key.

我必须定期在SQL Server数据库中插入一些数据。但是我读取数据的提要重复了之前插入的一些数据。当我使用linqto - sql插入数据库时，有些数据是重复的，或者是主键违反异常被提高，这取决于主键。

How to insert the data without duplications and without exceptions? I don't want to avoid the exception with a try-catch, because once the exception is raised the rest of the data isn't inserted.

如何插入没有重复的数据，没有例外?我不想用try-catch来避免异常，因为一旦异常被引发，其余的数据就不会被插入。

update I also found my own solution: I wrote a duplicated entries deletion stored procedure, which is run right after the InsertAllOnSubmit + SubmitChanges

我还找到了自己的解决方案:我编写了一个重复的条目删除存储过程，它在InsertAllOnSubmit + SubmitChanges之后运行

2 个解决方案

#1

All you have to do is create a new instance of your class and then call InsertOnSumbit() on the table:

您只需创建一个类的新实例，然后调用表上的InsertOnSumbit():

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:

你需要确定的另一件事是你如何增加你的ID列。通常，我总是确保在ID列上使用标识(1,1)设置。在LINQ实体的id列上声明如下:

[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:

为了避免重复，你真正需要的是我们在我的商店中所称的“附加”功能。IMHO，这是通过存储过程最容易实现的——我们甚至有一个用于存储过程的模板:

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

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):

在linq中也可以这样做，只需查询现有id的列表(或您要键控的任何列):

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's the case I'd wrap  dc.SubmitChanges() in a try-catch as well.
existingFoos.AddRange(foosToInsert);

#2

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.

不幸的是，没有办法绕过它，就像Linq to SQL在执行插入之前不检查数据库一样。这样做的惟一方法是首先查询数据库，以确定是否存在重复记录，然后在不存在重复记录的情况下添加记录。

Ideally Linq to SQL would support the Ignore Duplicate Keys property on a SQL column. But unfortunately it does not at the moment.

理想情况下，Linq to SQL将支持在SQL列上忽略重复键属性。但不幸的是，目前情况并非如此。

#1