Convert sql query to linq

Question

Convert sql query to linq

Navigation

#1 by (2 votes)
#2 by (1 votes)

0

I have basic concepts of linq, but I stuck with a query with group by someone could help me translate this query from sql to linq:

select at1,at2,count(1) 
from tabla 
where fecha=trunc(sysdate) 
group by at1,at2

Greetings.

c# .net linq

asked by RSillerico 12.08.2016 в 22:26

source

2 answers

Can not convert undefined or null to object in Javascript Include a dll as a reference and call objects of your class

score 2 · Answer 1

To express GROUP BY of multiple columns and COUNT with LINQ:

query syntax

from entidad in entidades
group entidad by new { entidad.Propiedad1, entidad.Propiedad2 } into grupo
select new { grupo.Key.Propiedad1, grupo.Key.Propiedad2, Count = grupo.Count() };

or lambda syntax

entidades
    .GroupBy(entidad => new { entidad.Propiedad1, entidad.Propiedad2 })
    .Select(grupo => new { grupo.Key.Propiedad1, grupo.Key.Propiedad2, Total = grupo.Count() });

score 1 · Answer 2

Apart from what Equiso has answered in a very correct way, you must also bear in mind that this fecha=trunc(sysdate) , in linq2Entities, is expressed through some static methods of the DbFunctions class or EntitiyFunctions , within the namespace System.Data.Entity , the first is for version 6 of EF and the second for the previous ones. That would be the thing:

link link

using System.Data.Entitiy;

from entidad in entidades

where DbFunctions(entidad.fecha) == DbFunctions(DateTime.Today)

group entidad by new { entidad.Propiedad1, entidad.Propiedad2 } into grupo
select new { grupo.Key.Propiedad1, grupo.Key.Propiedad2, Count = grupo.Count() };