CODEWITHSUNDEEP
sqllinq
Fernando
Fernando

Reputation: 2193

Convert this SQL to LINQ

How do I do this query in linq? All the tables already are list of objects.

This query give points to entities named "Empresas" (Companies) that fills the "Palavras" (Words) criterias.

select x.empresaid, sum(x.pontos)

from (

        select a.empresaid, sum(1) as Pontos
        from empresa  a
        inner join Palavras b on a.nome like '%' + b.Palavra + '%'
        group by a.empresaid

        union all

        select a.empresaid, sum(case when c.estabelecimento is null then 0 else 1 end) as Pontos
        from empresa  a
        left join estabelecimentoempresa b on b.empresaid = a.empresaid
        left join estabelecimento c on c.estabelecimentoid = b.estabelecimentoid
        left join Palavras d on c.estabelecimento like '%' + d.Palavra + '%'
        group by a.empresaid

        union all

        select a.empresaid, sum(case when c.Cozinha is null then 0 else 1 end) as Pontos
        from empresa  a
        left join Cozinhaempresa b on b.empresaid = a.empresaid
        left join Cozinha c on c.Cozinhaid = b.Cozinhaid
        left join Palavras d on c.Cozinha like '%' + d.Palavra + '%'
        group by a.empresaid
    ) x

group by x.empresaid

order by sum(x.pontos) desc, x.empresaid

Upvotes: 0

Views: 252

Answers (1)

Jeeva Subburaj
Jeeva Subburaj

Reputation: 1921

I don't think you would be able convert as it is from SQL to LINQ. You could still try this tool that convert SQL to LINQ syntax:

http://www.sqltolinq.com/

The preferable approach is to understand and write the LINQ syntax on your own.

Upvotes: 2

Related Questions