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