Sql Server Data Tools & Entity Framework - is there any synergy here?

Question

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.

Answer 1

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.

Answer 2

There is more than one way to use EF with a SQL Server database.

1. Code-First... You write classes, and EF generates the associated tables
2. Database First ... You design the tables, and EF generates the classes.

EF will not necessarily do all of the work for you. EF will get you about 80 to 95 percent there. The other 5 to 20 percent of your database development effort will be supplemented with optimizations like Views and Stored Procedures.

So no, they are not orthogonal. But you'll have to decide what your overall design strategy will be, and then rely on tools like SSDT to help you optimize those parts where EF produces less than ideal results.