New Entity Framework Feature Request: Migrations: Allow Multiple Migration SQL Generator per Provider

I’ve just added this feature request to Entity Framework Uservoice site and was wondering whether you’d like to up-vote / share / tweet it if you think it’s good to have.

Migrations: Allow Multiple Migration SQL Generator per Provider

Inline, here’s what the feature request is about:

One way to allow community to contribute to EF Migrations is allow us to create more `MigrationOperation`s.

For example, people can then add things like full text index, etc., things that are not in the core EF Migrations and are just boring SQL statements that need to be combined together.

Currently, to build a provider agnostic `MigrationOperation`, you need to write the SQL generated for the operation in a class derived from `MigrationSqlGenerator`. If you want to add `CreateFullTextCatalog` operation and support SQL Server for example, you inherit `SqlServerMigrationSqlGenerator`, add a `Generate` method for your operation, and ensure all other operations generators are still called from the base class.

Not just that thius is ugly, but now the `MigrationConfiguration` has to use your generator for `SqlClient` provider, the `SetGenerator` and `GetGenerator` methods in ‘MigrationConfiguration` only deal with one generator per DB-type provider.

So, if someone else wrote their own `MigrationOperation` and `SqlServerMigrationSqlGenerator` for it, I cannot use both in the same `MigrationConfiguration`.

This makes creating generic `MigrationOperation` quite a bad idea, maybe people will just forget about Db-agnostic code and build their operations deriving from `SqlOperation` and generating a provider-specific statement from their operation, but this means the entire `MigrationSqlGenerator` is unusable, right?

The simplest answer for this seems to be supporting multiple `MigrationSqlGenerator` for the same provider in `MigrationConfiguration`, or else maybe provide a different way to make building provider-agnostic operations possible.




Related Information

If you are interested in how I got to learn about how this works, well, there is of course trying to create my own `MigrationOperation`, and reading source code via Resharper downloading symbols and doing decompiling for me. However, this article is also a good source to learn about writing EF6 Migrations Operation:

EF6: Writing Your Own Code First Migration Operations



Generic SaveOrUpdate Method To Persist New/Dirty Entities With Entity Framework 4

In NHibernate there is a Save(entityObject) method, which creates a new row in the database with the given entity object, also, has an Update(entityObject) which updates the row corresponding to the entity object with the property values of this object. It also has a SaveOrUpdate(entityObject) method, which checks the whether the entity object corresponds to an existing row in the database, and chooses whether to call Save(…) or Update(…) based on that.

The way I usually do web applications across multiple tiers, when not using view models specifically, makes me encapsulate much code in Services layer that sometimes does not need to care about whether the given entity is persisted in database or not. Thus wanted to have similar method using Entity Framework as ORM.

Of course I have implemented the method number of times and the code evolved based on which version of Entity Framework I’m coding against, and my knowledge of the framework internals as well.  Actually, when you work with so many ORMs like I did, a new ORM or ORM version turns to only sound like “What’s new in the manual?” thing.

The Method

You must have heard about the new Entity Framework 4 Feature CTP 4.  I was playing with the example code of the Code First enhancements walkthrough, and writing some tweaks, I found myself implementing this function this way in the process ….

<span style="color: #071763;">using</span> System<span style="color: #23324e;">.</span>Data;

<span style="color: #071763;">using</span> System<span style="color: #23324e;">.</span>Data<span style="color: #23324e;">.</span>Objects;

<span style="color: #071763;">public</span> <span style="color: #071763;">static</span> <span style="color: #071763;">class</span> <span style="color: #006400;">ObjectContextExtensions</span>


    <span style="color: #071763;">public</span> <span style="color: #071763;">static</span> <span style="color: #071763;">void</span> SaveOrUpdate<span style="color: #23324e;"><</span>TEntity<span style="color: #23324e;">></span>

        (<span style="color: #071763;">this</span> <span style="color: #006400;">ObjectContext</span> context, TEntity entity)

        <span style="color: #071763;">where</span> TEntity : <span style="color: #071763;">class</span>


        <span style="color: #006400;">ObjectStateEntry</span> stateEntry <span style="color: #23324e;">=</span> <span style="color: #071763;">null</span>;

        context<span style="color: #23324e;">.</span>ObjectStateManager

            <span style="color: #23324e;">.</span>TryGetObjectStateEntry(entity, <span style="color: #071763;">out</span> stateEntry);

        <span style="color: #071763;">var</span> objectSet <span style="color: #23324e;">=</span> context<span style="color: #23324e;">.</span>CreateObjectSet<span style="color: #23324e;"><</span>TEntity<span style="color: #23324e;">></span>();

        <span style="color: #071763;">if</span> (stateEntry <span style="color: #23324e;">==</span> <span style="color: #071763;">null</span> <span style="color: #23324e;">||</span> stateEntry<span style="color: #23324e;">.</span>EntityKey<span style="color: #23324e;">.</span>IsTemporary)

            objectSet<span style="color: #23324e;">.</span>AddObject(entity);

        <span style="color: #071763;">else</span> <span style="color: #071763;">if</span> (stateEntry<span style="color: #23324e;">.</span>State <span style="color: #23324e;">==</span> <span style="color: #006400;">EntityState</span><span style="color: #23324e;">.</span>Detached)

            objectSet<span style="color: #23324e;">.</span>Attach(entity);



The method is implemented as an extension method to the ObjectContext class to call it on any context instance. Also, no namespace is there so that it’s available without having to add any using namespace … statement to fine it available.

Also, toy notice the method does NOT call context.SaveChanges(). This is on purpose so that the calling code is still in control when to submit the context changes, and makes the method similar in effect to other ObjectContext methods.

The Calling Code

A sample calling code to this method would be similar to below. This is not the code I used when testing it but the easiest to understand, as most people know the repository pattern already. Also, in many places there have been over-simplifications on purpose (no extra base classes or DI containers etc..)), but this is to serve the sample clarity purpose solely.

I’m assuming you have checked the walkthrough already, but if not, it’s enough to know that Book is a class that represents an entity, and BookCatalog is a class that inherits ObjectContext.

<span style="color: #071763;">namespace</span> EF<span style="color: #23324e;">.</span>CodeFirst<span style="color: #23324e;">.</span>Walkthrough


    <span style="background: #c8ffc8; color: #006400; font-weight: normal;">// Most likely this is going to inherit base class also</span>

    <span style="background: #c8ffc8; color: #006400; font-weight: normal;">// Not shown for simplification</span>

    <span style="color: #071763;">public</span> <span style="color: #071763;">class</span> <span style="color: #006400;">BookRepository</span> : <span style="color: #006400;">IRepository</span><span style="color: #23324e;"><</span><span style="color: #006400;">Book</span><span style="color: #23324e;">></span>


        <span style="color: #071763;">private</span> <span style="color: #006400;">BookCatalog</span> _objectCcontext;

        <span style="background: #c8ffc8; color: #006400; font-weight: normal;">//Set on top to highlight</span>

        <span style="color: #071763;">public</span> <span style="color: #071763;">void</span> SaveBook(<span style="color: #006400;">Book</span> book)


            _objectCcontext<span style="color: #23324e;">.</span>SaveOrUpdate(book);

            _objectCcontext<span style="color: #23324e;">.</span>SaveChanges();


        <span style="background: #c8ffc8; color: #006400; font-weight: normal;">// ..... Other parts of the repository</span>

        <span style="color: #071763;">public</span> BookRepository()


            <span style="background: #c8ffc8; color: #006400; font-weight: normal;">//In real example the BookCatalog constructor</span>

            <span style="background: #c8ffc8; color: #006400; font-weight: normal;">//  requires a connection, which is not to discuss here</span>

            _objectCcontext <span style="color: #23324e;">=</span> <span style="color: #071763;">new</span> <span style="color: #006400;">BookCatalog</span>();




Conclusion / Disclaimer

This method is not special at all. I just noticed that I have not posted code into my blog for very long, and wanted to change this by creating something as small as this one, hopefully to be followed by more advanced stuff that I should be taking out from my internal helper/toolkit libraries. I have stuff related to Entity Framework, NHibernate, Spark, ASP.NET, and others, but those are to come at another time…

Which ORM? LINQ To SQL, Entity Framework? LLBLGen? NHibernate?…?

While I was planning to write about the same topic and have the draft ready in my Windows Live Writer waiting to complete, I found an interesting question in StackOVerflow and couldn’t just resist to answer:

ORM/Persistence layer AdviceORM

The question starts with:

I’m starting a new project and I’m looking around for either a very good ORM or for a non-SQL-based persistence layer.

Then follows up with a REALLY GOOD summary of what he believes about each known ORM he knew out of his own findings and search. I advice you to go read it.

However, all this investigation didn’t get him to a single choice answer. And I can’t blame him. This is one fo the questions that will remain for so long without a single answer, or maybe having the popular “It depends” answer.

I have had a LONG research in this topic as well. I have read for so long (and watched videos/casts) to make sure of the best usage of many ORMs and then used them sometimes in test projects sometimes in production, and I wanted to share my thoughts based on this. I posted a long answer there on the question in StackOverflow, and I want to share this answer with you here. I may also have a second part of this post based on my existing Windows Live Writer draft, but, based on my previous times, I think I won’t!

Let me first quote some parts from the question itself:

I also want to avoid at all cost having to mess with string-based queries so tools supporting LINQ or otherwise intuitive and possibly strongly typed queries get a big bonus.
Finally working with POCO objects is another thing I’d really want to do
Here’s a list of products I’ve evaluated and why they don’t fit, just so that I don’t see any advice about using those:

  • NHibernate: crazy xml stuff, too much set up, high maintenance complexity and cost for model changes, session factories are messy and don’t fit well with my needs
  • Castle ActiveRecord: NHibernate based, little documentation plus some problems related to NHibernate still apply. Furthermore, to get decent models it takes so many attributes that one is better off creating the schema manually, and the way relations are handled is a shame.
  • Linq To SQL: missing POCO objects and according to MS it won’t improve much overtime (EF is what they’re committed to)
  • Entity Framweork: although in v4 POCO objects are possible, they’re still pretty hacky and force you into doing too much manual work to set things up. Besides, v4 is just a beta
  • LLBLGen Pro: good, especially with SelfServicing adapters, but not POCO. Also, the LINQ provider isn’t perfect yet. Finally, deleting a group of objects is not possible via LINQ which results in mixing APIs (one of which is far from intuitive) and that I don’t like.
  • SubSonic SimpleRepository: for a few minutes I thought I was dreaming. The deam came to an end as I figured out how the thing didn’t handle relationships
  • Here was my answer:

    If you can afford LLBLGen license, go for it.

    I seriously don’t like LINQ query-syntax the more I work with it (although I LOVE the language features related to it like Extension Methods and Expression Trees).

    I loved it at first like everybody else, but being uncertain whether [[ where employee.Name.StartsWith(“John Smit”) ]] in that XYZ LINQ provider will be done in SQL statement or in LINQ to Objects (after the SQL returns all results), and whether [[ user.Roles.Contains(role) ]] will at all work or not is a big step behind.

    LLBLGen can make deleting all items without loading them as easy as

    <code>MyEntityCollection.DeleteAll( new MyEntity {Condition = value} );</code>

    This is pretty simple and I like it. You get lazy loading and you set eager/deep loading by default and/or per query using Prefetch API. You can compose and construct dynamically (and easily) any filter/sort/loading at infinite levels. It’s very nice.

    There are only two problems about LLBLGen: first, it’s price not all companies would love to pay especially given the hype Microsoft alternatives have. Second, the naming convention although standard in RDBMS theories) like PredicateFactory instead of “where” or “filter” and Prefetch instead of deep loading and even SortExpression instead of orderby, those all are a little scary to a developer working with it for the first times, but soon you learn to love them, given the power and ease they give. There are talks about POCO support in LLBLGen 3.0. I cannot tell about it because I don’t know.

    Now given I no longer work in a company that uses LLBLGen, the company uses LINQ to SQL mainly because it’s “proven” in so many projects without big failures (unlike EF 1, which is lacking even LINQ features in LINQ to SQL and has very bad performance and can be quite limiting in advanced mapping – which it should have been best for!). This website StackOVerflow itself runs on top of it!!! I used both in this company (EF after L2S) and hated both. The decision for new projects remained LINQ to SQL, and doing all we can to overcome it’s limitations. You can work around it to do SEMI-POCO (you still need to use some L2S related types when it comes to associations).

    I also do some small projects at home. Since I nolonger have LLBLGen license, I decided to learn NHibernate and use it along with Fluent NHibernate and LINQ To NHibernate. I have learned through this that NHibernate is VERY strong. It changed how I work by some features like updating DB schema automatically (I never touched the DB almost when using it). LINQ provider (in NHibernate Contrib project) is quite lacking sometimes but there is the unreleased source code of NHibernate itself contains a better LINQ provider (haven’t tried it yet). The “Session” in NHibernate has problems when you are doing web development similar to those related to DataContext in L2S or ObjectContext in EF (LLBLGen doesn’t suffer from those thanks to self tracking entities).

    The biggest problems I had with NHibernate though was ability to find information. Too many pieces that should be put together in certain way and not much guidance can include advanced information for both mapping and querying. If not I had a friend (Tuna Toksoz , @tehlike on twitter) who happended to be a committer in NHibernate project source code, I’d really be in serious trouble.

    The moral I learned was: If you want something that just works and a bit basic use Linq To Sql or SubSonic, if you want something in the middle and your production environment can afford BETA .NET version (given golive exists) use Entity Framework 4.0, if you want something very powerful and can afford the hard learning process go to NHibernate, AND, BEST OF ALL, if you can afford LLBLGen, USE IT.

    Let me know your own thoughts on the topic.