Extending the Entity Framework Provider Model to support DDL

  • Article

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

  • Anonymous
    October 06, 2009
    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

  • Anonymous
    October 06, 2009
    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.

  • Anonymous
    October 06, 2009
    @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...

  • Anonymous
    October 07, 2009
    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.

  • Anonymous
    October 07, 2009
    The comment has been removed

  • Anonymous
    October 09, 2009
    +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.

  • Anonymous
    April 11, 2010
    Will Entity framework supports the features supported by NHibernate and Java Persistance architecture with Distributed queries and caching.