LINQ: Query Syntax - How can I identify everyone who has a higher income than someone else?

Question

So I have to identify everyone who has a higher income than "JONES".

Here is the Schema:

new Emp{name = "SMITH", income = 800},
new Emp{name = "JONES", income = 600},
new Emp{name = "ADAMS", income = 900},
new Emp{name = "KING", income = 400}

I can't find a way to build this in a Query Syntax...

So like [this](https://stackoverflow.com/questions/36438524/linq-greater-than-any-value-in-a-list/36438566), but adapt it for your problem? — gunr2171, Apr 26 '21 at 17:32

score 0 · Answer 1 · edited Apr 26 '21 at 18:56

so let's say you have your data like this. so this should solve your problem. so to explain the code below.

I have a list of data based of the Emp class.
I also have a variable of jones that contains information about jones.
I can then use Linq to query the data list of Emp where the emp income is greater than the matches Jones. then I return then in orderbydescending using Linq.

using System;
using System.Collections.Generic;
using System.Linq;

public class Program
{
    //Emp class for 
    public class Emp
    {
        public string name { get; set; }
        public double income { get; set; }
    }

    public static void Main()
    {
        //List of Emp data base off the Emp class object.
        var data = new List<Emp>
                   {
                       new Emp {name = "SMITH", income = 800},
                       new Emp {name = "JONES", income = 600},
                       new Emp {name = "ADAMS", income = 900},
                       new Emp {name = "KING", income = 400}
                   };

        //Jones data that will be used for querying
        var jones = new Emp {name = "JONES", income = 600};

        //List of Emp that have income higher than jones.
        var higherThanJones = data.Where(item => item.income > jones.income)
                                  .OrderByDescending(i => i.income)
                                  .ToList();

        //Foreach loop to show the people with income than jones
        foreach (var people in higherThanJones)
        {
            //printing out the names of the people higher than Jones
            Console.WriteLine(people.name);
        }
    }
}

score 0 · Answer 2 · answered Apr 26 '21 at 19:27

In query syntax, you can first create a query to find the matching record, or return the default (which will be 0) if there is no match:

var jonesIncome = (from e in emps
                   where e.name == "JONES"
                   select e.income).FirstOrDefault();

Then you can use the income query to find the rows desired:

var higherThanJones = from e in emps
                      where e.income > jonesIncome
                      select e;

Since queries use deferred execution, the two queries will actually be executed when higherThanJones results are used. If you are querying a database, the two queries will be translated into a single SQL query, depending on the LINQ you are using and the database provider.

You could also use lambda/fluent syntax to combine into a single query (I prefer not to combine query syntax as it doesn't read as well):

var matchName = "JONES";
var higherThanMatch = emps.Where(e => e.income > emps.Where(e2 => e2.name == matchName)
                                                     .Select(e2 => e2.income)
                                                     .FirstOrDefault());

score 0 · Answer 3 · answered Apr 28 '21 at 10:04

So I have to identify everyone who has a higher income than "JONES".

Are you certain there is a "Jones"? Is there exactly one "Jones"?

The answer depends on whether you are working IQueryable or IEnumerable.

If you need to do it as Queryable, you need to pack it in one Query:

IQueryable<Employee> employees = ...
var employeesWithHigherIncomes = employees
    .Where(employee => employee.Income >
        employees.Where(employee => employee.Name == name)
    .FirstOrDefault()));

Luckily your database is smart enough not to search Jones again for every Employee.

As Enumerable:

string name = "Jones"
IEnumerable<Employee> employees = ...
var incomeJones = employees.Where(employee => employee.Name == name)
    .Select(employee => employee.Income)
    .FirstOrDefault();

var employessWithHigherIncome = employees
    .Where(employee => employee.Income > incomeJones)
    .FirstOrDefault();

You will enumerate your sequence at utmost twice: once (partly) until you found the first "Jones", and once completely to find all higher incomes.

If I had put the query to find the income of Jones in the "Where", like I did in Queryable, then for every Employee I had to enumerate the sequence to find Jones again.

LINQ: Query Syntax - How can I identify everyone who has a higher income than someone else?

3 Answers3