How can I convert this sql statement to linq expression

Question

How can I convert this sql query to linq expression, I'm new in linq and searched this but I could not understand how to convert it.

SELECT 
   a.afiliacaoid,
   DATE_FORMAT(a.horario, '%d/%m/%Y') data,
   COUNT(a.afiliacaoid) acessos,
   IFNULL(p.pedidos,0) pedidos
FROM 
   acesso a LEFT JOIN 
       (SELECT p.afiliacaoid, 
           DATE_FORMAT(p.cadastro , '%d/%m/%Y') data, 
           COUNT(p.afiliacaoid) pedidos 
           FROM pedido p) p ON a.afiliacaoid = p.afiliacaoid 
               AND DATE_FORMAT(a.horario, '%d/%m/%Y') = data
WHERE 
   a.afiliacaoid=1
   GROUP BY DATE_FORMAT(a.horario, '%d/%m/%Y')

I would suggest you map the classes so there is a navigational property from acesso to pedido, so that you can write something like a.pedio.Id.Count(); — Thomas Koelle, Jul 15 '19 at 10:44
Possible OT: does the query above work at all? With the COUNT aggregation in subquery without GROUP BY clause? — Jan Drozen, Jul 15 '19 at 10:44
Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might be useful. — NetMage, Jul 15 '19 at 19:29

score 0 · Answer 1 · answered Jul 15 '19 at 10:29

0

I would suggest using this tool to convert: https://www.linqpad.net/

answered Jul 15 '19 at 10:29

andyb952

1,931
11
25

score 0 · Answer 2 · answered Jul 15 '19 at 11:02

0

This is a good tool that might be helpful to you - http://www.sqltolinq.com

answered Jul 15 '19 at 11:02

Human

67
1
3

How can I convert this sql statement to linq expression

2 Answers2