Coming out of a project using Linq2Sql, I suspect that the next (bigger) one might push me into the arms of Entity Framework. I've done some reading-up on the subject, but what I haven't managed to find is a coherent story about how SQL Server Data Tools and Entity Framework should/could/might be used together.
- Were they conceived totally separately, and using them together is stroking the wrong way?
- Are they somehow totally orthogonal and I'm missing the point?
Some reasons why I think I might want both:
- SSDT is great for having 'compiled' (checked) and easily versionable sql and schema
- But the SSDT 'migration/update' story is not convincing (to me): "Update anything" works ok for schema, but there's no way (AFAIK) that it can ever work for data.
- On the other hand, I haven't tried the EF migration to know if it presents similar problems, but the Up/Down bits look quite handy.
Best Answer
Let me put in another point of view. Entity Framework Database Maintenance is utterly useless in any enterprise or large database project.
Problems are:
Automatic schema updates. This is absolutely not what I want as it totally violates the fundamentals of database maintenance. Problems are: (a) someone running a newer version updates the database instead of getting an issue and (b) updates are scheduled with the dba normally taking a backup FIRST. So, automatic updates are useless.
Db creation only works on basically degenerate edge cases. Do not even try to use advanced database features - regardless which one. Sql server example: included fields in indices, filters on indices, partitioning, compression, validation rules for fields.
Migration - assumes degenerate edge cases again: no data transformation or multi step update easily. Example: Table X has a historical "user" field that records the user does something. New setup has a User table, so one needs to create the user table, then create the users, then create the user reference field in table x, then update this with the user it's from the user table, then delete the user field.
The only sensible way to deal with these scenarios are generation and migration scripts and proper versioning.
Now, SSDT - that is a great tool for versioning a specific database version much better than Entity Framework because it actually - works. As in: it records all features. On none of the databases I have I pretty much could use code first - because we always have filtered indices at least ;) EF would not even get me to 10% of what I need.
Our approach is:
Design database in the database, then sync down to a SSDT module that gets checked in. Schema sync allow developers to update their version fast. There is always an authoritative master database with the current version somewhere (on a special server) so we have a reference version to work against.
Generate delta scripts as needed for releases that also get versioned and have a nice mechanism to deploy them to a database.