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

CodePudding user response：

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:

//using System.ComponentModel.DataAnnotations.Schema;

public class PokemonDto
{
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public Guid MyIdProperty { get; set; } // replace with you id property

    // ...
}

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();
}

CodePudding user response：

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();
}