C# Using a linq query to filter on Values in an Array

Question

I'm struggling with this Linq query which queries the database.

var roles = _context.Schools
                    .Select(x =>
                            new SelectListItem
                            {
                                Value = x.SchoolId.ToString(),
                                Text = x.SchoolNamePostCode
                            });

My query currently gets the whole rows of Schools into a SelectListItem. However what I am trying to do is add in a Where clause, which contains an array of string value IDs.

string[] AssociatedSchoolsArray = AssociatedSchoolIDs.Split(",");

The variable AssociatedSchoolsArray contains an array, for example, "1","3"

So in my original Linq query, I want to filter the SelectListItem for SchoolIds 1 and 3 only

how can I achieve this?

Many thanks

An [mre] with the initialisation of few school (2-3) with the associated id that show all you test case. And the expected result will be nice. — Drag and Drop, Apr 20 '20 at 08:37
Does this answer your question? [Linq to Entities - SQL "IN" clause](https://stackoverflow.com/questions/857973/linq-to-entities-sql-in-clause) — Drag and Drop, Apr 20 '20 at 08:40

sommmen · Answer 1 · 2020-04-20T08:51:47.830

You can use where for that, eg;

   var roles = _context.Schools
                .Where(c=>c.SchoolId == 1 || c.schoolId == 3)
                .Select(x =>
                        new SelectListItem
                        {
                            Value = x.SchoolId.ToString(),
                            Text = x.SchoolNamePostCode
                        });

or of course also afterwards;

   var roles = _context.Schools
                .Select(x =>
                        new SelectListItem
                        {
                            Value = x.SchoolId.ToString(),
                            Text = x.SchoolNamePostCode
                        })
                .Where(c=>c.Value == "1" || c.Value == "3");

Based on a list of values:

   var searchWords = new List<string>(new [] { "1", "2"});
   var roles = _context.Schools
                .Select(x =>
                        new SelectListItem
                        {
                            Value = x.SchoolId.ToString(),
                            Text = x.SchoolNamePostCode
                        })
                .Where(c=>searchWords.Contains(c.Value));

note; instead of contains you could also use .Any(). That one is more flexible since it takes a delegate, but since .Contains() is a method inherent to a collection object it could be faster. In terms of EF and linq to sql, it all gets translated to an SQL query anyways so i doubt it matters.

Thanks, but the 1 and 3 are just examples, that array could be multiple values — Metzer, Apr 20 '20 at 08:38
now that i think of it, i believe i had a similar issue like this, where i needed to compare an in memory list of values with values in the database. I could not do this because you can't compare in memory values with db entities, and i opted to write my own custom query instead (`.. where in (...)`) to do this. — sommmen, Apr 20 '20 at 08:47
Ah it seems this issue highlights that concern; https://stackoverflow.com/a/21725298/4122889 — sommmen, Apr 20 '20 at 08:56

Fabio · Accepted Answer · 2020-04-20T08:43:48.297

.Contains method will generate ... WHERE column IN (1, 2, 3) sql query

Guess that SchoolId is of type int, so you need to parse string ids to integer first.

var selectedIds = Array.ConvertAll(AssociatedSchoolIDs.Split(","), int.Parse);

var items = _context.Schools
    .Where(school => selectedIds.Contains(school.SchoolId))
    .Select(school => new SelectListItem
        {
            Value = x.SchoolId,
            Text = x.SchoolNamePostCode
        })
    .ToArray();

score 2 · Answer 3 · answered Apr 20 '20 at 08:36

It seems as SchoolId is stored as int in database, so you should convert to int first before your where-statement to speed up perfomance.

int[] AssociatedSchoolsArray = AssociatedSchoolIDs.Split(",").Select(item => Int32.Parse(item).ToArray();
var roles = _context.Schools.Where(item => AssociatedSchoolsArray.Contains(item.SchoolId))
                .Select(x =>
                        new SelectListItem
                        {
                            Value = x.SchoolId.ToString(),
                            Text = x.SchoolNamePostCode
                        });

RoadRunner · Answer 4 · 2020-04-20T09:56:38.217

1

Adding to the other great answers, here's a way to do it with LINQ query syntax:

var AssociatedSchoolIDs = "1,3";

var selectedIds = AssociatedSchoolIDs
    .Split(",")
    .Select(x => int.Parse(x));

var roles = (from school
             in _context.Schools
             where selectedIds.Contains(school.SchoolId)
             select new SelectListItem
             {
                 Value = school.SchoolId,
                 Text = school.SchoolNamePostCode
             }).ToArray();

edited Apr 20 '20 at 09:56

answered Apr 20 '20 at 08:47

RoadRunner

25,803
6
42
75

There are no difference which type of collection is used `HashSet` or `IEnumerable`, when sql is generate collection is enumerated only once. – Fabio Apr 20 '20 at 08:58
@Fabio For doing ID lookups with many IDs, I'm sure using `HashSet.Contains` will be faster than `IEnumerable.Contains`. Like your solution, this also just enumerates the collection once. I simply added a method to do faster lookups. – RoadRunner Apr 20 '20 at 09:10
1

For DbContext `.Where( item => collection.Contains(item.Id))` is an `Expression` it will not execute `Contains` method on the collection (or hashset), but generate sql query based on the Expression info It will enumerate collection once onlym no lookup operations is executed. – Fabio Apr 20 '20 at 09:18
@Fabio I see what your saying. The translated DbExpression tree from `new []{1, 3}.Contains(i)` will be something like `((1 = @i) OR ((3 = @i))`, atleast in EF Core, so the collection doesn't matter. I've updated my answer to just show how to do this with query syntax, which could be useful so some readers. Let me know if there is something wrong with it :-) – RoadRunner Apr 20 '20 at 09:58

C# Using a linq query to filter on Values in an Array

4 Answers4