bulk insert and update with ADO.NET Entity Framework

Question

I am writing a small application that does a lot of feed processing. I want to use LINQ EF for this as speed is not an issue, it is a single user app and, in the end, will only be used once a month.

My questions revolves around the best way to do bulk inserts using LINQ EF.

After parsing the incoming data stream I end up with a List of values. Since the end user may end up trying to import some duplicate data I would like to "clean" the data during insert rather than reading all the records, doing a for loop, rejecting records, then finally importing the remainder.

This is what I am currently doing:

DateTime minDate = dataTransferObject.Min(c => c.DoorOpen);
DateTime maxDate = dataTransferObject.Max(c => c.DoorOpen);

using (LabUseEntities myEntities = new LabUseEntities())
{
    var recCheck = myEntities.ImportDoorAccess.Where(a => a.DoorOpen >= minDate && a.DoorOpen <= maxDate).ToList();
    if (recCheck.Count > 0)
    {
        foreach (ImportDoorAccess ida in recCheck)
        {
            DoorAudit da = dataTransferObject.Where(a => a.DoorOpen == ida.DoorOpen && a.CardNumber == ida.CardNumber).First();
            if (da != null)
                da.DoInsert = false;
        }
    }

    ImportDoorAccess newIDA;
    foreach (DoorAudit newDoorAudit in dataTransferObject)
    {
        if (newDoorAudit.DoInsert)
        {
            newIDA = new ImportDoorAccess
            {
                CardNumber = newDoorAudit.CardNumber,
                Door = newDoorAudit.Door,
                DoorOpen = newDoorAudit.DoorOpen,
                Imported = newDoorAudit.Imported,
                RawData = newDoorAudit.RawData,
                UserName = newDoorAudit.UserName
            };
            myEntities.AddToImportDoorAccess(newIDA);
        }
    }
    myEntities.SaveChanges();
}

I am also getting this error:

System.Data.UpdateException was unhandled
Message="Unable to update the EntitySet 'ImportDoorAccess' because it has a DefiningQuery and no element exists in the element to support the current operation."
Source="System.Data.SqlServerCe.Entity"

What am I doing wrong?

Any pointers are welcome.

Dave Swersky · Accepted Answer · 2010-04-06 19:15:06Z

2

You can do multiple inserts this way.

I've seen the exception you're getting in cases where the model (EDMX) is not set up correctly. You either don't have a primary key (EntityKey in EF terms) on that table, or the designer has tried to guess what the EntityKey should be. In the latter case, you'll see two or more properties in the EDM Designer with keys next to them.

Make sure the ImportDoorAccess table has a single primary key and refresh the model.

answered Apr 6, 2010 at 19:15

Dave Swersky

34.8k9 gold badges83 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Keith Barrows Over a year ago

Works like a champ! Since these were import tables I had not put a PK on them. Now that I have, and refreshed my model, no more error! Thanks.

Collectives™ on Stack Overflow

bulk insert and update with ADO.NET Entity Framework

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related