1

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.

jarlh
  • 42,561
  • 8
  • 45
  • 63
  • Why don't you just execute the SQL? – Liam Jun 04 '19 at 13:55
  • Instead of `g.Count()` (number of rows in group of `b`s) use `group new { b, rv.VISITID } into brvg` and then `brvg.Select(brv => brv.VISITID).Distinct().Count()`. – NetMage Jun 04 '19 at 20:08

0 Answers0