Using LINQ Contains versus SqlMethods.Like

Question

How do I replicate the following result in my LINQ query without calling in the helper library System.data.Linq.SqlClient?

Where SqlMethods.Like(e.POSITION, "%A[FGL]7%") _

I would like this query to be more purely LINQ if possible.

Solution 1

EDIT: based on my comments with Lette I initially missed the pattern matching which SqlMethods.Like supports.

Your query looks like VB so you can actually use the Like operator directly but you'll need to replace the % with an asterisk *:

Where e.POSITION Like "*A[FGL]7*" _

If you're using C# you'll need to either use Lette's suggestion after calling AsEnumerable() to get the data into memory (perhaps filter on any other criteria first then call the regex for further filtration) or follow Ruben's suggestion.

The methods I mentioned below do not act 100% like the SqlMethods.Like method since the pattern is essentially escaped. In other words it only treats it as literal text.

---

Where e.POSITION.Contains("A[FGL]7")

LINQ to SQL will translate .NET methods in this manner:

• text.StartsWith(...) = LIKE ...%
• text.Contains(...) = LIKE %...%
• text.EndsWith(...) = LIKE %...

Solution 2

Clarification: This answer is written with the assumption that this is not a LINQ to SQL question. If it is, please turn to the other answer(s).

For this to work, you would have to mimic the Like operator from SQL. This is in your case probably best done with a regular expression.

Here's an example:

using System.Text.RegularExpressions;

...

Regex regex = new Regex("^.*A[FGL]7.*$");

// assuming that 'e' is a sequence of elements of type Foo
IEnumerable<Foo> results = e.Where(foo => regex.IsMatch(foo.POSITION));

The results should now be filtered according to your needs.

Solution 3

Most LINQ providers support the VB Like operator, which does pretty much the same. From C# you can access this functionality through the Microsoft.VisualBasic.CompilerServices.LikeOperator's LikeString method:

using Microsoft.VisualBasic;
using Microsoft.VisualBasic.CompilerServices;

...

where LikeOperator.LikeString(e.POSITION, "*A[FGL]7*", CompareMethod.Text)

Do beware that VB's Like uses slightly different syntax than SQL's Like; notably it uses * and ? rather than % and _. (LINQ to SQL translates this VB syntax to SQL syntax, though.)

(Don't forget to reference Microsoft.VisualBasic.dll though.)

Comments

[Christoffer Lette]

Is this a Linq-To-Sql question? If so, you're probably correct. I'm under the impression that the OP wants a "pure" Linq solution.

[Ahmad Mageed]

@Lette I believe so since the OP is using the SqlMethods class which is only supported in LINQ to SQL (msdn.microsoft.com/en-us/library/bb355235.aspx).

[Ahmad Mageed]

It's important to note that using a regex in LINQ to SQL will throw a NotSupportedException since it "has no supported translation to SQL." The only way for this to work would be to use .AsEnumerable() on the table which is usually undesirable since the whole table would come across unfiltered. Regex is great but for this simple case the methods provided by the string class would suffice even if it weren't a LINQ to SQL query.

[Christoffer Lette]

@Ahmad - You will have to remember that this answer is written with the assumption that it is not a linq-to-sql question. In this context the string.Contains would not suffice. The OP clearly wants to filter by a pattern.

[Ahmad Mageed]

I absolutely agree and I didn't mean to attack your answer. If this were L2S then my comment is valid. Otherwise your solution is perfectly valid. However, I disagree with your take on String.Contains. The pattern %A[FGL]7% is equivalent to your regex; ie it occurs anywhere in the string, so the string does "contain" it and String.Contains would work.

[Christoffer Lette]

@Ahmad - No offense taken. I'm just saying that AG7 matches the regex. String.Contains does not use patterns, so AG7 does not contain A[FGL]7.

[Ahmad Mageed]

doh! I totally missed the [FGL] class and its relevance to LIKE. You're correct, the methods I mentioned do not take this into account (updated my response) and will not recognize patterns. I'm out of votes for another 45 mins so I'll def. +1 by then. Good catch :)

[mmcglynn]

The String.Contains method as described above returns no results.

[Ahmad Mageed]

@mmcglynn yes, it wouldn't since it doesn't recognize patterns the way SqlMethods.Like does. I realized this after posting it but kept it to show the difference. They would only be useful if you have a fixed string, not a pattern. Otherwise use the VB Like operator or SqlMethods.Like.