How do I update my database without having to wipe it each time I save data to it

Question

I've currently built a Visual Studio C# project that saves API data into a database through Entity Framework. Every time I run the project the table in the database is wiped then data is re-added again. This is to stop duplication. However, I'm wondering if there is an alternate route where I don't have to wipe the data but I can just add new data that isn't already there?

Here is the code in my project. Starting from the method in my main class that uses RestSharp to obtain the API data, deserializes to a JSON format, then saves to my DB.

public static void getAllRequestData()
{
    var client = new RestClient("[My API URL]");
    var request = new RestRequest();
    var response = client.Execute(request);

    if (response.StatusCode == System.Net.HttpStatusCode.OK)
    {
        string rawResponse = response.Content;
        AllRequests.Rootobject result = JsonConvert.DeserializeObject<AllRequests.Rootobject>(rawResponse);

        using (var db = new TransitionContext())
        {
            db.RequestDetails.RemoveRange(db.RequestDetails); //Wipes Data
            db.RequestDetails.AddRange(result.Operation.Details); //Adds Data
            db.SaveChanges(); 
        } //Utilising EF to save data to the DB
    }
} //Method that calls and stores API data

Here is the Entity Framework class below, as you can see it just supports one table (dataset).

public class TransitionContext : DbContext
{
    private const string connectionString = @"[My Server]";

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(connectionString);
    }

    public DbSet<AllRequests.Detail> RequestDetails { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<AllRequests.Detail>().HasKey(r => r.Id);
    }
}

And here is the DTO class, this holds the the data temrporarily and is used to be structured into a class fit for the API data.

public class AllRequests
    {
        public class Rootobject
        {
            public Operation Operation { get; set; }
        }

        public class Operation
        {
            public Result Result { get; set; }
            public Detail[] Details { get; set; }
        }

        public class Result
        {
            public string Message { get; set; }
            public string Status { get; set; }
        }

        public class Detail
        {
            [Key]
            public int Id { get; set; }
            public string Requester { get; set; }
            public string WorkOrderId { get; set; }
            public string AccountName { get; set; }
            public string CreatedBy { get; set; }
            public string Subject { get; set; }
            public string Technician { get; set; }
            public string IsOverDue { get; set; }
            public long DueByTime { get; set; }
            public string Priority { get; set; }
            public long CreatedTime { get; set; }
            public string IgnoreRequest { get; set; }
            public string Status { get; set; }
        }
    }

Here is the table that is produced (irrelevant data blocked out).

In order to get Entity Framework working, I had to create an ID. This ID does not hold any API data, it simply starts from 1 to however many rows there are. However, WorkOrderId is a unique ID for each row. How would I be able to make this project scan for WorkOrderId's in the table and only add new data where a WorkOrderId wasn't already there?

Because ideally I want to be running this project every 5-10 minutes to keep the table constantly updated, however, at the moment I feel that wiping the table isn't the ideal way to go and is a long process. I would prefer it if I could implement this procedure instead. Any help or pointers would be greatly appreciated.

Answer 1

Short answer - use unique constraints and configure EF.

Long answer - you should get more familiar with databases and Entity Framework. Database should ensure you that there are no duplicates.

Primary key (your Id here) is much more important than you think.

A table can have only one primary key, which may consist of single or multiple fields. When multiple fields are used as a primary key, they are called a composite key.

If a table has a primary key defined on any field(s), then you cannot have two records having the same value of that field(s).

(https://www.tutorialspoint.com/sql/sql-primary-key.htm)

What's more in some databases (SQL Server for example) you can have primary key built on several columns.

The other think is to properly configure Entity Framework. Basically you do this in OnModelCreating.

You can say here that a column should be unique, for example:

protected override void OnModelCreating(ModelBuilder builder)
{
    builder.Entity<User>()
        .HasIndex(u => u.Email)
        .IsUnique();
}

You can read more about indices here: https://en.wikipedia.org/wiki/Database_index and here: How does database indexing work?

It seems that you should make field WorkOrderId your primary key and delete Id field. I think you should also read more about foreign keys: https://www.w3schools.com/sql/sql_foreignkey.asp