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?
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.
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.
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.
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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With