What SQL Server 2017 features are not supported in Entity Framework Core code first?

asp.net-core-2.0 entity-framework-core sql-server-2017 sql-server-data-tools


Our team is thinking of utilizing Entity Framework Core code-first to help model the database. We can have both DB projects and EF models, as per article here Database Projects vs. Entity Framework Database Migrations utilizing schema compares, just trying to figure out what will be the source of truth?

Does Entity Framework support all features in SQL Server SSDT Database Projects?

What features does EF Core 2 not support? (eg, does it not support any of following: triggers, views, functions, stored procedures, encryption keys, certificates, db properties (ansi null, quoted identifier), partitions)

I am trying to locate the Microsoft Resource.

9/6/2018 6:33:01 PM

Accepted Answer

tl;dr Database Projects are feature-rich, but database-first. Migrations is code-first, but has a very limited built-in set of database features.

For many people it won't be relevant to compare Database Projects and Migrations. They represent two different modes of working with Entity Framework. Migrations is code-first, DP is database-first. Sure, you can use migrations to control the database schema and besides that keep a DP in sync with the generated database to satisfy DBAs (as the link suggests). But both lead their own separate lives and there's no Single Source Of Truth.

So comparing them is useful if you're not sure yet wich working mode you're going to choose.

For me the most important difference is that DP will cover all database objects and detect all changes between them when comparing databases. Migrations only detect changes between a database and the mapped model. And the set of options for generating database objects is very limited. For everything you need additionally you have to inject SQL statements into the migration code. These statements are your own responsibility. You have to figure out yourself if a migration needs an ALTER PROCEDURE statement or not (for example). EF won't complain if the script and the database differ in this respect.

This is the main reason why I've never been a great fan of migrations. It's virtually impossible to maintain a mature database schema including storage, file groups, privileges, collations, and what have you.

Another advantage of DP is that they're great in combination with source control. Each database object has its own file and it's very easy to check the change history of each individual object. That's not possible with generated migrations. Indeed, many intermediate changes may never make it to a generated migration.

Of course the obvious advantage of migrations is the possibility to do a runtime check (albeit incomplete) whether the code and the database match. In database-first projects you need to create your own mechanism for that.

9/6/2018 9:01:03 PM

Popular Answer

EF Core is only ORM.

1) You should be ready to create all DB objects except tables manually. What I create manually: constrates (defaults as well as conditions). Since this is code first - there is no need in SP, functions and so on. If you use ORM - DB is only storage. Of course practice is important. For me default constraints adds comfort on tables where I create test data manually. And conditions also are usefull in situations when you do not trust your (team) code.

2) you will do creation (and dropping) of views, triggers, sp and so on to the "migration" code (there is such concept in EF) in plain sql:

 migrationBuilder.Sql("CREATE VIEW ...");

As a result you could have a separate "migration" program (e.g. command line tool) that install or remove both Ef Core tables and your manually created objects, do and revert the data migrations.

"EF Core migrations" is quite complex api (reserve a week for learning). Interesting topics: managing several dbcontexts in one db, createing db object during migration from model annotations, unistall. Or find a freelancer for it (this part of project is good for outsourcing).

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow