Resolved SqlException: Cannot insert explicit value for identity column

[Mitchelln11]

How do you populate a database with multiple records from a REST API call?

I'm trying to populate information from a REST API into my MS SQL database.
I'm actually rebuilding on .NET Core from an original Framework project.

Using the National Park Service REST endpoint.

public async Task FetchParkApi()
{
    string url = $"https://developer.nps.gov/api/v1/parks?q=National%20Park&limit=91&api_key=api-key-here";
    HttpClient client = new HttpClient();
    HttpResponseMessage response = await client.GetAsync(url);
    string jsonresult = await response.Content.ReadAsStringAsync();
    Park park = new Park(); // Instantiate a new park so you can use it in this method
    if (response.IsSuccessStatusCode)
    {
        RestApiNationalParks parkInfo = JsonConvert.DeserializeObject<RestApiNationalParks>(jsonresult); // Run through entire JSON file
        var parkList = parkInfo.data.Select(m => m).ToList(); // returns entire list of Parks, up to 150, this only has 90 based of the National Parks query

        foreach (var singlePark in parkList)
        {
            if(singlePark.designation.Contains("National and State Parks") || singlePark.designation.Contains("National Park"))
            {
                park.ParkName = singlePark.fullName;
                park.ParkState = singlePark.states;
                park.ParkDescription = singlePark.description;
                park.ParkLatitude = singlePark.latitude;
                park.ParkLongitude = singlePark.longitude;
                park.ParkCode = singlePark.parkCode;

                var uniqueParkCode = _context.Parks.Where(c => c.ParkCode == singlePark.parkCode).FirstOrDefault();
                if (uniqueParkCode == null)
                {
                    _context.Parks.Add(park);
                    _context.SaveChanges(); // After running: "SqlException: Cannot insert explicit value for identity column in table 'Parks' when IDENTITY_INSERT is set to OFF."
                }
                await _context.SaveChangesAsync();
            }
        }
        await _context.SaveChangesAsync();
    }
}

I have a Park model and a model for the REST API results.
This worked in Framework, but not in Core.

This code should run through all listed parks, and add to my database one by one.
When it does run, it will add the first park that isn't already on the database. It fails on the second new park every time with the following message:
"SqlException: Cannot insert explicit value for identity column in table 'Parks' when IDENTITY_INSERT is set to OFF."
I've tried a few things in the if (uniqueParkCode == null) {} statement, but getting no further.

I've tried the following to turn the IDENTITY_INSERT ON: but that doesn't work either. (Identity Insert)

try
{
    context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.Customers ON");
    context.SaveChanges();
    context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.Customers OFF");
}
finally
{
    context.Database.CloseConnection();
}

Apparently, ExecuteSqlCommand is obsolete now, so I tried ExecuteSqlRaw.Still not working. I get the same error after adding 1 new record.

 

[jmcilhinney]

You're trying to fix the wrong problem. When it tells you that you can't insert into an identity column when IDENTITY_INSERT is OFF, the solution is not to set IDENTITY_INSERT ON. It's to stop trying to insert into the identity column. The whole point of such a column is that the database will generate the value. There are rare occasions that you need to insert data directly, e.g. migrations/imports from other databases, but the rest of the time you let the identity do its job. What is the identity column for that table?

 

[Mitchelln11]

You're trying to fix the wrong problem. When it tells you that you can't insert into an identity column when IDENTITY_INSERT is OFF, the solution is not to set IDENTITY_INSERT ON. It's to stop trying to insert into the identity column. The whole point of such a column is that the database will generate the value. There are rare occasions that you need to insert data directly, e.g. migrations/imports from other databases, but the rest of the time you let the identity do its job. What is the identity column for that table?

Model for my Parks:

public class Park
    {
        [Key]
        public int ParkId { get; set; }
        public string ParkName { get; set; }
        public string ParkState { get; set; }
        public string ParkLatitude { get; set; }
        public string ParkLongitude { get; set; }
        public string ParkDescription { get; set; }
        public string ParkCode { get; set; }
    }

The ParkId should be auto-generated. It does once, but never again after the first addition.

 

[jmcilhinney]

The problem is that you only create a single Park object. You create a Park instance before the loop and then you just keep editing that same object on each iteration. It works the first time because it is a new Park object that time but, after that, it would contain the ID generated the first time.

What you should be doing is creating the Park object inside the loop and then saving the changes outside the loop. That way, you create multiple new Park objects and add them to your context, then you save the lot as a batch.

 

[Mitchelln11]

The problem is that you only create a single Park object. You create a Park instance before the loop and then you just keep editing that same object on each iteration. It works the first time because it is a new Park object that time but, after that, it would contain the ID generated the first time.

What you should be doing is creating the Park object inside the loop and then saving the changes outside the loop. That way, you create multiple new Park objects and add them to your context, then you save the lot as a batch.

That's it! I knew it would be something very small.
Rookie mistake, I know.

Now that I look at it, it seems so obvious. Thank you so much.
I've noticed that it seems like you are on every post. I think we all appreciate your help.