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.

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. — Pankaj, Apr 03 '12 at 12:41
You may find [SQL queries in LINQ](http://www.codeducky.org/sql-queries-in-linq/#where-in) helpful. It's a list of common SQL queries represented in LINQ. — Steven Wexler, Mar 24 '15 at 05:51

Denis Troller · Accepted Answer · 2015-10-16T12:46:43.463

60

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

edited Oct 16 '15 at 12:46

answered May 22 '09 at 02:31

Denis Troller

7,411
1
23
36

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. – Denis Troller May 22 '09 at 02:43
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. – Pankaj Apr 03 '12 at 12:41
The array declaration isn't quite right. Combine this answer with @LukeSchafer's answer below and it will work. – boilers222 Oct 14 '15 at 18:04

score 16 · Answer 2 · answered May 22 '09 at 02:18

16

given array of items:

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

use:

where list.Contains(tm.TagId)

answered May 22 '09 at 02:18

Luke Schafer

9,209
2
28
29

I don't understand how that would work given the 3 table schema I have. – Brian David Berman May 22 '09 at 02:30

score 1 · Answer 3 · answered Jul 15 '13 at 06:49

1

You can simply use,

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

answered Jul 15 '13 at 06:49

Anish Karunakaran

987
3
17
39

score 1 · Answer 4 · answered Dec 28 '14 at 18:43

1

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

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

answered Dec 28 '14 at 18:43

IR.Programmer

119
3
4

score 1 · Answer 5 · answered Apr 30 '19 at 10:15

You may create an extension method "IN()"

public static class Extension
{
    public static bool IN(this object anyObject, params object[] list)
    { return list.Contains(anyObject); }
}

to be used like this

var q = from map in Context.TagMaps 
        where map.TagId.IN(2, 3, 4, 7)
        select map.Items;

Or just use the inline array.Contains() notation:

var q = from map in Context.TagMaps 
        where new[]{2, 3, 4, 7}.Contains(map.TagId)
        select map.Items;

Amy B · Answer 6 · 2009-05-22T14:09:33.380

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

Linq version of SQL "IN" statement

6 Answers6

Linked

Related