6

I'm trying to implement search functionality in an ASP.NET MVC 2 application. I create an expression based on criteria entered by the user:

public ViewResult FindCustomer( string forename, string familyname, DateTime? dob)
  {
  Expression<Func<Customer, bool>> searchCriteria = p => (
                                                            forename.IsNullOrEmpty() ? true : p.Forename == forename
                                                            && familyname.IsNullOrEmpty() ? true : p.FamilyNames.Any(n => n.Name == familyname)
                                                            && dob.HasValue ? true : p.DOB == dob
                                                            );

which then gets passed to a method in the repository

IQueryable<Customer> customers = CustomerRepository.FilterBy(searchCriteria);

The problem is when I run this I get the following exception

System.InvalidCastException: Unable to cast object of type 'NHibernate.Hql.Ast.HqlCast' to type 'NHibernate.Hql.Ast.HqlBooleanExpression'

According to this the problem is the use of the conditional operator in the expression.

So I suppose I have to create the Expression some other way but I'm not sure how to do that. I'm pretty new to Linq so any help would be gratefully accepted!

Community
  • 1
  • 1
Babakoto
  • 63
  • 1
  • 8

1 Answers1

10

What about dynamically create your query? Like this:

var customers = CustomerRepository.AllEntities();

if (!forename.IsNullOrEmpty())
    customers = customers.Where(p => p.Forename == forename);
if (!familyname.IsNullOrEmpty())
    customers = customers.Where(p => p.FamilyNames.Any(n => n.Name==familyname));
if (dob.HasValue)
    customers = customers.Where(p => p.DOB == dob);

I don't know if this works but I think this could be more efficient.

Balazs Tihanyi
  • 6,659
  • 5
  • 23
  • 24
  • That works! Thanks very much. I'd been trying to avoid writing something like that because I wanted to avoid multiple calls to the database so I thought I needed to construct a single expression containing all the parameters. Using your solution only executes a single SQL statement. Is that a feature of NHibernate? It builds a SQL statement from multiple LINQ expressions to minimise calls to the database? I think I need to learn more about both NHibernate and LINQ! – Babakoto Mar 20 '12 at 11:29
  • 1
    @Babakoto This is a feature of LINQ. Your query won't be evaluated till you call a `ToList` or `SingleOrDefault` (or similar) method at the end of your `IQueryable` chain, so you can dynamically add any filters to your query. After an explicit call the query processor (like NHibernate) will transform the whole query chain into a SQL expression. – Balazs Tihanyi Mar 20 '12 at 11:46
  • @Babakoto I have used such expressions when I used Entity framework and there, it just worked fine. Why it doesnt work in nHibernate ? – Bilal Fazlani Dec 10 '13 at 11:10