1

I have this method below that queries over database to get for instance User, Orders or any entity for that matter. I want to know if the below query is optimal or do i have to tweak that.

Does the query do where, only after storing all records in the memory ?

Is there a way i can specify Expression for the filtering rather than Func<> ?

What it the optimal way to get Count, specifying a condition/where without much memory consumption?

C# Sample Code

public IList<TEntity> Find(Func<TEntity, bool> predicate)
{
    //Criteria for creating
    ICriteria filterCriterea = _unitOfWork.CurrentSession.CreateCriteria(typeof(TEntity));

    //filtered result
    return filterCriterea.Future<TEntity>().Where(predicate).ToList();
}
Soner Gönül
  • 97,193
  • 102
  • 206
  • 364
Deeptechtons
  • 10,945
  • 27
  • 96
  • 178

1 Answers1

2

The are several things that you need to fix:

1. Ask for an Expression, not a Func

Your Find method specifies that it wants a Func. If you want an Expression, you have to say so:

public IList<TEntity> Find(Expression<Func<TEntity, bool>> predicate)

2. Use LINQ or QueryOver instead of ICriteria

Assuming you're not using an ancient version of NHibernate that doesn't have LINQ or QueryOver (oddly, there are a LOT of people still using NHibernate 1.2.1 - I can't figure out why)...

ICriteria doesn't understand Expressions. Replace CreateCriteria(typeof(TEntity)) with Query<TEntity>() or QueryOver<TEntity>() depending on which query syntax you prefer.

3. Where should go before Future

You need to move the Where before the Future. Future is used to batch several queries together so that they all get executed in one round trip to the database as soon as your code tries to evaluate the results of one of the "Future" queries. At the point your above code calls Future, the query only consists of CreateCriteria(typeof(TEntity)), which just tells it which table to query against, like so:

select * from TEntity

If you want a where clause in there, you have to switch those method calls around:

filterCriterea.Where(predicate).Future<TEntity>()

This should give you a SQL query like:

select * from TEntity where ...

It is very important to learn the difference between IQueryable and IEnumerable. You should notice that Query returns an IQueryable, whereas Future returns an IEnumerable. Manipulating an IQueryable using Expressions will result in changes to the SQL that gets executed. Manipulating an IEnumerable using a Func just changes the way you're looking at the in-memory data.

4. ToList immediately after Future negates benefit of Future

ToList iterates over the collection that is passed to it and puts each element in a list. The act of iterating over the collection will cause the Future query to be immediately executed, not giving you any chance to batch it together with other queries. If you must have a list, you should just omit the Future. I think however, that it would be a better idea to change your method to return an IEnumerable, which would give you the ability to batch this query together with other queries. Like so...

public IEnumerable<TEntity> Find(Expression<Func<TEntity, bool>> predicate)
{
    var query = _unitOfWork.CurrentSession.Query<TEntity>();
    return query.Where(predicate).Future<TEntity>();
}

In order to take advantage of the query batching, you are probably going to have to rearrange the code that calls this Find method. For example, instead of...

foreach (var openThing in thingRepository.Find(x => x.Status == Status.Open))
    CloseIt(openThing);

foreach (var negativeWhatsit in whatsitRepository.Find(x => x.Amount < 0))
    BePositive(negativeWhatsit);

... you should do this:

var openThings = thingRepository.Find(x => x.Status == Status.Open);
var negativeWhatsits = whatsitRepository.Find(x => x.Amount < 0);

// both queries will be executed here in one round-trip to database
foreach (var openThing in openThings)
    CloseIt(openThing);

foreach (var negativeWhatsit in negativeWhatsits)
    BePositive(negativeWhatsit);
Daniel Schilling
  • 4,829
  • 28
  • 60
  • AAhhh!!! I completely overlooked the fact that you're trying to use expressions and LINQ-style filtering with ICriteria. That's not possible. If you want to use expressions, you need to use LINQ or QueryOver. Editing... – Daniel Schilling Oct 04 '13 at 15:36
  • `_unitOfWork.CurrentSession.Query();` why does Visual Studio complain me that the method Query does not exist, Is it because of NHibernate 2.1 ? – Deeptechtons Oct 07 '13 at 04:58
  • Yes. LINQ support was officially added to NHibernate version 3.0, but you can add LINQ support to NHibernate 2.1 using the LINQ provider in nhcontrib: http://sourceforge.net/projects/nhcontrib/files/NHibernate.Linq/1.0/. After referencing NHibernate.Linq.dll, add `using NHibernate.Linq;` to the top of your file. That will give you a `session.Linq()` extension method (instead of `session.Query()`). – Daniel Schilling Oct 07 '13 at 12:27