我有一个标签表.如何使用LINQ批量插入? [英] I have a Tags Table. How to Bulk Insert using LINQ?

问题描述

我正在将VB.NET与LINQ一起用于MS SQL. 我有以下两个表.现在，我想使用LINQ在标签表"中插入多个项目，还想检查标签"表中是否存在任何标签.它不会再次插入它，并且在两种情况下(如果插入或发现存在)都带TagID

I am using VB.NET with LINQ to MS SQL. I have two following tables. Now I want to insert multiple items in Tags Table using LINQ, but also wants to check that if any of tag exists in Tags table. It doesn't insert it again and bring the TagID in both cases (if inserted or if found existed)

CREATE TABLE Tags
    (TagID bigint not null , 
    Tag varchar(100) NULL )

CREATE TABLE VideoTags
    (VideoID bigint not null , 
    TagID bigint not null )

使用LINQ实现这一目标的最佳方法是什么?

What's the best way to acheive this using LINQ?

预先感谢

推荐答案

LINQ是一项查询技术，但我想我们知道您的意思；无论是LINQ-to-SQL还是Entity Framework，您可能都需要更具体一些.您可能还想弄清楚批量"在您的情况下的含义...对于10-100条记录，您可能对10,000条记录使用不同的答案(其中SqlBulkCopy到暂存表中，并在数据库中导入存储过程将最好).

LINQ is a query technology, but I think we know what you mean; you might want to be a bit more specific whether this is LINQ-to-SQL or Entity Framework. You might also want to clarify what "bulk" means in your case... for 10-100 records you might use a different answer to 10,000 records (where SqlBulkCopy into a staging table and a stored procedure to import at the db would be the best idea).

对于相对较低的数字-只需使用您的ORM工具查找记录-例如使用LINQ-to-SQL(可能具有跨序列化事务)-并使用C#进行说明(已更新以显示循环和缓存):

For a relatively low number - just use your ORM tool to find the records - for example with LINQ-to-SQL (perhaps with a spanning serializable transaction) - and using C# for illustration (updated to show loop and cache):

Dictionary<string,Tag> knownTags = new Dictionary<string,Tag>();
foreach(... your data ...) {
    Tag tag;
    if(!knownTags.TryGetValue(tagName, out tag)) {
        tag = ctx.Tags.SingleOrDefault(t => t.Name == tagName);
        if(tag == null) {
            tag = new Tag { Name = tagName };
            ctx.Tags.InsertOnSubmit(tag);
        }
        knownTags.Add(tagName, tag);
    }
    // insert video tag
}
ctx.SubmitChanges();

实际上，出于性能原因，我想知道这是否可能是自然键有意义的场合之一，即使用Tag(varchar)作为主键，并复制它(作为外键) )在VideoTags中-那么您无需一直连接到Tags表.

Actually, for performance reasons I wonder whether this might be one of those occasions where a natural-key makes sense - i.e. use Tag (the varchar) as the primary key, and duplicate it (as a foreign key) in VideoTags - then you don't need to join to the Tags table all the time.

如果数字更大，则使用SqlBulkCopy非常容易；只需将数据放入DataTable并将其推入，然后在TSQL中完成工作即可.

If the numbers are larger, it is pretty easy to use SqlBulkCopy; just put the data into a DataTable and push it over, then do the work in TSQL.

这篇关于我有一个标签表.如何使用LINQ批量插入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！