C# 更新实体列表的有效方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11421370/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Efficient way of updating list of entities
提问by Stefan Bossbaly
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?
上面的代码有效，但是正如您所看到的，我们需要为每个实体访问数据库两次（一次用于检索，另一次用于更新）。使用实体框架有没有更有效的方法来做到这一点？我注意到每次更新都会生成一个单独的 SQL 语句。有没有办法在循环完成后提交所有更新？
采纳答案by Erik Philips
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
.Attach() 不是先去数据库检索记录，然后将您的更改与它合并吗？所以无论如何你最终都会往返
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 Movie2and End Attach Movie2. We also verify that the documentation that states:
如果 attach 进行任何数据库调用，我们将在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.
评论
附加用于使用已知已存在于数据库中的实体重新填充上下文。
SaveChanges 因此不会尝试将附加实体插入到数据库中，因为它假定已经存在。
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).
附加movie2后，我们可以尝试从数据库中选择它。它不应该在那里（因为 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
======== 开始添加：movie1 ==============
========== 结束添加：movie1 ============
========== 开始选择第一部电影 ==============
在 1/15/2020 5:29:23 PM +00:00 打开连接
选择顶部 (1)
[c].[Id] AS [Id],
[c].[标题] AS [标题]
从 [dbo].[电影] AS [c]
-- 于 1/15/2020 5:29:23 PM +00:00 执行
-- 在 23 毫秒内完成，结果：SqlDataReader
2020 年 1 月 15 日下午 5:29:23 +00:00 关闭连接
========== 结束选择第一部电影 ============
========== 开始附加电影 2 ==============
======== 结束附加电影2 ==============
========== 开始选择电影2 ==============
在 1/15/2020 5:29:23 PM +00:00 打开连接
选择顶部 (1)
[Extent1].[Id] AS [Id],
[Extent1].[Title] AS [Title]
FROM [dbo].[电影] AS [Extent1]
WHERE 2 = [Extent1].[Id]
-- 于 1/15/2020 5:29:23 PM +00:00 执行
-- 在 2 毫秒内完成，结果：SqlDataReader
2020 年 1 月 15 日下午 5:29:23 +00:00 关闭连接
========== 结束选择电影2 ==============
Movie2a.Id = null
So no SQL called during the attach, no error message attaching it, and it's not in the database.
所以在附加过程中没有调用 SQL，没有附加它的错误消息，它不在数据库中。
回答by HatSoft
I am not sure whether the current version in beta or RC of Entity Framework supports something like batch update. But their is an extension for EF 4.3.1 on Nuget
我不确定 Entity Framework 的 beta 或 RC 中的当前版本是否支持批量更新之类的东西。但它们是 Nuget 上 EF 4.3.1 的扩展
http://nuget.org/packages/EntityFramework.Extended
http://nuget.org/packages/EntityFramework.Extended
Hope this might help you to achieve your requirement
希望这可以帮助您实现您的要求
回答by Slauma
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 Containscan be potentially slowif the list of viewModelsis very long. But it will only run a single query.
回答by Java SE
HatSoft already mentioned EntityFramework.Extended. Just look at following example based on extended framework.
HatSoft 已经提到了 EntityFramework.Extended。只需查看以下基于扩展框架的示例。
