Convert SQL with group by a LINQ

Question

Convert SQL with group by a LINQ

Navigation

#1 by (1 votes)

2

I am starting in the linq world, I would like to convert the following SQL query to linq. It's a bit complex and that's why I get lost.

select T_Empresas.* , T_EmpresasIndicadoresK.*
  from dbo.T_Empresas, T_EmpresasIndicadoresK, 
       (select IdEmpresa, max(Fecha) fecha
          from T_EmpresasIndicadoresK group by IdEmpresa )T_EmpresasIndicadoresK2
 where T_EmpresasIndicadoresK2.IdEmpresa = T_EmpresasIndicadoresK.IdEmpresa
   and T_EmpresasIndicadoresK2.fecha     = T_EmpresasIndicadoresK.fecha
   and T_EmpresasIndicadoresK.IdEmpresa  = T_Empresas.IdEmpresa

sql c# linq

asked by ZEN 17.08.2016 в 14:55

source

1 answer

It does not connect with the twitter app Text separated by; in a list with id

score 1 · Answer 1

In order to convert that sql to linq you should study the use of join

join clause (C # Reference)

is more also you should study it in sql

SQL Joins

Then you must study the group by de linq

group (Clause, C # Reference)

How to: Perform a join using compound keys (C # Programming Guide)

var subquery = from k in dbcontext.EmpresasIndicadores
                group k by k.IdEmpresa into g
                select new {
                    IdEmpresa = g.Key,
                    fecha = g.Max(x=> x.Fecha)
                }


var result = from e in dbcontext.Empresas
            join k in dbcontext.EmpresasIndicadores on e.IdEmpresa equals k.IdEmpresa
            join k2 in subquery on new {k.IdEmpresa, k.fecha} equals new {k2.IdEmpresa, k2.fecha}
            select new {
                empresa = e,
                indicador = k
            }

in the select you should not return complete entities