Link to home
Start Free TrialLog in
Avatar of winmyan
winmyan

asked on

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!
Avatar of Fernando Soto
Fernando Soto
Flag of United States of America image

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
Avatar of winmyan
winmyan

ASKER

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?
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
Avatar of winmyan

ASKER

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...
ASKER CERTIFIED SOLUTION
Avatar of Fernando Soto
Fernando Soto
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of winmyan

ASKER

Awesome! It is exactly what I have been looking for...

Fernando, thank you so much!!!
Not a problem, glad I was able to help.  ;=)