Entity framework navigation property FirstOrDefault()

Question

I have the following line:

WorkPlaces.FirstOrDefault()
    .WorkSteps.Where(x=>x.Failcodes_Id != null)
    .OrderByDescending(x=>x.Timestamp)
    .FirstOrDefault()

There are about 10-20 workplaces and each workplace have thousands of worksteps. I would like to get the last workstep for each of the workplaces. The code above is an example from linqpad because I couldn't believe that the generated sql looks like this:

SELECT TOP (1) 
[Extent1].[Id] AS [Id], 
[Extent1].[Name] AS [Name], 
[Extent1].[Description] AS [Description], 
[Extent1].[Active] AS [Active], 
[Extent1].[ProductionLine_Id] AS [ProductionLine_Id], 
[Extent1].[DefaultTechnology_Id] AS [DefaultTechnology_Id], 
[Extent1].[PrinterName] AS [PrinterName], 
[Extent1].[Deleted] AS [Deleted], 
[Extent2].[Id] AS [Id1], 
[Extent1].[LoggedInUser_UserId] AS [LoggedInUser_UserId]
FROM  [dbo].[WorkPlaces] AS [Extent1]
LEFT OUTER JOIN [dbo].[WorkplaceParameterSet] AS [Extent2] ON [Extent1].[Id] = [Extent2].[WorkPlace_Id]
GO

-- Region Parameters
DECLARE @EntityKeyValue1 Int = 1
-- EndRegion
SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[Timestamp] AS [Timestamp], 
[Extent1].[Description] AS [Description], 
[Extent1].[WorkPlace_Id] AS [WorkPlace_Id], 
[Extent1].[WorkItemState_Id] AS [WorkItemState_Id], 
[Extent1].[UserId] AS [UserId], 
[Extent1].[WorkItem_Id] AS [WorkItem_Id], 
[Extent1].[Technology_Id] AS [Technology_Id], 
[Extent1].[Failcodes_Id] AS [Failcodes_Id], 
[Extent1].[DrawingNo] AS [DrawingNo], 
[Extent1].[ManualData] AS [ManualData], 
[Extent1].[Deleted] AS [Deleted], 
[Extent1].[WorkItemState_Arrival_Id] AS [WorkItemState_Arrival_Id]
FROM [dbo].[WorkSteps] AS [Extent1]
WHERE [Extent1].[WorkPlace_Id] = @EntityKeyValue1

Is there a way to get one line from worksteps without downloading 9000 records to pick one from the top of the list?

score 1 · Accepted Answer · answered Jun 10 '14 at 20:06

1

Rather than getting each workplace individually and then getting the workstep for that work place in a query you can use Select to project each workplace into the workstep that you want in one query:

var query = WorkPlaces.Select(workplace => workplace.WorkSteps
    .Where(x => x.Failcodes_Id != null)
    .OrderByDescending(x => x.Timestamp)
    .FirstOrDefault());

answered Jun 10 '14 at 20:06

Servy

202,030
26
332
449

Firstordefault -> top for the navigation property is working now, thanks! – Perrier Jun 11 '14 at 05:37

score -1 · Answer 2 · edited May 23 '17 at 12:20

-1

You should use IQueryable interface instead of IEnumerable. Also check this.

edited May 23 '17 at 12:20

Community

1
1

answered Jun 10 '14 at 19:40

MacGyver

2,983
1
17
14

He already is. If he wasn't, it wouldn't be generating SQL as he showed that it is. – Servy Jun 10 '14 at 19:57

Entity framework navigation property FirstOrDefault()

2 Answers2