CODEWITHSUNDEEP
c#linq
Metzer
Metzer

Reputation: 241

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

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

Upvotes: 0

Views: 7596

Answers (4)

RoadRunner
RoadRunner

Reputation: 26315

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

Upvotes: 1

sommmen
sommmen

Reputation: 7608

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.

Upvotes: 4

Fabio
Fabio

Reputation: 32445

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

Upvotes: 4

Pochen
Pochen

Reputation: 2887

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

Upvotes: 2

Related Questions