c# - 更新实体列表的有效方法

Question

我正在开发一个允许用户编辑实体列表的项目。我将这些实体映射到 View 模型并使用编辑器字段显示它们。当用户按下提交按钮时，我会遍历每个模型并像这样更新它:

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

上面的代码有效，但是如您所见，我们需要为每个实体访问数据库两次(一次用于检索，另一次用于更新)。使用 Entity Framework 是否有更有效的方法？我注意到每次更新都会生成一个单独的 SQL 语句。有没有办法在循环结束后提交所有更新？

Answer 1

以下是我知道的两种无需先检索实体即可更新数据库中实体的方法:

//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();
}

应该产生:

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

或者您可以在需要时指定字段(可能适用于具有大量列的表，或者出于安全目的，只允许更新特定列:

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

应该产生:

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

没有。 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; }
    }
}

如果附加进行任何数据库调用，我们将在Start Attach Movie2 和End Attach Movie2 之间看到它们。我们还验证了说明以下内容的文档:

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.

附加电影 2 后，我们可以尝试从数据库中选择它。它不应该在那里(因为 EF 只假设它在那里)。

========= 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

因此在附加期间没有调用 SQL，没有附加错误消息，并且它不在数据库中。