Cannot insert explicit value when IDENTITY_INSERT is set to OFF in Entity Framework

asp.net-mvc c# entity-framework-core ssms

Question

I'm trying to add data to my database and I keep getting this error:

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

The exception gets thrown on SaveChanges(). I've already tried using [DatabaseGenerated(DatabaseGeneratedOption.Identity)] above

public int TermId { get; set; }

and all the other ideas I found on here, but nothing worked. I don't know what to try anymore.

This is part of the OnPost method:

try
{
    int? termId;

    while ((termId = ReadTermId(ligne, worksheet)) != null)
    {
        var term = worksheet.Cell(ligne, 2).Value.ToString();
        var definition = worksheet.Cell(ligne, 3).Value.ToString();
        var listExamplesEntities = new List<Example>();

        foreach (var cell in worksheet.Cell(ligne, 4).ToString().Split("\n"))
        {
            listExamplesEntities.Add(new Example {TermId = termId.Value, LocalizationId = language, Text = cell});
        }

        var notes = worksheet.Cell(ligne, 6).Value.ToString();
        var occurence = worksheet.Cell(ligne, 7).Value.ToString();
        var roots = worksheet.Cell(ligne, 8).Value.ToString();
        var rootsEntities = new List<Root>();

        if (!string.IsNullOrWhiteSpace(roots))
        {
            var rawRoots = roots.Trim().Split("\n");

            for (int i = 0; i < rawRoots.Length; i += 2)
            {
                var description = "";

                if (i < rawRoots.Length - 1) 
                    description = rawRoots[i + 1];

                rootsEntities.Add(new Root { TermId = termId.Value, LocalizationId = language,
                                Definition = description, Word = rawRoots[0]});

                if (!string.IsNullOrWhiteSpace(description)) 
                     i++;
            }
        }

        // Create and add term
        var termEntity = new Term
                    { 
                        TermId = termId.Value
                    };

        if (db.Terms.Find(termId.Value) == null)
        {
            db.Terms.Add(termEntity);
        }
        else
        {
            db.Terms.Update(termEntity);
        }

        // Create and add termLocalization
        TermLocalization termLocalizationEntity = new TermLocalization
                    {
                        TermId = termId.Value,
                        Term = db.Terms.Find(language),
                        LocalizationId = language,
                        Localization = db.Localizations.Find(language),
                        Roots = rootsEntities,
                        Examples = listExamplesEntities,
                        Word = term,
                        Definition = definition,
                        Note = notes,
                        FirstOccurence = occurence,
                        LastUpdateDate = DateTime.Today
                    };

        if(db.TermLocalizations.SingleOrDefaultAsync(x => x.TermId == termId && x.LocalizationId == language).Result == null)
        {
            db.TermLocalizations.Add(termLocalizationEntity);
        }
        else
        {
            db.TermLocalizations.Update(termLocalizationEntity);
        }

        db.SaveChanges();

        ligne++;
    }

    Confirmation = "Le fichier a été téléchargé avec succès";
}
catch (Exception ex)
{
    Confirmation = "Fichier invalide à la ligne " + ligne;
}

And this is my Term entity:

namespace ENAP.Domain.Entities
{
    public class Term
    {
        public int TermId { get; set; }
    }
}
1
0
2/21/2020 10:51:52 PM

Accepted Answer

I found the solution to my problem. It may not be the cleanest code, but it worked just fine.

//Create and add term
var transaction = await db.Database.BeginTransactionAsync();

var termEntity = new Term
{
     TermId = termId.Value
};
if (db.Terms.Find(termId.Value) == null)
{
    db.Terms.Add(termEntity);
}
else
{
    db.Terms.Update(termEntity);
}
db.Database.ExecuteSqlCommand("SET IDENTITY_INSERT Terms ON;");
await db.SaveChangesAsync();
db.Database.ExecuteSqlCommand("SET IDENTITY_INSERT Terms OFF");

//Create and add termLocalization
TermLocalization termLocalizationEntity = new TermLocalization
{
    TermId = termId.Value,
    LocalizationId = language,
    Roots = rootsEntities,
    Examples = listExamplesEntities,
    Word = term,
    Definition = definition,
    Note = notes,
    FirstOccurence = occurence,
    LastUpdateDate = DateTime.Today
};

if (db.TermLocalizations.SingleOrDefaultAsync(x => x.TermId == termId && x.LocalizationId == language).Result == null)
{
    db.TermLocalizations.Add(termLocalizationEntity);
}
else
{
    db.TermLocalizations.Update(termLocalizationEntity);
}
await db.SaveChangesAsync();
await transaction.CommitAsync();

ligne++;
0
2/21/2020 11:04:58 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow