2

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!

Improve this question

3 Answers 3

Reset to default
2

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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);
Improve this answer

2 Comments

Thank you, but I would like, to the fulles possible extent, try to avoid returning data to the client which will then be discarded, i.e client side filtration is something I'd like to avoid so I do not have to use more bandwidth than necessary
I broke the query into two sections, "possibleitems" and "items" but in reality, the DB is never hit until after you start enumerating "items" - only the exact things you want are hit. If you're using Linq, you should know about "Deferred Execution"
1

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

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.