views:

302

answers:

3

I want to build a dlinq query that checks to see if a title has any number of items in it. I know you can do .Contains() with a list, but I need to check if the title contains any of the items, not if the items contain part of the title. For example: I have three items in the list "bacon, chicken, pork." I need the title of "chicken house" to match.

var results = (from l in db.Sites

where list.Contains(l.site_title) select l.ToBusiness(l.SiteReviews)).ToList();

If I try the first 2 answers, I get an error "Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains() operator."

The third solution gives me

Method 'System.Object DynamicInvoke(System.Object[])' has no supported translation to SQL."

+2  A: 

Try the following. You can use a combination of Where and Any to search for substring matches.

var results = (from l in db.Sites
                where list.Where(x => 0 != x.IndexOf(l)).Any()
                select l.ToBusiness(l.SiteReviews)).ToList();
JaredPar
i love linq .. and it's when u see answers like this (assuming it works) you love it even more!
Pure.Krome
@Pure i love LINQ and lambdas :)
JaredPar
+1  A: 

One way is to dynamically build a query as outlined here:

http://andrewpeters.net/2007/04/24/dynamic-linq-queries-contains-operator/

Andrew Peters
This seems like where I want to be. The only problem I have left is that it appears as though QueryExpression.Lambda doesn't exist.
Matthew Kruskamp
Well that didn't work either. :(. "Method 'System.Object DynamicInvoke(System.Object[])' has no supported translation to SQL."
Matthew Kruskamp
Sorry, that code is pretty old. Try Expression.Lambda
Andrew Peters
Basically, you need to build a lambda expression of the form: l => l.site_title == list[0] || l.site_title == list[1] ...
Andrew Peters
+1  A: 

I finally figured it out. Thanks to my good buddy Cory for the help. There are two ways you can do it.

var resultSets = (from k in list
                  select (from b in db.Sites
                          where b.site_title.Contains(k)
                          select b.ToBusiness()).ToList<Business>()).ToList();

 List<Business> all = new List<Business>();

 for (int i = 0; i < resultSets.Count; ++i)
 {
     all.AddRange(resultSets[i]);
 }

This is a linq query that will successfuly do what is stated. As well, you can also just build the sql query in plain text by hand.

Matthew Kruskamp