Linq contains for SQL Like %

Question

Basically I'd like to use a LINQ statement for an sql like %. I thought .Contains would be appropriate but I don't get the right result.

I have a model:

public class Model {
    public string Number { get; set; }
}

Inside my function:

private List<Model> getSearchResults(List<Model> models) 
{
    List<Model> result = models.Where(m => m.Number.Contains("3330")).ToList();

    return result;
}

Inside my models list is definitively an entry with number 3330, but the returned result is empty. :( This should also work with .Contains("333"). But it doesn't.

Please show a short but complete program demonstrating the problem. We don't know enough about the types involved to help you yet. The `Contains` method certainly *does* work, so the problem is likely to be how you're using it. Note that if `l.Number` is just a string, then you're not using the LINQ `Contains` method at all, but the `String.Contains` method.) — Jon Skeet, Feb 06 '14 at 12:15
List is from type List and is filled with content from my database. Number is a string. — mosquito87, Feb 06 '14 at 12:16
That doesn't really help us (partly because your comment may have included generic types but without using a backtick to quote, thus losing the types from what we can see). Please edit your post with a short but complete example. — Jon Skeet, Feb 06 '14 at 12:18
One thing i don't understand here. Why do u want to use contains for matching a single value. Contains is an equivalent to sql 'IN'. I don't think its appropriate using it here. — Saket Kumar, Feb 06 '14 at 12:32
I thought contains is the same as LIKE. In sql it would be: SELECT * FROM table WHERE number LIKE '%3330%'. So it will find the entry with number '3330', 'A3330', '3330Z' etc. — mosquito87, Feb 06 '14 at 12:33
@mosquito87 `Contains` most definitely works, look at this [sample code](https://ideone.com/o3guqK). The problem is with your logic or how you are using it. — James, Feb 06 '14 at 12:34
@mosquito87 NO Contains is an equivalent for IN. I have shared the link in answer. Plz check. — Saket Kumar, Feb 06 '14 at 12:37

score 2 · Answer 1 · answered Feb 06 '14 at 12:18

2

I suspect you're actually using the linq-to-sql provider. If that's the case, then you should use SqlMethods.Like instead of String.Contains:

list.Where(l => SqlMethods.Like(l.Number, "%12%")).ToList();

answered Feb 06 '14 at 12:18

dcastro

66,540
21
145
155

I'm not using the linq-to-sql provider. Sorry. – mosquito87 Feb 06 '14 at 12:27
-1 What makes you suspect that? There is absolutely no mention of L2S nor does the OP mention any sort of exception using `Contains` (which I am *pretty* sure L2S throws). This is guesswork. – James Feb 06 '14 at 12:28
1

@James An educated guess though. The OP did mention that the list contains data retrieved from the database, in the comments. And since L2S doesn't support `String.Contains`, this seemed like the case. It was a fair assumption to make, in my opinion. Tbh, I had no idea whether an exception is thrown. – dcastro Feb 06 '14 at 12:30

score 0 · Answer 2 · edited May 23 '17 at 12:28

0

Contains statements should be used like sql operator 'IN'. See the link for details:

tip-8-writing-where-in-style-queries-using-linq-to-entities

I think you are looking for something like this:

how-to-do-sql-like-in-linq

You should use like this:

List<Model> result = models.Where(m => m.Number.Contains("/3330/")).ToList();

You can also use .StartsWith() or .EndsWith()

edited May 23 '17 at 12:28

Community

1
1

answered Feb 06 '14 at 12:35

Saket Kumar

4,363
4
32
55

I'm doing exactly the same thing as provided in your second link, right? – mosquito87 Feb 06 '14 at 13:07
Thanks for your effort. But this is definitively not working! The solution you posted is for finding entries with "/3330/", but I'm looking for "3330". The original author had to use "/12/", as he was looking for a month! – mosquito87 Feb 06 '14 at 13:45

Sergey · Answer 3 · 2014-02-07T04:14:27.337

Ok, here is my sample of code for you issue, and yes, the Contains method certainly does work:

using System;
using System.Linq;
using System.Collections.Generic;

public class Program
{
    public class Model {
        public string Number { get; set; }
    }

    private static List<Model> getSearchResults(List<Model> models) 
    {
        List<Model> result = models.Where(m => m.Number.Contains("3330")).ToList();

        return result;
    }

    public static void Main()
    {
        List<Model> list = new List<Model>() {
            new Model() { Number = "13330A"},
            new Model() { Number = "13230A"},
            new Model() { Number = "3330A"},
            new Model() { Number = "543330"},
            new Model() { Number = "48913"},
            new Model() { Number = "97798133"},
            new Model() { Number = "542130"}
        };

        foreach(Model v in getSearchResults(list)) {
            Console.WriteLine(v.Number);
        }      
    }
}

and output is:

13330A
3330A
543330

Check your code, you can play with this example on http://dotnetfiddle.net/143UgC

Linq contains for SQL Like %

3 Answers3