Convert this SQL to LINQ

Question

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

Answer 1

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.