C# - Subselect in Lambda and Linq

Asked

Viewed 235 times

1

How to mount the query below in Lambda and/or Linq?

select 
emp.cd_empresa AS ID,
emp.nm_razao_social + ' (' + emp.nm_fantasia + ')' AS EMPRESA,
(SELECT COUNT(con.cd_consulta) from tb_consulta_empresa con where con.cd_empresa = emp.cd_empresa and con.cd_consulta = 590) AS STATUSfrom tb_empresaemp order by 3 desc
c# sql linq lambda-expressions

1 answer

2


Maybe this way?

var leftJoin = (from tbe in tb_Empresa
                join tce in tb_Consulta_Empresa on tbe.cd_empresa equals tce.cd_empresa into tbetce
                from tce in tbetce.DefaultIfEmpty()
                where tce.cd_consulta == 590
                select new
                {
                    ID = tbe.cd_empresa,
                    EMPRESA = $"{tbe.nm_razao_social} ({tbe.nm_fantasia})",
                    STATUS = tbetce.Count()
                });

It is necessary, of course, to pay attention to the name of the tables and fields. What I put in the answer are only suggestive.

  • Thank you! Exactly that.

Browser other questions tagged c# sql linq lambda-expressions

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