LINQ to SQL Query Against Bridge Table

Question

I'm having problems with a LINQ to SQL query in the following scenario:

I have items that have "Tags" applied via a bridge table.
I'm trying to filter a list of items to a subset that contain all of a specified set of tags and return the filtered list of items as the query result.

Tables Involved:

Item (ItemId, Name, ...other fields)
Tag (TagId, TagName)
Item_Tag(ItemId, TagId)

As an example if I had a list of Items with tags:

Item1 w/ (Tag1, Tag2)
Item2 w/ (Tag1, Tag2)
Item3 w/ (Tag1)

and I wanted to get all items where the item has both Tag1 AND Tag2 where the filter requirement is provided as an int[] of the required tagIds.

Assuming the Item and Tag Id's match the number at the end of the name. The filter for this example would be:

int[] tagFilterConditions = int[2]{1, 2};

var query = from i in itemList
//define filter here

where the result would be: Item1,Item2 (excludes Item 3 b/c it isn't tagged with Tag1 AND Tag2)

I'm having a tough time figuring out how to combine these tables to apply that filter on the source list, I've tried using a predicate builder and various joins but just can't get the correct results.

Thanks, for any help...

Have you created mappings (foreign keys) between the tables, so that you could use `item.Tags` (or `item.Item_Tags`) instead of having to query the `Item_Tag` table explicitly? — Markus Jarderot, Jul 09 '11 at 09:25

score 0 · Answer 1 · answered Jul 08 '11 at 22:28

0

I think the answer to your question is in .Contains(): http://blog.wekeroad.com/2008/02/27/creating-in-queries-with-linq-to-sql

Here's what I think is the relevant snippet from that site to your question:

int[] productList = new int[] { 1, 2, 3, 4 };

var myProducts = from p in db.Products
             where productList.Contains(p.ProductID)
            select p;

Hope this helps!

answered Jul 08 '11 at 22:28

ElonU Webdev

2,451
14
15

I've tried various scenarios using contains except what I am trying to compare is in the bridge table and not in the "Items" table itself..also contains() doesn't seem to do an AND comparison where all items in the array must exist. – jrob Jul 08 '11 at 22:39
My bad, I totally missed the AND requirement... Trying to think of alternatives... Is loading the entire contents of the bridge table into local memory an option? It looks like it's a table of integer tags, so could you put it into a local array? That would at least eliminate some potential LINQ-to-SQL weirdness if you need to use a snazzy C# feature that doesn't translate to SQL. – ElonU Webdev Jul 08 '11 at 22:46

Markus Jarderot · Accepted Answer · 2011-07-11T05:15:01.493

0

// Query for all the items in the list
int[] itemIds = itemList.Select(item => item.ItemId).AsArray();
var query =
    db.Item.Where(item =>
        itemIds.Contains(item.ItemId));

// Apply each tag condition
foreach (int tagid in tagFilterConditions)
{
    int temp = tagid;
    query = query.Where(item =>
        db.Item_Tag.Exists(item_tag =>
            item_tag.ItemId == item.ItemId && item_tag.TagId == temp)));
}

edited Jul 11 '11 at 05:15

answered Jul 09 '11 at 09:49

Markus Jarderot

86,735
21
136
138

Thanks, I was getting hung up trying to get something working in a single linq query...I did add the foreign key relationship like you mentioned above and got the following query to work as well I will post this as another answer below for clarity. – jrob Jul 11 '11 at 14:18

score 0 · Answer 3 · edited May 23 '17 at 10:34

0

Here is some sql.

and here is the LinqToSql..

edited May 23 '17 at 10:34

Community

1
1

answered Jul 09 '11 at 14:27

Amy B

108,202
21
135
185

score 0 · Answer 4 · edited May 23 '17 at 12:02

Got the following query to work using an anonymous type after defining the proper foreign key relationships the query was adapted from an answer on this question.

//the tagId's that the item in itemList must have
int[] tagFilterConditions = int[2]{1, 2};

var query =  
    itemList.Select( i=> new { i, itemTags= item.Item_Tags.Select(it=> it.TagId)})
            .Where( x=> tagFilterConditions.All( t=> x.itemTags.Contains(t)))
            .Select(x=> x.s);

LINQ to SQL Query Against Bridge Table

4 Answers4