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.
views:
721answers:
3
+1
A:
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.
Lette
2010-01-30 21:46:22
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.
Ahmad Mageed
2010-01-30 22:13:08
@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.
Lette
2010-01-30 22:19:44
@Lette 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.
Ahmad Mageed
2010-01-30 22:27:38
@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`.
Lette
2010-01-30 22:34:28
@Lette 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 :)
Ahmad Mageed
2010-01-30 23:15:14
+5
A:
Simply use the regular String.Contains method on it:
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.
Simply use the regular String.Contains method on it:
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 %...
Ahmad Mageed
2010-01-30 21:53:45
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.
Lette
2010-01-30 22:01:11
@Lette I believe so since the OP is using the SqlMethods class which is only supported in LINQ to SQL (http://msdn.microsoft.com/en-us/library/bb355235.aspx).
Ahmad Mageed
2010-01-30 22:04:13
@Ahmad Mageed - I knew that, but the question isn't tagged 'linq-to-sql'...
Lette
2010-01-30 22:11:37
@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`.
Ahmad Mageed
2010-02-01 14:43:39
+1
A:
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.)
Ruben
2010-01-30 22:45:12