C# Multiple Linq Query Search Issue

Question

Hello,

I have multiple linq queries for searching a database of information based on a single specific piece of criteria per query. e.g. by ID or by Name etc.

Currently, the user can search by use of only one query method. The problem is that I want the user to be able to search using multiple criteria, without having to write new queries that combine the criteria from multiple queries.

For Example:

Below I have a query that returns a set of questions based on the department name that they are stored under, and another query that returns a set of questions based on the module title that the questions are stored under. Since currently the user can only search by department name or by module title - e.g. Computer Science or Distributed Systems, I would like to change this so that the user can specify something like:

Return all questions that are of DepartmentName == Computer Science && ModuleTitle == Distributed Systems.

Help would be greatly appreciated.

Here is the current code:

//Department Name Query
public static IQueryable SearchByDepartmentNameInfo(string deptName)
    {

        ExamineDataContext dc = new ExamineDataContext();

        var queryResult = from q in dc.GetTable<Question>()
                          where q.Topic.Module.Department.DepartmentName.Equals(deptName)
                          join s in dc.Solutions
                          on q.QuestionID equals s.QuestionID
                          into qs // note grouping        
                          select new
                          {
                              Module = q.Topic.ModuleTitle,
                              Topic = q.TopicName,
                              Question = q.QuestionText,
                              QuestionType = q.QuestionType,
                          };
        return queryResult;
    }

    //Module Title Query
    public static IQueryable SearchByModuleTitleInfo(string modTitle)
    {

        ExamineDataContext dc = new ExamineDataContext();

        var queryResult = from q in dc.GetTable<Question>()
                          where q.Topic.Module.ModuleTitle.Equals(modTitle)
                          join s in dc.Solutions
                          on q.QuestionID equals s.QuestionID
                          into qs // note grouping        
                          select new
                          {
                              Module = q.Topic.ModuleTitle,
                              Topic = q.TopicName,
                              Question = q.QuestionText,
                              QuestionType = q.QuestionType,
                          };
        return queryResult;
    }

Answer 1

You can combine them into a single query, by only applying the condition if it's set:

where (cond1 == "" || row.field1 == cond1)
&& (cond2 == "" || row.field2 == cond2)
...

So if you search for cond1="" and cond2="somevalue", in effect you're only searching on cond2. But if you specify both, it will select the intersection of both conditions.

Add some checks to make sure at least one condition is specified.

Answer 2

Have you considered making the functions accept an IQueryable as a parameter instead of hard-coding the GetTable within them? This would allow you to combine queries by passing the result from one function to the input of another. Of course the query still doesn't execute until you evaluate the final result.