I am very new to using LINQ and have done a few things, right now I have a SQL query that I need to implement in LINQ and I have no idea how to do it.
SELECT Boletas.Boleta, DATEPART(DW, CAST(Boletas.Fecha AS datetime)) AS DiaDeLaSemana,
Materiales.Descripcion, Clientes.Nombre,
induscomer.NoCliente AS induscomerNoCliente
FROM Boletas
INNER JOIN Materiales
ON Boletas.Material = Materiales.Material AND Boletas.Familia = Materiales.Familia
INNER JOIN Clientes
ON Boletas.NoCliente = Clientes.NoCliente
LEFT OUTER JOIN induscomer
ON Clientes.NoCliente = induscomer.NoCliente
WHERE (Boletas.Fecha >= '20170101') AND (Boletas.Fecha <= '20170107')
AND (Boletas.Status = 'A')
ORDER BY Boletas.Material
In such a way that if the corresponding client does not exist in the induscomer table , then induscomerNoCliente would be null.
I tried following some helps I found to do it like this:
var boletaQuery = (
from b in _context.Boletas
join m in _context.Materiales on b.Material equals m.Material
join c in _context.Clientes on b.NoCliente equals c.NoCliente
join i in _context.induscomer on c.NoCliente equals i.NoCliente into indus
from ind in indus.DefaultIfEmpty()
where b.Fecha.CompareTo(FechaIni) >= 0
&& b.Fecha.CompareTo(FechaFin) <= 0
&& b.Status.Equals('A')
select new {
b,
induscomerNoCliente = ind.NoCliente,
NombreCliente = c.Nombre,
DescripcionMaterial = m.Descripcion
}
);
But when I do boletaQuery.ToList();
, I get the following error:
Unhandled exception of type 'System.NotSupportedException' in System.Data.Entity.dll Additional Information: A constant value of type 'System.Object' could not be created. Only primitive types or enumeration types are supported in this context.
There is a tool that can help you, it's called Linqer. This tool can help you convert a SQL query to Linq and vice versa.
I recommend that you use it, see the behavior it has when performing the conversions and, above all, assimilate that it is quite similar, although at first instance it seems that SQL and Linq are totally different, do not see it that way.
In the same way, I recommend that you review the official documentation (in English) and see how to start working with Linq.
Well, thank you all very much for your contributions, following the advice of @YanetSilvaFernández, @WeimarYamitMorenoPerez, @Flxtr and after a lot of tests the LEFT OUTER JOIN is now:
Then I load the data to my structure in which I defined the induscomerNoCliente field as int? (which allows nulls) with the following loop:
Greetings and thanks again
This worked for me:
...........................
......................... This is the execution plan of both queries in sql and they have the same cost. ( What is the difference between implicit and explicit joins? )
Your question is interesting, but apart from finding out how to convert it, I suggest you try this software:
LINQPad
It is a very useful tool, it can be used to test the direct linq code on the DB connection:
This other program does what you need to convert SQL to linq:
sqltolinq
Apparently both are paid :(