What is the best way to dynamically add to a where clause in a nhibernate query in C#?

Question

I have a some C# code that is querying a database using nhibernate that looks like this:

      public void Query()
      {

        IEnumerable<Project> list = session.Query<Project>()
            .Where(p => !p.IsDeleted)
            .FetchMany(r => r.UnfilteredProjectApplications)
            .ThenFetch(r => r.Application)
            .ToList()
      }

I now have a number of user driver filters so, based on the parameters passed in, i want to add to the where clause. So something like this:

       public void Query(string name)
      {

         if (!String.IsNullOrEmpty(name)
          {
            IEnumerable<Project> list = session.Query<Project>()
             .Where(p => !p.IsDeleted && p.Name == name)
            .FetchMany(r => r.UnfilteredProjectApplications)
            .ThenFetch(r => r.Application)
            .ToList()
          }
      }
      else
      {
        IEnumerable<Project> list = session.Query<Project>()
            .Where(p => !p.IsDeleted)
            .FetchMany(r => r.UnfilteredProjectApplications)
            .ThenFetch(r => r.Application)
            .ToList()
      }

the user can select one or many filters. As you can imagine, this code above would get ridiculously complicated given the large number of combinations. Is there an elegant way to append a where clause here with additional blocks of logic. Some might be simple such as

         p.Name == name

but others might be more complicated like:

         p.ProjectApplications.Select(r => r.Application).Any(s => applicationIds.Contains(s.Id)))

and as I said, there may be zero or many different filters . .

UPDATE:

i have seen in other cases, people suggesting building up the where clause like

query = query.where (r=>r.name = "XYZ");
query = query.where (r=>r.Age > 10);
query = query.where (r=>r.Gender = "Male");

but that does NOT seem to work with nhibernate so what started was a generic lambda question is now a specific question to nhibernate

possible duplicate of [LinQ query - Add Where dynamically](http://stackoverflow.com/questions/1637723/linq-query-add-where-dynamically) — Servy, Apr 08 '14 at 18:01
Some other duplicates: http://stackoverflow.com/questions/13880931/linq-dynamic-where-count http://stackoverflow.com/questions/2493412/build-dynamic-linq — Servy, Apr 08 '14 at 18:03
@Servy - it turns out that my question is now nhibernate specific as the suggestoins in the duplicates don't work for nhibernate linq. I can delete the question and rename if that is easier — leora, Apr 08 '14 at 19:30
You shouldn't ever just say, "doesn't work". I would be shocked to find out that it doesn't work, it almost certainly does, if you write it properly. In any event, it's important to explain in detail *why* it doesn't work, and *what exactly happens that shouldn't*. I can't fathom any reason why calling `Where` multiple times would fail. It's far more likely that you're simply casting the object to something that you shouldn't at some point. — Servy, Apr 08 '14 at 19:32
The LINQ provider for NHibernate is pretty buggy in my experience. You can use QueryOver as an alternate, strongly typed API for querying with NHibernate. With QueryOver, it's easy to dynamically build where clauses, select lists, etc. — Andrew Whitaker, Apr 08 '14 at 20:51
@Andrew Whitaker - do you have any examples using QueryOver to dynamically build the where clause. I am happy to use that for this one use case — leora, Apr 08 '14 at 21:11
query = query.Where definitely works. I have had very few problems with the LINQ support in NHibernate 3+ — Jamie Ide, Apr 09 '14 at 15:36

score 8 · Accepted Answer · answered Apr 08 '14 at 21:04

You can use the PredicateBuilder<T> to create the expression and apply it on your query, for sample:

public void Query(string name)
{
    Expression<Func<Project, bool>> filter = PredicateBuilder.True<Project>();

    filter = filter.And(p => !p.IsDeleted);

    if (!string.IsNullOrEmpty(name)
        filter = filter.And(p => p.Name == name);

    IEnumerable<Project> list = session.Query<Project>()
                                        .Where(filter)
                                        .FetchMany(r => r.UnfilteredProjectApplications)
                                        .ThenFetch(r => r.Application)
                                        .ToList();
}

With PredicateBuilder you can create the expression you need, adding conditions using And(), Or(), Not() methods.

score 0 · Answer 2 · edited May 23 '17 at 12:25

0

If you are looking something like this:

public IList<Bestellung> GetAll(Expression<Func<Order, bool>> restriction)
{
    ISession session = SessionService.GetSession();
    IList<Order> bestellungen = session.Query<Order>()
                    .Where(restriction).ToList();
    return bestellungen;
}

Read this.

edited May 23 '17 at 12:25

Community

1
1

answered Apr 08 '14 at 21:52

Najera

2,869
3
28
52

What is the best way to dynamically add to a where clause in a nhibernate query in C#?

UPDATE:

2 Answers2

Linked