Performing a Contains within an Intersect

Question

I have a fairly complicated linq query that returns an item and a string[] of one column in the item's many-to-many relationship.

So I return an anonymous type sort of like this:

var q = from k in db.Items
        join m in db.ManyRel on k.Id equals m.ItemID
        select new
        {
            k,
            list = (from t in m
                    where t.ItemID == k.ID
                    select t.Name)
        }

So the anonymous type is {IQueryable<k>, IQueryable<string>}.

What I then want to do is search through the list for intersections from string[] input.

q.Select(x => new 
                 { 
                    x.k, 
                    PartialMatches = ?????, 
                    ExactMatches = x.tags.Intersect(input).Count()
                 });

I currently have it working with .Intersect, but .Intersect() only matches against the entire element. I would like to somehow use .Contains() within .Intersect() to count the number of partial matches.

I would prefer to do this without an extension method if possible.

Edit: @Jon & Timwi

I tried both of your versions, they were pretty much the same, and they find if ANY item in input partially exists within x.tags. I'd like to be able to count how many items in input partially exist within x.tags, not just whether AN item exists. Thanks for the responses.

I think I have a better handle on the AsEnumerable() stuff than I did a few nights ago.

Also, for the record, because you where both curious, the Intersect() method does not translate to Linq to SQL and neither do the any methods you each proposed. They all work well and fast enough with AsEnumberable() however.

Answer 1

Well, the way you can express it is:

 PartialMatches = x.tags.Where(tag => input.Any(word => word.Contains(tag)))

(or vice versa - you haven't said which way you want the containment to work).

However, I don't expect this to be translated into SQL successfully... I'm not at all sure that you can get LINQ to translate this into SQL, I'm afraid.

Answer 2

You don’t need to use Intersect, and it doesn’t need to have a translation to SQL. You can do it in C#:

var results = q.AsEnumerable().Select(x => new 
              { 
                 x.k, 
                 PartialMatches = x.tags.Count(tag => input.Any(
                                     word => word.Contains(tag))),
                 ExactMatches = x.tags.Intersect(input).Count()
              });

The call to AsEnumerable() detaches this part of the query from the database. Everything before it will be retrieved from the database, and the rest will be processed in C#.

You could, in theory, do the ExactMatches in SQL (if LINQ to SQL can translate it to SQL, which I’m not sure it can), but it is not obvious whether that is more efficient that doing it in C#. It depends on the relative sizes of the collections involved, including input.

Answer 3

Try below code

 q.Select(x => new  
             {  
                x.k,  
                PartialMatches = from u in x.tags group u by u.Contains(input),
                ExactMatches = x.tags.Intersect(input).Count() 
             }); 

here you need to manully iterate PartialMatches in client code.