SQL Like keyword in Dynamic Linq

Question

Hi fellow programmer

I want to use SQL's Like keyword in dynamic LINQ.

The query that I want to make is like this

select * from table_a where column_a like '%search%'

Where the column_a can be dynamically changed to other column etc

In this dynamic LINQ

var result = db.table_a.Where( a=> (a.column_a.Contains("search")) );

But the column can't be dynamically changed , only the search key can

How do we create a dynamic LINQ like

var result = db.table_a.Where("column_a == \"search\"");

That we can change the column and the search key dynamically

Answer 1

http://weblogs.asp.net/rajbk/archive/2007/09/18/dynamic-string-based-queries-in-linq.aspx

Addition:

Use an expression tree http://stackoverflow.com/questions/278684/how-do-i-create-an-expression-tree-to-represent-string-containsterm-in-c That is what the dynamic linq library does internally.

Answer 2

I do not believe there is a direct translation to SQL for the LIKE keyword in LINQ. You could build one if you used expression trees, but I haven't gotten that good yet.

What I do is something like this:

using System.Data.Linq.SqlClient;

if (!string.IsNullOrEmpty(data.MailerName))
    search = search.Where(a => SqlMethods.Like(a.Mailer.Name, string.Format("%{0}%", data.MailerName)));

where search is the query I'm building and data is the object containing the properties that hold the search criteria. I build the query dynamically by listing all of the possible search criteria in this way, which adds the appropriate Where methods to search.