Fluent NHibernate does not create IN part of WHERE clause

Question

I have Fluent NHibernate Linq queries where I check values based on run time arrays. A basic example would be something like:

var array = [1,2,3,4,5,6];
using (var session = SessionProvider.SessionFactory.OpenSession())
{
  return session.Query<MyObject>().Where(x => array.Contains(x.CompareVal)).ToList();
}

I would expect the generated SQL statement to look something like this:

SELECT CompareVal, Column1, Column2
FROM MyObject
WHERE CompareVal IN (1,2,3,4,5,6)

However, what I'm finding instead is that the generated SQL statement simply emits the WHERE clause (proven by watching in Profiler) and selects the entire table, and then seems to run the filter in memory once it gets all the data back.

Something to note - I have a Generic Repository class that all of these calls are funneled through. The Query method is as follows:

public IList<T> Query(Func<T, bool> criteria)
{
  using (var session = SessionProvider.SessionFactory.OpenSession())
  {
    return session.Query<T>().Where(criteria).ToList();
  }
}

Obviously this (lack of a where clause) is not acceptable in a table with a large amount of data. What can I do to force NHibernate to generate the query correctly with the WHERE clause and still keep a generic pattern for repositories?

Fluent NH is not related to the linq provider. Do you have to use linq? NH has more features with `QueryOver`. — dotjoe, Feb 01 '13 at 13:52
@dotjoe Yes Linq is essential to the architecture of this system (or we'd be using stored procedures). This call is also made as part of a Generic Repository call. QueryOver can't be used as I pass T rather than a reference type. — BlackSpy, Feb 01 '13 at 14:11
Might be a problem of matching of the type of your array with the type of CompareVal. Could you give the exact declaration for array ? the one you provided does not seem to pass syntax check. — jbl, Feb 01 '13 at 14:46
I have 2 types of array - and both are simple types. Int and String — BlackSpy, Feb 01 '13 at 14:47
You mention FNH in the comments below also - please understand that Fluent NHibernate is only about configuration and mappings - Linq2NH and everything else is pure NHibernate. — Oskar Berggren, Feb 01 '13 at 18:46

jbl · Answer 1 · 2013-02-01T15:31:13.523

3

Does it make a difference if you change your Query method to the following ?

public IList<T> Query(Expression<Func<T, bool>> criteria)
{
  using (var session = SessionProvider.SessionFactory.OpenSession())
  {
    return session.Query<T>().Where(criteria).ToList();
  }
}

This is how I usually proceed with a generic Query :

    public List<TOut> GetEntitiesLinq<TIn,TOut>(Expression<Func<IQueryable<TIn>,IQueryable<TOut>>> myFunc)
    {
        var t = (myFunc.Compile())(_session.Query<TIn>()) ;
        return t.ToList();
    }

Then how I would use it in your case :

var myObjList = myQueryManager.GetEntitiesLinq<MyObject,MyObject>(x=>x.Where(myObj => array.Contains(myObj.CompareVal)));

Hope this will help

edited Feb 01 '13 at 15:31

answered Feb 01 '13 at 14:54

jbl

15,179
3
34
101

Thanks for this - looked promising. Unfortunetly FNH throws the following exception as it executes the query: Boolean IsNullOrEmpty(System.String) – BlackSpy Feb 01 '13 at 15:01
@BlackSpy : is CompareVal a direct property ? (no code involved ?) – jbl Feb 01 '13 at 15:07
Yup - depending on the query it's either an Int or a String - no code behind the accessor – BlackSpy Feb 01 '13 at 15:15
2

This is the right answer -- you are passing a delegate to the Query method but you need to pass an expression tree. See http://stackoverflow.com/a/4738396/12752. – Jamie Ide Feb 01 '13 at 18:26
1

@BlackSpy jbl is correct in that you need to pass the expression typed as Expression>, not just Func<>. In the latter case, NHibernate will only see compiled code and will be unable to convert it to SQL. You said you tried it, but that it resulted on an exception - please post more in this exception. – Oskar Berggren Feb 01 '13 at 18:48

Michael Buen · Accepted Answer · 2013-02-01T17:47:21.357

2

Use Any:

 return session.Query<MyObject>().Where(x => array.Any(y => y == x.CompareVal)).ToList();

Your repository pattern (using plain Func) automatically materializes your query to list, if you want something to be deferredly executed, use IQueryable, don't use Func only

Something to note - I have a Generic Repository class that all of these calls are funneled through. The Query method is as follows:

public IList<T> Query(Func<T, bool> criteria)
{
  using (var session = SessionProvider.SessionFactory.OpenSession())
  {
    return session.Query<T>().Where(criteria).ToList();
  }
}

Your repository just mimic what is already provided out of the box by NHibernate

edited Feb 01 '13 at 17:47

answered Feb 01 '13 at 14:32

Michael Buen

38,643
9
94
118

Just tried this now (again through the generic repository by changing my Func) and makes no difference - same result – BlackSpy Feb 01 '13 at 14:42
Ok thanks Michael - If i don't use a templated generic repository and pass a concrete type instead of T then it works. Poor solution, and breaks the design of the system somewhat but I've managed to refactor it so it's still neat and easy to maintain. P.S. Not a mimic of the out the box features - the wrapped class allows me to abstract knowledge of the SessionFactory away from the caller through dependency injection, meaning the caller can use GenericRepository for any concrete POCO. – BlackSpy Feb 04 '13 at 12:18
I also make a repository component (shameless plug): https://github.com/MichaelBuen/ToTheEfnhX To make the repository component truly agnostic of SessionFactory, while making repository componentfor NHibernate, I also make the repository pattern compatible to Entity Framework. Doing that, it keeps the repository pattern honest, i.e. it must work across different ORMs, no knowledge of SessionFactory nor DbContext – Michael Buen Feb 04 '13 at 12:38
ToTheEfnhX repository component sample usage here: http://www.ienablemuch.com/2012/06/friction-less-object-graph-n-tier-using.html – Michael Buen Feb 04 '13 at 12:39
The only part I was not able to abstract away on ToTheEfnhX repository component is the eager-loading mechanism. It's easy to abstract away eager-loading mechanism on one level on both ORMs, for two or more levels it's hard. Actually it's NHibernate that is difficult to abstract away, in Entity Framework, eager-loading on deeper object is a mere: db.Set().Include("Comments").Include("Answers").Include("Answers.Comments"). In NHibernate, you have to do this: http://www.ienablemuch.com/2012/08/eager-loading-repository.html – Michael Buen Feb 04 '13 at 12:45
I must be honest, I haven't looked at Entity Framework in a long time now. My main drawback was that it added framework related public properties to my POCOs, meaning I either could use the POCOs as REST objects on service endpoints, or had to write custom serialization code to strip them out. Has this changed? – BlackSpy Feb 04 '13 at 14:18
EF has changed, you can now use POCO(via DbContext) with it since version 4.1. And, though you can use its [independent association(class' object reference properties)](http://www.ladislavmrnka.com/2011/05/foreign-key-vs-independent-associations-in-ef-4/) similar to NHibernate, independent association leave nothing to be desired. Independent association objects **always** needed be loaded from database, which will incur performance overhead. When using EF, many are using its foreign key association, instead of independent association – Michael Buen Feb 04 '13 at 23:39
I love NHibernate architecture and its way of modeling domain classes, it promotes using object references instead of primitive type, object reference closely mimics the real-world objects. When you are referencing the Person's Country, you don't reference the person's country by GUID, there's no GUID in real world, just an example :-) – Michael Buen Feb 04 '13 at 23:46
My FNH favorite feature is convention mapping - lends itself to very clean code (no xml files or mapping classes - at most just some overrides). I'd find it very hard to be convinced to use another ORM – BlackSpy Feb 05 '13 at 07:44

score 0 · Answer 3 · answered Feb 01 '13 at 14:30

0

Can you use QueryOver and WhereRestrictionOn instead?

session.QueryOver<MyObject>().WhereRestrictionOn(o => o.CompareVal).IsIn(array).List();

answered Feb 01 '13 at 14:30

nils

558
5
13

The problem here is that this is part of a generic repository, and won't compile when I use QueryOver because it seems to require a concrete reference Type. – BlackSpy Feb 01 '13 at 14:38
public IList Query(Func criteria) { using (var session = SessionProvider.SessionFactory.OpenSession()) { return session.Query().Where(criteria).ToList(); } } – BlackSpy Feb 01 '13 at 14:38

Fluent NHibernate does not create IN part of WHERE clause

3 Answers3

Linked