LINQ to SQL - further modifying IQueryable result set with Contains

Question

I am using LINQ to SQL and I am allowing users to set up the query via assigning values to queryStrings in the UI. I set up the primary query to return an IQueryable result and then keep refining the results set by continuing to act upon the resulting IQueryable object. Everything works fine and the code looks similar to this

var result = (from record in db.Companies
select new Company
{
     Id = record.Id,
     Name = record.Name,
     City = record.City,
     Status = record.Status
});
if (queryName != null && queryName!= "")
{
  result = result.Where(p => p.Name.Contains(queryName));
}

if (queryCity != null && queryCity!= "")
{
   result = result.Where(p => p.City.StartsWith(queryCity));
}

Now I want to extend the query my getting matching a set of elements similar to “IN CLAUSE” in SQL. Where there is a list of elements to use in the query e.g.

string[] queryStatusList = {"x", "y" };

And now I can write the code like this and everything is still OK.

var result = (from record in db.Companies
where queryStatusList.Contains(record.status)
   select new Company
   {
      Id = record.Id,
      Name = record.Name,
      City = record.City,
      Status = record.Status
   });
if (queryName != null && queryName!= "")
{
  result = result.Where(p => p.Name.Contains(queryName));
}

if (queryCity != null && queryCity!= "")
{
   result = result.Where(p => p.City.StartsWith(queryCity));
}

But, I don’t want to have a where clause in the initial query. I want to build from the refined query result as done in the previous example. My question is how would I structure such a query. I tried

if (queryStatusList != null && queryStatusList.Count() > 0)
{
    result = result.Where(queryStatusList.Contains(result.Select(p => p.Status.ToString())));
}

But I get a compiler error: “The type arguments for method 'System.Linq.Enumerable.Contains(System.Collections.Generic.IEnumerable, TSource)' cannot be inferred from the usage. Try specifying the type arguments explicitly” I have tried a few variations but I’m not sure how to fix the issue.

Answer 1

I think if you change it slightly, it may work:

if (queryStatusList != null && queryStatusList.Count() > 0) 
{ 
    result = result.Where( r=> queryStatusList.Contains( r.Status )); 
}

On the other hand, you might want to look at using a PredicateBuilder to build up a single Where clause selector and use it instead. The PredicateBuilder will give you more control and the ability to create complex queries with a mix of AND and OR clauses while still building them up dynamically.

var predicate = PredicateBuilder.True<Company>();

if (queryName != null && queryName!= "") 
{ 
    predicate = predicate.And( p => p.Name.Contains(queryName) );
} 

if (queryCity != null && queryCity!= "") 
{ 
   predicate = predicate.And(p => p.City.StartsWith(queryCity)); 
}

if (queryStatusList != null && queryStatusList.Count() > 0) 
{ 
    predicate = predicate.And( p => queryStatusList.Contains( p.Status )); 
}

var result = db.Companies
               .Select( c => new Company 
                { 
                    Id = record.Id, 
                    Name = record.Name, 
                    City = record.City, 
                    Status = record.Status 
                }
               .Where( predicate );