Entity Framework Core: Bulk updates λ₯Ό μ½κ³ , μ΄λ μ λ μ±λ₯ν₯μμ΄ μμκΉ κΆκΈν΄μ κ°λ΅νκ² μ½λλ₯Ό μμ±ν΄μ μ€ν μκ°μ μΈ‘μ νμ΅λλ€.
νλ‘μ νΈ μ€λͺ
νκ²½
- Visual Studio 2022 (v17.0.4)
- Localdb (appsettings.json μμ μ°κ²°λ¬Έμμ΄μ νμΈνμΈμ.)
- .NET 6
- EntityFrameworkCore v6.0.1
λ§μ΄κ·Έλ μ΄μ
μν°ν°λ₯Ό λ³κ²½ν΄μ νμΈνλ €λ©΄ μλ λͺ λ Ήμ μ°Έμ‘°ν΄μ λ§μ΄κ·Έλ μ΄μ μ½λλ₯Ό μΆκ°ν΄μΌ ν©λλ€.
# DataContext κ° ν¬ν¨λ νλ‘μ νΈ λλ ν°λ¦¬λ‘ μ΄λν©λλ€.
$ cd src/Sample.Data
# λ§μ΄κ·Έλ μ΄μ
μ½λλ₯Ό μμ±ν©λλ€.
$ dotnet ef migrations add "Initialize database" --context AppDbContext --startup-project ../Sample.App --project ../Sample.Data.SqlServer --json
νμΈ
ν μΆκ° (#1-1, #1-2)
-- Declaring variables
DECLARE @inserted0 TABLE ([Id] bigint, [_Position] [int]);
MERGE [UserToken] USING (
VALUES (@p0, @p1, @p2, @p3, 0),
(@p4, @p5, @p6, @p7, 1),
(@p8, @p9, @p10, @p11, 2),
-- μλ΅
(@p160, @p161, @p162, @p163, 40),
(@p164, @p165, @p166, @p167, 41)) AS i ([CreatedAt], [ExpiresAt], [Purpose], [Token], _Position) ON 1=0
WHEN NOT MATCHED THEN
INSERT ([CreatedAt], [ExpiresAt], [Purpose], [Token])
VALUES (i.[CreatedAt], i.[ExpiresAt], i.[Purpose], i.[Token])
OUTPUT INSERTED.[Id], i._Position
INTO @inserted0;
ν μμ (#2)
DELETE λ¬Έμ΄ κ° νλ³λ‘ μμ±λμ΄ μ€νλ©λλ€.
1,000 νμ λμμΌλ‘ μ½ 507 λ°λ¦¬μ΄κ° μμλμμ΅λλ€.
var deleteCandidate = Context.UserTokens.Where(x => x.ExpiresAt <= DateTime.UtcNow);
Context.UserTokens.RemoveRange(deleteCandidate);
await Context.SaveChangesAsync();
-- Declaring variables
DELETE FROM [UserToken]
WHERE [Id] = @p0;
SELECT @@ROWCOUNT;
DELETE FROM [UserToken]
WHERE [Id] = @p1;
SELECT @@ROWCOUNT;
DELETE FROM [UserToken]
WHERE [Id] = @p2;
SELECT @@ROWCOUNT;
-- μλ΅
ν μμ (#4)
SQL λ¬ΈμΌλ‘ μμ λͺ λ Ήμ μ μνκ³ , μ€νν©λλ€.
ANSI sql λ¬ΈμΌλ‘ μμ±νλ©΄ DBMS μμ‘΄μ μ€μΌ μ μλ€κ³ μκ°ν©λλ€.
1,000 νμ λμμΌλ‘ μ½ 17 λ°λ¦¬μ΄κ° μμλμμ΅λλ€.
var sql = string.Empty;
sql += $"DELETE FROM {nameof(UserToken)} ";
sql += $" WHERE {nameof(UserToken.ExpiresAt)} <= GetUtcDate()";
await Context.Database.ExecuteSqlRawAsync(sql);
SQL λ¬Έ μ€νμ νΈλμμ νμΈ (#3)
SQL λ¬ΈμΌλ‘ λͺ λ Ήμ μ€νν λ, νΈλμμ μ νμΈν©λλ€.
SQL λ¬Έμ κ²°κ³Όκ° μ»€λ°λμ§ μκ³ μ μμ μΌλ‘ λ‘€λ°±λ¨μ νμΈνμ΅λλ€.
using (var transaction = Context.Database.BeginTransaction())
{
try
{
await Context.Database.ExecuteSqlRawAsync(sql);
throw new Exception("Test");
await transaction.CommitAsync();
}
catch (Exception)
{
await transaction.RollbackAsync();
}
}
λ§μΉ¨
μΌκ΄ μμ μΌλ‘ λ³κ²½, μμ μμ μ μ€νν λ, μ±λ₯ν₯μμ μν΄ SQL λ¬Έμ κ³ λ €ν νμκ° μμ κ² κ°μ΅λλ€.
λ§λ£λ ν ν° μ 리, λ‘κ·Έ μ 리 λ±μ μ¬μ©νλ©΄ μ’μ κ²μΌλ‘ μκ°λ©λλ€.