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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

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
Fernando SotoRetiredCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.