< BACK TO BLOG

Delete batch using Entity Framework Core

πŸ—“οΈ2022-01-08

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 문을 κ³ λ €ν•  ν•„μš”κ°€ μžˆμ„ 것 κ°™μŠ΅λ‹ˆλ‹€.

만료된 토큰 정리, 둜그 정리 등에 μ‚¬μš©ν•˜λ©΄ 쒋을 κ²ƒμœΌλ‘œ μƒκ°λ©λ‹ˆλ‹€.

GitHub Repository


Profile picture

Pon Cheol Ku (ꡬ본철)

Software developer

Other sites

If does not find interesting topic, you might visit other site on below link.

Β© 2024, Built with Gatsby