LINQ-NHibernate - Selecting only a few fields (including a Collection) for a complex object

Question

I'm using Fluent NHibernate in one of my projects (and ASP.NET MVC application), with LINQ to query to data (using the LINQ to NHibernate libraries).

The objet names are changed to protect the innocent.

Let's say I have the following classes Foo, Bar, Baz, and their appropriate tables in the database (MySQL).

Foo has a many-to-many relationship with both Bar (table "FooBar") and Baz (table "FooBaz"), defined in the Fluent mappings. As such, the class interface is defined as follows:

public class Foo {
    public virtual int id { get; set; }
    public virtual string name { get; set; }
    public virtual string email { get; set; }
    public virtual IList<Bar> bars { get; set; }
    public virtual IList<Baz> bazes { get; set; }
}

This is a pretty standard class. We can see that a Foo object will have a list of bars and bazes.

The problem comes when trying to do a LINQ query.

If I do a simple query like this, it works fine (the where clause is unimportant) :

var foos = from foo in session.Linq<Foo>()
           where email.equals("[email protected]")
           select foo;

IList<Foo> listFoos = foos.ToList();

This will return a list of Foos, with all the fields populated (id, name, email, bars, bazes). log4net shows that NHibernate performs separate queries for the collections.

The problem arises when I want to load only some fields. For example, I might want to load only the bars in the query, but not the bazes.

This query compiles, but produces an error at runtime:

var foos = from foo in session.Linq<Foo>()
           where email.equals("[email protected]")
           select new Foo() 
           {
               id = foo.id,
               name = foo.name,
               email = foo.email,
               bars = foo.bars
           };

IList<Foo> listFoos = foos.ToList();

The error I get is something along the lines of an array index out of bounds exception. The stack trace shows some methods names relating the collection handling on LINQ-NHibernate's side, but nothing else. The query reported by log4net shows no sign of a query on bars, which means than an error was caught before it had the time to perform the query to select the bars.

Did anyone else had this kind of problem before? How did you solve it? I don't want to have to select all the objects everytime I open a web page in my application!

Thank you!

Edit: here is the stacktrace, as requested.

System.IndexOutOfRangeException: L'index se trouve en dehors des limites du tableau. (read: "Index is out of bounds for the array.")

[IndexOutOfRangeException: L'index se trouve en dehors des limites du tableau.]
   NHibernate.Transform.TypeSafeConstructorMemberInitResultTransformer.InvokeMemberInitExpression(MemberInitExpression expression, Object[] args, Int32& argumentCount) +404
   NHibernate.Transform.TypeSafeConstructorMemberInitResultTransformer.TransformTuple(Object[] tuple, String[] aliases) +150

[QueryException: could not instantiate: Foo]
   NHibernate.Transform.TypeSafeConstructorMemberInitResultTransformer.TransformTuple(Object[] tuple, String[] aliases) +265
   NHibernate.Loader.Criteria.CriteriaLoader.GetResultColumnOrRow(Object[] row, IResultTransformer resultTransformer, IDataReader rs, ISessionImplementor session) +171
   NHibernate.Loader.Loader.GetRowFromResultSet(IDataReader resultSet, ISessionImplementor session, QueryParameters queryParameters, LockMode[] lockModeArray, EntityKey optionalObjectKey, IList hydratedObjects, EntityKey[] keys, Boolean returnProxies) +330
   NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) +704
   NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) +70
   NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) +111
   NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters) +18
   NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet`1 querySpaces, IType[] resultTypes) +79
   NHibernate.Impl.SessionImpl.List(CriteriaImpl criteria, IList results) +407
   NHibernate.Impl.CriteriaImpl.List(IList results) +41
   NHibernate.Impl.CriteriaImpl.List() +35
   NHibernate.Linq.<GetEnumerator>d__0.MoveNext() +71
   System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) +7665172
   System.Linq.Enumerable.ToList(IEnumerable`1 source) +61
   FooRepository.List(Int32 count) in C:\...\FooRepository.cs:38
   FooController.List() in C:\...\FooController.cs:30
   lambda_method(ExecutionScope , ControllerBase , Object[] ) +39
   System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters) +17
   System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters) +178
   System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters) +24
   System.Web.Mvc.<>c__DisplayClassa.<InvokeActionMethodWithFilters>b__7() +52
   System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation) +254
   System.Web.Mvc.<>c__DisplayClassc.<InvokeActionMethodWithFilters>b__9() +19
   System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodWithFilters(ControllerContext controllerContext, IList`1 filters, ActionDescriptor actionDescriptor, IDictionary`2 parameters) +192
   System.Web.Mvc.ControllerActionInvoker.InvokeAction(ControllerContext controllerContext, String actionName) +399
   System.Web.Mvc.Controller.ExecuteCore() +126
   System.Web.Mvc.ControllerBase.Execute(RequestContext requestContext) +27
   System.Web.Mvc.ControllerBase.System.Web.Mvc.IController.Execute(RequestContext requestContext) +7
   System.Web.Mvc.MvcHandler.ProcessRequest(HttpContextBase httpContext) +151
   System.Web.Mvc.MvcHandler.ProcessRequest(HttpContext httpContext) +57
   System.Web.Mvc.MvcHandler.System.Web.IHttpHandler.ProcessRequest(HttpContext httpContext) +7
   System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +181
   System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +75

Linguiform

Answer 1

A couple of suggestions...

First, NHibernate defaults to lazy loading, so there may not even be a need to do the more "efficient" query you're attempting.

Second, the following code does not quite do what your example code does - it creates a list of anonymous types called miniFoos that contain a subset of the fields in Foo. But the net effect is similar to what you seem to be attempting. It also uses what I believe is called LINQ method syntax.

I tested this in on an entity in my own application, which also has an IList property, and it does work (though I should note that I'm currently using Eager loading). I just cut and pasted the working code and substituted your example's names.

using (var session = sessionFactory.OpenSession())
{
    session.BeginTransaction();

    var foos = session.CreateCriteria(typeof(Foo))
        .List<Foo>();

    var miniFoos = foos.Select(f => new { f.email, f.bars })
        .Where(f => f.email.Equals("[email protected]")
        .ToList();

    session.Close();
}

Nephoscope

I'm new to the NHibernate world, but there were some problem with lazy loading, as Fluent NHibernate actives it by default. The effect of that is that I can query objects and manipulate them, but eventually, when I'm in my view and need to access those objects, I get errors due to the fact that the Session is closed (being in the View), so I turned it off. I did not go into the details of that issue, though, so I might have been doing something wrong in regards to lazy-loading. – Linguiform

OK, I tested your code and it seems to work, at least in the debugger. Do you know how I can cast it to a concrete IList<Foo> (because now it's just a list of anonymous type according to the compiler). – Linguiform

I'm fairly new to NH as well, and have gone back and forth on Lazy Loading for similar reasons. Can you keep the session open all the time? I solved this by adding a "session" variable which I set when I start up my program, and only close the session on program exit. This way, NH handles all the dirty work for you, which is one of the main reasons for using NH in the first place ;-) – Nephoscope

I believe this example is using LinqToObjects against the Query API, not the NHibernate Linq provider. – Sibylsibylla

@Michael - yeah, upon reflection, I think you're right. Should I take down my answer? – Nephoscope

I'm using the NHibernate Linq provider (as far as I know!). – Linguiform

Answer 2

I tried answering this already, but am not happy with it.

The more I think about your question, the more it seems like you are trying to do your own version of Lazy Loading, which NHibernate handles pretty well on it's own, in my experience.

I know (from a comment) that you switched to Eager Loading because you were not keeping a session open. I think it would be more useful for you in the long run if you focused on that angle.

If I've misunderstood, perhaps you could edit the question to explain why you think you need to handle it yourself?

Nephoscope

I think you response makes sense. However, I know I can define, in my Fluent mappings, which fields will be Lazy Loaded. But, going further, can I specified, in my LINQ query, what I want to specifically load (for example, if my collection of bazes is huge and I don't need them on my index page, but I need the bars collection, can I specify the lazy load the bazes, but load the bars completely? Does that make sense?). Assuming lazy loading works, I must devise a way to keep the session open and accessible throughout the ASP.NET MVC application, but that another subject... – Linguiform

Lazy loading works - believe me! NH will only load the data for an individual member variable in an object if it is read directly by your program. This means you can just load Foo and show the Bazes collection to your users. Bazes will get loaded, Bar will not, because you don't reference it explicitly in your code, even though you are referencing Foo. Trust the Force, Luke! Check out #2013967 for some good advice on sessions. – Nephoscope

Answer 3

I could manage to project not the whole collection but its fields

   ... 
   Select(a => new Forum {
   Id = a.Id, 
   Name = a.Name, 
   CategoryName = a.Categories.Select(b => b.Name).First() 
   })

CategoryName is an computed filed in the Forum Entity which does not have any mapping with DB

In result I got a small projection of the huge Forum Entity and a possibility to sort on the field.

Hope that it will help somebody.

Trefler

Answer 4

i think you can solve your problem by defining the result transformer and the result set names, if in some way you can access the Criteria that being generated you can avoid anonymous object.

the problem is that the property names and column names are not the same, and when using projection nhibernate/linq to nhibernate does not check what the column name and the property name. so if u'll define them by yourself in the result properties (i think its called result set) in the criteria before you execute the query it should work.

it's pretty simple, give it a try

Bates

Chen, how can you define a result transformer on an IQueryable? I know you can do it on an IQuery, but I don't think that's what we have here. I'm having the same issue, so I'd love it if you could elaborate on your suggestion. Also, isn't LINQ to NHibernate different from using Criteria? – Execration