LINQ To Entities - Items, ItemCategories & Tags

Question

Hi There,

I have an Entity Model that has Items, which can belong to one or more categories, and which can have one or more tags.

I need to write a query that finds all tags for a given ItemCategory, preferably with a single call to the database, as this is going to get called fairly often.

I currently have:

Dim q = (From ic In mContext.ItemCategory _
         Where ic.CategoryID = forCategoryID _
         Select ic).SelectMany(Function(cat) cat.Items).SelectMany(Function(i) i.Tags) _
         .OrderByDescending(Function(t) t.Items.Count).ToList

This is nearly there, apart from it doesn't contain the items for each tag, so I'd have to iterate through, loading the item reference to find out how many items each tag is related to (for font sizing).

Ideally, I want to return a List(Of TagCount), which is just a structure, containing Tag as string, and Count as integer.

I've looked at Group and Join, but I'm not getting anywhere so any help would be greatly appreciated!

Answer 1

This should work (C#, sorry):

var tags = context.Tags.Include("Items").Where(t => t.Items.Any(i => i.ItemCategories.Any(ic => ic.CategoryID == forCategoryID))).ToList();  

Second one gets tag name and item count:

var tags = context.Where(t => t.Items.Any(i => i.ItemCategories.Any(ic => ic.CategoryID == forCategoryID))).Select(t => new { TagName = t.Name, ItemCount = t.Items.Count });  

We take only tags from items that belong to desired category.

Using VB (I am not sure that it works, I used translator):

Dim tags = context.Where(Function(t) t.Items.Any(Function(i) i.ItemCategories.Any(Function(ic) ic.CategoryID = forCategoryID))).[Select](Function(t) New With { .TagName = t.Name, .ItemCount = t.Items.Count })