Efficient way of updating list of entities

asp.net-mvc c# entity entity-framework sql

Question

In the project I'm working on, a user may change a list of entities. These entities are mapped to view models and are shown with editor fields. When the user clicks the submit button, I update each model individually as follows:

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 aforementioned code works, however as you can see, we must access the database twice for each entity (once to retrieve and another to update). Is there an Entity Framework solution that does this task more effectively? I discovered that every modification results in a unique SQL query. Is there a way to commit every change once the loop is over?

1
28
5/23/2016 1:02:13 PM

Accepted Answer

Here are the two methods I am aware of for updating a database object without retrieving it 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 give way:

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

Alternatively, if you just need to define a few fields (usually useful for tables with a lot of columns or for security reasons, allowing only certain 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 give way:

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

False. Zzz-31-Zzz

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; }
    }
}

We will detect any database calls made by attach between the begin attaching movie 2 and Finalize Attach Movie2. Additionally, we confirm the paperwork 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.

We might try to choose the movie2 from the DB after connecting it. It ought not to 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

Therefore, no SQL was performed during the attach, there was no error message attached, and it was not stored in the database.

21
1/15/2020 5:47:02 PM

Popular Answer

The following may be used to reduce 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
}

Being mindful of the list ofviewModels is rather lengthy. However, it will only execute one query.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow