transform sql with subquery into query in Entity

Asked

Viewed 110 times

0

I’m in need and I’m having trouble getting this sql with subquery and doing the same thing in c# Entity.

SELECT  Id,Nome,
      (SELECT Count(Id) FROM Pedidos
      WHERE Pedidos.ColaboradorId = Colaborador.Id) AS NumeroPedidos
  FROM Colaborador
  ORDER BY Nome DESC;

I need to transform this SQL above.

var retornoColaborador = db.Colaboradores.Where(a => a.Id == db.Pedidos.Where(b => b.ColaboradorId == a.Id).Count());

I tried to do something like this but it didn’t work.

c# sql entity-framework

  • I use the Linqpad to help you build these expressions, it can help you: http://www.linqpad.net/

  • What does this linqpad do?

  • is a tool that helps you test code, you can connect in the database and go assembling the expressions or Linq and it will show the corresponding sql query

  • Too much top. thanks for the tip

  • if within Colaborador you have Pedidos just select Employees, and the number of orders would be colaborador.Pedidos.Count

2 answers

1

You don’t need a subquery to solve this problem, a LEFT JOIN combined with a SUM() can help you, look at that:

SELECT
    Colaborador.Id,
    Colaborador.Nome,
    SUM( CASE WHEN Pedidos.Id IS NULL THEN 0 ELSE 1 END) AS NumeroPedidos
FROM
    Colaborador
LEFT JOIN
    Pedidos ON ( Pedidos.ColaboradorId = Colaborador.Id )
GROUP BY
    Colaborador.Id,
    Colaborador.Nome
ORDER BY
    Colaborador.Nome DESC;

  • I need to turn the query I made into Entity for c#

  • But you gave me an idea here, I can use pure sql for it

0

Try this way, when giving include, is already made the sub query

var retornoColaborador = db.Colaboradores.Include(p=> p.Pedidos).Select(p => new
    {
        p.ColaboradorId,
        p.Nome,
        p.Pedidos.Count
    });

  • This request does not work, do not think

  • Is there any way to add to the question the two classes of yours? , I thought I had a rsrs navigative property

Browser other questions tagged c# sql entity-framework

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