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

Question

Hi,

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

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.

Answer 1

Hi,@Cenk. Welcome Microsoft Q&A.

For improved performance when checking whether records already exist in the database before inserting, you could leverage the capabilities of Entity Framework Core (EF Core) and perform a batch insert operation with a single database query.

You can try to refer to the following codes according to your needs.

Extract the Serial and Pin values from the gameBank list:

var serials = gameBank.Select(g => g.coupons.GameBankPin);
var pins = gameBank.Select(g => g.coupons.Pin);

Query the database to check if any records already exist with the same Serial and Pin values:

var existingRecords = await _oyunPalasContext.GameBanks
    .Where(g => serials.Contains(g.coupons.Serial) && pins.Contains(g.coupons.Pin))
    .ToListAsync();

Identify the Serial and Pin values that already exist in the database:

var existingSerials = existingRecords.Select(g => g.coupons.Serial);
var existingPins = existingRecords.Select(g => g.coupons.Pin);

Filter the gameBank list to exclude the records that already exist in the database:

var newGameBank = gameBank.Where(g => !existingSerials.Contains(g.coupons.Serial) || !existingPins.Contains(g.coupons.Pin)).ToList();

Perform the batch insert operation with the filtered newGameBank list:

await _oyunPalasContext.GameBanks.AddRangeAsync(newGameBank);
await _oyunPalasContext.SaveChangesAsync();

By performing the batch insert operation, you reduce the number of database queries needed to check for existing records, resulting in improved performance compared to checking one record at a time within a loop.

---

If the response is helpful, please click "Accept Answer" and upvote it.

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.

Answer 2

For EF Core, consider EF Extensions which offers an Upsert/Merge. It's a paid library but the author says You can extend your trial for several months by downloading the latest version at the beginning of every month.

If using SQL-Server, there is the MERGE statement. See a basic example at the following repository (there are two branches).

Answer 3

How to check the same check the table record before inserting this string value

of within the model GameBank and not in GameBankPin
public string? Version { get; set; }