Linq with SQL type GROUP and HAVING

Epicor ERP 10
1

My Linq skills are not robust but I usually find answers to most of my questions by “Googling it”.

I’ve been having a hard time trying to create an equivalent Linq statement to the SQL statement below.
I was wondering if anyone had any pointers or is this even possible?

I’m using code and multiple queries to get what I want for now but it would be nice if I could just get this into a Linq statement and iterate through the rows.

SELECT EmpBasic.Company, EmpBasic.EmpID, EmpBasic.Name, LaborDtl.ClockInDate, SUM(LaborDtl.LaborHrs) AS TotalHrs
FROM Erp.LaborDtl
INNER JOIN dbo.EmpBasic ON EmpBasic.Company = LaborDtl.Company AND LaborDtl.EmployeeNum = EmpBasic.EmpID
WHERE EmpBasic.Company = ‘CT’ AND LaborDtl.ClockInDate = ‘4/20/2023’ AND EmpBasic.EmpStatus = ‘A’
GROUP BY EmpBasic.Company, EmpBasic.EmpID, EmpBasic.Name, LaborDtl.ClockInDate
HAVING SUM(LaborDtl.LaborHrs) > 12 OR SUM(LaborDtl.LaborHrs) < 8

2

Try something like this

string company = "CT";
DateTime clockInDate = DateTime.Parse("4/20/2023");
string empStatus = "A";

var query = from laborDtl in Db.LaborDtl
			join empBasic in Db.EmpBasic
			on new { laborDtl.Company, EmployeeNum = laborDtl.EmployeeNum }
			equals new { empBasic.Company, EmployeeNum = empBasic.EmpID }
			where empBasic.Company == company
			&& laborDtl.ClockInDate == clockInDate
			&& empBasic.EmpStatus == empStatus
			group laborDtl by new
			{
				empBasic.Company,
				empBasic.EmpID,
				empBasic.Name,
				laborDtl.ClockInDate
			} into grouped
			let totalHrs = grouped.Sum(x => x.LaborHrs)
			where totalHrs > 12 || totalHrs < 8
			select new
			{
				grouped.Key.Company,
				grouped.Key.EmpID,
				grouped.Key.Name,
				grouped.Key.ClockInDate,
				TotalHrs = totalHrs
			};


var result = query.ToList();

Note I didn’t test this thoroughly but it seems to work fine in my LinqPad

image
image638×591 27 KB

BTW that Linq I just gave translates to this in SQL thank you LinqPad

-- Region Parameters
DECLARE @p0 NVarChar(1000) = 'C001'
DECLARE @p1 Date = '2023-04-20'
DECLARE @p2 NVarChar(1000) = 'A'
DECLARE @p3 Decimal(7,5) = 12
DECLARE @p4 Decimal(6,5) = 8
-- EndRegion
SELECT [t2].[Company], [t2].[EmpID], [t2].[Name], [t2].[ClockInDate], [t2].[value] AS [TotalHrs]
FROM (
    SELECT SUM([t0].[LaborHrs]) AS [value], [t1].[Company], [t1].[EmpID], [t1].[Name], [t0].[ClockInDate]
    FROM [LaborDtl] AS [t0]
    INNER JOIN [EmpBasic] AS [t1] ON ([t0].[Company] = [t1].[Company]) AND ([t0].[EmployeeNum] = [t1].[EmpID])
    WHERE ([t1].[Company] = @p0) AND ([t0].[ClockInDate] = @p1) AND ([t1].[EmpStatus] = @p2)
    GROUP BY [t1].[Company], [t1].[EmpID], [t1].[Name], [t0].[ClockInDate]
    ) AS [t2]
WHERE ([t2].[value] > @p3) OR ([t2].[value] < @p4)

And I did verify just now that your original query returns the same results as this at least through the first few lines

image
image849×517 26.7 KB

3 Likes
3

You could also call a BAQ…

Where are you calling this from?

2 Likes
4

Instead of googling try ChatGPT if you and your org are comfortable utilizing that service.

5

Awesome! Thanks Jose!

6

I am working within an Epicor Function

7

Ah! I didn’t think of that. Good idea.

9

Suspicious Monkey GIF by MOODMAN
:rofl::rofl::rofl:

how do you think I generated that so quickly :yum:

1 Like