Merge statement how to insert only non-duplicates and return duplicates?

Question

Hello,

In my Blazor Server application, I am inserting a list of data into a SQL database with EF Core 6. There are tens of thousands of data in the database and the list to be compared might be 5000 records.

I wonder if I can only insert the non-duplicate values based on 2 columns on the database (Serial and Pin) and return the duplicates? I searched and came up with MERGE but I am not sure if I can do it with it.

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

Here is how I do it with EF Core;

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();

            if (existingRecords.Count > 0)
            {
                existingRecords = gameBank
                    .Where(g => existingRecords.Any(er => er.coupons.Serial == g.coupons.Serial || er.coupons.Pin == g.coupons.Pin))
                    .ToList();
            }

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

Answer 1

how to insert only non-duplicates and return duplicates?

Sorry, for me your post ist absolutely unclear. What do you now have as data source, duplicate or unique data? How do you define duplicate / unique data? You have a PK only on one table and this on an identity column.

Please post table design as DDL, some sample data as DML statement and the expected result.

Answer 2

First of all, if (Serial, Pin) are supposed to be unique, they should should be the primary key in the table.

If you only want to insert keys not already in the table, you would do:

INSERT tbl (...)
   SELECT ... 
   FROM  src
   WHERE NOT EXISTS (SELECT *
                     FROM  tbl 
                     WHERE tbl.pin = src.pin
                      AND  tbl.serial = src.serial)

You said you had your source data in Excel. You would somehow send that data to SQL Server first. You can use a table-valued parameter to for that purpose - if EF6 Core supports it, which I am not sure of. But then again, you don't need to use EF6, but you can use .NET calls directly.