What is the best practice to implement advanced search using Entity Framework?

Question

I am using two different ways to implement an advanced search where each approach has their advantages and disadvantages.

The user can perform an advanced search using 3 parameters (name, familyname, and mobile).

The first way I tried is to check which of those parameters are provided by the user; that requires me to do 8 if & else checks (2 ^ number of parameters) and in each conditions I write a separate query which accepts the corresponding parameters, for example if the user has entered name & family name the where clause of the query will look like this:

where(x=>x.name.contains(name) && x.familyname.contains(familyname))

or in another case if the user has entered only the mobile the where clause of the query will look like this :

where(x=>x.mobile==mobile)

the advantage of this way is that I hit the databse once but the disadvantage is that I have to write a lot more code.

The second way I tried is that I declared an IQueryable<> object and I feed data into It with no condition at first and then I check search parameters one by one and if any of them has value I filter the IQueryable with that value and at last I perform a .ToList(). This way has the advantage of much less code but hits the database twice which is a problem.

Here is a code sample of the second way:

List<ShoppingCardForGridView> list_ShoppingCardForGridView = 
    new List<ShoppingCardForGridView>();
IQueryable<ShoppingCardForGridView> outQuery = 
    from x in db.ShoppingCards
    orderby x.TFDateBackFromBank descending
    where x.TFIsPaymentCompleted == true
    select new ShoppingCardForGridView
    {
        Id = x.Id,
        TFCustomerFullName = 
            x.Customer.TFName + " " + x.Customer.TFFamilyName,
        TFDateBackFromBank = x.TFDateBackFromBank.Value,
        TFIsDelivered = x.TFIsDelivered,
        TFItemsPriceToPay = x.TFItemsPriceToPay,
        TFDateBackFromBankPersian = x.TFDateBackFromBankPersian

    };

if (!string.IsNullOrEmpty(CustomerFullName))
{
    outQuery = outQuery.Where(x => 
        x.TFCustomerFullName.Contains(CustomerFullName));
}
if (!string.IsNullOrEmpty(IsDelivered))
{
    bool deliveryStatus = Convert.ToBoolean(IsDelivered);
    outQuery = outQuery.Where(x => x.TFIsDelivered == deliveryStatus);
}
list_ShoppingCardForGridView = outQuery.ToList();

I wonder if there is any better way or a best practice to perform an advanced search using entityframework?

Answer 1

You're not hitting the database multiple times with your second solution. Remember that an IQueryable object is a query itself, not the results of a query. Execution is deferred until the query is actually iterated. Conditionally appending multiple Where clauses based on various if checks is changing the query itself, not processing the results of the query (since there are no results at that point in time).

You can use a profiler on your database to verify that only one query is being executed, and that the one query contains all of the filtering in that one go.

This is a reasonably common pattern; I've used it on a number of instances for making custom search pages.