Linq version of SQL "IN" statement

I have the following 3 tables as part of a simple "item tagging" schema:

==Items==

ItemId int
Brand varchar
Name varchar
Price money
Condition varchar
Description varchar
Active bit

==Tags==

TagId int
Name varchar
Active bit

==TagMap==

TagMapId int
TagId int (fk)
ItemId int (fk)
Active bit

I want to write a LINQ query to bring back Items that match a list of tags (e.g. TagId = 2,3,4,7). In my application context, examples of items would be "Computer Monitor", "Dress Shirt", "Guitar", etc. and examples of tags would be "electronics", "clothing", etc. I would normally accomplish this with a SQL IN Statement.

Something like

var TagIds = new int[] {12, 32, 42};

var q = from map in Context.TagMaps 
        where TagIds.Contains(map.TagId)
        select map.Items;

should do what you need. This will generate an In ( 12, 32, 42 ) clause (or more specifically a parameterized IN clause if I'm not mistaken).

given array of items:

var list = new int[] {2,3,4}

use:

where list.Contains(tm.TagId)

List<int> tagIds = new List<int>() {2, 3, 4, 7};
int tagIdCount = tagIds.Count;
    //
// Items that have any of the tags
//  (any item may have any of the tags, not necessarily all of them
    //
var ItemsAnyTags = db.Items
  .Where(item => item.TagMaps
    .Any(tm => tagIds.Contains(tm.TagId))
  );

    //
// Items that have ALL of the tags
//  (any item may have extra tags that are not mentioned).
    //
var ItemIdsForAllTags = db.TagMap
  .Where(tm => tagIds.Contains(tm.TagId))
  .GroupBy(tm => tm.ItemId)
  .Where(g => g.Count() == tagIdCount)
  .Select(g => g.Key);
    //
var ItemsWithAllTags = db.Items
  .Where(item => ItemsIdsForAllTags.Contains(item.ItemId));

//runs just one query against the database
List<Item> result = ItemsWithAllTags.ToList();

You can simply use,

var TagIds = {12, 32, 42}
var prod =entities.TagMaps.Where(tagmaps=> TagIds .Contains(tagmaps.TagId));

Linq version of SQL "IN" statement

Related

Recent Posts