LINQ for finding average salaries of department in column

Help Studio
, ,
1

hi all,
i am trying to write a query for find average of salary per department,but i am getting error
“avg salaries of department.xaml: No compiled code to run
error BC30456: ‘CopyToDataTable’ is not a member of ‘IEnumerable(Of <anonymous type: Department As String, AvgSalary As Double>)’. At line 2”.

The image shows a VB.Net code snippet in a Code Editor window, demonstrating how to group employee data by department and calculate the average salary. (Captioned by AI)
The image shows a VB.Net code snippet in a Code Editor window, demonstrating how to group employee data by department and calculate the average salary. (Captioned by AI)759×454 17.5 KB

2

@JK_MECH,

Try this LINQ

Dim result = dt_Emp.AsEnumerable() _
    .GroupBy(Function(x) x("Dept").ToString()) _
    .Select(Function(deptGroup) New With {
        .Department = deptGroup.Key,
        .AvgSalary = deptGroup.Average(Function(r) Convert.ToInt32(r("Salary")))
    }).CopyToDataTable()

resultTable1 = result
3

Hey @JK_MECH,

The error “BC30456: ‘CopyToDataTable’ is not a member of ‘IEnumerable’” typically occurs when trying to convert the result of a LINQ query directly into a DataTable using .CopyToDataTable() with an anonymous type. Since .CopyToDataTable() does not support anonymous types, you’ll need a workaround.

Solution:

Here’s how you can calculate the average salary per department using LINQ and properly convert the result into a DataTable:

  1. Using LINQ to Calculate Average:

    • You can use LINQ to group by department and calculate the average salary.
    • Then, convert the result into a DataTable using a .Select() projection.

  2. LINQ Query:

    Dim avgSalaries = (From row In dt.AsEnumerable()
                   Group row By dept = row.Field(Of String)("Department") Into grp = Group
                   Select New With {
                       .Department = dept,
                       .AvgSalary = grp.Average(Function(r) r.Field(Of Double)("Salary"))
                   }).ToList()

' Convert to DataTable
Dim resultTable As DataTable = New DataTable()
resultTable.Columns.Add("Department", GetType(String))
resultTable.Columns.Add("AvgSalary", GetType(Double))

For Each item In avgSalaries
    resultTable.Rows.Add(item.Department, item.AvgSalary)
Next

Explanation:

  • Group By: Groups rows by the “Department” column.
  • Average: Calculates the average of the “Salary” column for each department.
  • Anonymous Type: Creates a new object with Department and AvgSalary.
  • ToList(): Converts the result into a list.
  • DataTable Conversion: Loops through the list and adds rows to a new DataTable.

Important Tips:

  • Ensure your “Salary” column is of type Double. You might need to adjust Field(Of Double) based on your actual column type.
  • If your column names have spaces or special characters, ensure they are properly referenced.

Mark as Solution:

If this solution works for you, please mark it as the solution. :blush:

4

@JK_MECH

Create a datatable with two columns both of string type using build datatable

then use assign activity with

resultdt = dt.AsEnumerable.GroupBy(function(x) x("Department").ToString).Select(function(x) resultdt.LoadDataRow({x.Key,x.Average(function(y) CDBL(y("Salary"))).ToString},False)).CopyToDataTable

cheers