ASP.NET MVC C# Select and Where Statements

Question

I'm having trouble understanding .Select and .Where statements. What I want to do is select a specific column with "where" criteria based on another column.

For example, what I have is this:

var engineers = db.engineers;
var managers = db.ManagersToEngineers;
List<ManagerToEngineer> matchedManager = null;
Engineer matchedEngineer = null;

if (this.User.Identity.IsAuthenticated)
{
    var userEmail = this.User.Identity.Name;
    matchedEngineer = engineers.Where(x => x.email == userEmail).FirstOrDefault();
    matchedManager = managers.Select(x => x.ManagerId).Where(x => x.EngineerId == matchedEngineer.PersonId).ToList();
}
if (matchedEngineer != null)
{
    ViewBag.EngineerId = new SelectList(new List<Engineer> { matchedEngineer }, "PersonId", "FullName");
    ViewBag.ManagerId = new SelectList(matchedManager, "PersonId", "FullName");
}

What I'm trying to do above is select from a table that matches Managers to Engineers and select a list of managers based on the engineer's id. This isn't working and when I go like:

matchedManager = managers.Where(x => x.EngineerId == matchedEngineer.PersonId).ToList();

I don't get any errors but I'm not selecting the right column. In fact the moment I'm not sure what I'm selecting. Plus I get the error:

Non-static method requires a target.

Ehsan Sajjad · Accepted Answer · 2016-10-07 15:56:01Z

5

if you want to to select the manager, then you need to use FirstOrDefault() as you used one line above, but if it is expected to have multiple managers returned, then you will need List<Manager>, try like:

Update:

so matchedManager is already List<T>, in the case it should be like:

matchedManager = managers.Where(x => x.EngineerId == matchedEngineer.PersonId).ToList();

when you put Select(x=>x.ManagerId) after the Where() now it will return Collection of int not Collection of that type, and as Where() is self descriptive, it filters the collection as in sql, and Select() projects the collection on the column you specify:

List<int> managerIds = managers.Where(x => x.EngineerId == matchedEngineer.PersonId)
                         .Select(x=>x.ManagerId).ToList();

edited Oct 7, 2016 at 15:56

answered Oct 7, 2016 at 15:46

Ehsan Sajjad

62.6k16 gold badges112 silver badges171 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Nathan McKaskle Over a year ago

Yours seems like the closest answer I think but I don't want a list of int Ids but rather a list of manager names associated with the Id.

Nathan McKaskle Over a year ago

I tried using List<Manager> and .Select(x => x.manager).ToList(); instead which compiled but ended up being empty, despite there being matching data in that table. It makes no sense to me why this is happening.

Nathan McKaskle Over a year ago

Actually nevermind it's not empty, I count 2 items but it's not showing the names. Urgh.

krillgar · Accepted Answer · 2016-10-07 15:52:25Z

The easiest way to remember what the methods do is to remember that this is being translated to SQL.

A .Where() method will filter the rows returned.

A .Select() method will filter the columns returned.

However, there are a few ways to do that with the way you should have your objects set up.

First, you could get the Engineer, and access its Managers:

var engineer = context.Engineers.Find(engineerId);
return engineer.Managers;

However, that will first pull the Engineer out of the database, and then go back for all of the Managers. The other way would be to go directly through the Managers.

return context.Managers.Where(manager => manager.EngineerId == engineerId).ToList();

Although, by the look of the code in your question, you may have a cross-reference table (many to many relationship) between Managers and Engineers. In that case, my second example probably wouldn't work. In that case, I would use the first example.

Chirag Rupani · Accepted Answer · 2016-10-07 15:55:51Z

1

You want to filter data by matching person Id and then selecting manager Id, you need to do following:

matchedManager = managers.Where(x => x.EngineerId == matchedEngineer.PersonId).Select(x => x.ManagerId).ToList();

In your case, you are selecting the ManagerId first and so you have list of ints, instead of managers from which you can filter data

Update: You also need to check matchedEngineer is not null before retrieving the associated manager. This might be cause of your error

edited Oct 7, 2016 at 15:55

answered Oct 7, 2016 at 15:45

Chirag Rupani

1,7152 gold badges18 silver badges37 bronze badges

Comments

IonB · Accepted Answer · 2016-10-07 15:46:08Z

0

You use "Select" lambda expression to get the field you want, you use "where" to filter results

answered Oct 7, 2016 at 15:46

IonB

116 bronze badges

Collectives™ on Stack Overflow

ASP.NET MVC C# Select and Where Statements

4 Answers 4

Update:

3 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Update:

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related