What effect does MARS have on NHibernate?

Question

I'm comparing Entity Framework with NHibernate, and I'd like to know if when using SQL Server, what effect (if any) would enabling or disabling MARS support have on NHibernate?

MARS = Multiple Active Result Sets

The Entity Framwork documentation states the following:

When you call the Load method during a foreach (C#) or For Each (Visual Basic) enumeration, the Entity Framework tries to open a new data reader. This operation will fail unless you have enabled multiple active results sets by specifying multipleactiveresultsets=true in the connection string. For more information, see Using Multiple Active Result Sets (MARS) on MSDN. You can also load the result of the query into a List collection, which closes the data reader and enables you to enumerate over the collection to load referenced entities.

Does NHibernate has the same issue?

Additional information when connecting to SQL Azure

Sanatorium

Same as MOON (Multiple Object Oriented Network :) -- sorry, couldn't resist. – Malvoisie

Answer 1

The issue you're referring to is linked to "server side cursors", and as far as I know of nHibernate this shouldn't be an issue, simply because it don't use them.
If you're using LINQ to load objects in nHibernate, on the first access to the foreach enumeration, nHibernate load in memory the whole resultset of the query, and this way it can use the session's connection to load everything else. When using HQL query or Criteria, it will load the resultset when you call "List()" then it close the connection.

Entity framework on the flip side, try to be smart and make use of server side cursors when scrolling a collection via a foreach enumeration, so the objectContext's connection is "busy" with the server side cursor until the foreach enumeration is ended. If MARS is not enabled, EF can't use the connection to load another resultset (you can still issue other statements such update, insert and delete) and thus it will give you an error like "There is already an open DataReader associated with this Command which must be closed first" or something similar.

Hope this helps,
Marco
EDIT:
After some research I've found that nHibernate could use MARS, but still in ver 3.2.0.4000 there's no driver for SqlServer that supports it. Of course in the SqlClientDriver is not supported (as it is inteded for Sql2000 that has no support for MARS) but even in the Sql2008ClientDriver the relevant property is set to false. Anyway this is something I'll post to the nHibernate team as soon as possible

Bar

So then does NH not care about MARS at all? What if you try to lazy-load another entity while enumerating an IQueryable result set? Wouldn't that require MARS? Perhaps NH has some way around this, or just doesn't support it? – Sanatorium

Well, I'm tempted to but I can't say that. I've taken a look at nHibernate source and found out that the nHibernate could potentially use MARS. The SqlClientBatchingBatcher is derived from AbstractBatcher which in turn check if "_factory.ConnectionProvider.Driver.SupportsMultipleOpenReaders" then it use an implementaion of IDataReader that make use of MARS (as far as I know just for SQLServer and Oracle). So nHibernate is aware of MARS and looking at the cose seems able to use it for batching, if it is available, however I haven't tried it so far. If you 'll do some test on it, let us know – Bar

Thanks for looking. I'll do some testing at some point. The concern came up because we are going to (eventually) be using SQL Azure with Federations, and it doesn't support MARS. We chose NHibernate for other reasons, but I'm working on the assumption that it does not require MARS and will still work when not enabled. I'll award you the bounty since yours was the only answer and was at least detailed enough to point me in the right direction. Thanks. – Sanatorium

Glad to be helpful, glad for the points and... Glad you choose nHibernate! I've done the same comparison about 1 year and an half ago, and now I lead a big project with nHibernate. I also help on two other smaller projects developed with EF so I'm up to date with both, and day by day I see that are no other reason that a gentler learning curve to use EF with a real world business application (more than 50 entities) – Bar

The distinguishing factor for me was that NH can be extended through event listeners. EF had something that could achieve the same effect in CTP5 called "Custom Pluggable Conventions", but they dropped it for the 4.1 release and it hasn't come back yet in 4.2. So NH it is. – Sanatorium