LINQ to SQL where clause verifying string contains list element

Question

I'm using a view returning Domains according to an id. The Domains column can be 'Geography' or can be stuffed domains 'Geography,History'. (In any way, the data returned is a VARCHAR)

In my C# code, I have a list containing main domains:

private static List<string> _mainDomains = new List<string>()
{
    "Geography",
    "Mathematics",
    "English"
};

I want to filter my LINQ query in order to return only data related to one or many main Domain:

expression = i => _mainDomains.Any(s => i.Domains.Contains(s));
var results = (from v_lq in context.my_view
                select v_lq).Where(expression)

The problem is I can't use the Any key word, nor the Exists keyword, since they aren't available in SQL. I've seen many solutions using the Contains keyword, but it doesn't fit to my problem.

What should I do?

Running the query using the `Any` keyword generates this error: Local sequence cannot be used in LINQ to SQL implementations of query operators except the Contains operator. — Francis P, Sep 07 '12 at 20:13

score 3 · Answer 1 · answered Sep 07 '12 at 20:15

3

You can use contains:

where i.Domains.Any(s => _mainDomains.Contains<string>(s.xxx))

Notice that the generic arguments are required (even if Resharper might tell you they are not). They are required to select Enumerable.Contains, not List.Contains. The latter one is not translatable (which I consider an error in the L2S product design).

(I might not have gotten the query exactly right for your data model. Please just adapt it to your needs).

answered Sep 07 '12 at 20:15

usr

168,620
35
240
369

It does fit your need if you try. I just edited my post to make the code a little easier to adapt. I did not understand your model fully so you have to make it work fully yourself. I know from experience that Contains is what you need, though. You need to call contains on the local collection. – usr Sep 07 '12 at 20:19
2

use `Contains` from the other direction *not s.Contains but _mainDomains.Contains(s)* – S3ddi9 Sep 07 '12 at 20:21
what is suposed to be `s.xxx` ? – Francis P Sep 07 '12 at 20:28
Can't get _mainDomains.Contains(s) to be built. (Error: 'Error 11 'System.Collections.Generic.List' does not contain a definition for 'Contains' and the best extension method overload 'System.Linq.Queryable.Contains(System.Linq.IQueryable, TSource)' has some invalid arguments.') – Francis P Sep 07 '12 at 20:34
@FrancisP, s is of type Domain (at least I think so. Correct?). You can't search a list of strings with a Domain as argument. You need to provide a string as an argument. So you probably want to write s.Name or s.DomainName or whatever the string property is called on your Domain class. Also, Séddik Laraba is right: It matters where you call Contains. – usr Sep 07 '12 at 20:34
Maybe I wasn't clear anough but Domains is the column in my view and it can be stuffed domains (so a string looking like this: 'Geography,History,English')....so s is of type string. – Francis P Sep 07 '12 at 20:36
Ok that changes everything. I did not pick that up.; This is impossible for an arbitrary list size. If the list size is fixed you must do this (Domains.Contains(list[0]) || Domains.Contains(list[1]) || ...). This is the sad truth. If the list size is dynamic, you *must* build an expression tree manually using the Expression class. There is no other way, alas. – usr Sep 07 '12 at 20:48

Francis P · Accepted Answer · 2012-09-10T13:56:31.633

0

I figured it out. Since I can't use the Any keyword, I used this function:

    public static bool ContainsAny(this string databaseString, List<string> stringList)
    {
        if (databaseString == null)
        {
            return false;
        }
        foreach (string s in stringList)
        {
            if (databaseString.Contains(s))
            {
                return true;
            }
        }
        return false;
    }

So then I can use this expression in my Where clause:

expression = i => i.Domains.ContainsAny(_mainDomains);

Update: According to usr, the query would return all the values and execute the where clause server side. A better solution would be to use a different approach (and not use stuffed/comma-separated values)

edited Sep 10 '12 at 13:56

answered Sep 07 '12 at 20:46

Francis P

13,377
3
27
51

1

Ok it does work but it is not what you want. This is doing the filtering on the client. This is a performance problem getting bigger the bigger the dataset grows. – usr Sep 07 '12 at 20:50
I mean you can *always* solve anything with client-side queries but that has huge drawbacks. – usr Sep 07 '12 at 20:51
so if the list has ~ 20 strings, it would be better to use 20 ORs? – Francis P Sep 07 '12 at 20:55
Yes... That might sound strange but it is the only way to remote the query over to SQL Server so that you don't pull the entire table over the network into memory. – usr Sep 07 '12 at 20:57
Oh of course there is a different solution: Don't stuff the domains into one text field. Instead save them as multiple rows in a separate table. That way you can use the code from my answer (it was the way I originally understood the question!). – usr Sep 07 '12 at 21:03
Yes thought of that too. thanx – Francis P Sep 07 '12 at 21:04

LINQ to SQL where clause verifying string contains list element

2 Answers2