Turning SQL Code to LINQ

Asked

Viewed 477 times

3

With transforming codes SQL for LINQ?

Questions are in the code comments on LINQ:

Code SQL:

Select CL.NomeDeGuerra as Colaborador, CL2.NomeDeGuerra as Gerente, Concat(C.Codigo,'-',C.NomeFantasia) as Cliente,  Concat(CONCAT(C.Codigo, if(P.Numero<=9, Concat('0', P.Numero), P.Numero), '.',
if(FP.Ordem<=9, Concat('0', FP.Ordem), FP.Ordem)),'-',FP.Nome) as Frente, RI.DataReferencia, A.Nome as Atividade, RI.QtdeDias
From RecursoVendaGestaoItem RI
Join RecursoVenda RV on (RI.RecursoVendaID = RV.ID)
join RecursoVendaEquipe RE on (RV.ID = RE.RecursoVendaID)
join FrenteProjeto FP on (RV.FrenteProjetoID = FP.ID)
Join Projeto P on (P.ID = FP.ProjetoID)
join Cliente C on (C.ID = P.ClienteID)
join Colaborador CL on (RE.ColaboradorID = CL.ID)
join Colaborador CL2 on (FP.ColaboradorID = CL2.ID)
join Atividade A on (RV.AtividadeID = A.ID)
Where P.IsDeleted = 0
and FP.IsDeleted = 0
and CL.Socio = 0
UNION ALL
Select CL.NomeDeGuerra as Colaborador, '' as Gerente, 'Bloqueio' as Cliente,  'Bloqueio' as Frente, 
Date_Format(A.DataReferencia, '%Y-%m-01 00:00:00') as Periodo, 'Bloqueio' as   Atividade, Sum(0.5) as QtdeDias
From Agenda A
join Colaborador CL on (A.ColaboradorID = CL.ID)
Where CL.Socio = 0 and A.TipoAgenda = 2 and A.IsDeleted = 0
group by CL.NomeDeGuerra, Concat('BLOQUEIO-', A.Descricao), Date_Format(A.DataReferencia, '%Y/%m')

Code LINQ:

var recursos = (from ri in this.Context.RecursoVendaGestaoItem
                            join rv in this.Context.RecursoVenda on ri.RecursoVendaID    equals rv.ID
                            join re in this.Context.RecursoVendaEquipe on rv.ID equals re.RecursoVendaID
                            join fp in this.Context.FrenteProjeto on rv.FrenteProjetoID equals fp.ID
                            join p in this.Context.Projeto on fp.ProjetoID equals p.ID
                            join c in this.Context.Cliente on p.ClienteID equals c.ID
                            join cl in this.Context.Colaborador on re.ColaboradorID equals cl.ID
                            join cl2 in this.Context.Colaborador on fp.ColaboradorID equals cl2.ID
                            join a in this.Context.Atividade on rv.AtividadeID equals a.ID
                            where !p.IsDeleted
                                 && !fp.IsDeleted
                                 && cl.Socio == 0

                            select new
                            {
                                Colaborador = cl.NomeDeGuerra,
                                Gerente = cl2.NomeDeGuerra,
                                Cliente = c.Codigo + c.NomeFantasia,
                                Frente = c.Codigo,         //Como formatar em Concat(CONCAT(C.Codigo, if(P.Numero<=9, Concat('0', P.Numero), P.Numero), '.', if(FP.Ordem<=9, Concat('0', FP.Ordem), FP.Ordem)),'-',FP.Nome)
                                DataReferencia = ri.DataReferencia,
                                Atividade = a.Nome,
                                QtdeDias = ri.QtdeDias

                            });
            var recursos2 = (from a in this.Context.Agenda
                            join cl in this.Context.Colaborador on a.ColaboradorID equals cl.ID
                            where cl.Socio == 0
                                && a.TipoAgenda == 2
                                && !a.IsDeleted
                            select new{
                                Colaborador = cl.NomeDeGuerra,
                                Gerente = "",
                                Cliente = "Bloqueio",
                                Frente = "Bloqueio",
                                Periodo = a.DataReferencia,       //Formatar Data com Date_Format(A.DataReferencia, '%Y-%m-01 00:00:00')
                                Atividade = "Bloqueio",
                                QtdeDias = a.QtdeDias,           //Como formatar o .Sum(0.5)?
                                Descricao = "BLOQUEIO-"+ a.Descricao
                            });


            var recursos2Group = recursos2.GroupBy(x => new { x.Colaborador, x.Descricao, x.Periodo });        //Como formatar o x.Periodo parecido com em Date_Format(A.DataReferencia, '%Y/%m')?

            var query = recursos.Concat(recursos2Group);        //Esse Union está com erro. Como faço o Union nesse caso?
c# asp.net-mvc linq

  • In the expression Linq return the data without formatting and format them later when displaying them, for example in a Grid. Post the error of Union to facilitate aid.

  • Following errors are showing up: Error 27 'System.Linq.IQueryable<AnonymousType#1>' does not contain a definition for 'Concat' and the best extension method overload 'System.Linq.ParallelEnumerable.Concat<TSource>(System.Linq.ParallelQuery<TSource>, System.Collections.Generic.IEnumerable<TSource>)' has some invalid Arguments Error 26 Instance argument: cannot Convert from 'System.Linq.Iqueryable<Anonymoustype#1>' to 'System.Linq.Parallelquery<Anonymoustype#2>'

1 answer

1

Browser other questions tagged c# asp.net-mvc linq

You are not signed in. Login or sign up in order to post.