更新实体列表的有效方法 [英] 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);
}
上面的代码有效，但是如您所见，对于每个实体，我们需要两次访问数据库(一次检索，另一个进行更新).是否有使用实体框架执行此操作的更有效方法?我注意到每次更新都会生成一个单独的SQL语句.循环结束后，是否可以提交所有更新?
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?
推荐答案
以下是我知道的两种在不先检索实体的情况下更新数据库中实体的方法:
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();
}
应该产生:
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();
}
应该产生:
Update [schema].[table]
Set Name = @p__linq__0
Where id = @p__linq__1
.Attach()是否不先进入数据库以检索记录，然后将其与之合并?所以你还是要往返
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
不. 我们可以对此进行测试
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; }
}
}
如果attach进行了任何数据库调用，我们将在 Start Attach Movie2 和 End Attach Movie2 之间看到它们.我们还验证了说明以下内容的文档:
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:
备注
附加用于使用已知在数据库中已经存在的实体重新填充上下文.
Attach is used to repopulate a context with an entity that is known to already exist in the database.
SaveChanges因此将不会尝试将附加的实体插入数据库，因为假定该实体已经存在.
SaveChanges will therefore not attempt to insert an attached entity into the database because it is assumed to already be there.
在附加movie2之后，我们可以尝试从数据库中选择它.它不应该在那里(因为EF仅假定它在那里).
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).
==========开始添加:movie1 ==============
========= Start Add: movie1 ==============
==========结束添加:movie1 ============== p
========= END Add: movie1 ==============
==========开始选择第一部电影==============
========= Start SELECT FIRST movie ==============
在1/15/2020 5:29:23 PM +00:00打开连接
Opened connection at 1/15/2020 5:29:23 PM +00:00
选择顶部(1)
[c].[Id] AS [Id]，
[c].[Id] AS [Id],
[c].[标题] AS [标题]
[c].[Title] AS [Title]
FROM [dbo].[电影] AS [c]
FROM [dbo].[Movies] AS [c]
-在1/15/2020 5:29:23 PM +00:00执行
-- Executing at 1/15/2020 5:29:23 PM +00:00
-在23毫秒内完成，结果为:SqlDataReader
-- Completed in 23 ms with result: SqlDataReader
在1/15/2020 5:29:23 PM +00:00断开连接
Closed connection at 1/15/2020 5:29:23 PM +00:00
==========结束选择第一部电影==============
========= End SELECT FIRST movie ==============
==========开始附加Movie2 ==============
========= Start Attach Movie2 ==============
==========结束附上Movie2 ==============
========= End Attach Movie2 ==============
==========开始SELECT Movie2 ==============
========= Start SELECT Movie2 ==============
在1/15/2020 5:29:23 PM +00:00打开连接
Opened connection at 1/15/2020 5:29:23 PM +00:00
选择顶部(1)
[Extent1].[Id] AS [Id]，
[Extent1].[Id] AS [Id],
[Extent1].[标题] AS [标题]
[Extent1].[Title] AS [Title]
从FROM [dbo].[电影] AS [扩展1]
FROM [dbo].[Movies] AS [Extent1]
其中2 = [Extent1].[Id]
WHERE 2 = [Extent1].[Id]
-在1/15/2020 5:29:23 PM +00:00执行
-- Executing at 1/15/2020 5:29:23 PM +00:00
-在2毫秒内完成，结果为:SqlDataReader
-- Completed in 2 ms with result: SqlDataReader
在1/15/2020 5:29:23 PM +00:00断开连接
Closed connection at 1/15/2020 5:29:23 PM +00:00
==========结束SELECT Movie2 ==============
========= End SELECT Movie2 ==============
Movie2a.Id = null
Movie2a.Id = null
因此在附加过程中没有调用SQL，也没有附加错误消息，并且它不在数据库中.
So no SQL called during the attach, no error message attaching it, and it's not in the database.
这篇关于更新实体列表的有效方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
