NHibernate LINQ and Many to Many Mapping

Question

I have this clases mapped on NHibernate 3.2 with FluentNHibernate 1.3:

public class ClassA
{
    public virtual Int32 Id{ get; }
    ....
    public ICollection<ClassB> ClassesB { get; }
    public ICollection<ClassC> ClassesC { get; }
}

public class ClassB
{
    public virtual Int32 Id{ get; }
    ....
    public ICollection<ClassA> ClassesA { get; }
    public ICollection<ClassC> ClassesC { get; }
}

public class ClassC
{
    public virtual Int32 Id{ get; }
    ....
    public ICollection<ClassA> ClassesA { get; }
    public ICollection<ClassB> ClassesB { get; }
}

All the mappings works correctly and as spected. But I need to make queries with LINQ that returns, for example, all the ClassC instances in a given ClassB or ClassA instances, and viceversa. I need to do this without loading any ClassB or ClassA instances before getting the list of ClassC, just make the query with the ID of the instance.

I know that I can load, for example, a ClassB instance and then get the ClassesC collection, but with my application desing I must do this without doing that, I'll use this for things like paging on a Grid, and other operations diferent from the paging.

On pure SQL I can do that with a simple join query, but I dont know how to do that with LINQ. And I dont want the option of HQL or ICriteria, it must be done with LINQ (Extensions preferable) because the application uses plugins that must not have awareness of the NHibernate presence. The plugin receives a IQueryable instance to make the queries (this allow us to easely port the core application to other data base schema, like NoSQL or something else without even recompilation of the plugins or other parts of the aplication).

Thanks for your help in advance.

Btw, working with `IQueryable` and `Linq2nHibrenate` could be very tricky. And you have to think twice before providing `IQueryable` to plugins. — Akim, Jul 18 '12 at 08:16
I uses NHibernate 3.2 and it have LINQ "integrated", I'mm using it for other "normal" queries without any issues. The plugins are developed for a very closed system, all the plugins are developed by inside programmers, the application will be used only by the same "company", not intended for plublic use. The plugins will be mostly for reporting and file generation. Trust me, I gave to them all the warnings about this aproach, but my clients decided that this must be this way, hehehehehehe. — ElMangau, Jul 18 '12 at 09:39
I'll just giving to them what they are asking four and I did my part warning them (I even have a letter signed by the CEO, just in case, hehehehe). I really appreciate your advice about this. — ElMangau, Jul 18 '12 at 09:40
One of example where `Linq2nHibernate` could make problems while using with `IQueryable`: `q.Where(sameName => …).Where(sameName => …)` — using of same variable name `sameName` could break your query with meaningless exception. And here are some general articles: [IQueryable is Tight Coupling]( http://blog.ploeh.dk/2012/03/26/IQueryableIsTightCoupling.aspx) and [IQueryable Can Kill Your Dog, Steal Your Wife, Kill Your Will To Live, etc.](http://www.weirdlover.com/2010/05/11/iqueryable-can-kill-your-dog-steal-your-wife-kill-your-will-to-live-etc/) — Akim, Jul 18 '12 at 10:04

Akim · Answer 1 · 2012-07-18T12:31:06.270

0

By default HasMany in FluentNHibernate will load collections lazy. You could configure this using fluent syntax.

Example of lazy loading:

class ClassAMap : ClassMap<ClassA>
{
    public ClassAMap()
    {
    // ...
    // lazy loading using select
    HasMany(x => x.ClassesB)
        .LazyLoad()
        .Fetch.Select()
    // ...
}

or greedy loading using join:

class ClassAMap : ClassMap<ClassA>
{
    public ClassAMap()
    {
    // ...
    // greedy loading using join
    HasMany(x => x.ClassesB)
        .Fetch.Join()
    // ...
}

or conventions

Table.Is(x => x.EntityType.Name + "Table")
    DefaultLazy.Always()

Anyway this configuration topic is quite complex and depends on how exactly you configure your mapping. I advice you to take a look at hHibernate SQL logs using log4net to see SQL command are generated by ORM.

edited Jul 18 '12 at 12:31

answered Jul 18 '12 at 08:16

Akim

8,469
2
31
49

Thanks very much Akim, for your quick answer!!! I know about the lazy loading stuff and fetching on the mappings and conventions too, but I need it to do that with IQueryable interface, I need to make queries using other fields of the mapped classes. For example: Session.Query().Where(inst => inst.ClassC.Id == XXX && inst.ProcessStatus == ProcessStatusEnum.Pending).OrderBy........ Something like that. – ElMangau Jul 18 '12 at 09:28
That should work fine. Just *check SQL generated by ORM in your log file and tune mapping*. You'll a lot of surprises ;) – Akim Jul 18 '12 at 10:51

NHibernate LINQ and Many to Many Mapping

1 Answers1