Checking existance before inserting via Linq to SQL

Question

I'd like to know if there's an easier way to batch insert a set of records if they don't already exist in a table. For example I have a Tags table in the database having ID, Name columns - given a list of tag names I want to add only those that are not already present. Here's what I came up with:

private static void InsertTags(IEnumerable<string> tagNames)
{
    MyDataContext db = new MyDataContext();

    var tags = from tagName in tagNames
    where (db.Tags.Where(tag => tagName == tag.Name).FirstOrDefault() == null) 
    select new Tag
    {
        Name = tagName 
    };
    db.Tags.InsertAllOnSubmit(tags);
}

Is there a better approach?

Answer 1

I'm not sure but I actually think that is about optimal. The only way I can think to improve performance, if critical, would be to use an SP, an SP can be accessed through linq to sql.

Bear in mind the InsertAllOnSubmit command does not do a bulk update, just lots of separate updates. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2747627&SiteID=1

Answer 2

Note that you don't actually submit those changes, and that ideally you should be using the context to ensure it gets Dispose()d.

Re being optimal - there is a glitch in LINQ-to-SQL where-by the best way to get an individual record is via the Single(pred) or SingleOrDefault(pred) methods, since these methods use the identity manager for primary-key queries. Meaning: if you ask juts by the primary key, and the data-context already knows about the record, it won't hit the database. Most other queries do hit the database (including .Where(pred).Single()). So if the Name is the primary key, I Would tweak to:

db.SingleOrDefault(tag => tagName == tag.Name)

In this case it might not make much difference (since the context probably won't have seen it), but it is good to know about.