Can entity framework database-first successfully build models from any SQLServer db?

Question

I'm considering using entity framework database-first approach. The database in question uses various non-standard techniques, eg 'informal' foreign keys. I'd like to confirm that it's still possible to create a usable EF model from this database, even if all the relationships are not recognised?

Answer 1

Yes, you can do this. Generate the model from the database as normal, then modify the generated entities in order to introduce the relationships. You'll need to set a Null database initializer to tell EF it should not try and update the database to match its model:

Database.SetInitializer(new NullDatabaseInitializer<MyContext>());

There's no requirement for foreign keys to exist in the database to match your model - as long as the navigation properties have been added on the entities, EF will generate the correct SQL queries accordingly.

I would recommend the use of the [ForeignKey] attribute to clarify the names of your foreign key fields for when they don't conform to EF's expected patterns (or you could modify the Conventions accordingly). For example...

public class Child {
    // ...

    public int Parent_ID { get; set; }     // <-- non-standard name

    // Add these to introduce the navigation property without a formal relationship
    [ForeignKey("Parent_ID")]
    public virtual Parent Parent { get; set; }
}

I have used this approach on a MySQL database with no foreign keys at all.