EF Core 6 - Adding records based on a condition

Cenk 956 Reputation points
2023-02-01T17:33:09.4066667+00:00

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

How can I implement this in EF Core?

Thank you.

Entity Framework Core
Entity Framework Core
A lightweight, extensible, open-source, and cross-platform version of the Entity Framework data access technology.
700 questions
ASP.NET Core
ASP.NET Core
A set of technologies in the .NET Framework for building web applications and XML web services.
4,226 questions
Blazor
Blazor
A free and open-source web framework that enables developers to create web apps using C# and HTML being developed by Microsoft.
1,411 questions
0 comments No comments
{count} votes

Accepted answer
  1. Zhi Lv - MSFT 32,036 Reputation points Microsoft Vendor
    2023-02-02T05:26:40.8833333+00:00

    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,'test@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.

    image1

    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


0 additional answers

Sort by: Most helpful