How to apply multi-criteria search to LINQ?

I have a WebForm with 4 combo boxes, which allow user to define different search criteria. In case user selects nothing in a combo box, it does mean that user want to select all data in this criterion (in other words, omit the filter)

The idea is simple, but when I implement it in Linq, it painful. I don't know how to optionally add criteria into the query. Here is my "concept" query, which, of course, does not work:

var context = new Entities();
        var complaints = from c in context.Complaints
                         join s in context.Statuses on c.Status equals s.Id
                         join service in context.SERVICES on c.ServiceId equals service.Id
                         join u in context.Users on c.CreatedBy equals u.UserId
                         from technician in context.Users.Where(technician => technician.UserId == c.AssignedTo).DefaultIfEmpty()
                         where c.ResellerId == CurrentUser.ResellerID
                         && c.CreatedBy == (decimal)ASPxComboBoxSupporter.Value //if Supporter is selected and so on
                         && c.AssignedTo == (decimal)ASPxComboBoxTechnician.Value
                         && c.ServiceId == (decimal)ddlService.Value
                         && c.Status == (decimal)ddlStatus.Value
                         select new
                         {
                             c.Id,
                             c.Status,
                             s.Name,
                             c.ServiceId,
                             Service = service.Name,
                             c.Title,
                             c.Customer,
                             c.Description,
                             c.CreatedDate,
                             c.CreatedBy,
              开发者_运维技巧               Author = u.Username,
                             c.AssignedBy,
                             c.AssignedTo,
                             Technician = technician.Username,
                             c.AssignedDate
                         };

How can I do this?

Instead of creating it using the more SQL style Linq syntax (sorry I don't know the proper name for it), you could try taking advantage of the way IQueryable objects work. Basically what you can do is add where clauses where necessary, otherwise omit them. Something along these lines should work:

var context = new Entities();
var complaints = from c in context.Complaints
                 join s in context.Statuses on c.Status equals s.Id
                 join service in context.SERVICES on c.ServiceId equals service.Id
                 join u in context.Users on c.CreatedBy equals u.UserId
                 from technician in context.Users.Where(technician => technician.UserId == c.AssignedTo).DefaultIfEmpty()
                 select new
                 {
                     c.Id,
                     c.Status,
                     s.Name,
                     c.ServiceId,
                     Service = service.Name,
                     c.Title,
                     c.Customer,
                     c.Description,
                     c.CreatedDate,
                     c.CreatedBy,
                     Author = u.Username,
                     c.AssignedBy,
                     c.AssignedTo,
                     Technician = technician.Username,
                     c.AssignedDate
                 };

So keep in mind you haven't actually queried anything yet, because Linq uses deferred execution. Now you can go through and add where clauses

if (ASPxComboBoxSupporter.Value != null)
{
    complaints = complaints.Where(c => c.CreatedBy == (decimal)ASPxComboBoxSupporter.Value);
}
if (ASPxComboBoxTechnician.Value != null)
{
    complaints = complaints.Where(c => c.AssignedTo == (decimal)ASPxComboBoxTechnician.Value);
}

if (ddlService.Value != null)
{
    complaints = complaints.Where(c => c.ServiceId == (decimal)ddlService.Value);
}

if (ddlStatus.Value != null)
{
    complaints = complaints.Where(c => c.Status == (decimal)ddlStatus.Value);
}

I haven't tested this, so let me know if something isn't working right still.

First, you have to parse the text input from the user to see if it's really a number or not, and if yes, convert it to the decimal type. There is a handy method decimal.TryParse for that:

    decimal supporter;
    bool supportedSpecified = decimal.TryParse( ASPxComboBoxSupporter.Value, out supporter );

And then you can use this information in the query:

    where c.ResellerId == CurrentUser.ResellerID
    && ( !supporterSpecified || c.CreatedBy == supporter )
    ...

Repeat for other criteria.

Take a look at the PredicateBuilder class described in the C# in a Nutshell book(s). It allows you to dynamically build a predicate to use with LINQ to SQL and Entity Framework.