Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Efficient way of updating list of entities

I am working on a project which allows the user to edit a list of entities. I map these entities to view models and display them with editor fields. When the user presses the submit button, I go through each model and update it like so:

foreach (var viewModel in viewModels) {     //Find the database model and set the value and update     var entity = unit.EntityRepository.GetByID(fieldModel.ID);     entity.Value = viewModel.Value;     unit.EntityRepository.Update(entity); } 

The above code works, however as you can see we need to hit the database twice for every entity (once to retrieve and another to update). Is there a more efficient way of doing this using Entity Framework? I noticed that each update generates a separate SQL statement. Is there a way of committing all the updates after the loop has finished?

like image 658
Stefan Bossbaly Avatar asked Jul 10 '12 20:07

Stefan Bossbaly


People also ask

How do you update an entity?

How to Update a new record in to a existing entity using update entity action? First you need to check the id if id exist then you can use the update entity action and if id is new then you can used the create entity action.

How do I refresh entity model?

Right click on the designer surface of the EDMX designer and click Update Model From Database... All entities are refreshed by default, new entities are only added if you select them. EDIT: If it is not refreshing well. Select all the tables and view-s in the EDMX designer.

How do you update related entities in Entity Framework Core?

This can be achieved in several ways: setting the EntityState for the entity explicitly; using the DbContext. Update method (which is new in EF Core); using the DbContext. Attach method and then "walking the object graph" to set the state of individual properties within the graph explicitly.

What difference does AsNoTracking () make?

AsNoTracking(IQueryable)Returns a new query where the entities returned will not be cached in the DbContext or ObjectContext. This method works by calling the AsNoTracking method of the underlying query object.


2 Answers

Here are two ways I know of to update an entity in the database without doing a retrieval of the entity first:

//Assuming person is detached from the context //for both examples public class Person {   public int Id { get; set; }   public string Name { get; set; }   public DateTime BornOn { get; set; }    }  public void UpdatePerson(Person person) {   this.Context.Persons.Attach(person)   DbEntityEntry<Person> entry = Context.Entry(person);   entry.State = System.Data.EntityState.Modified;   Context.SaveChanges(); } 

Should yield:

Update [schema].[table] Set Name = @p__linq__0, BornOn = @p__linq__1 Where id = @p__linq__2 

Or you can just specify fields if you need to (probably good for tables with a ton of columns, or for security purposes, allows only specific columns to be updated:

public void UpdatePersonNameOnly(Person person) {   this.Context.Persons.Attach(person)   DbEntityEntry<Person> entry = Context.Entry(person);   entry.Property(e => e.Name).IsModified = true;   Context.SaveChanges(); } 

Should yield:

Update [schema].[table] Set Name = @p__linq__0 Where id = @p__linq__1 

Doesn't the .Attach() go to the database to retrieve the record first and then merges your changes with it ? so you end up with roundtrip anyway

No. We can test this

using System; using System.Data.Entity; using System.Linq; using System.Collections.Generic; using System.ComponentModel.DataAnnotations.Schema; using System.ComponentModel.DataAnnotations;  public class Program {     public static void Main()     {          var movie1 = new Movie { Id = 1, Title = "Godzilla" };         var movie2 = new Movie { Id = 2, Title = "Iron Man" };         using (var context = new MovieDb())         {             /*             context.Database.Log = (s) => {                 Console.WriteLine(s);             };             */              Console.WriteLine("========= Start Add: movie1 ==============");             context.Movies.Add(movie1);             context.SaveChanges();             Console.WriteLine("========= END Add: movie1 ==============");              // LET EF CREATE ALL THE SCHEMAS AND STUFF THEN WE CAN TEST              context.Database.Log = (s) => {                 Console.WriteLine(s);             };              Console.WriteLine("========= Start SELECT FIRST movie ==============");             var movie1a = context.Movies.First();             Console.WriteLine("========= End SELECT FIRST movie ==============");              Console.WriteLine("========= Start Attach Movie2 ==============");             context.Movies.Attach(movie2);             Console.WriteLine("========= End Attach Movie2 ==============");              Console.WriteLine("========= Start SELECT Movie2 ==============");             var movie2a = context.Movies.FirstOrDefault(m => m.Id == 2);             Console.WriteLine("========= End SELECT Movie2 ==============");             Console.Write("Movie2a.Id = ");             Console.WriteLine(movie2a == null ? "null" : movie2a.Id.ToString());         }     }      public class MovieDb : DbContext     {         public MovieDb() : base(FiddleHelper.GetConnectionStringSqlServer()) {}         public DbSet<Movie> Movies { get; set; }     }      public class Movie     {         [Key]         [DatabaseGenerated(DatabaseGeneratedOption.None)]         public int Id { get; set; }          public string Title { get; set; }     } } 

If attach makes any DB calls, we will see them between the Start Attach Movie2 and End Attach Movie2. We also verify that the documentation that states:

Remarks

Attach is used to repopulate a context with an entity that is known to already exist in the database.

SaveChanges will therefore not attempt to insert an attached entity into the database because it is assumed to already be there.

After attaching the movie2, we can attempt to select it from the DB. It should not be there (because EF only assumes it is there).

========= Start Add: movie1 ==============

========= END Add: movie1 ==============

========= Start SELECT FIRST movie ==============

Opened connection at 1/15/2020 5:29:23 PM +00:00

SELECT TOP (1)

[c].[Id] AS [Id],

[c].[Title] AS [Title]

FROM [dbo].[Movies] AS [c]

-- Executing at 1/15/2020 5:29:23 PM +00:00

-- Completed in 23 ms with result: SqlDataReader

Closed connection at 1/15/2020 5:29:23 PM +00:00

========= End SELECT FIRST movie ==============

========= Start Attach Movie2 ==============

========= End Attach Movie2 ==============

========= Start SELECT Movie2 ==============

Opened connection at 1/15/2020 5:29:23 PM +00:00

SELECT TOP (1)

[Extent1].[Id] AS [Id],

[Extent1].[Title] AS [Title]

FROM [dbo].[Movies] AS [Extent1]

WHERE 2 = [Extent1].[Id]

-- Executing at 1/15/2020 5:29:23 PM +00:00

-- Completed in 2 ms with result: SqlDataReader

Closed connection at 1/15/2020 5:29:23 PM +00:00

========= End SELECT Movie2 ==============

Movie2a.Id = null

So no SQL called during the attach, no error message attaching it, and it's not in the database.

like image 73
Erik Philips Avatar answered Sep 22 '22 13:09

Erik Philips


You can try the follwoing to minimize queries:

using (var ctx = new MyContext()) {     var entityDict = ctx.Entities         .Where(e => viewModels.Select(v => v.ID).Contains(e.ID))         .ToDictionary(e => e.ID); // one DB query      foreach (var viewModel in viewModels)     {         Entity entity;         if (entityDict.TryGetValue(viewModel.ID, out entity))             entity.Value = viewModel.Value;     }      ctx.SaveChanges(); //single transaction with multiple UPDATE statements } 

Be aware that Contains can be potentially slow if the list of viewModels is very long. But it will only run a single query.

like image 20
Slauma Avatar answered Sep 26 '22 13:09

Slauma