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.

 

[Skydiver]

There is no getting around checking one by one in a loop. Even if you used the LINQ Intersect() under the covers it would still be checking one by one, it's just not readily visible to you that is what happening under the covers.

The question is what are you trying to optimize? Databases are built to do fast searches. Are the database hits really that slow? Or is the database fast doing searches, but EF is the one causing the overhead?

If you have a lot of memory available to you, you could build an in memory hashtable with all the serial+pin that is in your datatabase. You can then check against the hashtable rather than searching the database. If you used a classic computer science hashtable -- as opposed to a modern C# Dictionary where nobody expects hash collisions -- then you would still need to check your database anyway, albeit less often that for every object.

Depending on the number of records in your database, a Bloom filter maybe more appropriate. (I know a lot people will disagree, but to me a Bloom filter is just a hashtable on steroids -- it just uses multiple hashes.)

 

[raysefo]

he question is what are you trying to optimize? Databases are built to do fast searches. Are the database hits really that slow? Or is the database fast doing searches, but EF is the one causing the overhead?

Thank you for your reply @Skydiver. The data in the database is increasing, eventually, this process will slow down. What will I do then?

 

[Skydiver]

If you have indexes on those search fields, the searching should not decrease that much unless the number of items you have in the database causes the database indexes to span more than 8 levels deep. (Recall that indexes are built as N-trees.)

 

[raysefo]

If you have indexes on those search fields, the searching should not decrease that much unless the number of items you have in the database causes the database indexes to span more than 8 levels deep. (Recall that indexes are built as N-trees.)

Actually here is the related table, there is no index for serial and pins. Should I add non-clustered index for them?

CREATE TABLE [dbo].[GameBankPins](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [GameBankID] [int] NOT NULL,
    [expiryDate] [datetime] NULL,
    [Serial] [nvarchar](max) NULL,
    [Pin] [nvarchar](max) NULL,
 CONSTRAINT [PK_dbo.GameBankPins] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

 

[Skydiver]

If you anticipate doing lots of searches for matching serial and PIN, yes.

 

[cjard]

Take a look at borisdj's EF core bulk extensions; it will upload your ents all to a temp table using a bulk copy then run a MERGE statement to insert or update as appropriate. For my hardware it merges about 70,000 records a second

Instructions here: GitHub - borisdj/EFCore.BulkExtensions: Entity Framework EF Core efcore Bulk Batch Extensions with BulkCopy in .Net for Insert Update Delete Read (CRUD), Truncate and SaveChanges operations on SQL Server, PostgreSQL, MySQL, SQLite

 

[Skydiver]

Specifically from that link:

If we want Insert only new and skip existing ones in Db (Insert_if_not_Exist) then use BulkInsertOrUpdate with config PropertiesToIncludeOnUpdate = new List<string> { "" }

 

[Skydiver]

But again someone at some point is still doing a check one by one, but you it may not be you explicitly doing that check.

 

[raysefo]

@Skydiver, @cjard thank you for your help.

If we want Insert only new and skip existing ones in Db (Insert_if_not_Exist) then use BulkInsertOrUpdate with config PropertiesToIncludeOnUpdate = new List<string> { "" }

I couldn't get how to use it in my case, can you please elaborate?

 

[cjard]

Your logic is:

If record does not exist, insert
If record does exist, do nothing
Your gamebankpin table has a primary key but also the serial and pin are unique?(they could be the primary key)


Correct?

 

[raysefo]

If record does not exist, insert
If record does exist, do nothing
Your gamebankpin table has a primary key but also the serial and pin are unique?(they could be the primary key)

serial and pin are non-clustered indexes.

 

[cjard]

Doesn't tell me much; considered together, are they unique?

 

[raysefo]

Doesn't tell me much; considered together, are they unique?

yes, they should be unique serial and pin, considered together.

 

[cjard]

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