How to put one more condition in the Join on clause in a Linq/Lambda query

Asked

Viewed 107 times

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.

No answers

Browser other questions tagged

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