Linq - Sum of values inside a select statement with joins

Question

I have s select statement with joining 3 tables which returns something like salary of Individual employees, i want the sum of salary of all employees.

my statement looks like below.

This statement is returning array of Salaries of individual employees, how can i get the SUM ?

var x = (from e in EmpDB.Employees
      join d in EmpDB.Departments on e.DepId equals d.Id
      join s in EmpDB.EmpSalaries on new { d.Zone, d.Vendor, d.Status } equals new { s.Zone, s.Vendor, s.Status}
      where e.Status = "Active" && e.BAID != NULL && e.DepId == 3
      select new { s.Salary.Value });

Tried using SUM but not sure about it

Instead of posting the query you have. Could post a minimal example of your model classes, filled with only the important navigation properties? — MindSwipe, Nov 06 '19 at 14:49
related ? https://stackoverflow.com/questions/1597181/linq-to-sql-how-to-aggregate-without-a-group-by — xdtTransform, Nov 06 '19 at 14:50
tried using SUM in "select new { s.Salary.Value }) " but there is some issue — user3198688, Nov 06 '19 at 14:51
Are you asking for the sum of the salaries of **all** employees (which is what you ask for) or the sum of the salaries of the employees `where e.Status = "Active" && e.BAID != NULL && e.DepId == 3` (which is what your posted code is doing)? These are two very different things. It's important to be precise about exactly what you are expecting. — Flater, Nov 06 '19 at 15:39

CPerson · Accepted Answer · 2019-11-06T17:01:57.727

2

Updated to reflect Mindswipe's suggestion for more clarity.

The statement select new { s.Salary.Value } creates an enumerable collection of objects containing a salary value. To simplify this, we can instead use the following to create an enumerable collection of numeric values:

select s.Salary

Since you are using .Value, I assume that Salary is a nullable type meaning that we may get a null value in the set of salaries, so to avoid that, we will use the null coalescing operator ?? to return 0 for the null values:

select s.Salary ?? 0

Once you have a collection of the individual salary values, you can use the LINQ Sum function:

var x = (from e in EmpDB.Employees
      join d in EmpDB.Departments on e.DepId equals d.Id
      join s in EmpDB.EmpSalaries on new { d.Zone, d.Vendor, d.Status } equals new { s.Zone, s.Vendor, s.Status}
      where e.Status = "Active" && e.BAID != NULL && e.DepId == 3
      select s.Salary ?? 0)
      .Sum();

There are cleaner and more expressive ways to write this same query, but this should give you the result you are looking for.

edited Nov 06 '19 at 17:01

answered Nov 06 '19 at 14:50

CPerson

1,222
1
6
16

assuming the type of `EmpDB.EmpSalaries.Salary.Value`. Perhaps it is nullable, perhaps it is a `string`, perhaps it is something else. – Jodrell Nov 06 '19 at 14:54
tried this but getting the compilation error - iqueryable<> does not contain a definition for 'Sum' and the best extenion method overload 'Queryable.Sum(IQueryable)' requires a receiver of type 'IQueryable' – user3198688 Nov 06 '19 at 14:57
1

Good answer. Great job catching the `select new { ...`. But this answer could be better by stating what was wrong and what you decided to change. Feel free to pirate my answer for this, you were here first – MindSwipe Nov 06 '19 at 15:10
select s.Salary.value).sum() is getting the count value, but when there is null it is throwing an error, where as select s.Salary.GetValueOrDefault() is throwing an runtime error stating " linq to entities does not recognize the method 'int32 getvalueordefault()' method, and this method cannot be translated into a stored expression" – user3198688 Nov 06 '19 at 16:47
Understood. Can you try the null coalescing operator instead? I'll update the code. – CPerson Nov 06 '19 at 17:00

score 2 · Answer 2 · answered Nov 06 '19 at 15:08

The problem lies that you are selecting a new anonymous type, instead of just the salaries. All you have to do is change the last line from select new { s.Salary.Value }); to select s.Salary.GetValueOrDefault()); This will give you a list of whatever datatype s.Salary is (maybe a double??)

So your query is now this:

var x = (from e in EmpDB.Employees
      join d in EmpDB.Departments on e.DepId equals d.Id
      join s in EmpDB.EmpSalaries on new { d.Zone, d.Vendor, d.Status } equals new { s.Zone, s.Vendor, s.Status}
      where e.Status = "Active" && e.BAID != NULL && e.DepId == 3
      select s.Salary.GetValueOrDefault());

For everyone who wants to use Expression LINQ here it is, for everyone who doesn't care you can stop reading.

var allSalaries = EmpDB.Employees.Join(EmpDB.Departments, employee => employee.DepId, department => department.Id,
        (employee, department) => new {Employee = employee, Department = department})
    .Join(EmpDB.EmpSalaries, emplDept => new
            {emplDept.Department.Status, emplDept.Department.Vendor, emplDept.Department.Zone}
        , emplSalary => new {emplSalary.Status, emplSalary.Vendor, emplSalary.Zone},
        (emplDept, emplSalary) => emplSalary.Salary).Sum();

Both queries aren't very readable as they are quite complex queries

select s.Salary.GetValueOrDefault()); is throwing an runtime error "linq to entities does not recognize the method 'int32 getvalueordefault()' method, and this method cannot be translated into a stored expression" — user3198688, Nov 06 '19 at 16:52
What datatype exactly is the Property `Salary` on `EmpDB.EmpSalaries`? I (and the other answer) assumed that it was just a nullable `int` — MindSwipe, Nov 06 '19 at 17:38

Linq - Sum of values inside a select statement with joins

2 Answers2