Where IN clause in LINQ

Question

How to make a where in clause similar to one in SQL Server?

I made one by myself but can anyone please improve this?

    public List<State> Wherein(string listofcountrycodes)
    {
        string[] countrycode = null;
        countrycode = listofcountrycodes.Split(',');
        List<State> statelist = new List<State>();

        for (int i = 0; i < countrycode.Length; i++)
        {
            _states.AddRange(
                 from states in _objdatasources.StateList()
                 where states.CountryCode == countrycode[i].ToString()
                 select new State
                 {
                    StateName  = states.StateName                    

                 });
        }
        return _states;
    }

Daniel Brückner · Accepted Answer · 2012-12-17T17:54:32.347

314

This expression should do what you want to achieve.

dataSource.StateList.Where(s => countryCodes.Contains(s.CountryCode))

edited Dec 17 '12 at 17:54

answered Jun 06 '09 at 14:37

Daniel Brückner

59,031
16
99
143

12

this will compare string values, but what about ids?? – Jitendra Pancholi Feb 22 '13 at 07:32
1

@JitendraPancholi if you create a List you can check for ids. It is supported in .Net 4. Not sure of the earlier versions. – Rashmi Pandit Jun 10 '14 at 05:16

Scott Ivey · Answer 2 · 2009-06-06T14:42:56.943

This will translate to a where in clause in Linq to SQL...

var myInClause = new string[] {"One", "Two", "Three"};

var results = from x in MyTable
              where myInClause.Contains(x.SomeColumn)
              select x;
// OR
var results = MyTable.Where(x => myInClause.Contains(x.SomeColumn));

In the case of your query, you could do something like this...

var results = from states in _objectdatasource.StateList()
              where listofcountrycodes.Contains(states.CountryCode)
              select new State
              {
                  StateName = states.StateName
              };
// OR
var results = _objectdatasource.StateList()
                  .Where(s => listofcountrycodes.Contains(s.CountryCode))
                  .Select(s => new State { StateName = s.StateName});

Thank you ;) Helped me as well +1 – Thiago Loureiro Aug 31 '17 at 14:42 — Thiago Loureiro, Aug 31 '17 at 14:42

score 51 · Answer 3 · answered Jun 17 '13 at 18:19

51

I like it as an extension method:

public static bool In<T>(this T source, params T[] list)
{
    return list.Contains(source);
}

Now you call:

var states = _objdatasources.StateList().Where(s => s.In(countrycodes));

You can pass individual values too:

var states = tooManyStates.Where(s => s.In("x", "y", "z"));

Feels more natural and closer to sql.

answered Jun 17 '13 at 18:19

nawfal

70,104
56
326
368

2

where should i write this extension method – RobertKing Feb 04 '14 at 05:46
@Rohaan, in any static class (that is not generic and not nested) – nawfal Feb 04 '14 at 17:24
1

There's only mileage in writing the extension class if you're going to be reusing the Where In part of your linq. Upvoted the answer, but just wanted to let others who come across this question and go straight for the extension method route. – JARRRRG Jan 27 '16 at 10:09
@Jurg It's pretty frequently required, no? I use it all the time. Works fine for Linq to Objects, havent tried for IQueryables. – nawfal Jan 27 '16 at 11:15
No what I meant was, it's used frequently sure however in a particular project if you only need to do this in say a single controller (aka once) then there's an extension method is not required. This was more for people who are new to c#/linq :) – JARRRRG Jan 27 '16 at 12:07
@nawfal How to make it work of list of list? for example there is a list of Students which has multiple SubjectIds. Now I Want to find out, all students with SubjectIds...let's say 1,2,3 – Gopu_Tunas Sep 30 '20 at 10:08
@Gopu_Tunas use SelectMany to flatten out the collection, in general. Or may be `students.Where(s => s.SubjectIds.Any(subj => subj.In(1, 2, 3)))` – nawfal Sep 30 '20 at 10:19
Is this method efficient like 'WHERE IN' in SQL? I think the implemented 'In' method should be executed for each member of list, which I think it will cause too much overhead for Linq-2-SQL queries. – VSB Jun 29 '23 at 11:42
1

@VSB Extension methods wont even work for Linq-2-SQL. You will have to call Contains directly, which will be executed as `IN`. – nawfal Jul 16 '23 at 09:31

score 17 · Answer 4 · answered Jun 12 '14 at 11:49

17

public List<Requirement> listInquiryLogged()
{
    using (DataClassesDataContext dt = new DataClassesDataContext(System.Configuration.ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString))
    {
        var inq = new int[] {1683,1684,1685,1686,1687,1688,1688,1689,1690,1691,1692,1693};
        var result = from Q in dt.Requirements
                     where inq.Contains(Q.ID)
                     orderby Q.Description
                     select Q;

        return result.ToList<Requirement>();
    }
}

answered Jun 12 '14 at 11:49

Libertine Makinta

403
5
10

1

What is it's equivalence method syntax? – Vahid Ghadiri Jun 12 '18 at 08:55
2

Thank you so very much for this, been struggling for days and this was the only thing that worked. So simple and effective a solution. – Dennis Sep 26 '19 at 07:24

nikmd23 · Answer 5 · 2013-05-03T12:25:41.510

10

The "IN" clause is built into linq via the .Contains() method.

For example, to get all People whose .States's are "NY" or "FL":

using (DataContext dc = new DataContext("connectionstring"))
{
    List<string> states = new List<string>(){"NY", "FL"};
    List<Person> list = (from p in dc.GetTable<Person>() where states.Contains(p.State) select p).ToList();
}

edited May 03 '13 at 12:25

answered Jun 06 '09 at 14:37

nikmd23

9,095
4
42
57

score 4 · Answer 6 · answered Jun 06 '09 at 14:37

4

from state in _objedatasource.StateList()
where listofcountrycodes.Contains(state.CountryCode)
select state

answered Jun 06 '09 at 14:37

Andrija

14,037
18
60
87

score 2 · Answer 7 · answered Jul 11 '12 at 02:54

This little bit different idea. But it will useful to you. I have used sub query to inside the linq main query.

Problem:

Let say we have document table. Schema as follows schema : document(name,version,auther,modifieddate) composite Keys : name,version

So we need to get latest versions of all documents.

soloution

 var result = (from t in Context.document
                          where ((from tt in Context.document where t.Name == tt.Name
                                orderby tt.Version descending select new {Vesion=tt.Version}).FirstOrDefault()).Vesion.Contains(t.Version)
                          select t).ToList();

score 1 · Answer 8 · answered Aug 24 '11 at 12:23

1

public List<State> GetcountryCodeStates(List<string> countryCodes)
{
    List<State> states = new List<State>();
    states = (from a in _objdatasources.StateList.AsEnumerable()
    where countryCodes.Any(c => c.Contains(a.CountryCode))
    select a).ToList();
    return states;
}

answered Aug 24 '11 at 12:23

Siva

27
1

6

Welcome on SO, here, it is a good practice to explain why to use your solution and not just how. That will make your answer more valuable and help further reader to have a better understanding of how you do it. I also suggest that you have a look on our FAQ : http://stackoverflow.com/faq. – ForceMagic Nov 13 '12 at 19:05

Where IN clause in LINQ

8 Answers8

Linked

Related