EF Core 6 - 根据条件添加记录

Question

朋友们，

在我的 Blazor 服务器应用程序中，我将 BulkPurchaseRequest 记录插入到 DB 中，如下所示。

public class BulkPurchaseRequestsRepository : IBulkPurchaseRequestsRepository
    {
        private readonly OyunPalasContext _oyunPalasContext;

        public BulkPurchaseRequestsRepository(OyunPalasContext oyunPalasContext)
        {
            _oyunPalasContext = oyunPalasContext;
        }

        public async Task AddBulkRequestAsync(BulkPurchaseRequest bulkPurchaseRequest)
        {
            await _oyunPalasContext.BulkPurchaseRequests.AddAsync(bulkPurchaseRequest);
            await _oyunPalasContext.SaveChangesAsync();
        }
        public async Task<IEnumerable<BulkPurchaseRequest?>> GetBulkPurchaseRequestAsync()
        {
            var result = await _oyunPalasContext.BulkPurchaseRequests.ToListAsync();
            return result;
        }
    }

下面是 BulkPurchaseRequest 实体;

public partial class BulkPurchaseRequest
    {
        public int Id { get; set; }
        public int TotalAmount { get; set; }
        public string ProductCode { get; set; } = null!;
        public string? Description { get; set; }
        public string? Email { get; set; } 
        public int? Status { get; set; } = 0;
        public DateTime? RequestDateTime { get; set; } = DateTime.Now;
        public List<BulkPurchase> BulkPurchases { get; set; }
    }

下面是 BulkPurchase 实体;

public partial class BulkPurchase
    {
        public int Id { get; set; }
        public int BulkId { get; set; }
        public int Amount { get; set; }
        public string ProductCode { get; set; } = null!;
        public int? Status { get; set; }
        public DateTime? PurchaseDateTime { get; set; }
        public BulkPurchaseRequest BulkPurchaseRequest { get; set; }
    }

我也想在 BulkPurchase 中插入记录。如果 BulkPurchaseRequest 中的 TotalAmount= 1000，则 BulkPurchase 中将插入 10 条记录。（总金额/100）

Id	TotalAmount	ProductCode	Description	Email	Status	RequestDateTime
1	1000	0187209	0187209	test@gmail.com	0	2023-01-04 21:53:43.677

Id	BulkID	Amount	ProductCode	Status	PurchaseDateTime
1	1	100	0187209	0	2023-01-04 20:41:00.000
2	1	100	0187209	0	2023-01-25 15:43:00.000
3	1	100	0187209	0	2023-01-25 15:47:00.000
4	1	100	0187209	0	2023-01-25 15:50:00.000
5	1	100	0187209	0	2023-01-25 16:46:00.000
6	1	100	0187209	0	2023-01-25 17:01:00.000
7	1	100	0187209	0	2023-01-25 17:13:00.000
8	1	100	0187209	0	2023-01-25 16:46:00.000
9	1	100	0187209	0	2023-01-25 17:01:00.000
10	1	100	0187209	0	2023-01-25 17:13:00.000

如何在 EF Core 中实现此功能？

Note：此问题总结整理于: EF Core 6 - Adding records based on a condition

Answer 1

在 AddBulkRequestAsync 方法中，插入新的 bulkPurchaseRequest 后，可以查询 BulkPurchaseRequests 表以获取总计数，然后使用 remainder 运算符 （“%”） 检查计数，然后根据结果将记录插入到 BulkPurchase 表中。

代码如下：

复制

        public async Task AddBulkRequestAsync(BulkPurchaseRequest bulkPurchaseRequest)
        {
            await _oyunPalasContext.BulkPurchaseRequests.AddAsync(bulkPurchaseRequest);
            await _oyunPalasContext.SaveChangesAsync();
            //get the total count of the BulkPurchaseRequests
            int totalcount = _oyunPalasContext.BulkPurchaseRequests.Count();
            if(totoalcount % 100 == 0){
                  //insert record into the BulkPurchase table.
            }
        }

此外，您使用的是哪种数据库？是否支持 Trigger？如果它支持 SQL Server 数据库等触发器，则可以尝试在数据库中使用带有条件的触发器。

更新：

要使用触发器，

复制

--create the trigger
Create Trigger [dbo].[bprinserttrigger]
on [dbo].[BulkPurchaseRequests]
after insert
AS
Begin
	set nocount on;
	Declare @totalcount int
	select @totalcount = COUNT(*) from BulkPurchaseRequests
	if @totalcount % 10 = 0
	begin
        //get the first record from the BulkPurchaseRequests, and then insert it into the BulkPurchases table. You can change it based on your requirement, pay more attention to the foreign key BulkPurchaseRequestId.
	    INSERT INTO BulkPurchases(BulkID, Amount, ProductCode,[Status], PurchaseDateTime, BulkPurchaseRequestId)
		SELECT top 1 Id as id1, TotalAmount, ProductCode, [Status],RequestDateTime, Id
		FROM BulkPurchaseRequests
		order by id; 
	end 
end
go
  
--insert data into the BulkPurchaseRequests table
INSERT INTO BulkPurchaseRequests (TotalAmount, ProductCode, [Description], Email,[Status], RequestDateTime)
VALUES (1000, 0187209, 0187209,'test@gmail.com',0, GETDATE());
go
select * from BulkPurchaseRequests
go
select * from BulkPurchases
go

结果如下：在此屏幕截图中，我使用 sql query 语句插入数据，如果我从 Blazor 插入数据，它也会触发 SQL 触发器，并获得相同的结果。

有关触发器的更多详细信息，请参阅 CREATE TRIGGER （Transact-SQL）。

我不熟悉 SQL 端操作，如何创建触发器以插入我想要的方式？

可以通过 SSMS 打开 SQL 查询，然后使用 SQL 命令创建 SQL 触发器。请参阅我在回复中的更新。

---

如果答案是正确的，请点击“接受答案”并点赞。 如果您对此答案还有其他疑问，请点击“评论”。

注意：如果您想接收相关电子邮件，请按照我们的文档中的步骤启用电子邮件通知 此线程的通知。