Duplicate data problem in .net core project on postgresql

Question

using the Repository pattern, I am first querying the same database in the code block below, and then processing according to the query result. However, it adds the same data to the database more than once in the api request that comes in different milliseconds.

I used transantion scope because I was adding to two databases at the same time.

I have shared the sample code block and the database screenshot below(the first databases I query and add are the same).

public void Add(AddCookieRequest request)
    {
        
        var beforeCookie = ICookieRepository.Get(m => m.IP == request.IP && m.FK_OrganizationGuid == new Guid(request.FK_OrganizationGuid));
        if (beforeCookie == null)
        {
            using (var scope = new TransactionScope())
            {
                var cookie = new Entities.Cookie
                {
                    City = request.City,
                    ContinentCode = request.ContinentCode,
                    ContinentName = request.ContinentName,
                    CountryCode = request.CountryCode,
                    CountryName = request.CountryName,
                    FK_OrganizationGuid = new Guid(request.FK_OrganizationGuid),
                    FK_CookieContentId = request.FK_CookieContentId,
                    IP = request.IP,
                    StateProv = request.StateProv,
                    Status = request.Status,
                };

                ICookieRepository.Add(cookie);

                request.SelectedCookies.ToList().ForEach(c =>
                {
                    cookiePermissionRepository.Add(new CookiePermission
                    {
                        FK_CookieId = cookie.Id,
                        FK_OrganizationCookiePermissionId = c.Id,
                        IsSelected = c.IsSelected,
                        FK_OrganizationGuid = new Guid(request.FK_OrganizationGuid),

                    });
                });

                scope.Complete();
            }
        }
    }

example data on postgresql database

This doesn't look like valid syntax. Can you put the actual code in the question. Also, can you clearly state the problem and clearly state the question? Otherwise your question will be downvoted and closed. — JustBeingHelpful, Apr 14 '23 at 07:33
I shared right code. Do I need to share any other details? I think must use unit of work? — ereninal, Apr 14 '23 at 07:44
I want to add a record belonging to the same ip and org guid fields and it should be like this but in some api requests it adds the same data more than once in different milliseconds. — ereninal, Apr 14 '23 at 07:49
Have you learned the basics of object orientation in .NET or .NET core? Or are you new to programming? Last question. Do you understand what an abstract class is in .NET? — JustBeingHelpful, Apr 14 '23 at 08:32
If the answer to any of those is No, then I would like to suggest that you not use the Repository Pattern. It is a senior level programmer framework. I would suggest learning how to write a simple C# program that connects to a database using a simple library first. If someone gives you the answer to this, you won't learn much. You need to understand several object orientation concepts to be able to accomplish this. I'm just being honest with you. — JustBeingHelpful, Apr 14 '23 at 08:37
If it takes two months for someone to answer a question about avoiding duplicate records in a database, then it's not a very reliable framework (repository pattern). This is a very simple thing to avoid with a library in C# and controlling your own SQL code. Trust me. Read this. It was already asked. https://stackoverflow.com/questions/75375689/how-to-prevent-duplicate-insertion-of-record-with-entity-framework-and-repositor — JustBeingHelpful, Apr 14 '23 at 08:45
My guess is that some young college students decided to start programming an object oriented framework to make database programming easier, but didn't have experience with databases in the real world. Don't waste your time with this framework. Learn database syntax. And learn stored procedures. And then simply call the stored procedures. If you call a database in any other ways, there are security risks. With this framework, all of the database calls are done in a black box (cannot see them). So to debug something could take days for something as simple as what you are asking. — JustBeingHelpful, Apr 14 '23 at 08:47
I'm not saying this is a bad question. I'm just giving you professional advice. I've been in the healthcare industry for 18 years and work as a database administrator for a major cancer center. I'm being 100% honest with you here. — JustBeingHelpful, Apr 14 '23 at 08:52
One of the most important things to avoid SQL injection in a database is "DO NOT put your database queries in the application layer". Use stored procedures. It's in many security books for databases. — JustBeingHelpful, Apr 14 '23 at 08:53
This would be the syntax for SQL syntax. https://stackoverflow.com/questions/2513174/avoid-duplicates-in-insert-into-select-query-in-sql-server — JustBeingHelpful, Apr 14 '23 at 09:03
Thank you for all your answer. :)) I think I better use stored procedure as you said. But since I'm recording in two tables and there is a virtual relationship between these two tables, I need the id of the record that I throw when I add a record to the first table. I think I can get this id in the stored procedure. Would it be the same correct id I get? — ereninal, Apr 14 '23 at 09:12
You're welcome bud. Here's how it can be done if you want to do it this way. You'll notice someone with 400K reputation was the only one to understand how to answer it. This is a complex task for such a simple thing you are asking. You just need to replace the primary key with your primary key of "Id". https://stackoverflow.com/questions/5377049/entity-framework-avoiding-inserting-duplicates — JustBeingHelpful, Apr 14 '23 at 09:14
If you have two tables, then yes, you need to make sure you look up the primary key of the parent table. We use the term "association" between tables that have referential integrity (pk and fk). — JustBeingHelpful, Apr 14 '23 at 09:19
Once you write the code for your stored procedure, you can still use the Entity Framework from your application to call that stored procedure. Just put some parameters on it to pass the variables (column names) and values to the database. https://stackoverflow.com/questions/58207182/how-to-call-a-stored-procedure-in-ef-core-3-0-via-fromsqlraw — JustBeingHelpful, Apr 14 '23 at 09:54

Duplicate data problem in .net core project on postgresql

0 Answers0