Extending the Entity Framework Provider Model to support DDL


As part of the first previews of Code-Only we shared some code to create a database:

// Create a context using code-only
using (var mycontext = builder.Create(dbConnection))
{
    // Create the database if it doesn’t already exist
    if (!myContext.DatabaseExists())
        myContext.CreateDatabase();
    // Standard EF code goes here.
}

But in the first preview of Code-Only this code only worked against SQL Server.

The problem is that our public Provider Model (i.e. DbProviderServices) has no Database Definition Language or DDL features.

Provider Model Changes

To support DDL features across different databases, we plan to extend DbProviderServices, with DDL specific services.

These services will be accessed through these public methods:

public string CreateDatabaseScript(
   string providerManifestToken,
   StoreItemCollection storeItemCollection);

public void CreateDatabase(
   DbConnection connection,
   int? commandTimeout,
   StoreItemCollection storeItemCollection);

public bool DatabaseExists(
   DbConnection connection,
   int? commandTimeout,
   StoreItemCollection storeItemCollection);

public void DeleteDatabase(
   DbConnection connection,
   int? commandTimeout,
   StoreItemCollection storeItemCollection);

Now internally those methods will call through to the following ‘protected virtual’ methods which will do the actual work:

protected virtual string DbCreateDatabaseScript(
    string providerManifestToken,
    StoreItemCollection storeItemCollection);

protected virtual void DbCreateDatabase(
   DbConnection connection,
   int? commandTimeout,
   StoreItemCollection storeItemCollection);

protected virtual bool DbDatabaseExists(
   DbConnection connection,
   int? commandTimeout,
   StoreItemCollection storeItemCollection);

protected virtual void DbDeleteDatabase(
   DbConnection connection,
   int? commandTimeout,
   StoreItemCollection storeItemCollection);

And the base implementations of these methods in DbProviderServices will simply throw ProviderIncompatibleExceptions.

Which means the provider writers job will be to override these ‘protected virtual’ methods with an implementation that makes sense for their backend database.

The key is to understand that the StoreItemCollection (aka the SSDL or StorageModel part of the EDMX) represents the intended shape of the database.

This means the provider writer will need to iterate over the EntitySets (tables) and the corresponding EntityTypes (table structures) in the StoreItemCollection and create / drop / script the database and tables as required.

Provider writers will be expected to override these functions so that:

  • DbCreateDatabaseScript: creates a native text command to create the tables and foreign key constraints defined in the StoreItemCollection. I.e. for SqlClient this would be the contents of a .sql DDL file.
  • DbCreateDatabase: is similar to DbCreateDatabaseScript except it should actually goes ahead and create the database, tables and foreign key constraints.
  • DbDatabaseExists: checks to see if the database exists. The SqlClient provider will simply check that the database itself exists, but custom provider writers could get more fancy and check to see if every table / foreign key constraint is found too.
  • DbDeleteDatabase: should go ahead and delete the database, or if the database server has a single database model (like Oracle) the provider writer should delete just the tables defined in the StoreItemCollection.
Simplifying Wrapping Providers

We are also planning something to simplify writing Wrapping Providers. A wrapping provider is just a provider that wraps an existing provider (i.e. SqlClient) and adds additional services (i.e. Auditing, Logging, Caching etc).

Jarek has some some sample wrapping providers if you are interested.

Today writing a wrapping provider is a little tricky, in fact one ‘protected’ method is impossible to wrap without reflection. So to help we plan to add one public wrapper method:

public DbCommandDefinition CreateCommandDefinition(
   DbProviderManifest providerManifest,
   DbCommandTree commandTree);

One of the reasons we plan on doing this, is we think people might take a ‘basic’ provider that has no DDL support and wrap it to add DDL support.

End User API

Now so far we’ve been looking at the planned extensions to Provider Services, but Provider Services is a very low level API that few developers will ever program against.

Most people will work directly against the ObjectContext, to which we plan to add these methods:

public void CreateDatabase()

public void DeleteDatabase();

public bool DatabaseExists();

public String CreateDatabaseScript();

This little snippet shows how easy it will be to script, create, check and delete a database:

MyContext ctx = new MyContext();
String sql = ctx.CreateDatabaseScript();
ctx.CreateDatabase();
Assert.True(ctx.DatabaseExists());
ctx.DeleteDatabase();
Assert.False(ctx.DatabaseExists());

As you it could hardly be easier to use.

Summary:

While Code-Only provides the catalyst to add DDL support to the Entity Framework’s Provider model, this feature is about more than just Code-Only.

In fact we think this feature will add significantly to the usability of the Entity Framework.

But as always we are keen to hear what you think.

Alex James
Program Manager, Entity Framework Team, Microsoft.

This post is part of the transparent design exercise in the Entity Framework Team. To understand how it works and how your feedback will be used please look at
this post.

Comments (7)

  1. KristoferA says:

    Just a minor thing: wouldn’t it be better to go into a more detail than just Create/Delete/Exists for the entire db? Internally each implementation of this has to work with things like ‘create schema’, ‘create table’, ‘alter table’ so why not prepare the providers to expose functionality to generate individual statements? That way it will be easier to move on to do incremental schema migration…    JMHO

  2. Eugene Burmako says:

    Kristofer, I think that would be too low-level of an approach (just like the raw change management API suggested here: http://blogs.msdn.com/efdesign/archive/2008/11/20/n-tier-improvements-for-entity-framework.aspx is too low-level for the matters of change tracking).

    For the purposes of incremental migration I’d love to have a tool that accepts a db provider + two EF configs (tho this will have troubles with code-only mappings) and produces a migration script. After getting that script the programmer might also insert some queries that migrate data using non-trivial rules (if that’s necessary).

    We could even make use of this functionality being available as a part of EF API (e.g. the mentioned tool could use such API under hood). However personally I don’t see this being very useful: auto-creating the database is ok, but auto-migrations are not – there are too many possible non-trivial migration issues that require attention of a human.

  3. KristoferA says:

    @Eugene,

    Yes, auto-migrations can be risky and won’t take data migration into account. Generally, diff-script generation would belong in the designer/dev tools and not in the runtime although having support for it in both can be useful; there are plenty of situations where run-time auto-migration would work fine too. But for those situations more granularity is definitely needed; e.g. a programmable way to check if individual columns, tables, constraints, schemas etc exist db-side and for getting the create/alter/drop statements to deal with those as needed. App developers would then be able to use those to add customized migration features into their apps…

    Anyway, what I am after here is standardizing what will hide behind the four methods described in this article. Think of it like a sort of ‘CodeDom for SQL-DDL’. That way, any work invested in adding SQL-DDL support for a specific db platform can be reused in many other situations, and will be reusable in future versions of EF, in third-party tools for EF, and in other OR mappers and tools.

    I have a bit of sample code on this topic that I will try to post in my blog… …just need to clean up the structure and document it a bit better…

  4. Sam Meacham says:

    Have you guys thought of using anything like uservoice.com to let people vote on the features they want to see implemented in EFv4?

    "Model first" and "code first" are cool, but I’d be curious to know how many people are actually asking for it.

    I’d be MUCH more interested in mapping to enums.  With EFv1, I cringe every time I have to do something like this:

    order.Status = OrderStatus.GetByName("Complete");

    Meaning I have to actually fetch the OrderStatus record from the database, just to mark an order as complete.  It would be much better this way:

    order.Status = OrderStatus.Complete;

    Everything could be set up in the mapping files, to avoid unnecessary database traffic.  It’s strongly typed, and now you have full intellisense support.  Mapping to enums would be a HUGE feature!

    Another feature I think you’d see more people asking for is caching.  I know you have Velocity CTP 3 out right now, but when it comes to caching, people want to be able to just flip a switch, and just have it magically work in the background.  Drop it in, maybe set a few options, and POW!  Caching is on.

    Really, use something like uservoice.com, or some kind of voting system, where people can make feature requests and vote on them.

  5. The problem, as I’m sure you can imagine if you’ve ever worked on a large project with many developers is that there are all kinds of things that go into deciding what features can be implemented in what order.  The reason that Enums are not supported directly in this release is not because we are working on code-only or things like that.  It has to do with a number of other factors such as enums being a feature which touches many different components and layers in the system.  Another thing to keep in mind is that while code-only is being designed and worked on right now, it’s not going to arrive in EF4.

    With regard to the two features you mention above, there are some workarounds which can help a lot at the moment.  One of them is that the introduction of FKs to the product (which will arrive in beta 2) you could just set the status ID rather than retrieving the status object.  Further, you could even build something which would code-gen based on the status values so that you could have static properties on the status class which return the ID of the corresponding entitiy.  So that your code above could become:

    order.StatusID = Status.Complete;

    Where Status is the entity type and Complete is a static property which returns the ID of the entity that means complete.

    For caching you could take a look at the post from a little while back about a wrapping provider for the EF which does caching with velocity.

  6. shawn says:

    +1 that some basic enum support would be really great to see, especially since LINQ2SQL has had it from the start, so it will be a major pain point in migrating from L2S to EF4.

  7. ravikumar says:

    Will Entity framework supports the features supported by NHibernate and Java Persistance architecture with Distributed queries and caching.