NHibernate or LINQ to SQL?

[thj]

Hi,

I was wondering what you guys are using and why?

LINQ to SQL or NHibernate?

Thanks in advance,
Tommy

 

[Pavel Minaev]

Hi,

I was wondering what you guys are using and why?

LINQ to SQL or NHibernate?

Between those two, I'd definitely pick NHibernate. LINQ to SQL is not really
a proper ORM (and not advertised as such).

Now NHibernate vs Entity Framework is more interesting. The ORM part of
NHibernate is clearly more mature, but Entity Framework has LINQ support.

 

[thj]

I see. So whats the pros/cons of Entity Framework vs NHibernate?

Do you know of some describing articles on the subject?

 

[Pavel Minaev]

I see. So whats the pros/cons of Entity Framework vs NHibernate?

Do you know of some describing articles on the subject?

A quick Google search on "NHibernate vs Entity Framework" gives plenty of
hits. Here's one interesting article, which includes a couple of links to a
few more discussions as well:

http://www.infoq.com/news/2008/05/Entity-Framework-Sparks-a-Debate

 

[Peter Morris]

I use ECO from www.capableobjects.com - reasons

Dedicated UML modeler.
You can prototype your model/changes before generating/updating code.
You can define constraints using OCL expressions (Object constraint
language).
State machine diagram execution, including triggers, transition guards,
effects, entry/exit actions, nested state diagrams, and parallel states.
Lazy evaluation of calculated members. The result is then cached and
automatically invalidated if any of the values it depends on change (e.g.
when lastName = firstName + lastName) the cached value will only be
invalidated if firstname or lastname change.
Automatic DB generation / evolve, or optionally you can provide XML info to
map to an existing DB structure.
In memory transactions.
Able to specify 1 model and have it update multiple databases (employee db,
customer db, orders db, etc)
Able to re-use packages in multiple applications
Supports about 10 DB's natively. Switching to another DB takes less than 30
seconds. You can easily add your own DB implementation.
You may optionally implement a remote persistence server so that DB requests
go via a service, preventing you from having to expose your DB server. In
addition this allows you to enable a feature which lets you request a list
of elements changed by other users so that you may resolve conflicts without
first having to attempt a DB update. Also looks good when your on screen
data updates with changes made by other users, especially for statistics
pages.

I've been using ECO since about 2003, and its Win32 native predecessor since
2001, and I love it. It's feature list reads like a wish list. Seeing as
it has been around since about 1998 (in Win32 native form) you can rely on
the product being very mature. A few months ago I successfully released a
CRM application written in ECO for a company within the Imperial Tobacco
group, it took very little time to develop and is working very well!

PS: I wrote some of the docs for the product because I have been using it
for so long. I am recommending it because of how good I think it is, I will
not receive any financial benefit from you buying it




Pete

 

[Marc Scheuner]

I was wondering what you guys are using and why?

LINQ to SQL or NHibernate?

LINQ-to-SQL:

Pros:
* nicely integrated in VS 2008
* visual
* allows you to work with LINQ, simple

Cons:
* strictly SQL Server - nothing else
* failry straightforward one-table-to-one-class mapper - no means to
do mappings from several tables to a single object class (that's what
Entity Framework offers)
* currently no update functionality (if something changes, you need to
basic remove that table (and any dependants tables) and re-add it,
losing all your customizations you might have made (EF has update
physical model functions)


NHiberate on the other hand supports several database backends (SQL
Server, Oracle, MySQL etc.), is fairly mature and well established
(see www.summerofnhibernate.com for tutorial videos), but it does
require more knowledge and manually crafted (or generated and possibly
manually adapted) mapping files (no visual designer).


So for a simple app which will only ever run on SQL Server, I'd pick
LINQ-to-SQL for its simplicity, visual designer, and LINQ.

For a more complex, larger-scale, enterprise app, and especially if
you need multiple backends, it's NHibernate vs. Entity Framework.

Marc

 

[Alun Harford]

Between those two, I'd definitely pick NHibernate. LINQ to SQL is not really
a proper ORM (and not advertised as such).

Now NHibernate vs Entity Framework is more interesting. The ORM part of
NHibernate is clearly more mature, but Entity Framework has LINQ support.

NHibernate 2.1 now supports LINQ, although that does pretty much mean
checking out and building the trunk. For previous versions, you can use
Ayende's Linq for NHibernate.

Alun Harford

 

[Frans Bouma [C# MVP]]

Hi,

I was wondering what you guys are using and why?

LINQ to SQL or NHibernate?

How are these two comparable? I mean, linq to sql has a great linq
provider, nhibernate doesn't, but for the rest it means that linq to sql
has a lot of catching up to do (which won't happen, since the
maintainers are... the EF team!)

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

 

[Frans Bouma [C# MVP]]

NHibernate 2.1 now supports LINQ, although that does pretty much mean
checking out and building the trunk. For previous versions, you can use
Ayende's Linq for NHibernate.

I wouldn't call that a linq provider. Linq providers have the
disadvantage that you have to cover EVERY situation, otherwise the
developer can't use linq at all, as you can't mix and match native query
APIs with linq to form a single query. Therefore, a non-mature linq
provider (and nhibernate's linq provider is far from finished), is
totally useless, even though it might mean it can do a couple of from
where select queries.

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

 

[Pavel Minaev]

I wouldn't call that a linq provider. Linq providers have the disadvantage
that you have to cover EVERY situation, otherwise the developer can't use
linq at all, as you can't mix and match native query APIs with linq to
form a single query.

Is there even a LINQ IQueryable provider which can handle absolutely
anything I could throw at plain IEnumerable - arbitrary .NET method calls
etc - and give me the exact same semantics (sans unexpected errors such as
dropped connections, server down, etc)?

It was my impression so far that it's not possible to write advanced LINQ
queries in a generic fashion, because you always have to keep in mind the
limitations of the LINQ provider you're querying against (such as "what
methods of String are supported?" etc).

Therefore, a non-mature linq provider (and nhibernate's linq provider is
far from finished), is totally useless, even though it might mean it can
do a couple of from where select queries.

For one thing, if it provides static typing for otherwise typical
join-where-select, it's immediately quite useful.

 

[Frans Bouma [C# MVP]]

Is there even a LINQ IQueryable provider which can handle absolutely
anything I could throw at plain IEnumerable - arbitrary .NET method calls
etc - and give me the exact same semantics (sans unexpected errors such as
dropped connections, server down, etc)?

That's not the point. The point is that many 'linq providers' can't
even do decent joins, group joins, left joins etc. If you take one step
further than the default from where select, they often break.

It was my impression so far that it's not possible to write advanced LINQ
queries in a generic fashion, because you always have to keep in mind the
limitations of the LINQ provider you're querying against (such as "what
methods of String are supported?" etc).

Of course not. A decent linq provider would offer you a facility to map
methods which aren't mapped by default to db constructs yourself without
a lot of effort.

For one thing, if it provides static typing for otherwise typical
join-where-select, it's immediately quite useful.

for an hour... sure. Then you are faced with more complex queries and
you first run into errors, then wonder if it's you, then lose another
hour trying to write workarounds and then you give up and fall back onto
the native api which is often using string-based queries...

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

 

[Pavel Minaev]

That's not the point. The point is that many 'linq providers' can't even
do decent joins, group joins, left joins etc. If you take one step further
than the default from where select, they often break.

Does NHibernate LINQ provider have the flaws you mentioned?

In general, I would agree that a full-featured provider should support joins
(both equi- and generic) and group joins.

Of course not. A decent linq provider would offer you a facility to map
methods which aren't mapped by default to db constructs yourself without a
lot of effort.

How does any such mapping facility helps? Let's say I write a method:

void Foo<T>(IQueryable<T> q);

As an author of the method, I don't know what is given to me, so I can't use
any provider-specific facilities for mapping. As a user of the method, I
treat it as a black box, and so I don't know what kind of method calls it
might make.

for an hour... sure. Then you are faced with more complex queries and you
first run into errors, then wonder if it's you, then lose another hour
trying to write workarounds and then you give up and fall back onto the
native api which is often using string-based queries...

It depends on the API. For example, I wouldn't really expect joins from a
SharePoint LINQ provider, because the underlying query language simply
doesn't support them. So long as I know its limitations - and I do - static
typing is a clear benefit.

 

[Frans Bouma [C# MVP]]

Does NHibernate LINQ provider have the flaws you mentioned?

last time I checked it couldn't do a groupjoin for example (join ..
into x )

It's not that hard to get things up and running with a linq provider so
you can parse simpler queries. The fun begins when you have to wrap
parts of the query in their own queries, their own scope and have to
shuffle the things around. Then it becomes complex very quickly.

How does any such mapping facility helps? Let's say I write a method:

void Foo<T>(IQueryable<T> q);

As an author of the method, I don't know what is given to me, so I can't use
any provider-specific facilities for mapping. As a user of the method, I
treat it as a black box, and so I don't know what kind of method calls it
might make.

If you do:
var q = from c in md.Customer
where MyMethods.SomeMethod(c.CompanyName)
select c;

the call to SomeMethod is in the expression tree. 'md' here is an
IQueryable which contains the provider. So before this query, I can pass
a mapping construct which maps SomeMethod(string) to a db construct and
returns a bool. (CASE construct)

When the provider sees the method call, it checks its mappings to see
if there's a known mapping. If you passed such a mapping, it's in that
list, so the provider will then use the db construct provided to handle
the SomeMethod methodcall. As you gave the example of 'what methods of
String are supported'... this thus means: the ones mapped by the
provider + the ones you map yourself.

This is very powerful, and essential for a linq provider. Of course,
not only methods can be mapped, also properties.

What to do if there's no mapping? This is something which shows that
things aren't straight forward: it depends on where the call is . if
it's in the outer projection, the whole call etc. can be threated as a
piece of .NET code. Say you have this:

var q = from c in md.Customer
select new { CompanyName = MyMethods.SomeMethod(c.CompanyName),
...
};

here, MyMethods.SomeMethod, doesn't have to have a mapping and still
would make the query runnable on the DB. Here, the companyname is simply
fetched from the DB, and then passed to a compiled form of a call to
MyMethods.SomeMethod(string), and the result is used as the value for
the projection. This is valid because it all happens in memory anyway.

The thing is that in other places, such a method without a mapping
won't work and you can't use a delegate. Also, to find all fields inside
these calls isn't straightforward (you need a separate visitor).

Like I said: creating a from-where-select provider... that's not that
hard, adding the rest is ...

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

 

[Pavel Minaev]

If you do:
var q = from c in md.Customer
where MyMethods.SomeMethod(c.CompanyName)
select c;

the call to SomeMethod is in the expression tree. 'md' here is an
IQueryable which contains the provider. So before this query, I can pass a
mapping construct which maps SomeMethod(string) to a db construct and
returns a bool. (CASE construct)

I think you've missed my point. Foo<T> is a part of a reusable library.
Inside, it contains the LINQ query you gave, but it has no way to map
SomeMethod (or even know that it should be mapped). Outside, the client of
the library shouldn't have to second-guess what methods are used by
implementation of Foo<T>, and should be mapped as needed.

In short, it's very hard to deal with IQueryable<T> in a generic way even
with full-featured LINQ providers. Therefore, I question the assertion that
there is some significant leap between limited and full-featured providers -
if you can't use either one in a generic fashion in encapsulated code,
without minding the limitations of a specific provider (which kills the
genericity right on the spot), what's the real difference, aside from the
actual number of limitations?

 

[Frans Bouma [C# MVP]]

I think you've missed my point. Foo<T> is a part of a reusable library.
Inside, it contains the LINQ query you gave, but it has no way to map
SomeMethod (or even know that it should be mapped). Outside, the client of
the library shouldn't have to second-guess what methods are used by
implementation of Foo<T>, and should be mapped as needed.

In that case, it will never work, or better: never use a .NET
method/property in your additional extension method calls. I mean, even
an entity class E with 10 properties might cause problems because
property Bar is a property without a mapping in the DB...

i.o.w.: don't use Linq this way. You want encapsulation? Sure, but
encapsulate it properly. If you want to append predicates to an existing
queryable outside of the scope where the queryable was created, make
sure that appending is done at the same abstraction level, otherwise use
the specification pattern.

the thing is that you THINK you abstracted away everything, but that's
a fallacy. You clearly assume that the IQueryable q is adjustable with
whatever you throw at it. This is of course wrong: the IQueryable might
us an IQueryable datasource D and Foo() might add elements using
IQueryable datasource E, though RUNNING the query, i.e. enumerating it,
will use a single linq provider, not two. Which one? Can you tell
without looking in how Linq providers are written? No. Will it work? No
way: if D is Linq to Sql and E is Linq to NHibernate, your query will
never run. Yes it's a silly example, but it illustrates the point that
abstracting things away for the purpose of "Now I can do whatever I want
in a generic way" requires a different approach than the low-level
approach you chose, for the simple reason that an IQueryable isn't a
query specification (I wished it was) but a query specification AND a
query result, created from the already embedded provider.

In short, it's very hard to deal with IQueryable<T> in a generic way even
with full-featured LINQ providers. Therefore, I question the assertion that
there is some significant leap between limited and full-featured providers -
if you can't use either one in a generic fashion in encapsulated code,
without minding the limitations of a specific provider (which kills the
genericity right on the spot), what's the real difference, aside from the
actual number of limitations?

Try to write normal queries for a day or two without running into
problems that you have to fall back to native query api's. Sure, simple
queries might work (although most simple providers fail on simple group
joins which result is used further in the query) but simple things
always work, it's in the queries which aren't simple but will make you
fall back to native query api's because the linq provider failed, and
thus make the usage of a linq provider quite unnecessary as well,
because simple linq queries are also simple in most native query api's.

example:
var q = from c in ctx.Customer
group c by c.City into g
select g;

result: a hierachical structure of IGrouping<string, Customer> objects,
grouped by key 'City'. VERY handy in a lot of situations. 2 queries
tops. I can assure you, doing this in a native query api of any o/r
mapper isn't as simple as this. That's why linq providers need to be
solid, and full features to be useful in every day work: the developer
using the provider is payed to write code WITH linq to solve the
client's problems, not to fight with an O/R mapper's Linq provider and
first wondering if the developer made an error in the linq query, then
fall back onto native query api syntaxis.

But of course, it's your time and money

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------