How to reload database with Entity Framework in C#-CodePudding

I am trying to do some checks on my database with an automated process. On a schedule the process goes out to a service and checks all the entries in the database against a list.

I want to re-insert the records that may have been deleted and update ones that are out of date

foreach (Category x in CustomeClass)
{
    Category exists = Context.SSActivewear_Category
                            .Where(b => b.CategoryID == x.CategoryID)
                            .FirstOrDefault();

    if (exists == null)
        Context.Add(x);
    else
        Context.Update(x);
 }

Not sure but I keep getting messages about tracking an instance with the same key etc. Can someone point me to a best practice on something like this

Danka!

CodePudding user response：

This type of error is common when re-using the same instance of EF dbContext, especially when trying to load the same entity more then once from database using the same context. If this is the case, then simply recreate the context (either new it up or use context factory) and then try update or modify your database data. Creating new context is cheap, so no worries there. After updating, save changes and dispose of the context (or use it in using statement to begin with).

If you are modifying the same entity multiple times using the same context, then do not load it from database multiple times.

In Your particular code example I would check if there is no duplication of Category objects in CustomeClass collection. Is there duplication of CategoryID? Is CategoryID for sure auto-generated when saving data (entity configuration)? Is code not trying to update multiple entities with same id? etc.

CodePudding user response：

Entity framework works with references. Two instances of a class with the same data amount to two different references and only one reference can be associated with a DbContext, otherwise you get errors like that.

As per your example below:

foreach (Category x in CustomeClass)
{
    Category exists = Context.SSActivewear_Category
                            .Where(b => b.CategoryID == x.CategoryID)
                            .FirstOrDefault();

    if (exists == null)
        Context.Add(x);
    else
        Context.Update(x);
 }

Category is assumed to be your Entity, so "CustomeClass" would be a collection of instances that are not associated with your Context. These are Detached instances.

When your "exists" comes back as #null, this will appear to work as the Category "x" gets added and tracked by the Context. However, when "exists" comes back as not #null, you now have two instances for the same entity. "exists" is tracked by the DbContext, while "x" is not. You cannot use Update() with "x", you must copy the values across.

The simplest way to do this would be Automapper where you can create a map from Category to Category, then use Map to copy all values from "x" over into "exists":

var config = new MapperConfiguration(cfg => cfg.CreateMap<Category, Category>());
var mapper = config.CreateMapper();
mapper.Map(x, exists);

This is purely an example, you'll probably want to configure and inject a mapper that handles your entity copying. You can configure the CreateMap to exclude columns that shouldn't ever change. (Using ForMember, etc.)

Alternatively you can copy the values across manually:

// ...
else
{
    exists.Name = x.Name,
    exists.SomeValue = x.SomeValue,
    // ...
}

In general you should avoid using the Update method in EF as this will result in a statement that overwrites all columns in a table, rather than updating just the column(s) that changes. (If no columns changed then no UPDATE SQL will actually get run)

On another side note, when getting "exists", you should use SingleOrDefault() not FirstOrDefault() as you expect 0 or 1 row back. First* methods should be used in cases where you expect there can be multiple matches but only want the first match, and should always be used with an OrderBy*() method to ensure the results are predictable.

You can use Update by performing an Exists check query that doesn't load a tracked entity. Examples would be:

Category exists = Context.SSActivewear_Category
    .AsNoTracking()
    .Where(b => b.CategoryID == x.CategoryID)
    .SingleOrDefault();

or better:

bool exists = Context.SSActivewear_Category
    .Where(b => b.CategoryID == x.CategoryID)
    .Any();

Then you could use Context.Update(x). AsNoTracking() tells EF to load an instance but not track it. This would really be a waste in this case as it's a round trip to the DB to return everything in the Category only to check if something was returned or not. The Any() call would be a round trip but just does an EXISTS db query to return true or false.

However, these are not fool-proof as there is no guarantee that the Context instance isn't already tracking an instance for that Category from some other operation. Something as trivial as having a Category appear in CustomeClass twice for any reason would be enough to trip the above examples up as once you call Context.Update(x) that instance is now tracked, so the loop iteration for the second instance would fail.