Fetching items which has a specific set of child elements (advanced query - possible?)

Question

I have a SQL database (SQL Server 2008) which contains the following design

ITEM

• ID (Int, Identity)
• Name (NVarChar(50))
• Description (NVarChar(200))

META

• ID (Int, Identity)
• Name (NVarChar(50))

There exists a N-N relationship between these two, i.e an Item can contain zero or more meta references and a meta can be associated with more than one item. Each meta can only be assocated with the same item once. This means I have the classic table in the middle

ITEMMETA

• ItemID (Int)
• MetaID (Int)

I would like to to execute a LinqToSql query to extract all of the item entities which contains a specific set of meta links. For example, give me all the Items which have the following meta items associated with it

• Car
• Ford
• Offroad

Is it possible to write such a query with the help of LinqToSql? Let me provide some more requirements

• I will have a list of Meta tags I want to use to filter the items which will be returned (for example in the example above I had Car, Ford and Offroad)
• An item can have MORE meta items associated with it than what I provide in the match, i.e if an item had Car, Ford, Offroad and Red assocated to it then providing any combination of them in the filter should result in a match
• However ALL of the meta names which are provided in the filter MUST be assocated with an item for it to be returned in the resultset. So sending in Car, Ford, Offroad and Red SHOULD NOT be a match for an item which has Car, Ford and Offroad (no Red) associated with itself

I hope its clear what I'm trying to achieve, I feel Im not being quite as clear as I'd hoped =/ Let's hope its enough :)

Thank you!

Answer 1

This should work for you:

string[] criteria = new[] { "Car", "Ford", "Offroad" };

var items = 
    from i in db.Item
    let wantedMetas = db.Meta.Where(m => criteria.Contains(m.Name))
    let metas = i.ItemMeta.Select(im => im.Meta)
    where wantedMetas.All(m => metas.Contains(m))
    select i;

Basically it compares the "wanted" metas against each item's metas, and selects the items which have all the wanted metas (or more).

Answer 2

You could filter the items by counting the filtered metas.

List<string> metaList = new List<string>() { "Car", "Ford", "Offroad" };
int metaListCount = metaList.Count;
List<Item> result =
  db.Items
  .Where(i => metaListCount ==
    i.ItemMeta.Meta
    .Where(m => metaList.Contains(m.Name))
    .Count()
  )
  .ToList();

Be aware that there is an upper limit for this in-memory collection .Contains imposed by SqlServer's parameter limit (it's either ~200 or ~2000, I can never remember).

Answer 3

this brings back anything that matchs any of the meta criteria, and then filters it down to only things that match all the criteria. (also, keep in mind that you'll need to have your relationship defined in your datacontext). Let us know if you need clarification.

var db = new YourDataContext();
var possibleItems = (from m in db.Metas where <meta criteria> select m.ItemMetas.Item);

var items = possibleItems.GroupBy(y=>y).Where(x=>x.Count() == criteriaCount).Select(x=>x.Key);