2

Our product supports multiple databases. At the moment, we support FireBird and MSSql and there is future support coming for Oracle.

When we push out an update for our product there is the potential that we also need to update the client's database schema as well.

Traditionally we have scripts that are flavored to the DB version which would do things like "Alter table add column" which are executed in order to bring the database up to the correct version. This is becoming a hassle because we have to maintain two sets of sql scripts (with more on the way if we add Oracle to the mix).

We use the Entity Framework in our db layer. The EF already contains a schema of the database. I wonder - is there a slick way to use the EF and it's knowledge of the schema to handle the updates to the client DB?

EDIT -

This is EF 4.0

bugfixr
  • 7,997
  • 18
  • 91
  • 144

1 Answers1

0

You haven't mentioned which version of EF you are using. In V4, you can use the "Code First" model. Check out ScottGu's post. I'm not 100% sure if it is supported across all the DBs you need though. [edit]This will only create new schemas, not update existing schemas[/edit]

It might be some work, but it may be worth switching to nHibernate, which supports a wider variety of DBs and auto-updates the schema.

Robert Wagner
  • 17,515
  • 9
  • 56
  • 72
  • 2
    The problem with Code First auto schema generation is that it doesnt support versioning, it just drops your database and recreates it again, I dont think his clients would want that. Same happens with fluent Nhibernate schema generation, the EF team is supposedly working on a migrations solution for the next CTP. – ryudice Dec 06 '10 at 23:40
  • Check out http://stackoverflow.com/questions/366176/how-to-update-database-table-schemas-with-nhibernate-schema-generation. I thought EF4 would do the same, but I can't find anything – Robert Wagner Dec 06 '10 at 23:45
  • Found a no on EF4 (officially): http://weblogs.asp.net/scottgu/archive/2010/07/23/entity-framework-4-code-first-custom-database-schema-mapping.aspx#7572584 – Robert Wagner Dec 06 '10 at 23:48
  • I will -1 that. Because CodeFirst does not handle any refactoring mechanism - I consider the schema generation in all .NET products (including visual studio) something for development, not for production use. Any database side refactoring is impossible to do with them. – TomTom Jan 08 '13 at 07:42