How to use "contains" or "like" in a dynamic linq query?

Question

The help file that came with Dynamic Linq in the CSharpSamples.zip does not show any examples of using contains or like.

Are there any simple workarounds for doing this? i.e where (col like @col) doesn't work.

Solution 1

Here is the answer! The Dynamic Linq does support the . operator,

According to the docs:

"Instance field or instance property access. Any public field or property can be accessed."

Thus, it is possible to use this syntax

.Where("MyColumn.Contains(@0)", myArray)

Thanks for all the suggestions! And thanks to me for finding the solution.

Solution 2

For me the solution was outerIt.

   class User { public string Name { get; set; } }
   ...
   IQueryable<User> query = db.Users;
   ...
   query = query.Where("@0.Contains(outerIt.Name)", list);

Note that outerIt is kind of a keyword built in the library (you don't need to modify it as you can read it in an answer here). You can access the property of your query's type through it.

Solution 3

Actually, there is direct support for the like operator in Linq2Sql:

db.MyTable.Where(a => SqlMethods.Like(a.Name, "%"+searchTerm+"%"))

See here:

http://msdn.microsoft.com/en-us/library/system.data.linq.sqlclient.sqlmethods_members.aspx

... but I prefer using startsWith, endsWith and contains for most applications.

Comments

[Thea]

How can I do that on a nullable column?

[Xaris Fytrakis]

@Teddy .Where("MyColumn != null && MyColumn.Contains(@0)", myArray)

[PeterX]

How do you convert a Grid FilterExpression to this syntax? There are some NuGet Dynamic LINQ libraries, including .NET 4.0 support... do we know if any have added conversion support from LIKE to Contains (or other)?

[Triynko]

Yeah, you better check for null once SQL Server permanently switches on ANSI_NULLs in a future release (2016?). x not like '%something%' and x like '%something%' will BOTH evaluate to false when x is null, because the result of the comparison in both cases is unknown. Very unintuitive. Alternatively, null-coalese your fields to an empty string before comparing like so: (MyColumn ?? "").Contains(@0), which LINQ will hopefully translate to the SQL ISNULL function call before the 'like' comparison.

[Triynko]

I wonder if I can use this in a computed column for a search? I would like to use the above syntax in a Select call, so I can return Boolean columns telling me whether the value contains a search term.

[Can Ürek]

How can we do that case insensitive?