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

Question

Hi,

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 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);

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 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 4

@Lucas, your example looks very interessting, however I'm not able to get it to work. When I drag my three tables on the "Linq To SQL" design area and then look at the generated classes I do not get the Item.ItemMeta property, so the line

let metas = i.ItemMeta.Select(im => im.Meta)

Fails because the collection property does not exist? Do you have any idea what I might be doing wrong? I'm made sure all references between the tables are setup and all tables have a primary key (which is an identity field with auto increment).

Thanks

Answer 5

Re-reading all of the answers in this thread makes me realise something; all of the proposed solutions either use Item.ItemMeta och Meta.ItemMeta in their code, but those two collection properties doesn't appear on my entities even though the relationship is visualized in the designer :-(

Learning, no matter how satisfying it can be, can be damn frustrating as well, he he

Answer 6

Nevermind. My entities and model had somehow managed to get out of synch even though I had removed them from the designed and added them again. After some refreshing and readding of stuff in the designer the collection properties appeared and the Linq executed beautifully!

Thank you all whom had contributed with suggestions - I've upvoted all of you.