EF Core - adding range - possible duplicate

Question

I have a service that posts data to web-server (asp.net core 3.1) each second that I store to a sql server using EF Core 3.1. Up til now I have, when trying to store new data, for each new data row separately:

1. Checked if data entity exist in database (the entity type is configured with .IsUnique() index in the OnModelCreating() method)
2. If not exists - add single entity
3. DBContext.SaveChanges()

However, this seems like it is a bit "heavy" on the sql server with quite a lot of calls. It is running on Azure and sometimes it seems that the database has some problems following along and the web-server starts returning 500 (internal server error as far as I understand). This happens sometimes when someone calls another controller on the web-server and tries to retrieve some data (larger chunks) from the sql server. (that's perhaps for another question - about Azure SQL server reliability)

Is it better to keep a buffer on the web-server and save all in one go, like: DBContext.AddRange(entities) with a bit coarser time resolution (i.e. each minute)? I do not know exactly what happens if one or more of the data is/are duplicates? Are the ones not being duplicates stored or are all inserts refused? (I can't seem to find an explanation for this).

Any help on the matter is much appreciated.

EDIT 2021-02-08: I try to expand a bit on the situation:

outside my control: MQTT Broker(publishing messages)

---

in my control:

• MQTT client (currently as an azure webjob), subscribes to MQTT Broker
• ASP.NET server
• SQL Database

---

The MQTT client is collecting and grouping messages from different sensors from mqtt broker into a format that (more or less) can be stored directly in the database.

The asp.net server acts as middle-man between mqtt client and sql database. BUT ALSO sends continuously "live" updates to anyone visiting the website. So currently the web-server has many jobs (perhaps the problem arises here??)

• receive data form MQTT service
• to store/retrieve data to/from database
• serve visitors with "live" data from MQTT client as well as historic data from database

Hope this helps with the understanding.

Answer 1

I ended up with a buffer service with a ConcurrentDictionary that I use in my asp.net controller. That way I can make sure that duplicates are handled in my code in a controlled way (updates existing or discarded based on quality of the received data). Each minute I empty last minutes data to the database so that I always keep one minute of data. Bonus: I can also serve current data to visitors much more quickly from the buffer service instead of going to the database.