将SQL转换为LINQ

Question

我有这个sql查询:

SELECT
      Count(Distinct([AgenziaCompleta])) as Age
      ,Count(Distinct([Corso])) as NumCorsi
      ,[PV_Prov]
  FROM [SvilDB].[dbo].[V_PreiscrittiSistemiCorsiFiniti_ConRegPvCom]
  WHERE [SvilDB].[dbo].[V_PreiscrittiSistemiCorsiFiniti_ConRegPvCom].[PV_Reg] = N'XXXXXX'
  GROUP BY [PV_Prov] 

我怎么把它变成linq?

谢谢

Answer 1

Dim table = V_PreiscrittiSistemiCorsiFiniti_ConRegPvCom
Dim q = table.Where(Function(x) x.PV_Prov = N'XXXXXX')
             .GroupBy(Function(x) x.PV_Prov)
             .Select(Function(Grp) New With {.Age = Grp.Select(Function(x) x.AgenziaCompleta).Distinct().Count(),
                                             .NumCorsi = Grp.Select(Function(x) x.Corso).Distinct().Count() })

，但是因为您筛选的是单个PV值，您可以简单地:

Dim q = table.Where(Function(x) x.PV_Prov = N'XXXXXX')
Dim Age      = q.Select(Function(x) x.AgenziaCompleta).Distinct().Count()
Dim NumCorsi = q.Select(Function(x) x.Corso)          .Distinct().Count()