0

I have two tables in database with 1 to Many relationship like this:

Job:

iID, Other columns...

Mailbox:

iID, iJobID, Other columns...

where

a Job can contain many Mailboxes and iID column of both tables is set to auto increment.

The Mailboxes are added in collection of Job and the parent entity (Job) is then added to database and SaveChanges() is called, like this:

Job foo = new Job
{
  strJobName = "fooJob",
  //other properties...

  Mailboxes = collectionOfMailboxes;
};

dbContext.Jobs.Add(foo);

dbContext.SaveChanges();

where

Mailboxes is the child collection property generated by EF.

Everything works fine up to this point. Mailboxes and Job both are added to database with foreign key relation.

But then I needed to enable the foreign key constraint for which I read here that SQLite doesn't enforce it by default. So I did it by adding

foreign keys=true;

in the connection string, which I read here and here but after adding the constraint I'm no longer able to add a Job record. and I get:

System.Data.SQLite.SQLiteException: constraint failed FOREIGN KEY

If I remove the constraint then my code works fine. Why is this happening and how can I add foreign key constraint in EF so that it is checked after adding the record.

Community
  • 1
  • 1
Syed Ali Taqi
  • 4,898
  • 3
  • 34
  • 44
  • I think you will need to make the foreign key nullable, as in http://stackoverflow.com/questions/5189701/how-to-get-entity-framework-code-first-and-nullable-foreign-key-properties-to-wo – stuartd Mar 31 '16 at 09:45
  • I tried by setting the `id` property as `Nullable` in generated entity class but it didn't work. Let me try by adding it with `model builder`. – Syed Ali Taqi Mar 31 '16 at 09:48
  • @stuartd: not working. I tried this `modelBuilder.Entity().HasOptional(m => m.Job).WithOptionalPrincipal(); ` – Syed Ali Taqi Mar 31 '16 at 10:03
  • it might help to [trace the Sql statements](https://www.sqlite.org/c3ref/profile.html) to see exactly what's happening. – stuartd Mar 31 '16 at 10:30
  • @stuartd: I'll give it a try, thanks. – Syed Ali Taqi Mar 31 '16 at 10:42
  • Did you ever find an answer ? – AJ_ Nov 28 '16 at 22:45
  • @John: sadly, no. I had to enable the foreign key attribute, because it was a must requirement. As a workaround for this issue, I switched to adding the entities in their tables separately. – Syed Ali Taqi Nov 29 '16 at 06:05

0 Answers0