transform sql with subquery into query on entity

Question

transform sql with subquery into query on entity

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

I'm needing and am 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 turn this SQL up.

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 did not work.

sql c# entity-framework

asked by anonymous 18.06.2018 / 14:27

2 answers

Problems with CRUD using MySQL java.lang.NumberFormatException: For input string: "code"

score 1 · Answer 1

You do not need a subquery to solve this problem, a LEFT JOIN combined with a SUM() can help you, see:

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;

score 0 · Answer 2

Try as follows, by giving include , already done sub query

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