40

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.

Curtis White
  • 6,213
  • 12
  • 59
  • 83
  • Please note everyone I am discussing the DYNAMIC LINQ that comes with Visual Studio in the examples file. In this version, I can define a where query using a string "mycol = @mycol". I know of the simple cases. – Curtis White Mar 16 '10 at 15:38
  • What I'd *really* like to do is insert the string "Like" in place of the "=" in my string and have the Dynamic Linq to parse it. But, I'll take a work around. I think the reason it was not added is they built their parser on System.Linq.Expressions which doesn't have this method. – Curtis White Mar 16 '10 at 15:43
  • To be clear, I can do this "UserName = @0,Contact.FirstName = @1" But not this "UserName like @0,Contact.FirstName like @1" Where these are strings, and using Dynamic LINQ syntax: dc.table.where(mystring, array) – Curtis White Mar 16 '10 at 15:57

3 Answers3

64

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.

Curtis White
  • 6,213
  • 12
  • 59
  • 83
  • 1
    How can I do that on a nullable column? – Thea Jul 09 '12 at 13:34
  • 8
    @Teddy .Where("MyColumn != null && MyColumn.Contains(@0)", myArray) – Xaris Fytrakis Oct 14 '13 at 11:51
  • 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)? – PeterX Mar 04 '15 at 00:41
  • 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 Sep 24 '15 at 20:10
  • 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. – Triynko Sep 24 '15 at 21:37
  • How can we do that case insensitive? – Can Ürek Jun 21 '16 at 07:19
12

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.

user2042930
  • 640
  • 1
  • 7
  • 14
4

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.

spender
  • 117,338
  • 33
  • 229
  • 351