Dynamic LINQ to Entities, how to build query based on variables

Question

The query I need to build is this:

query = query.Where(s => 
           (
              (s.Title.Contains(title1) && s.EpisodeTitle.Contains(episodeTitle1))
               ||
              (s.Title.Contains(title2) && s.EpisodeTitle.Contains(episodeTitle2)))
            );

The only issue is, s.Title and s.EpisodeTitle are dynamic.

Meaning that the following variables could be part of the query:

(string title1 = null,
  string title2 = null,
  string episodeTitle1 = null,
  string episodeTitle2 = null,
  string genre = null,
  string directorName = null,
  string releaseYear = null,
  string seasonEpisode = null,
  string showTypeDescription = null)

e.g.

query = query.Where(s => 
           (
              (s.DirectorName.Contains(directorName) && s.ShowTypeDescription.Contains(ShowTypeDescription))
               ||
              (s.releaseYear.Contains(releaseYear) && s.genre.Contains(genre)))
            );

In ANY type of combination.

How can I construct this query without having to take into account EVERY SINGLE possibility here?

Try reading up on `PredicateBuilder` and `LINQKit`. This might be a good place to start: [How does PredicateBuilder work](http://stackoverflow.com/questions/11490893/how-does-predicatebuilder-work) — Sphinxxx, Jun 26 '13 at 21:22

score 3 · Accepted Answer · answered Jun 26 '13 at 22:27

If you only need AND logic you could just call .Where() repeatedly for every attribute that requires searching on.

if(title != null) query = query.Where(x=>x.Title == title);
if(genre != null) query = query.Where(x=>x.Genre == genre);

If your query is always of a certain structure and you want to ignore null search values you could do one big query but short circuit the attribute comparison with null checks.

query = query.Where(s => 
  (
    ((title1 == null || s.Title.Contains(title1)) 
        && (episodeTitle1 == null || s.EpisodeTitle.Contains(episodeTitle1))
     ||
    ((title2 == null || s.Title.Contains(title2)) 
       && (episodeTitle2 == null || s.EpisodeTitle.Contains(episodeTitle2))))
        );

However if you need full control over the query then you will need to look at using PredicateBuilder or System.Linq.Expressions to build a specific query to search on the necessary attributes. Here is a useful tutorial on Linq.Expressions - http://msdn.microsoft.com/en-us/library/vstudio/bb882637.aspx

score 0 · Answer 2 · answered Mar 02 '14 at 21:03

the best solution is to use linqExtension with LINQKIT.

    using (var context = new workEntities() )
{

    Dictionary<string, List<string>> dictionary = new Dictionary<string, List<string>>();
    dictionary["Title"] = new List<string> {  
                    "Network Engineer", 
                    "Security Specialist", 
                    "=Web Developer"
                };
    dictionary["Salary"] = new List<string> { ">=2000" };
    dictionary["VacationHours"] = new List<string> { ">21" };
    dictionary["SickLeaveHours"] = new List<string> { "<5" };                
    dictionary["HireDate"] = new List<string> { 
                    ">=01/01/2000",
                    "28/02/2014" 
                };
    dictionary["ModifiedDate"] = new List<string> { DateTime.Now.ToString() };

    var data = context.Employee.CollectionToQuery(dictionary).ToList();
}

Dynamic LINQ to Entities, how to build query based on variables

2 Answers2

Linked