Why do I get the Exception SqlException IDENTITY_INSERT is set to OFF-CodePudding

HeuteDb heute = new HeuteDb();
string filePath = @"C:\Users\Elias\Desktop\ASPlearning\DATA\Data.csv";

using (StreamReader reader = new StreamReader(filePath))
{
    string line;

    while ((line = reader.ReadLine()) != null)
    {
        List<string> items = line.Split(';').ToList();

        heute.Uhrzeit = items[0];
        heute.Energie = items[1];

        _context.HeutesDb.Add(heute);
        _context.SaveChanges();
    }
}

I have a CSV file which I want to store into my database. There is an ID which is set as a primary key and the Uhrzeit and the Energie values.

After every line of the CSV file, I save the changes to the database. It only saves the first line in the database, then it throws an error.

What is the best way to save a CSV file into a database?

CodePudding user response：

You might need to either set autoincrement of your Identity column in the database (value is increased by 1 automatically) - or you need to specify a value on your identity Column while inserting,

the first entry is saved successfully because the int datatype has an default value of 0, since 0 does not exist as an entry it will allow to be saved (next record needs different id)

CodePudding user response：

Look at your loop

while ((line = reader.ReadLine()) != null)
{
    List<string> items = line.Split(';').ToList();

    heute.Uhrzeit = items[0];
    heute.Energie = items[1];

    _context.HeutesDb.Add(heute);
    _context.SaveChanges();
}

You add again and again the same instance heute to your database. But the instance is the same, so after the first successful insert, EF will set the IDENTITY column with the ID obtained from the insert. But at the next loop the same instance is passed again for adding and at this time the property with the IDENTITY column is set and this is not allowed if you have an identity.

Your fix if just to move the initialization for the heute variable inside the loop

while ((line = reader.ReadLine()) != null)
{
    
    List<string> items = line.Split(';').ToList();

    HeuteDb heute = new HeuteDb();
    heute.Uhrzeit = items[0];
    heute.Energie = items[1];

    _context.HeutesDb.Add(heute);
    _context.SaveChanges();
}

Now at each loop the property for the IDENTITY column is not set and the db engine will insert your record