Linq version of SQL "IN" statement

Question

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.

Solution 1

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

Solution 2

given array of items:

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

use:

where list.Contains(tm.TagId)

Solution 3

You can simply use,

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

Solution 4

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

Solution 5

string[] names = {"John", "Cassandra", "Sarah"};

var results = (from n in db.Names
               where names.Contains(n.Name)
               select n).ToList();

View more solutions

Comments

[Pankaj]

Why the inner join is not prefered here? You know if the Context.TagMaps contains 10 records , it will iterate 36 times in background , irrespective of the fact that whether there is a match or not.

[Steven Wexler]

You may find SQL queries in LINQ helpful. It's a list of common SQL queries represented in LINQ.

[Brian David Berman]

I don't understand how that would work given the 3 table schema I have.

[Denis Troller]

it comes into play through the select map.Item part. In SQL you would have to join the TagMap to the Item table. Linq does that for you because of the relationship from TagMap to Item. You are essentially saying "find all TagMaps that reference any of my TagIds and return me their item". This Linq query is the same as the following SQL: SELECT Items.* FROM TagMaps INNER JOIN Items ON Item.ItemId = TagMap.ItemId WHERE TagMaps.TagId IN (12,32,24) Linq takes care of the INNER JOIN part for you, because it knows how to go from TagMap to Item.

[Pankaj]

Why the inner join is not prefered here? You know if the Context.TagMaps contains 10 records , it will iterate 36 times in background , irrespective of the fact that whether there is a match or not.

[boilers222]

The array declaration isn't quite right. Combine this answer with @LukeSchafer's answer below and it will work.