Dynamically generate a linq

Question

I have a form with several optional text boxes. If a textbox is filled that would become a part of the search criteria. How do I dynamically generate a where clause in Linq depending if one of several search criteria has been requested by the operator?

---

Certified Geek. Data dude.

Answer 1

public List<Table> Search(string column1Search, string column2Search)
{
 MyDataContext dc = new MyDataContext();
 IEnumerable<Table> results = (from row in dc.Table 
 //Maybe a where statement here
 select row);
 if(!String.IsNullOrEmpty(column1Search))
 {
 results = (from row in results
 where row.Column1.ToLower().
  Contains(column1Search.ToLower())
 select row);
 }
 if(!String.IsNullOrEmpty(column2Search))
 {
 results = (from row in results
 where row.Column2.ToLower().
  Contains(column2Search.ToLower())
 select row);
 }
 return results.ToList();
}


 

This will delay going to the database for values until ToList is called, or the IEnumerable is iterated over.

You can also return the IEnumerable<Table> and delay the database call until later.

You can also you SqlMethods Like within like which will be a little faster.  You have to add an assembly.  Msdn SqlMethod.Like

Answer 2

Interesting . . .

Will that work if more than one input box is filled out?

---

I should have asked:
I have a form with several optional text boxes. If a textbox is filled that would become a part of the search criteria. How do I dynamically generate a where clause in Linq depending if one or more of several search criteria has been requested by the operator?

 

---

 

Certified Geek. Data dude.

 

Answer 3

Yeah your just appending to the query.  It will work reguardless of which are filled out, all, none, the last, the first, etc.. 

Debug and onhover the result you will see the SQL query grow as it is needed.

 

The where clause will grow based on the input to that function.

So you could ideally call it like...

List<Table> results = TableBLL.Search(Textbox1.Text.Trim(), Textbox2.Text.Trim());

If the Search method was within a Business Logic Layer class called TableBLL.

 

BTW the format for Like is

where SqlMethods.Like(row.Column1, String.Format("%{0}%", column1Search))

Answer 4

Hello Garvander,

 

Welcome to LINQ to SQL forum!

 

I think LitEnders’s posts are very helpful to create such a dynamic LINQ query.   Besides, you can also provide us with more detailed information about your scenario, some samples may be very helpful to us.  

 

The end users may input several search values into different text boxes, or maybe they input several values into one single text box.   Under different condition, we may need distinct code logic.

 

For additional references about dynamic LINQ queries in LINQ to SQL, see Dynamic LINQ Library and PredicateBuilder class:

http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

http://www.albahari.com/nutshell/predicatebuilder.aspx

 

Have a nice weekend, both!

 

 

Best Regards,
Lingzhi Sun

MSDN Subscriber Support in Forum

If you have any feedback on our support, please contact [e-mail]

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 5

Hi,

Great ideas have been thrown in this thread and I just want to add my two cents by sharing a blog post related to this topic: LINQ: How to build complex queries utilizing deferred execution and anonymous types

Of course, the ultimate flexibility will be available if we use the PredicateBuilder or Dynamic LINQ Library but the step by step filtering posted by LitEnders is a very nice approach for simple scenarios.

Have a great weekend, all of you.

Regards,

---

Syed Mehroz Alam
My Blog | My Articles

Answer 6

Hi Garvander,

 

I am writing to check the status of the issue on your side.  Would you mind letting us know the result of the suggestions? 

 

If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

 

Have a nice day!

 

 

Best Regards,
Lingzhi Sun

MSDN Subscriber Support in Forum

If you have any feedback on our support, please contact [e-mail]

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.