Unable to insert records in SQLite database using Entity Framework 5

Question

I was trying to use SQLite database using ADO.NET Entity Framework 5 and I was able to connect to the SQLite database and was able to query records from a table in database. But When I am trying to insert a record into table the SaveChanges throws an exception

"An error occurred while updating the entries. See the inner exception for details."

Code looks like this:

var connectionStr = string.Format("data source={0};Version=3;UseUTF16Encoding=True;",
    fileName);
var conn = new SQLiteConnection(connectionStr);

this.personDataContext = new PersonDataContext(conn);

var persons = (from person in File.ReadAllLines(fileName)
               let p = person.Split(new[] { ',', ' ' },
                                    StringSplitOptions.RemoveEmptyEntries)
               select new Person
               {
                   ID = Convert.ToInt32(p[0]),
                   Name = p[1],
                   Address = p[2],
                   MobNo = Convert.ToInt32(p[3]),
                   PhNo = Convert.ToInt32(p[4]),
                   Designation = p[5]
               }).ToList();

if (this.personDataContext != null && this.personDataContext.Persons != null)
{
    foreach (var person in persons)
    {
      this.personDataContext.Persons.Add(person);
    }

    var saveFailed = false;

    do
    {
        try
        {
            this.personDataContext.SaveChanges();
        }
        catch (DbUpdateConcurrencyException ex)
        {
            saveFailed = true;
            ex.Entries.Single().Reload();
        }
    }
    while (saveFailed);
}

and when I check the inner exception, then it says that primary key is not defined. So, after defining the primary key into the table, same exception comes every time.

I am attaching the sample application here. Use Open Db to open the sample database first and then try to import the CSV file.

I am using the Code First approach in which I have designed a Model class and connected it with the table in the SQLite database.

Please help in fixing the problem. Thanks!

Answer 1

Try to disable database generated identity for your key property in the Person class:

[DatabaseGenerated(DatabaseGeneratedOption.None)]
public int ID { get; set; }

To me it looks like you want to supply key values manually (ID = Convert.ToInt32(p[0])) but EF won't send this value to the database because by default it expects that the ID value is generated in the database. If it isn't generated in the DB you get the exception. By setting the DatabaseGeneratedOption to None EF will take the value you supply and send it in the INSERT statement to the database.