EF Core 6 - Adding records based on a condition

Question

Hi friends,

In my Blazor server application, I am inserting BulkPurchaseRequest records into DB as follows.

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;
        }
    }

Here is the BulkPurchaseRequest entity;

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; }
    }

Here is the BulkPurchase entity;

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; }
    }

I would like to insert records into BulkPurchase as well. If TotalAmount= 1000 in BulkPurchaseRequest, then 10 records will be inserted into BulkPurchase. (TotalAmount/100)

Id	TotalAmount	ProductCode	Description	Email	Status	RequestDateTime
1	1000	0187209	0187209	******@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

How can I implement this in EF Core?

Thank you.

Answer 1

Hi @Cenk

In the AddBulkRequestAsync method, after insert the new bulkPurchaseRequest, you can query the BulkPurchaseRequests table to get the total count, then use the remainder operator("%") to check the count and then based on the result to insert records into the BulkPurchase table.

Code like this:

        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.
            }
        }

Besides, which kind of database you are using? Whether it support Trigger? If it supports trigger such as SQL Server Database, you can try to use trigger with condition in the database.

Update:

To use trigger,

--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,'******@gmail.com',0, GETDATE());
go
select * from BulkPurchaseRequests
go
select * from BulkPurchases
go

The result as below: in this screenshot, I insert the data using the sql query statement, if I insert data from Blazor, it will also trigger the SQL trigger, and get the same result.

More detail information about trigger, see CREATE TRIGGER (Transact-SQL).

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best regards,

Dillion