Using the ALL operator in linq to filter child items of EntitySet

Question

Hi,

I have a two objects as follows:

public class Item
{
    public int ItemId {get;set;}
    public string ItemName {get;set;}
    public List<Tag> ItemTags {get;set;}
    public DateTime DateCreated {get;set;}
}

public class Tag
{
    public int TagId {get;set;}
    public string TagName {get;set;}
}

These are LINQ-to-SQL objects, so the ItemTags will be an EntitySet.

I am trying to perform a search query where a user can provide a comma delimited list of tags as a search filter.

How do I filter my list of items to those which contains all of the tags in the comma delimited list.

EDIT2

e.g.
Item1 has tags of Apple, Banana, Orange
Item2 has tags of Banana, Orange
Item3 has tags of Pineapple, Orange
If the tag filter is "Banana, Orange" I need the results to be Item1 and Item2.

/EDIT2

This is what I have tried thus far:

string tags = "Manchester United,European Cup,2008";
List<string> tagsList = tags.Trim().ToLower()
    .Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries)
    .Distinct(StringComparer.CurrentCultureIgnoreCase)
    .ToList();

List<Item> itemList = ItemRepository.FetchAll();

var query = itemList
    .OrderByDescending(p => p.DateCreated)
    .ToList();

if (tagsList.Count() > 0)
{
    query = query
        .Where(p => p.ItemTags
            .Select(q => q.TagName.ToLower())
            .All(r => tagsList.Contains(r)))
        .ToList();
}

However, this doesn't seem to work. Any ideas on what I am doing wrong please?

EDIT1: tags are trimmed and are 'lowercased'.

Answer 1

That because you're puting the tags from the items to lowercase, but not the searched tags.

With this modification it should work:

List<string> tagsList = tags
    .Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries)
    .Select(s => s.ToLower())
    .Distinct()
    .ToList();

---

EDIT: OK, I see what the problem is: you're doing it backwards. You're searching for items that have only the tags that you're looking for.

Try that instead:

query = 
    (from item in query
     let itemTags = p.ItemTags.Select(it => it.TagName.ToLower())
     where tags.All(t => itemTags.Contains(t))
     select item).ToList();

---

UPDATE: here's a version with the lambda syntax. It's pretty ugly because of the temporary anonymous type, but that's how the let clause translates to lambda...

query =
    query.Select(item => new { item, itemTags = item.ItemTags.Select(it => it.TagName.ToLower()) })
         .Where(x => tagsList.All(t => x.itemTags.Contains(t)))
         .Select(x => x.item)
         .ToList();

Answer 2

I think you need to do something like this:

var query = itemList.OrderByDescending(p => p.DateCreated).ToList();

var results = query.Where(i => i.ItemTags
   .All(it => tagsList.Contains(it.TagName.ToLower())));

Then results should then be a list of matching items.

PS. Your code shows you fetching itemList as a List from your repository and then sorting by date created. This means the sorting isn't being done in the database. Once you turn something into a List you give up the benefits of deferred execution as you will bring back the entire collection into memory.

EDIT: Here's the test code to prove it works in Linq to Objects:

public class Item
{
    public int ItemId { get; set; }
    public string ItemName { get; set; }
    public List<Tag> ItemTags { get; set; }
    public DateTime DateCreated { get; set; }
}

public class Tag
{
    public int TagId { get; set; }
    public string TagName { get; set; }
}

class Program
{
    static void Main(string[] args)
    {
        RunTags();
    }

    private static void RunTags()
    {
        Item i1 = new Item()
        {
            ItemId = 1,
            ItemName = "Item1",
            ItemTags = new List<Tag>() { new Tag { TagId = 1, TagName = "2008" }, new Tag { TagId = 2, TagName = "Donkey" } }
        };

        Item i2 = new Item()
        {
            ItemId = 2,
            ItemName = "Item2",
            ItemTags = new List<Tag>() { new Tag { TagId = 4, TagName = "Cat" }, new Tag { TagId = 2, TagName = "Donkey" }, new Tag { TagId = 3, TagName = "Seattle" } }
        };

        Item i3 = new Item()
        {
            ItemId = 3,
            ItemName = "Item3",
            ItemTags = new List<Tag>() { new Tag { TagId = 523, TagName = "Manchester united" }, new Tag { TagId = 10, TagName = "European Cup" }, new Tag { TagId = 1, TagName = "2008" } }
        };

        Item i4 = new Item()
        {
            ItemId = 4,
            ItemName = "Item4",
            ItemTags = new List<Tag>() { new Tag { TagId = 05, TagName = "Banana" }, new Tag { TagId = 140, TagName = "Foo" }, new Tag { TagId = 4, TagName = "Cat" } }
        };

        Item i5 = new Item()
        {
            ItemId = 5,
            ItemName = "Item5",
            ItemTags = new List<Tag>() { new Tag { TagId = 05, TagName = "Banana" }, new Tag { TagId = 140, TagName = "Foo" } }
        };

        List<Item> itemList = new List<Item>() { i1, i2, i3, i4, i5 };

        string tags = "Manchester United,European Cup,2008";
        List<string> tagsList = tags.Trim().ToLower()
            .Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries)
            .Distinct(StringComparer.CurrentCultureIgnoreCase)
            .ToList();

        var query = itemList
            .OrderByDescending(p => p.DateCreated).ToList();


        var results = query.Where(i => i.ItemTags.All(it => tagsList.Contains(it.TagName.ToLower())));

        foreach (var item in results)
        {
            Console.WriteLine(item.ItemName); // Should return "Item3"
        }

        Console.ReadLine();
    }

If you want to match any of the tags in the Item's ItemTag list then just change All to Any i.e.

var results = query.Where(i => i.ItemTags.Any(it => tagsList.Contains(it.TagName.ToLower())));