Dynamic conditions in a Linq-to-Entities query

Question

I am trying to convert some old code that directly builds SQL queries into the Entity Framework, and ran into a problem that many seem to have (judging by the large number of questions surrounding this topic): how to express dynamics when conditions are in linq .

How can I express the following code with a linq request:

switch (status) { case "0": sqlwhere = " WHERE status < 0 "; break; case "-1": sqlwhere = " WHERE status = -1 "; break; case "-100": sqlwhere = " WHERE status = -100 "; break; case "1": default: sqlwhere = " WHERE status >= 0 "; break; } if (strsearch != "") sqlwhere += " AND desc LIKE '%" + strsearch + "%' "; string sqlc = "SELECT top 10 * FROM c " + sqlwhere + " order by date desc"; 

I read about PredicateBuilder and Linq dynamic extensions in other posts, but I think a simple case is kind of solvable without external libraries.

Using .net 4.5, EF 5.0, C #, can this be done in a "dynamic" way without creating a complete linq statement for each individual case?

Answer 1

If you do not want to use something external, just use the free API:

 var query = db.YourTableName .Where(x => x.desc.Contains(strsearch)); switch (status) { case "0": query = query.Where(x => x.status < 0); break; case "-1": query = query.Where(x => x.status == -1); break; case "-100": query = query.Where(x => x.status == -100); break; case "1": default: query = query.Where(x => x.status >= 0); break; } var result = query.OrderByDescending(x => x.date) .Take(10); 

BTW You can create an extension method to filter by status. And your request will look like this:

 var query = db.YourTableName .FilterByStatus(status) .Where(x => x.desc.Contains(strsearch)) .OrderByDescending(x => x.date) .Take(10); 

Extension Method:

 public static IQueryable<YourType> FilterByStatus(this IQueryable<YourType> query, string status) { switch (status) { case "0": return query.Where(x => x.status < 0); case "-1": return query.Where(x => x.status == -1); case "-100": return query.Where(x => x.status == -100); case "1": default: return query.Where(x => x.status >= 0); } } 

Answer 2

In your case use PredicateBuilder as below

Also check out my blog post: Dynamic Query with Linq

 var outer = PredicateBuilder.True<Entity>(); switch (status) { case "0": outer = outer.And (p => p.status<0); break; case "-1": outer = outer.And (p => p.status==-1); break; case "-100": outer = outer.And (p => p.status==-100); break; case "1": default: outer = outer.And (p => p.status>=0); break; } if (strsearch != "") outer = outer.And (p => p.desc.Contains(strsearch )); dataContext.Entity.Where (outer ); 

Answer 3

you can use LinkExtension 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(); }