Bulk inserts and duplicate records with LINQ to SQL

Question

Is there a "best practice" way of handling bulk inserts (via LINQ) but discard records that may already be in the table? Or I am going to have to either do a bulk insert into an import table then delete duplicates, or insert one record at a time?

---

08/26/2010 - EDIT #1:
I am looking at the Intersect and Except methods right now. I am gathering up data from separate sources, converting into a List, want to "compare" to the target DB then INSERT just the NEW records.

List<DTO.GatherACH> allACHes = new List<DTO.GatherACH>();
State.IState myState = null;
State.Factory factory = State.Factory.Instance;
foreach (DTO.Rule rule in Helpers.Config.Rules)
{
    myState = factory.CreateState(rule.StateName);
    List<DTO.GatherACH> stateACHes = myState.GatherACH();
    allACHes.AddRange(stateACHes);
}

List<Model.ACH> newRecords = new List<Model.ACH>();  // Create a disconnected "record set"...
foreach (DTO.GatherACH record in allACHes)
{
        var storeInfo = dbZach.StoreInfoes.Where(a => a.StoreCode == record.StoreCode && (a.TypeID == 2 || a.TypeID == 4)).FirstOrDefault();

        Model.ACH insertACH = new Model.ACH
        {
            StoreInfoID = storeInfo.ID,
            SourceDatabaseID = (byte)sourceDB.ID,
            LoanID = (long)record.LoanID,
            PaymentID = (long)record.PaymentID,
            LastName = record.LastName,
            FirstName = record.FirstName,
            MICR = record.MICR,
            Amount = (decimal)record.Amount,
            CheckDate = record.CheckDate
        };
        newRecords.Add(insertACH);
}

The above code builds the newRecords list. Now, I am trying to get the records from this List that are not in the DB by comparing on the 3 field Unique Index:

AchExceptComparer myComparer = new AchExceptComparer();
var validRecords = dbZach.ACHes.Intersect(newRecords, myComparer).ToList();

The comparer looks like:

class AchExceptComparer : IEqualityComparer<Model.ACH>
{
    public bool Equals(Model.ACH x, Model.ACH y)
    {
        return (x.LoanID == y.LoanID && x.PaymentID == y.PaymentID && x.SourceDatabaseID == y.SourceDatabaseID);
    }

    public int GetHashCode(Model.ACH obj)
    {
        return base.GetHashCode();
    }
}

However, I am getting this error:

LINQ to Entities does not recognize the method 'System.Linq.IQueryable1[MisterMoney.LARS.ZACH.Model.ACH] Intersect[ACH](System.Linq.IQueryable1[MisterMoney.LARS.ZACH.Model.ACH], System.Collections.Generic.IEnumerable1[MisterMoney.LARS.ZACH.Model.ACH], System.Collections.Generic.IEqualityComparer1[MisterMoney.LARS.ZACH.Model.ACH])' method, and this method cannot be translated into a store expression.

Any ideas? And yes, this is completely inline with the original question. :)

Answer 1

You can't do bulk inserts with LINQ to SQL (I presume you were referring to LINQ to SQL when you said "LINQ"). However, based on what you're describing, I'd recommend checking out the new MERGE operator of SQL Server 2008. Inserting, Updating, and Deleting Data by Using MERGE

Another example here.

Answer 2

One thing to watch out for is if you queue more than 2000 or so records without calling SubmitChanges() - TSQL has a limit on the number of statements per execution, so you cannot simply queue up every record and then call SubmitChanges() as this will throw an SqlException, you need to periodically clear the queue to avoid this.

Answer 3

I recommend you just write the SQL yourself to do the inserting, I find it is a lot faster and you can get it to work exactly how you want it to. When I did something similar to this (just a one-off program) I just used a Dictionary to hold the ID's I had inserted already, to avoid duplicates.

I find LINQ to SQL is good for one record or a small set that does its entire lifespan in the LINQ to SQL.

Or you can try to use SQL Server 2008's Bulk Insert .