EF Invalid Object name when saving changes to _context

Question

I have an WebAPI to consume the Pokemon public API. I've built it using .NET 5.0 and EF 5.0. Everything went fine until i added my logic to consume the API to the controller and tried to save the changes to the context.

When i run _context.SaveChangesAsync() i'm having an error that says:

"Cannot insert explicit value for identity column in table 'PokemonDtos' when IDENTITY_INSERT is set to OFF."

The thing is, everything was created by EF. I have not created any Table. The classes for deserializing json i created using json2csharp and jsonformatter so i could understand how to properly deserialize them.

What am i doing wrong? Everything seems okay with the code, i'm just lost with this database issue. The database is created also, and tables ar there.

PokemonDtoController.cs

{
    [Route("api/[controller]")]
    [ApiController]
    public class PokemonDtoesController : ControllerBase
    {
        private readonly DataContext _context;
        private PokemonDto currPoke = null;

        public PokemonDtoesController(DataContext context)
        {
            _context = context;
        }

        // GET: api/PokemonDtoes
        [HttpGet]
        public async Task<ActionResult<IEnumerable<PokemonDto>>> Get_pokemons()
        {
            int limit = 3;
            string url = $"https://pokeapi.co/api/v2/pokemon/?ffset={limit}&limit={limit}";
            string json = string.Empty;
            using (HttpClient _client = new HttpClient())
            {
                _client.BaseAddress = new Uri(url);
                _client.DefaultRequestHeaders.Accept.Clear();
                _client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
                HttpResponseMessage response = await _client.GetAsync(url);
                if (response.IsSuccessStatusCode)
                {
                    json = await response.Content.ReadAsStringAsync();
                    BaseResults myDeserializedClass = JsonConvert.DeserializeObject<BaseResults>(json);
                    if (myDeserializedClass != null)
                    {
                        foreach (var result in myDeserializedClass.results)
                        {
                            using (HttpClient insideclient = new HttpClient())
                            {
                                insideclient.BaseAddress = new Uri(result.url);
                                response = await insideclient.GetAsync(result.url);
                                if (response.IsSuccessStatusCode)
                                {
                                    json = await response.Content.ReadAsStringAsync();
                                    //AbilitiesRoot currPokeAbilities = JsonConvert.DeserializeObject<AbilitiesRoot>(json);
                                    currPoke = JsonConvert.DeserializeObject<PokemonDto>(json);
                                    if (currPoke != null)
                                    {
                                        try
                                        {
                                            _context.Add(currPoke);
                                            await _context.SaveChangesAsync();
                                    //        _context.SaveChanges();

                                        }
                                        catch(Exception e)
                                        {
                                            return null;
                                        }
                                        
                                    }

                                }
                            }
                        }
                    }
                }
            }
            return await _context.PokemonDtos.ToListAsync();
        }

DataContext.cs

public class DataContext : DbContext
    {

        protected readonly IConfiguration Configuration;
        public DataContext(IConfiguration configuration)
        {
            this.Configuration = configuration;
        }

        protected override void OnConfiguring(DbContextOptionsBuilder options)
        {
            options.UseSqlServer(Configuration.GetConnectionString("DefaultConnectionString"));
        }

        public DbSet<PokemonDto> PokemonDtos { get; set; }        

        
    }

Perhaps i should list my steps also. After all code was created, i ran the following commands:

dotnet ef migrations add PokeMigrations3 dotnet ef database update

Firstly, why do you have an entity named `PokemonDto` and a table named `PokemnonDtos`? DTO stands for Data Transfer Object. They exist to TRANSFER data. You copy data from your entities to your DTOs in order to transfer that data elsewhere. If you don't have separate entities and DTOs then you don't have DTOs at all, so don't name your entities as though they were DTOs. — jmcilhinney, Oct 21 '22 at 02:31
This was from my old code using repositories. Even using the Pokemon entity the problem persists. I do prefer focusing on fixing the problem, then changing the entities name :) ! — Pablo Costa, Oct 21 '22 at 02:51

score 1 · Accepted Answer · answered Oct 21 '22 at 06:44

I think there are 2 options.

Option 1:
Id is auto increment but you was inserting manuel with SET IDENTITY_INSERT [TableName] OFF. After that, you forgot execute SET IDENTITY_INSERT [TableName] ON from MSSQL.

Option 2:
You're using Guid for Id field but you dont generate new Guid when it was inserting. You can use someEntity.Id = Guid.NewGuid(); or you can do that with 2 way below.

Way 1:

[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
public Guid Id { get; set; }

Way 2:
You have to identify abstract(is-a)/interface(can-do) entity and other entities must be inheritance this abstracted entity.

override Task<int> SaveChangesAsync(..)/override int SaveChanges(..)
{
    var insertingEntities = ChangeTracker.Entries()
.Where(e => e.Entity is BaseEntity && (e.State == EntityState.Added));
    foreach (var entry in insertingEntities)
    {
        if (entry.Entity is BaseEntity entity)
        {
            if (entry.State == EntityState.Added)
            {
                entity.Id = Guid.NewGuid();
            }
        }
    }        
    return base.SaveChangesAsync(..)/base.SaveChanges();
}

I noticed your deserialize entities now. You can use `await respons.Content.ReadAsAsync();`instead of `json = await response.Content.ReadAsStringAsync();currPoke = JsonConvert.DeserializeObject(json);` — sergin, Oct 21 '22 at 06:49
I'll try it out, thanks! I managed to get it working by manually adding the Pokemon object to the context (_context.Add(new PokemonDto(){...}); . I liked your approach! — Pablo Costa, Oct 21 '22 at 16:55

Ibrahim Timimi · Answer 2 · 2022-10-21T15:34:13.590

I believe you need to auto generate the Id property for PokemonDto because the database is not set to automatically generate it.

To make it auto-generate this primary key property, you can use the attribute DatabaseGeneratedAttribute to force the database to auto generate the key:

public class PokemonDto
{
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public Guid PokemonDtoId { get; set; }

    // ...
}

An alternative will be to assign the id property before saving changes to DB.

try
{
    currPoke.MyIdProperty = Guid.NewGuid(); // or assign to whatever type it is
    _context.Add(currPoke);
    await _context.SaveChangesAsync();
}

score 0 · Answer 3 · answered Oct 22 '22 at 17:42

The Json that you are deserializing most likely has a field named id. This is setting your Id property of your PokemonDto Entity, which is not allowed by your database, as it is using autoincrement.

A quick fix would be to change your PokemonDto's Id property to PokemonDtoId, this will ensure that it does not get set by the json value.

Another option would be to use JsonIgnore attribute above the id property in your PokemonDto class. https://learn.microsoft.com/en-us/dotnet/standard/serialization/system-text-json/ignore-properties?pivots=dotnet-6-0.

If you want to use the Id that is being sent through from your json object, then you can look at this answer: Insert new entity with EF Core with a manually assigned Id

Hi! I've fixed it by manually creating a new Pokemon object when adding to the database. It was exactly what you mentioned above, there was an conflict between the table auto-increment id and the id deserialized on the json file. — Pablo Costa, Oct 23 '22 at 21:14

EF Invalid Object name when saving changes to _context

3 Answers3