LINQ - Search value in dynamic column

I have a dropdownlist for search field, and a text box for search value. User can search by selecting a search field from dropdownlist and type in the textbox.

@SearchField nvarchar(50)
@SearchValue nvarchar(50)

SELECT * FROM Customer WHERE @SearchField LIKE @SearchValue

I would not know the name of the column in design time. I have tried to do the same feature using ExecuteQuery<Customer >(query), but I don't really like it.

How can I write that kind select statement in LINQ (Example, Customer.where(c => c....) . Thank you for your comments!
winmyanAsked:
Who is Participating?
 
Fernando SotoConnect With a Mentor RetiredCommented:
Hi winmyan;

In Linq Contains( ) is equivalent to the Like in TSQL, so try it this way to see if it works.

var query= dataContext.Customers.Where(searchField + ".Contains(@0)", searchValue);

Fernando
0
 
Fernando SotoRetiredCommented:
Hi winmyan;

Because your field name is dynamic about the only other way is to use a library Microsoft had put out but not part of the Linq distribution called LINQ Dynamic Query Library. A member of the Microsoft staff has a blog giving info on it and where to download the extensions from on the Microsoft site. This extension will allow you to do something like this:

Customer.where("CustomerFieldName = ValueFromTextBox")

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

Fernando
0
 
winmyanAuthor Commented:
Hi Fernando,

Thank you for your comment. Do I need to modify Dynamic.cs in order to use in my project?

Or just simply put it in the folder where dbml is located and use it right away?
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Fernando SotoRetiredCommented:
Hi winmyan;

All that needs to be done is to copy the file Dynamic.cs file to the project you want to use it in and then you will have access to it in the project. You will also need a using statement:

using System.Linq.Dynamic;

Fernando
0
 
winmyanAuthor Commented:
Hi Frenando,

I downloaded and tried dynamic link class. It doesn't let me use " LIKE ", although it is fine with " = ". Am I missing something?

var query= dataContext.Customers.Where(searchField + " LIKE @0", searchValue);

Some post mention the following code. But it is not really efficient for me becuase I have many data in searchFields.
var query = from C in db.Customers select c;
if (seachFirstName)        
          query = query.Where(c=>c.ContactFirstname.Contains(searchValue));
if (seachLastName)
          query = query.Where(c=>c.ContactLastname.Contains(searchValue));
if (seachAddress)
          query = query.Where(c=>c.Address.Contains(searchValue));

Any luck for me! Thank you again for your help...
0
 
winmyanAuthor Commented:
Awesome! It is exactly what I have been looking for...

Fernando, thank you so much!!!
0
 
Fernando SotoRetiredCommented:
Not a problem, glad I was able to help.  ;=)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.