[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1129
  • Last Modified:

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!
0
winmyan
Asked:
winmyan
  • 4
  • 3
1 Solution
 
Fernando SotoCommented:
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 SotoCommented:
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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
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 SotoCommented:
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
 
winmyanAuthor Commented:
Awesome! It is exactly what I have been looking for...

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

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now