How to check if the record exists with the same value in the database before insert

[raysefo]

Hello there,

In my Blazor Server application, I am inserting a list of data into a database as follows with EF Core 6;

public async Task AddStockAsync(List<GameBank> gameBank)
{
   await _oyunPalasContext.GameBanks.AddRangeAsync(gameBank);
   await _oyunPalasContext.SaveChangesAsync();
}

I wonder if I can check the serial and pin inserted before? I am using an SQL server and EF Core 6.

public partial class GameBank
    {
        public int GameBankId { get; set; }
        public Guid ReferenceId { get; set; }
        public string? ProductCode { get; set; }
        public int Quantity { get; set; }
        public string? Version { get; set; }
        public DateTime? RequestDateTime { get; set; } = DateTime.Now;
        public int? CustomerId { get; set; }
        public string? Password { get; set; }
        public DateTime? ResponseDateTime { get; set; } = DateTime.Now;
        public string? InitiationResultCode { get; set; }
        public string? CompanyToken { get; set; }
        public int Used { get; set; }
        public string? ProductDescription { get; set; }
        public string? Currency { get; set; }
        public double UnitPrice { get; set; }
        public double TotalPrice { get; set; }
        public string? ApplicationCode { get; set; }
        public double EstimateUnitPrice { get; set; }
        public string? ValidatedToken { get; set; }
        public string? Signature { get; set; }
        public int Status { get; set; }
        public virtual GameBankPin coupons { get; set; }
        public string? ClientTrxRef { get; set; }
    }
public partial class GameBankPin
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }
        public int GameBankId { get; set; }
        public DateTime? ExpiryDate { get; set; }
        public string? Serial { get; set; }
        public string? Pin { get; set; }
        public virtual GameBank GameBanks { get; set; }
    }

There are tens of thousands of data in the database and the list to be compared (List<GameBank> gameBank) might be 5000 records. Is there a more performant method instead of checking one by one in the loop?


Thank you.

 

[raysefo]

Is serial unique on its own or does it need the pin to make it unique?

should be unique on its own.

Someone has this solution;

public async Task<List<GameBank>> AddStockUniqueSerialPinAsync(List<GameBank> gameBank)
        {
            // Extract the serial and pin values from the list
            var serials = gameBank.Select(g => g.coupons.Serial);
            var pins = gameBank.Select(g => g.coupons.Pin);

            // Check if any matching records exist in the database
            var existingRecords = await _oyunPalasContext.GameBanks
                .Include(g => g.coupons)
                .Where(g => serials.Contains(g.coupons.Serial) && pins.Contains(g.coupons.Pin))
                .ToListAsync();

            // Filter out the records that already exist
            var newGameBanks = gameBank.Where(g => !existingRecords.Any(e => e.coupons.Serial == g.coupons.Serial && e.coupons.Pin == g.coupons.Pin));

            // Insert the new records into the database
            await _oyunPalasContext.GameBanks.AddRangeAsync(newGameBanks);
            await _oyunPalasContext.SaveChangesAsync();

            return existingRecords;
        }

 

[Skydiver]

So how does that avoid checking all the items one by one? What do you think those Where() methods are doing under the covers?

 

[raysefo]

So how does that avoid checking all the items one by one? What do you think those Where() methods are doing under the covers?

This solution is just in case, I am looking for a more performant way because as I mentioned before there are much more data in the database and it is growing.

 

[cjard]

Someone has this solution;

hah, and I'd tell them to keep it; dragging umpteen records out of the db to do the check on the client is a waste of resources, Where/!Any is O(n^2), and it's illogical to check for serials and pins (which are paired together) by doing "serial list contains serial AND pins list contains pin" - that's dissociative. It might work here because it's really only serial that counts for anything but it doesn't teach a right approach

Make the serial the primary key of the gamebankpin table and use EF bulk extensions

I am looking for a more performant way

Then have a process of "dear database, here are 5000 serials, please insert all those that do not exist in this index". It's very simple.

That's what the bulk route does; have a stab at it using the documentation linked earlier

Ask ChatGPT to write it for you if you're feeling efficient

 

[alexhortdog95]

This solution is just in case, I am looking for a more performant way because as I mentioned before there are much more data in the database and it is growing.

Gonna be hard to do with a primary key that is only an identity field. Some schema enhancement might be in order. Then, you could write your insert to do an OUTER JOIN to prevent from inserting rows into your table if they already exist.

My other answer was to not use EF but use Dapper instead