I have a Tags Table. How to Bulk Insert using LINQ

问题:

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 )

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

Thanks in advance

回答1:

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

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

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.

---

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.