You must Sign In to post a response.
  • Category: LINQ

    Transform sql into linq

    I have a list of objects that populate a gridview and I populate a new gridview filtered.
    I take the filter on DropDownList

    I can not do a database query beccause the table have 100000 rows
    i try this, but it does not work

    Dim filter As String = DdlScegliregione.SelectedValue

    GdrProvincia.DataSource = listCorsiFiniti.Where(Function(x) x.Provincia = filter) .GroupBy(Function(x) x.Provincia) .Select(Function(Grp) New With {.Age = Grp.Distinct(Function(x)x.AgenziaCompleta).Count(), .NumCorsi = Grp.Distinct(Function(x) x.Corso).Count()})

    The sql-query is like this:

    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]

    How can I turn it into linq?

    Thanks
  • #742792
    I found a solution but it's not very "elegant":

    From lc In listCorsiFiniti
    Where lc.Regione = DdlScegliregione.SelectedValue
    Group lc By key = lc.Provincia Into Group
    Select Provincia = key, NumeroCorsi = Group.Count(),
    NumeroAgenzie = (From lc In listCorsiFiniti Where (lc.Regione = DdlScegliregione.SelectedValue And lc.Provincia = key)
    Group By lc.Agenzia Into g = Group Select g).Distinct.Count()

  • #745543
    Hi Simone,

    var result=from ic in V_PreiscrittiSistemiCorsiFiniti_ConRegPvCom Group By ic.PV_Prov where ic.PV_Reg.Equel(N'XXXXXX')select new{age=ic.AgenziaCompleta,
    NumCorsi=ic.Corso}..Distinct.Count();


    return to column age and NumCorsi with count and not douplication


  • Sign In to post your comments