I am trying to convert an SQL query to LINQ but i need to make use of HAVING Count(Distinct) which I am unable to do in LINQ and I cant seem to find an alternative to get the same results
SELECT
B.BranchCode ,
B.BranchName ,
rv.ENGINEER ,
CONVERT ( DATE, rv.VISITDATE ) [Date] ,
CONVERT ( TIME, rv.STARTTIME ) [Start] ,
COUNT ( DISTINCT rv.VISITID ) V
FROM
dbo.ltbl_Branch AS B
INNER JOIN dbo.tbl_contract AS C ON C.BranchCode = B.BranchCode
INNER JOIN dbo.tbl_repair_order AS r ON r.CONTRACT = C.Contract
INNER JOIN dbo.tbl_repair_visit AS rv ON rv.ORDERID = r.ORDERID
WHERE
CONVERT ( DATE, rv.VISITDATE ) BETWEEN '2019-02-01'
AND '2019-02-01'
AND ISNULL ( rv.FromHHT, 0 ) = 0
GROUP BY
B.BranchCode ,
B.BranchName ,
rv.ENGINEER ,
CONVERT ( DATE, rv.VISITDATE ) ,
CONVERT ( TIME, rv.STARTTIME )
HAVING
COUNT ( DISTINCT rv.VISITID ) > 1
Above is the SQL query I am trying to convert and the LINQ query i have so far is below
from b in Ltbl_Branches
join c in Tbl_contracts on b.BranchCode equals c.BranchCode
join r in Tbl_repair_orders on c.Contract equals r.CONTRACT
join rv in Tbl_repair_visits on r.ORDERID equals rv.ORDERID
where rv.VISITDATE >= DateTime.Today.AddDays(-123)
&& rv.VISITDATE <= DateTime.Today.AddDays(-122)
&& rv.FromHHT == false
group b by new { b.BranchCode, b.BranchName, rv.ENGINEER, rv.VISITDATE,
rv.STARTTIME } into g
where g.Count() > 1
select new {
BranchCode = g.Key.BranchCode,
BranchName = g.Key.BranchName,
Engineer = g.Key.ENGINEER,
Date = g.Key.VISITDATE,
Time = g.Key.STARTTIME,
V = g.Count()
}
It is the Having Count(DISTINCT rv.VISITID) > 1 that i am struggling to get working in linq, I think the rest of the code is working as intended.
I am pulling data from a database on the network, Feb 1st is just a random date at the moment as it has duplicate entries and it is currently 123 since the date which is where the .AddDays(-123) is coming from. The two queries return different results, the SQL query is returning a count of 170 while my LINQ query is only returning 98.