Dynamic WHERE clause in LINQ
Solution 1
(source: scottgu.com)
You need something like this? Use the Linq Dynamic Query Library (download includes examples).
Check out ScottGu's blog for more examples.
Solution 2
I have similar scenario where I need to add filters based on the user input and I chain the where clause.
Here is the sample code.
var votes = db.Votes.Where(r => r.SurveyID == surveyId);
if (fromDate != null)
{
votes = votes.Where(r => r.VoteDate.Value >= fromDate);
}
if (toDate != null)
{
votes = votes.Where(r => r.VoteDate.Value <= toDate);
}
votes = votes.Take(LimitRows).OrderByDescending(r => r.VoteDate);
Solution 3
You can also use the PredicateBuilder from LinqKit to chain multiple typesafe lambda expressions using Or or And.
http://www.albahari.com/nutshell/predicatebuilder.aspx
Solution 4
A simple Approach can be if your Columns are of Simple Type like String
public static IEnumerable<MyObject> WhereQuery(IEnumerable<MyObject> source, string columnName, string propertyValue)
{
return source.Where(m => { return m.GetType().GetProperty(columnName).GetValue(m, null).ToString().StartsWith(propertyValue); });
}
Solution 5
It seems much simpler and simpler to use the ternary operator to decide dynamically if a condition is included
List productList = new List();
productList =
db.ProductDetail.Where(p => p.ProductDetailID > 0 //Example prop
&& (String.IsNullOrEmpty(iproductGroupName) ? (true):(p.iproductGroupName.Equals(iproductGroupName)) ) //use ternary operator to make the condition dynamic
&& (ID == 0 ? (true) : (p.ID == IDParam))
).ToList();
Keith Barrows
Keith lives in Florida and specializes in Information Technology applications utilizing web technologies. He has been working in software ever since high school and stepped forward as a professional in the early 1990s. He is very good at figuring out new things on the fly. Technology is always changing. What is hot today is a memory tomorrow. Realizing this early on he spent his time becoming a Self-Sufficient Developer, somebody who can learn new things as they arise. He has demonstrated a passion to be highly proficient in any project he tackles. With over 20 years of experience Keith has consulted on all aspects of the software development life cycle from design and development to quality assurance and maintenance. He has worked in both the Agile and Water Fall methodologies of software creation, to include Scrum, Kan-ban and XP. Keith has a broad set of skills in the web sphere from light UI design to a deeper server-side knowledge of .NET including Core, Entity Framework, Web Forms, MVC, Web API, C#, VB as well as T-SQL and NoSql. Some of Keith's highlights include: Designed and built a Web Forms based system to replace a highly manual and error prone process that ended up saving the client over $10 million in governmental fines. Worked on updating a legacy system that could no longer handle the client load allowing the system to go from 100 clients with 100 users each to 500 clients with 250 users each. Experience leading 3 to 10 member development teams. Volunteered to be part of a 4 man team to self lead the development teams consisting of 26 developers and 8 QA personnel as the company was missing a CTO. Currently, Keith is an independent consultant with 20+ years of industry experience actively pursuing an even deeper understanding of .NET, cloud based security and web development. He is active on Stack Overflow, was a former ASP.NET MVP and a founding member of the ASPInsiders.
Updated on July 05, 2022Comments
-
Keith Barrows almost 2 years
What is the best way to assemble a dynamic WHERE clause to a LINQ statement?
I have several dozen checkboxes on a form and am passing them back as: Dictionary<string, List<string>> (Dictionary<fieldName,List<values>>) to my LINQ query.
public IOrderedQueryable<ProductDetail> GetProductList(string productGroupName, string productTypeName, Dictionary<string,List<string>> filterDictionary) { var q = from c in db.ProductDetail where c.ProductGroupName == productGroupName && c.ProductTypeName == productTypeName // insert dynamic filter here orderby c.ProductTypeName select c; return q; }
-
Ryan Gates about 8 yearsThere is a ported version on github (github.com/kahanu/System.Linq.Dynamic), which I contribute to and help manage.
-
user6121177 over 6 yearsBest suited for my need and easy to use. Thank you.
-
FelixSFD over 6 yearsA link to a solution is welcome, but please ensure your answer is useful without it: add context around the link so your fellow users will have some idea what it is and why it’s there, then quote the most relevant part of the page you're linking to in case the target page is unavailable. Answers that are little more than a link may be deleted.
-
KJM over 6 yearsI do apologize. I am new here.
-
khalil over 4 yearspossible to do other than string ?
-
Sushant Yelpale over 4 yearsbrilliant Answer !!
-
harvzor over 2 yearsMy application is complaining that this can't be translated to SQL (I'm using Postgres provider). Perhaps your code is running in memory rather than on the db? This would lead to bad performance on large datasets.
-
MtnManChris about 2 yearsBoth links are not completely valid. Scott Guthrie's Blog is missing the code and the System.Linq.Dyanmic is a 404