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

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)
{
   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) might be 5000 records. Is there a more performant method instead of checking one by one in the loop?

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Mohamed Azarudeen Z3y ago
Accepted answer
Hi ray

Instead of checking each record in a loop, you can query the database once to determine if any matching records exist. Here's an example of how you can modify your AddStockAsync method to incorporate this approach:
```
public async Task AddStockAsync(List 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
        .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();
}
```
In this modified code, the serials and pins variables extract the serial and pin values from the gameBank list. Then, a query is performed using Where() to retrieve any matching records from the database. The existingRecords variable will contain the records that already exist.

Next, the newGameBanks variable filters out the records from the gameBank list that have matching serial and pin values in the existingRecords list.

Finally, only the new records are added to the database using AddRangeAsync(), and SaveChangesAsync() is called to persist the changes.
Sam Hobbs3y ago
I want to add that you can probably increase performance by creating indexes for serial and pin.