Problem with Filtering in LINQ to EF

Problem with Filtering in LINQ to EF

Old forum URL: forums.lhotka.net/forums/t/7828.aspx

CyclingFoodmanPA posted on Tuesday, October 20, 2009

Ok, I have a LINQ query utilizing EF as follows:

NYMEXProduct data = (from record in context.ObjectContext.NYMEXProducts.Include("NYMEXHistories")
where record.NYMEXProductId == criteria.Value
&& record.NYMEXHistories.Any((h) => h.NYMEXDate > new System.DateTime(2009,10,13))
select record).FirstOrDefault();

MapEntityToObject(data);

LoadProperty<BLNYMEXProductNYMEXHistory>(_NYMEXHistoryCollection, BLNYMEXProductNYMEXHistory.GetChildren(data.NYMEXHistories));

context.ObjectContext.Detach(data);

What I want to have happen, is I only receive the particular NYMEXHistory records retrieved that have a NYMEXDate > 10/13/2009 which would be 1,280 rows.  However, I am getting 181725 rows back and here is the SQL that is being generated as acquired by SQL Server Profiler:

SELECT
[Project3].[NYMEXProductId] AS [NYMEXProductId],
[Project3].[NYMEXProductShortName] AS [NYMEXProductShortName],
[Project3].[NYMEXProductName] AS [NYMEXProductName],
[Project3].[LastChanged] AS [LastChanged],
[Project3].[LastChangedBy] AS [LastChangedBy],
[Project3].[C1] AS [C1],
[Project3].[C3] AS [C2],
[Project3].[C2] AS [C3],
[Project3].[NYMEXHistoryId] AS [NYMEXHistoryId],
[Project3].[NYMEXDate] AS [NYMEXDate],
[Project3].[ForwardDate] AS [ForwardDate],
[Project3].[ForwardMonth] AS [ForwardMonth],
[Project3].[NYMEXLow] AS [NYMEXLow],
[Project3].[NYMEXHigh] AS [NYMEXHigh],
[Project3].[NYMEXSettle] AS [NYMEXSettle],
[Project3].[LastChanged1] AS [LastChanged1],
[Project3].[LastChangedBy1] AS [LastChangedBy1],
[Project3].[NYMEXProductId1] AS [NYMEXProductId1]
FROM ( SELECT
[Limit1].[NYMEXProductId] AS [NYMEXProductId],
[Limit1].[NYMEXProductShortName] AS [NYMEXProductShortName],
[Limit1].[NYMEXProductName] AS [NYMEXProductName],
[Limit1].[LastChanged] AS [LastChanged],
[Limit1].[LastChangedBy] AS [LastChangedBy],
[Limit1].[C1] AS [C1],
[Extent3].[NYMEXHistoryId] AS [NYMEXHistoryId],
[Extent3].[NYMEXDate] AS [NYMEXDate],
[Extent3].[NYMEXProductId] AS [NYMEXProductId1],
[Extent3].[ForwardDate] AS [ForwardDate],
[Extent3].[ForwardMonth] AS [ForwardMonth],
[Extent3].[NYMEXLow] AS [NYMEXLow],
[Extent3].[NYMEXHigh] AS [NYMEXHigh],
[Extent3].[NYMEXSettle] AS [NYMEXSettle],
[Extent3].[LastChanged] AS [LastChanged1],
[Extent3].[LastChangedBy] AS [LastChangedBy1],
CASE WHEN ([Extent3].[NYMEXHistoryId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C2],
CASE WHEN ([Extent3].[NYMEXHistoryId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C3]
FROM (SELECT TOP (1)
[Extent1].[NYMEXProductId] AS [NYMEXProductId],
[Extent1].[NYMEXProductShortName] AS [NYMEXProductShortName],
[Extent1].[NYMEXProductName] AS [NYMEXProductName],
[Extent1].[LastChanged] AS [LastChanged],
[Extent1].[LastChangedBy] AS [LastChangedBy],
1 AS [C1]
FROM [dbo].[NYMEXProduct] AS [Extent1]
WHERE ([Extent1].[NYMEXProductId] = 4) AND ( EXISTS (SELECT
cast(1 as bit) AS [C1]
FROM [dbo].[NYMEXHistory] AS [Extent2]
WHERE ([Extent1].[NYMEXProductId] = [Extent2].[NYMEXProductId]) AND ([Extent2].[NYMEXDate] > convert(datetime, '2009-10-13 00:00:00.000', 121))
)) ) AS [Limit1]
LEFT OUTER JOIN [dbo].[NYMEXHistory] AS [Extent3] ON [Limit1].[NYMEXProductId] = [Extent3].[NYMEXProductId]
) AS [Project3]
ORDER BY [Project3].[NYMEXProductId] ASC, [Project3].[C3] ASC

When I type:
SELECT np.*, nh.*
FROM dbo.NYMEXProduct np
INNER JOIN dbo.NYMEXHistory nh ON nh.NYMEXProductId = nh.NYMEXProductId
WHERE nh.NYMEXDate > '10/12/2009' AND np.NYMEXProductId = 4
ORDER BY nh.NYMEXDate, np.NYMEXProductName

I get the 1280 rows, which is what I want.  Will someone please tell me what I am doing wrong with my LINQ sql.  Any recomendations would be most appreciated.

CyclingFoodmanPA

tmg4340 replied on Tuesday, October 20, 2009

"Any" and "All" are not filtering operations - they are existence checks.  If you look at the SQL, you'll see your "Any" translating into an EXISTS clase.

Presuming you want both the Product record and the associated history records, try switching your ".Any" to a ".Where" and see if that gets you what you want.  You shouldn't have to change anything else.  I think there may be a "more elegant" solution using Entity SQL, but I don't have my book handy, and I haven't done enough EF yet to have it committed to memory.  Smile [:)]

HTH

- Scott

CyclingFoodmanPA replied on Wednesday, October 21, 2009

Scott,

Thanks for the info.  You were right.  I rechecked page 94 of Julia's book and sure enough it was an existance check.  I have written the following utilizing the where method, but am getting a number of errors.  If anyone could steer me in the right direction, I would be most greatful.  I am trying to return a particular NYMEXProduct record and a number of NYMEXHistory records after a certain date.  The NYMEXHistory table has a FK to NYMEXProduct.  Here is my LINQ query with errors:

DateTime dt = DateTime.Now.AddDays(-7);
NYMEXProduct data = (from record in context.ObjectContext.NYMEXProducts.Include("NYMEXHistories")
                                          where record.NYMEXProductId == criteria.Value
                                          && record.NYMEXHistories.Where((NDate) => NDate.NYMEXDate > dt))
                                          select record).FirstOrDefault();

I know by the error messages that I am incorrectly building the LINQ query, but not sure how to correctly do it.  My SQL query is as follows that work:
SELECT np.*, nh.*
FROM dbo.NYMEXProduct np
INNER JOIN dbo.NYMEXHistory nh ON nh.NYMEXProductId = np.NYMEXProductId
WHERE np.NYMEXProductId = 4
AND nh.NYMEXDate > '10/14/2009'

Any recomendations for my LINQ Query would be most appriciated.  I know I am close and have been searching for an example but cannot find one.  Essentially, I am filtering an Entity Collection.

CyclingFoodmanPA

 

Copyright (c) Marimer LLC