#SQLite Database #NuGet Package: Common Problems Solved



SQLite is a file based database, or an embedded database that you can use without need for any special installation from your side. It’s a bit popular for non-LOB (line of business) desktop applications and even mobile applications like Android apps. It also has an "In-Memory" mode where there is no physical file used as a database, which is usually used in testing.

It has a similar syntax (sort of) to SQL Server. In Microsoft .NET world,, those using NHibernate are usually using the in-memory mode for applying unit testing or trying out NHibernate stuff, although Microsoft has its own embedded database called SQL Compact Edition (CE), which doesn’t seem to support in-memory databases (v4.0).


You can download the latest Sqlite database (v3.0) from:


Since it’s not SQL Server, you need a special ADO.NET provider too. It’s called "System.Data.Sqlite"., which is very similar to the normal System.Data.SqlClient stuff. It’s included in the above download link.



If you don’t know what NuGet is, go to www.nuget.org or check my old posts mentioning it, or if you are an Arab maybe check this post in Arabic.

NuGet has become the No. 1 way I use to find libraries, and I usually really get mad if I don’t find the library in there or I have to get a more recent version from library source code, which luckily doesn’t happen that often.

Today I discovered that NuGet already has a NuGet package:



I was willing to make some test to what I believed was a bug in NHibernate 3.2, but this is another story.



I thought (like most other NuGet packages) the package will do configuration needed to up and running with SQLite, but I found that I was still getting different configuration issues.

Here is the basic code I was trying to get to work:



All the code in NHibernate code (even the dialect thing). Don’t bother yourself with it if you don’t know NHibenrate. I just wanted to show the exact path I went into when facing this.

You do NOT need NHibernate to use SQLite, you can use it just like SQL Server under System.Data.Sqlite same as you use System.Data.SqlClient for SQL Server. I’m just covering usual use of it and part of my actual real situation.

The only relevant SQLite part is the connection string, which, as this is for testing, was using in-memory database.


Configuration: Problems & Solutions


I have updated the NuGet package to include the required config entries.

This configuration section is now obsolete and for reference only.


It should be helpful though if you download the SQLite library manually not from NuGet or modified your configuration after installing the package.


The updated packages start from version




When I tried to run this, I got the following error:

Unhandled Exception: NHibernate.HibernateException: Could not create the driver from NHibernate.Driver.SQLite20Driver. --->

System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. --->

System.ArgumentException: Unable to find the requested .Net Framework Data Provider.  It may not be installed.

The first 2 lines are NHibernate thing as I know (don’t bother the "2" part in there, just because I tell you not to). The following 2 lines are just generic stuff.

The last two are what really matter, specifically:

Unable to find the requested .Net Framework Data Provider. It may not be installed.

SQLite has a similar ADO.NET provider like SqlClient for MS SQL Server, we know we have it, but .NET is unable to find it.

To Solve this problem, I created an app.config file (if it’s a website, you’ll use web.config), and I added the following directly under <Configuration> root node:



And then tried to run again…

This above problem "might" happen with the download file included in the website directly also, not only the NuGet package.


Mixed Mode Assembly

Next time the Console had the following errors

Unhandled Exception: NHibernate.HibernateException: Could not create the driver from NHibernate.Driver.SQLite20Driver. --->

System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. --->

System.IO.FileLoadException: Mixed mode assembly is built against version 'v2.0.50727' of the runtime and cannot be loaded in the 4.0 runtime without additional configuration information.

As usual the important bit is the last bit:

System.IO.FileLoadException: Mixed mode assembly is built against version 'v2.0.50727' of the runtime and cannot be loaded in the 4.0 runtime without additional configuration information.

To understand what’s happening, let’s read the SQLite package description in NuGet:

System.Data.SQLite is the original SQLite database engine and a complete ADO.NET 2.0/3.5 provider all rolled into a single mixed mode assembly. It is a complete drop-in replacement for the original sqlite3.dll (you can even rename it to sqlite3.dll if you’re using it natively).

OK, now we know where this "mixed mode assembly" thing is coming from. It’s an assembly that merges both native and .NET assemblies. To be honest I didn’t even know this was possible before. One more thing learned, now time to see how to get this working in .NET 4.0 – I am expecting it’s strongly attached to the version of the .NET part of the assembly.


As you expect, those kinds of things are solved in configuration also. Here is the related configuration part, also added directly under <configuration> node in app.config:



That was all. It did work afterwards.


Bonus: Using SQLite In Web / ASP.NET On Windows x64 bit


I was scared of the "<startup>" tag that it may not work nicely with ASP.NET, so, I tested the configuration and NHibernate-based test in a new web application and it worked nicely in VS 2010 and IIS.

The Problem

However, the x86 (32 bit) of SQLite (which is the one on NuGet) does not work in IIS on Windows x64 (63 bit) directly. You must enable 32-bit in the application pool.

The error:

Could not load file or assembly 'System.Data.SQLite' or one of its dependencies. An attempt was made to load a program with an incorrect format.


The Solution

As mentioned, you need to set the app pool to support 3-bit assemblies, this, go to IIS, open Application pools:


Right click the application pool you use in your website and pick "Advanced Settings…"


Then change the 32-bit setting…


And now you are done.


On a shared hosting they should be taking care of this already although I’m not sure if you’ll have more severe issues with shared hosting supporting only medium trust security. This always brings problems.


The complete Standard SQLite Configuration

For convenience, here is my complete app.config file:with required config sections.

As mentioned above, you can use those sections in any app.config or web.config files.



I intestinally added the connection string, just to show you how you should be able to do it. Of course the connection string name is up to you and you can choose a file instead of in-memory and I haven’t covered anything about SQLite really. Gogle is your friend if you are interested in more, and you can ask me also as I "might" know!


The Code On Github


As usual, you can browse or download or clone the almost-empty sample application used here from:


If you just want to download it locally I highly advise you get "git" source control and clone the code, however, if you insist on something for direct download, here it is:


I hope this has been helpful to someone trying to get started with this NuGet package.

Mapping José Romaniello’s Review Domain Using FluentNHibernate

This article is the third in a series of articles not written by me, but by José F. Romaniello. He is a big NHibernate guy, so, he created a sample domain trying to evaluate how close the latest Entity Framework 4.1 Code First stuff is getting to NHibernate features,

Later, he chose to show how to do the same Code-First mapping using NHibernate and confORM, NHibernate mapping library that is created by Fabio Maulo, a primary developer in NHibernate source code.


I asked him to do it also with FluentNHibernate, so, he took the time and effort to create a nicely put visual studio solution. at some point he gave it to me as I was familiar with FluentNHibernate in general, not with automapping, which we wanted to use for this sample. so, I am now posting about this experiment.

This article’s very late than it should. Apologies to those who have been waiting.

Convention Based Mapping, AKA, Automapping

My audience is slightly different than Jose, I might need to explain this one. skip if not needed.

When you do Code-First (or Domain First with POCOs, or your favourite name), normally you need to define the mapping between you classes and DB tables class-by-class. In each class you define which table(s) it maps to and which properties correspond to which columns, etc..

Your mapping library, being Entity Framework 4.1 Code-First bits, NHibernate XML/HBM mapping bits, confORM, FluentNHibernate, etc… can give you some help with that by having some defaults for mappings. For example, assuming the table name is the same as the class name, properties map to columns with the same name, assuming your class a single column PK and is called “Id” or “<ClassName>Id’, etc..

Of course they might also allow you to modify those defaults to your likings, and to have exceptions to specific classes/columns to those defaults (conventions). The purpose still is to need as few such customizations as possible.

This is what was first introduced in FluentNHibernate as Automapping, and is also starting to get known as convention based mapping while other libraries apply it.

The Domain

Let me steal borrow the class diagram Jose had for his test/review domain:


The Libraries

All needed to do was a NuGet command that brings FluentNHibernate and all its dependencies:


Remember, you don’t need to do it from the PowerShell console, right click the project and “Add Project Library Dependency” and finding the FluentNHibernate library in the wizard (select “Online” from the left and type FluentNHibernate in the top right text box) will simply do.

I did this just to show the dependencies easier in the picture. (note also I already had this in my project, so, created a new project “CleanFNhSample” just to show you this).

There is another dependency that i needed, not just because Jose used (and created)., but also because it’s really convenient also. NHibernate by default uses custom types for collections of type “set”, because those were not supported before .NET 4.0.. Jose has a code file NuGet package that makes using the built-in .NET 4.0 HashSet type instead of the custom type possible. it’s called “NHibernate.SetForNet4”:


Like the other packages, I could have added this from console.  just showing you both.

The Application Code

I’ll be showing snippets of the code here, then later giving you a GitHub URL to play with it as you wish Smile.

The main test code Jose created uses the domain classes in the diagram above is pretty simple. It creates a new NHibernate (NH) configuration (including class mappings to database), uses the mappings to create schema in the database (replacing anything that might exist, yes. This is not the only option), and creates an NH session (equivalent to EF DataContext if I over-simplify it), creates some records, and tries to save them to DB.

Now before we get into “ConfigureNHibernate()” method which is mainly the most important method here, let’s look at some other classes…

Project Structure

This is how my project looks right now:


The only difference from Jose’s confORM review and even his original FluentNHibernate work is the “Mapping” folder. While the few ConfORM samples used to have different methods in the same file to do the mappings, FluentNHibernate people seem to prefer (or I think they do) having a different class per part of the mapping. Of course you can still have those classes in the same file and no one would even complain!


So, there are 3 FluentNHibernate classes: in there. We’ll see why those are needed after we look at the main configuration. Note that not all this configuration is required as we were trying to meet specific requirements that were met in confORM demo.

Main Configuration

Each part exists for a specific purpose that in mentioned in the comments above it.

I hope the comments are enough. We need to set some mapping rules in StoreConfiguration class, apply some non-default standards for our collections in CollectionConvention class, and some exceptions for a field of Order entity in the OrderOverride class.

I know you might be worried about the big method, and the fluent API that makes the whole thing is one statement. From my experience, this makes debugging each part of the statement that might fail a problem, but this is not the case here, because, NH configuration / mapping problems only show when you build configuration or create session factory anyway. It doesn’t fail on the individual steps. But don’t worry about that too, usually (but not all the time), when you navigate the InnerException (sometimes recursively through multiple inner exceptions), you get specific information what part was wrong and maybe what was wrong about it too.


FluentNHibernate Classes

The way you write conventions, overrides, and other kinds of FluentNHibernate stuff is by creating classes that implement certain interfaces or inherit from certain base classes. There are so many interfaces provided but you only need to implement the ones that allow you to provide other defaults than FluentNHibernate own defaults. Also, a lot of the features in the classes could have been added to configuration method, but you don’t want it to grow even bigger, at least not for this explanation sample!


StoreConfiguration Class

In this class we do not implement an interface, instead, we derive from the default mappings class, which defines all defaults in automapping. This one was originally created by Jose and for myself I’m not sure whether the typical use is by having such a class or doing it directly in configuration.

What this method does is specify which classes represent DB entities and should be mapped automatically. It tells FNH to map all types in the EntityBase class namespace, except Enums and except EntityBase itself.


If I understand correctly, the part that we should have needed is only the namespace part. in “ShouldMap” and the “IsConcreteBaseType” (which basically tells FNH to not try to create separate table for EntityBase and make all other tables reference it for their PK. Instead, it should treat EntityBase as part of its children classes themselves as if the “Id” property was implemented in each of the classes not in their base class). I believe we shouldn’t have needed to exclude the enum explicitly (if it’s there the configuration is invalid, not some easier effect like having a table for enum values).


CollectionConvention Class

As mentioned, conventions are applied by implementing I….Convention interfaces, and there are many of them to override whatever defaults you might want. In this example, if we wanted our collection convention to only subset of collections we could have also implemented “ICollectionConventionAcceptance”. You can implement as many I<SomeCriteria>Convention and <SomeCriteria>ConventionAcceptance in the same class as you like.


Here we tell NHibernate to apply cascading to all (you can only restrict it to updates or deletes or none, check NH docs). We also tell FNH that all collections we use will be of type “set”.  This is different from what we had in the configuration because the configuration part tells NHibernate what actual type to use when we want our collections to be sets, and that’s why it’s not per-class or per-collection (note the conventions are like mappings for each collection but done all at once), The convention here makes NHibernate understand to treat it as “set” and use the Collection Factory we provided in the configuration for dealing with sets.


Also note that usually I had to add extra code to check if the collection is collection of string or int or similar non-entity type and have the instance.Name(“Value”) or else it doesn’t work. Just while writing this article I tried it and found that I didn’t need to add it anymore (works without it normally).


OrderOverride Class

If you have ever worked with normal ClassMap<TEntity> in FluentNHibernate, this one is going to be familiar. Having an interface instead of base class, and using an “Override” method instead of constructor, and having to use the parameter mapping (or whatever you call it) instead of calling “Map” methods directly are the only differences.

If you are not, then simply those overrides classes places to add mapping for the specific classes and properties that have special cases, without having conventions that apply to many other classes at the same time. Here we had a read only property in the Order class and we wanted to set its access (property, field, none, …) to read only. Jose and I thought it might be good example of overrides. This is also the place if you use special SQL formula for one specific property or so.


NuGet And Resources

Now, as promised, you can have the code on GitHub so that you can clone, fork, or simply download it and have a look.

The Code URL is:


I’d highly recommend you not only browse the code online, but also use TourtoiseGit or whatever client you may feel friendly (including console) to bring the code locally and start playing with it

However, if you insist on just old-school download-the-code style, no problem, here is the direct download link:


Sorry for the length of the post and I hope it wasn’t a show stopper that made you just skip to end.


For context, here are some links that were mentioned during the post:

#NHibernate 3.1.0 on #NuGet – Important Details

If you don’t know what is NuGet, please skip to the end first

NHibernate Package

For some time NHibernate 3.0 package on Nuget was listed as owned by Fabio Maulo (@fabiomaulo), a main comitter to NHibernate, but was not owned by him until NHibernate 3.1 came out and there was a need to upgrade the package, which happened today.


You can find NHibernate package on Nuget by searching for the word NHibernate in the gallery, or going to the package page directly on :


Do Not Install This Package Directly

However, in this page (or summary pane in Nuget tool in Visual Studio when you select the package, you’ll read:

DONT INSTALL IT DIRECTLY Only as reference for others frameworks. NHibernate is a mature, open source object-relational mapper for the .NET framework. It’s actively developed , fully featured and used in thousands of successful projects.

Why Is That?

The reason for that is that NHibernate requires using something called a proxy factory. This is the tool that creates proxies of your entities (usually in runtime) for things like Lazy Loading to work. NHibernate has built-in support for those proxy factories, Castle Dynamic Proxy, .LinFu Dynamic Proxy, Spring AOP Framework.

So, What Should I Install ?

What you should actually do is to choose whether you want to use the Castle, LinFu, or Spring proxies, and based on that, install one (and –typically- only one) of the following packages:


NHibernate.Castle –


NHibernate.LinFu –


NHibernate.Spring –



Those packages all created by Fabio as well and depend on the main package. I will not give any differentiation between them and/or when to use them, but maybe if you use FluentNHibernate, you’ll want to use Castle Dynamic Proxy as this is their default.


FluentNHibernate/ConfORM & Other Libraries Depending on NHibernate

The way NHibernate package works from now on will make it a bit confusing to get other packages. For example, if you want to get FluentNHibernate / ConfORM, you will find that it explicitly depends on NHibernate package, but in the same time you are likely to need to install any of the other proxy-specific packages which is not set in the package dependencies.

The creator of FluentNHibernate may decide to depend on a proxy specific package instead, but this will be a problem if another NHibernate-dependent library may decide to depend on another proxy-specific package and it becomes a mess. So, I guess library creators will better not do that and we’ll need to live with the non-explicit dependency that is still likely required.



There is a really nice article on how to install for how to install NHibernate 3.0, and Windsor both from NuGet on ASP.NET MVC projects, The same rules though apply to NHibernate 3.1 and other types of projects than ASP.NET MVC.’

Create an ASP.NET MVC application with Windsor and NHibernate at the speed of nuget

Other than that, there is something you need to be careful of, assembly versions:


Installing Libraries That Depend on Specific Earlier Version of NHibernate|

Covers FLuentNHibenrate

Let’s take FluentNHibernate a an obvious example. FluentNHibernate package on NuGet is set to depend on a pre-release version of NHibernate 3.0. Not much changed in the FluentNHibernate library since then that will make you worry, so, to install it you need to do the following:

  1. Install one of the proxy packages, preferably NHibernate.Castle since this is the default in FluentNHibernate
  2. Install FluentNHibernate (don’t install it first so that it doesn’t just install old version of NH package as dependency)
  3. Apply Assembly Binding Redirect (see below)


Assembly Binding Redirect

Assembly Redirect is a way in .NET that you can use if you reference an assembly with specific version , and you want to use a different version of this assembly without recompiling your code or anything, you can tell .NET to redirect any request for the old version of the assembly to the new one. This is exactly what FluentNHibernate and any library that references specific old version of NHibernate need to do.

God news is, NuGet can setup the correct assembly redirects for you. In the current version of NuGet you need to explicitly call this functionality though. Next version of NuGet will run it automatically when you install a package.

Here is how to do it:


Make sure you build your solution once after adding the packages and before you do this, or else, the NuGet command will not do anything!

From VS 2010, Go to tools –>


The NuGet consolse will show up – Make sure the project you installed NHibernate and FluentNHibernate to is the one selected in the console:


Then type “Add-“ (no quotes) and press TAB, you’ll get intellisense, choose from there “Add-BindingRedirect” (no quotes) and press ENTER.


This should get you the following result (If not, try to build your project and do it again):


Now, you should find you application configuration file (web.config for web projects, app.config otherwise) having a similar section to this one (NuGet will create the file if not exists):


This is the assembly redirection that should allow things to work. Try it!


Other Notes


What Libraries on Nuget are updated to support NHibernate 3.1?

By the time of writing, the only package that supports NHibenrate 3.1 package is ConfORM (also created by Fabio

ConfOrm –



What is NHibernate.Linq Package?

There is another package on NuGet called NHibernate.Linq. Do NOT install this package is you work with NHibernate 3.x. This is the old LINQ provider that is created for NHibernate 2.1, and is no longer maintained.

LINQ support is now built into NHibenrate itself, not any separate package.


Boring Introductions Pushed To The End

If you feel lost going through this post, check out the below questions. If you still have confusion, just leave a comment with your question.


What is there in NHibernate 3.1.0?

Starting NHibernate 3.0.400, NHibernate ORM had interesting features like LINQ support and new query API called QueryOver, as long as hooks for writing entity mapping by code (as used in another library called ConfORM).

NHibernate 3.1.0 adds more LINQ support for more use cases, and adds more bug fixes and improvements on the same paths as 3.0.


What is NuGet?

If you know Ruby/Rails, it’s enough to say it’s Microsoft equivalent of Ruby Gems. Since few of my readers are Ruby guys. I’d explain this by saying it’s a tool that allows you to get software libraries (like NHibernate, Log4Net, Entity Framework 4 Code First CTPs, Castle Windsor etc..) from central repository (feed) where they are published. It allows you to get the library and all its dependencies (for example, NHibernate depends on Iesi.Collections) at one time without having to worry what those are. Each library is expressed by a package, where the package creator can include the library as code or binary and define library version and dependencies of other libraries/packages, and you just point your Visual Studio at the package and get them all, and easily update to future versions later.

It also comes with a PowerShell console inside Visual Studio that allows you to run those commands via scripts and do some interesting things like have libraries that support different versions of dependencies than the latest versions to support those latest versions by assembly redirection.

You can learn more about NuGet by going to


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 ….

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.

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…

Thank you very much for attending CairoCodeCamp`10 – Session Slides

My first DotNetwork Cairo Code Camp in 2009 was a great experience, because it was one of the biggest events I’ve spoken at (in terms of audience count, similar to SilverKey Demo Day II). However, Cairo Code Camp 10 had a much different taste!


Not just that I met Scott Hanselman, one of the most popular Microsoft guys, and hold him down as you see in the picture (which I’ll never forget), the great person and popular guru…


Not just that I met so many great other speakers, many of them are my friends and some of my friends talking for the first time in such event or after long pause…


But also because we all (speakers and organizers – both volunteers) met so many great attendees. Some of them were interesting people I’ve followed on Twitter for some time and haven’t seen them yet. Some of them were old lovely faces that we meet very rarely (especially I no longer work  in Egypt). Some of them were totally new faces, bringing a lot of ideas and very useful discussions….

Thanks a lot everybody. You made Cairo Code Camp 10 the sweetest speaking experience yet for me. I really hope you had as much fun.

Give Me Those Slides

Now to the part you are probably reading this for! I promised the great guys who asked that the slides will be up on this blog in 3 days maximum after the event closing although probably they’ll be available by DotNEtwork pretty soon. So here they’re below.

It’s really sad we couldn’t get recording this event also. You can’t imagine how hard the setup for this can be. I may consider next time to buy an extra microphone and record the talk as a webcast, but usually things are not that easy.

Using jQuery To Survive In ASP.NET Webforms World

here are the slides hosted on SlideShare:

Session Downloads:

Download: Using jQuery To Survive In ASP.NET Webforms World.pptx

Download: Using jQuery To Survive In ASP.NET Webforms World.Demos.zip

It’s interesting how we went through jQuery all from beginning. I’m so happy the goal from this session was achieved, which is to show that jQuery can does regular tasks we see in many websites without much of the pain we picture in our heads once we think about JavaScript.

Introduction To ORM Patterns With Code Samples In NHibernate

Here are the slides hosted on SlideShare:

Session Downloads:

Download: Introduction to ORM Patterns with Code Samples in NHibernate.pptx

This session had three goals, one is to open minds about patterns and practices and choices instead of follows, which was accomplished. the second goal was to introduce some common ORM patterns, which was  partially accomplished. The 3rd goal was introducing NHibernate, which we didn’t have time to since the open talk was getting much more benefiting information.

I hope most of you liked how this went, and for the rest and everybody, would you like me to refer to existing NHibernate resources that I find most useful? (most of them are in the last slide in presentation), or create my own article on it? Or Screencast? And if so, would you like them to be in English or Arabic? (which reserving terms in English of course).

Tell me and I’ll work on something…

Thanks to all of you…

This event would never have been so great without the participation of every organizer, speaker and attendee. You guys created all the great atmosphere that I will be missing probably until the next big event – only if it has you or people as great as you were. Thank you very much for everything.

Related Resources

Usually the twitter accounts are the most updated of course (maybe more than you ask for sometimes), and the facebook group/page profiles are used for emailing members/fans with the more important news. By the way, my twitter username is @Meligy.

Thanks a  lot.

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

    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.