在Entity Framework Core 6 C#中执行SQL批量更新以更新一个属性的方法

huangapple
go评论109阅读模式
英文:

How to perform SQL batch updates to update one property in Entity Framework Core 6 C#

问题

以下是您要的翻译：

  1. 我有以下代码，用于使用Entity Framework在Azure SQL表中执行更新操作
  3. public class DatabaseRepo
  4. {
  5.     private readonly UserContext _context;
  6.     
  7.     public DatabaseRepo(UserContext userContext)
  8.     {
  9.         _context = userContext;
  10.     }        
  12.     public async Task BatchUpdateUsersAsync(List<User> users)
  13.     {           
  14.         foreach (var user in users)
  15.         {
  16.             var entity = await _context.Users
  17.                                        .FirstOrDefaultAsync(item => item.Id == user.Id);
  18.             entity.StartDate = user.StartDate;
  20.             await _context.SaveChangesAsync();
  21.         }
  22.     }
  23. }
  25. 我只想更新所有这些用户的“StartDate”值。是否有更好的批量更新方法？
  27. 我正在使用EF Core 6
  29. 我尝试了这个：
  31. public async Task BatchUpdateUsersAsync(List<User> users)
  32. {
  33.         foreach (var user in users)
  34.         {                
  35.             var entry = _context.Set<User>().Add(user);
  36.             entry.State = EntityState.Modified;
  37.         }
  39.         await _context.SaveChangesAsync();
  40. }
  41. 但这会引发关于其他属性不能为空的错误。
英文:

I have the following code to perform an update in Azure SQL table using Entity Framework

  1. public class DatabaseRepo
  2. {
  3.     private readonly UserContext _context;
  4.     
  5.     public DatabaseRepo(UserContext userContext)
  6.     {
  7.         _context = userContext;
  8.     }        
  10.     public async Task BatchUpdateUsersAsync(List<User> users)
  11.     {           
  12.         foreach (var user in users)
  13.         {
  14. 	        var entity = await _context.Users
  15.                                        .FirstOrDefaultAsync(item => item.Id == user.Id);
  16. 	        entity.StartDate = user.StartDate;
  18. 	        await _context.SaveChangesAsync();
  19. 	    }
  20.     }
  21. }

All I want to do update StartDate value of all these users. Is there a better way to do batch update?

I'm using EF Core 6

I tried this:

  1. public async Task BatchUpdateUsersAsync(List<User> users)
  2. {
  3.         foreach (var user in users)
  4.         {                
  5.             var entry = _context.Set<User>().Add(user);
  6.             entry.State = EntityState.Modified;
  7.         }
  9.         await _context.SaveChangesAsync();
  10. }

but this throws an error about some other property can't be null.

答案1

得分: 3

EF Core 7引入了新的批量更新功能，包括更新和删除。
来自Microsoft的信息：ExecuteUpdate和ExecuteDelete

您可以使用以下代码：

  1. var dateTime = DateTime.Now;
  2. _context.Users
  3.        .Where(p => p.IsActive || p.Username == "DoNotUpdateMe")
  4.        .ExecuteUpdate(p => p.SetProperty(x => x.StartDate, x => dateTime));
  5. _context.SaveChanges();
英文:

EF Core 7 introduces new bulk update features, including update and delete.
Info from Microsoft: ExecuteUpdate and ExecuteDelete

You can use the following:

  1. var dateTime = DateTime.Now;
  2. _context.Users
  3.        .Where(p => p.IsActive || p.Username == "DoNotUpdateMe")
  4.        .ExecuteUpdate(p => p.SetProperty(x => x.StartDate, x => dateTime));
  5.       _context.SaveChanges();

答案2

得分: 0

  1. `Attach` 更新仅需要的列，这些列的 `IsModified` 值为 true，代码如下：
  3. public async Task BatchUpdateUsersAsync(List<User> users)
  4. {
  5.     foreach (var user in users)
  6.     {
  7.         _context.Set<User>().Attach(user);
  8.         _OSTContext.Entry(user).Property(x => x.StartDate).IsModified = true; // 仅更新 Startdate 列
  9.     }
  11.     await _context.SaveChangesAsync();
  12. }
  13. 这将适用于任何 Entity Framework 版本
英文:

Attach Updates only Required column's those are IsModified =true code will be as follows

  1.    public async Task BatchUpdateUsersAsync(List&lt;User&gt; users)
  2.     {
  3.         foreach (var user in users)
  4.         {
  5.             _context.Set&lt;User&gt;().Attach(user);
  6.             _OSTContext.Entry(user).Property(x =&gt; x.StartDate).IsModified = true; //updates only Startdate Column
  7.         }
  9.         await _context.SaveChangesAsync();
  10.     }

This will be use any Entity Frame work version

答案3

得分: -2

你可以使用Select方法进行批量更新，文档中的性能数值如下所示。

更多信息

  1. public class DatabaseRepo
  2. {
  3.     private readonly UserContext _context;
  4.     
  5.     public DatabaseRepo(UserContext userContext)
  6.     {
  7.         _context = userContext;
  8.     }        
  10.     public async Task BatchUpdateUsersAsync(List<User> users)
  11.     {           
  12.         var entities = users.Select(async (user) => await _context.Users.FirstOrDefaultAsync(item => item.Id == user.Id));
  13.         entities.ExecuteUpdate(p => p.SetProperty(u => u.StartDate, u => DateTime.Now));
  14.         await _context.SaveChangesAsync();
  15.     }
  16. }
英文:

You can use the Select method for batch updates, the performance values ​​in the documentation are shared below.

More Info

  1. public class DatabaseRepo
  2. {
  3.     private readonly UserContext _context;
  4.     
  5.     public DatabaseRepo(UserContext userContext)
  6.     {
  7.         _context = userContext;
  8.     }        
  10.     public async Task BatchUpdateUsersAsync(List&lt;User&gt; users)
  11.     {           
  12.         var entities = users.Select(async (user) =&gt; await _context.Users.FirstOrDefaultAsync(item =&gt; item.Id == user.Id));
  13.         entities.ExecuteUpdate(p =&gt; p.SetProperty(u =&gt; u.StartDate, u =&gt; DateTime.Now));
  14.         await _context.SaveChangesAsync();
  15.     }
  16. }

huangapple
  • 本文由 发表于 2023年7月11日 06:05:23
  • 转载请务必保留本文链接：https://go.coder-hub.com/76657620.html
  • c#
  • ef-core-6.0
  • entity-framework-core
  • sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定