1
This query I run in the bank and is working as it should
select rci.markuppercentual from resellercatalogitem rci
join [Order] od on rci.ResellerCatalogId = od.ResellerCatalogId
join OrderItem oi on rci.ProductId = oi.ProductId and oi.OrderId = od.Id
where od.id = 4647 and rci.MarkupPercentual > 0
On the app I did this
var qry = _resellerCatalogItemRepository.Table
.Join(_orderRepository.Table, rci => rci.ResellerCatalogId, od => od.ResellerCatalogId, (rci, od) => new { rci, od })
.Join(_orderItemRepository.Table, rci => rci.rci.ProductId, oit => oit.ProductId, (rci, oit) => new { rci, oit })
.Where(nod => nod.rci.od.Id == 4647 && nod.rci.rci.MarkupPercentual > 0)
.Select(s => new { s.rci.rci.MarkupPercentual}).ToList();
The difference between the database query and the application query, is that in the application comes the record 3 times. And when observing in the database query I did this
join OrderItem oi on rci.ProductId = oi.ProductId and oi.OrderId = od.Id
I added another table and this solved. Running the query I did, I get this in the debug output
SELECT
[Extent1].[ResellerCatalogId] AS [ResellerCatalogId],
[Extent1].[MarkupPercentual] AS [MarkupPercentual]
FROM [dbo].[ResellerCatalogItem] AS [Extent1]
INNER JOIN [dbo].[Order] AS [Extent2] ON [Extent1].[ResellerCatalogId] = [Extent2].[ResellerCatalogId]
INNER JOIN [dbo].[OrderItem] AS [Extent3] ON [Extent1].[ProductId] = [Extent3].[ProductId]
WHERE (4647 = [Extent2].[Id]) AND ([Extent1].[MarkupPercentual] > cast(0 as decimal(18)))
As I added in the above query the equivalent to and the bank Join in a Linq/Lambda query? In the same Join one more condition
When you ask about a problem in your code, you’ll get better answers if you give people code that they can use to reproduce the problem. See how to create a minimum, complete and verifiable example to use in your question.
– Sorack