5

I made a table to associate a store with Table1 OR Table2 (and other infos)

Id
IdStore_FK
IdTable1_FK
IdTable2_FK
SomeOtherFieldsThatDoesntMatterRightNow

And I created a check constraint to make sure that it will NEVER have both FK's filled at the same time, and only one of them must be filled (XOR):

ALTER TABLE TABLE0 ADD CHECK ((IdTable1_FK IS NULL AND IdTable2_FK IS NOT NULL) OR (IdTable1_FK IS NOT NULL AND IdTable2_FK IS NULL))

But I ran that alter table manually after my add-migration and update-database. I need to develop those restrictions with code-only, to be able to send to my manager.

How can I tell entity framework to create that using only my code?
Is there any DataAnnotation to do that?

I tried to add modelBuilder.Sql("ALTER TABLE XXX....") inside the method Up of an specific migration. I can only add that line to the migration file after the add-migration, which creates the file.

A seed query after every startup checking if the constraint exists, and creating it if don't does not seems cool to me. Also it'll run only if I run the code, and not with the migration/update-database.

Bruno Miquelin
  • 651
  • 1
  • 8
  • 22
  • Possible duplicate of [check constraint entity framework](https://stackoverflow.com/questions/13232777/check-constraint-entity-framework) – Devin Goble Feb 08 '18 at 20:38
  • @kettch I actually saw that question before, which is what made me try the `modelBuilder.Sql("...")` thing. But back in 2012 there was no .Net Core. Just thought maybe EntityFramework could have implemented that after it's rework for Core. (EF 6.1 implemented a way to create indexes with annotations) – Bruno Miquelin Feb 09 '18 at 12:46

1 Answers1

5

There is no way to do it using just code. However, you can leverage the EF migration process to run arbitrary SQL. You can also run sql during the database initialization in case you need to start with a fresh database.

That being said, a custom validation attribute might still be a good idea so that you can catch errors earlier.

Sorry for the meta answer, but these are resources that have helped me in the past.

Devin Goble
  • 2,639
  • 4
  • 30
  • 44
  • I actually liked [that](https://stackoverflow.com/a/4425371/8242325) approach you linked, which appears to run only at the database initialization, and not everytime I startup my application, gonna check how to implement that on Core. I don't think a custom validation could work for me, as it apparently works only for **ONE** property (I have IdTable1_FK and IdTable2_FK, and one's validation depends on the other) – Bruno Miquelin Feb 09 '18 at 12:56