Using LINQ Contains versus SqlMethods.Like

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开发者_StackOverflow社区 would like this query to be more purely LINQ if possible.

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 %...

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.

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.)

consider something like this for membership profile information:

matches = matches.Union(memberDB.aspnet_Profile
                    .Where("it.PropertyValuesString Like @first", new ObjectParameter("first", "%<FirstName>%" + firstName + "%</FirstName>%"))
                    .Select(p => p.UserId));