Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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.
share|improve this question

2 Answers 2

up vote 2 down vote accepted

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 tablel, 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 acutally - works. As in: it records all features. On none of the databses I have I pretty much could use cod first - bcause we always hav 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 refernce version to work against.

  • Generate delta scripts as needed for releases that also get versioned and ahve a nice mechanism to pdeploy them to a database.

share|improve this answer

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.

share|improve this answer
    
Weird, this answer didn't show up in my inbox... –  Benjol Sep 4 '13 at 7:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.