Data Context error when saving data to database .NET 5.0

Question

I have a little problem saving data in the database. In a given method, I make a request to an API, serialize the data, and try to save to the database, as shown in the image, but I end up getting an error referring to the application's DataContext, saying:

System.InvalidOperationException: The instance of entity type 'Launch ' cannot be tracked because another instance with the same key value for {'id'} is already being tracked. When attaching existing entities, ensure that only one entity instance with a given key value is attached. Consider using 'DbContextOptionsBuilder.EnableSensitiveDataLogging' to see the conflicting key values.

Any tips on how to resolve this issue?

Save Database Method

using System.Collections.Generic;
using System.Threading.Tasks;
using Newtonsoft.Json;
using RestSharp;
using SpaceFlightApiChallenge.Data.Context;
using SpaceFlightApiChallenge.Domain.Entities;

namespace SpaceFlightApiChallenge.Data
{
    public class DatabaseImport
    {
        private readonly SpaceFlightContext _context;

        public DatabaseImport(SpaceFlightContext context)
        {
            _context = context;
        }

        public async Task ImportData()
        {
            string url = "https://api.spaceflightnewsapi.net/v3/";
            var client = new RestClient(url);
            var apiRequest = new RestRequest("articles", Method.Get);
            apiRequest.AddHeader("Accept", "application/json");

            var response = await client.ExecuteAsync(apiRequest);
            var content = response.Content;
            var articles = JsonConvert.DeserializeObject<List<Article>>(content);
            
            _context.Articles.AddRange(articles);
            await _context.SaveChangesAsync();
        }
    }
}

Article Class

using System.Collections.Generic;

namespace SpaceFlightApiChallenge.Domain.Entities
{
    public class Article
    {
        public int id { get; set; }
        public bool featured { get; set; }
        public string title { get; set; }
        public string url { get; set; }
        public string imageUrl { get; set; }
        public string newsSite { get; set; }
        public string summary { get; set; }
        public string publishedAt { get; set; }
        public List<Launch> launches { get; set; }
        public List<Event> events { get; set; }
    }
}

Launch Class

namespace SpaceFlightApiChallenge.Domain.Entities
{
    public class Launch
    {
        public string id { get; set; }
        public string provider { get; set; }
    }
}

Event Class

namespace SpaceFlightApiChallenge.Domain.Entities
{
    public class Event
    {
        public string id { get; set; }
        public string provider { get; set; }
    }
}

EDIT: I believe the whole problem is with the id's. All objects that the external API returns me (Article, event, launch) have their own id, but when they enter the database, EF Core wants to assign a new id, due to the identity property. I don't need to change the id's, the data must be saved in the database as it comes from the API, so that later I can consult them. Here's the API link from where I'm getting the data: https://api.spaceflightnewsapi.net/v3/documentation

Answer 1

Maybe your list contains duplicates. EF does not allow to track entites with same key. If you have that error, try

_context.Entry(<entity>).State = EntityState.Detached;

Answer 2

I can see in your Article class

[DatabaseGenerated(DatabaseGeneratedOption.None)] 
public int id { get; set; } 

Try to change it

[Key]
public int id { get; set; } 

I think the promblem is a primary key, but for testing pourposes try to add one by one , to see what which item has the problem.Try it using a debugger, and see is the first item will get an exception or not

foreach(var article in articles)
{
 context.Articles.Add (article);

 context.SaveChanges();
}

Answer 3

I'd say it's because the external service is giving you repeated IDs, and your JSON deserialize routine is just making new objects with the same ID and attaching them to the object graph. This means EF ends up receiving something like:

{ 
  id: 13479,
  title: Engineers taking more time ...
  events: [ { id: 482 } ]                 //object at memory address 1234
},
{ 
  id: 13477,
  title: NASA takes break in JWST deployment ...
  events: [ { id: 482 } ]                 //object at memory address 4567
}

Two different Event objects with the same ID; EF would expect those to be the same object instance

Whether you can get the JSON deser routine to fix this up (by e.g. keeping a dictionary of seen-before objects based on ID) without a custom deser, I don't know*..

..but it should be possible to fix the objects up so the graph entities are unique:

var events = articles.SelectMany(a => a.Events).ToLookup(e => e.Id);
var launches = articles.SelectMany(a => a.Launches).ToLookup(l=> l.Id);

foreach(var a in articles){
  a.Events = a.Events.Select(e => events[e.Id].First()).ToList();
  a.Launches = a.Launches.Select(l => launches[l.Id].First()).ToList();
}

Turning the events into a Lookup on id (something like a Dictionary<int, List<Event>>) will group up all the different event objects into enumerations accessible by id. This can then be used to recreate an event list where all the events within refer to a common Event. If we enumerate the event list, pull the id out of the Event we find in the list and use that to lookup the First event in the lookup, it points everything to the same event instance for that id

It means you have a lookup called events that is:

events -> lookup of [ { id:482 /*mem address 1234*/ }, {id: 482 /*mem address 4567*/ } ]

And where you once had an Article 13477 whose Events had one Event (with id 482, at memory address 4567), that list is replaced with an event list whose event is again 482, but this time pointing to the Event at memory address 1234

{ 
  id: 13479,
  title: Engineers taking more time ...
  events: [ { id: 482 } ]                 //object at memory address 1234: first element in lookup 482
},
{ 
  id: 13477,
  title: NASA takes break in JWST deployment ...
  events: [ { id: 482 } ]                 //object at memory address 1234: first element in lookup 482
}

Now.. I hope that these events aren't repeated across invocations of the download (i.e. next week there will be another different article that also has id 482), because if they do you'll probably get a primary key violation.

The solution to that one is probably going to be to extend the loop that is patching up the object graph so that it first looks up in the DB the event with 482, and put that one into the Article (and then fall back to a new event 482 if the DB doesn't know it)

---

Note; you can use a [JsonProperty("json name here")] attribute to declare the name in json vs the name in C#, so you don't have to have C# that violates normal PascalCase convention for its props

Here's a bunch of code generated from http://quicktype.io (no affiliation) that is C# naming convention square, and deser's your JSON. You can blend it with your entities if you want (my code block above uses PascalCase syntax derived from this set of deser classes, or if you don't plan to change your deser classes you can adjust the code above to camel case) :

// <auto-generated />
//
// To parse this JSON data, add NuGet 'Newtonsoft.Json' then do:
//
//    using SpaceX;
//
//    var article = Article.FromJson(jsonString);

namespace SpaceX
{
    using System;
    using System.Collections.Generic;

    using System.Globalization;
    using Newtonsoft.Json;
    using Newtonsoft.Json.Converters;

    public partial class Article
    {
        [JsonProperty("id")]
        public long Id { get; set; }

        [JsonProperty("title")]
        public string Title { get; set; }

        [JsonProperty("url")]
        public Uri Url { get; set; }

        [JsonProperty("imageUrl")]
        public Uri ImageUrl { get; set; }

        [JsonProperty("newsSite")]
        public string NewsSite { get; set; }

        [JsonProperty("summary")]
        public string Summary { get; set; }

        [JsonProperty("publishedAt")]
        public DateTimeOffset PublishedAt { get; set; }

        [JsonProperty("updatedAt")]
        public DateTimeOffset UpdatedAt { get; set; }

        [JsonProperty("featured")]
        public bool Featured { get; set; }

        [JsonProperty("launches")]
        public List<Launch> Launches { get; set; }

        [JsonProperty("events")]
        public List<Event> Events { get; set; }
    }

    public partial class Event
    {
        [JsonProperty("id")]
        public long Id { get; set; }

        [JsonProperty("provider")]
        public string Provider { get; set; }
    }

    public partial class Launch
    {
        [JsonProperty("id")]
        public Guid Id { get; set; }

        [JsonProperty("provider")]
        public string Provider { get; set; }
    }

    public partial class Article
    {
        public static List<Article> FromJson(string json) => JsonConvert.DeserializeObject<List<Article>>(json, SpaceX.Converter.Settings);
    }

    public static class Serialize
    {
        public static string ToJson(this List<Article> self) => JsonConvert.SerializeObject(self, SpaceX.Converter.Settings);
    }

    internal static class Converter
    {
        public static readonly JsonSerializerSettings Settings = new JsonSerializerSettings
        {
            MetadataPropertyHandling = MetadataPropertyHandling.Ignore,
            DateParseHandling = DateParseHandling.None,
            Converters =
            {
                new IsoDateTimeConverter { DateTimeStyles = DateTimeStyles.AssumeUniversal }
            },
        };
    }
}

_{* I expect it would be possible but I've never deser'd directly to a DB entity (nor would i recommend to do so; I'd generally have one set of Dtos for API shuttling and another set of entities for DB work)}

---

Edit:

Actually, try this, which is a slight rethink of the above:

//your code
var articles = JsonConvert.DeserializeObject<List<Article>>(content);       

//my code
foreach(var a in articles){
  a.Events = a.Events.Select(e => _context.Events.Find(e.Id) ?? e).ToList();
  a.Launches = a.Launches.Select(l => _context.Launches.Find(l.Id) ?? l).ToList();

  _context.Articles.Add(a);
}

//your code
//_context.Articles.AddRange(articles); - not needed now

Ok, so that above is what I mean with the tweaks : after you deserialize the json you have articles with repeated same-id events and launches. For each article we'll rebuild the events. This code also incorporates the idea of "look up the event by id in the db first" - using Find should be fairly quick as it will first look locally for the entity- if it has been downloaded before then it is returned from local cache in the context. If Find doesn't get the item locally it hits the db, if that also returns null then we use the event/launch that we're currently processing. This should then become the event that Find will find next time (because we add to the context soon after) which resolves the duplicate entity problem by reusing the one the context knows about

If that doesn't work as expected, this one should:

var events = articles.SelectMany(a => a.Events).ToLookup(e => e.Id);
var launches = articles.SelectMany(a => a.Launches).ToLookup(l=> l.Id);

foreach(var a in articles){
  a.Events = a.Events.Select(e => _context.Events.Find(e.Id) ?? events[e.Id].First()).ToList();
  a.Launches = a.Launches.Select(l => _context.Launches.Find(l.Id) ?? launches[l.Id].First()).ToList();

  _context.Articles.Add(a);
}

//your code
//_context.Articles.AddRange(articles); - not needed now