CODEWITHSUNDEEP
sqllinqlinq-to-sqlmaster-detail
Duncan
Duncan

Reputation: 10291

Return master rows based on detail filter

I have a query where I want to return Master rows based on whether the detail fulfil a certain criteria.

For example, I only want to return a particular Master row if AT LEAST one of the Detail rows have SomeProperty = X.

Based on the following predicate:

        predicate = predicate.And(p =>
                                  p.BasketItems.Where(obi => obi.BasketItemTypeID ==
                                                                  (int) BasketType.Refund).Count() > 0);

generates the following SQL:

SELECT COUNT(*)
    FROM [dbo].[BasketItems] AS [t3]
    WHERE ([t3].[BasketId] = [t0].[OrderBasketID]) AND ([t3].[BasketItemTypeID] = 3)
    )) > 0)

Problem with this is it's doing a table scan, so the query takes a while to run.

Just checking that I'm not doing anything crazy and wonder if there's anything that can speed up this query?

Thanks Duncan

Upvotes: 2

Views: 406

Answers (1)

Hellion
Hellion

Reputation: 1740

select M.basketID, max(M.field1) as field1, max(M.field2) as field2 
from dbo.basketItems as M
Inner join detail on M.basketID = detail.basketID
where detail.basketItemTypeID = '3'
group by M.basketID

(Join master to detail. select all rows where detail has the required criterion. Squish the resulting rows down to 1 per master record.)

Upvotes: 1

Related Questions