RecordedEvent List

gistfile1.cs

context = context.AsNonChangeTrackingContext();
return context
    .AsQueryable<RecordedEvent>()
    .Include(x => x.EventStreams)
    .AsExpandable()
    .Where(where)
    .OrderBy(orderByField, ascending)
    .Skip((pageNumber - 1)*recordsPerPage)
    .Take(recordsPerPage)
    .Select(x => new RecordedEventProjection
    {
        Id = x.Id,
        Reid = x.Reid,
        IsLocked = x.IsLocked,
        VehicleId = x.VehicleID,
        StartTime = x.StartTime,
        EndTime = x.EndTime,
        Category = x.Category,
        OfficerName = x.OfficerName,
        OfficerId = x.OfficerId,
        DepartmentId = x.DepartmentId,
        BadgeNumber = x.BadgeNumber,
        DurationTicks = x.DurationTicks,
        CamerasCount = x.CamerasCount,
        FirstStreamNumber = x.EventStreams.Select(s => s.StreamNumber).FirstOrDefault(),
        DestinationStorageDeviceSerialNumber = x.DestinationStorageDeviceSerialNumber,
        DestinationStorageDeviceName = x.DestinationStorageDevice.UserFriendlyName,
        DestinationStorageDevicePath = x.DestinationStorageDevice.Path,
        EventFileSize = x.EventFileSize,
        Source = x.Source,
        HasLinkedEvents = x.LinkedEvents.Any(),
        GroupId = x.GroupId,
        TotalParts = x.TotalParts,
        Part = x.Part,
        ImportedOn = x.ImportedOn,
    });

gistfile2.sql

SELECT TOP (25) 
    [top].[Id] AS [Id], 
    [top].[Reid] AS [Reid], 
    [top].[IsLocked] AS [IsLocked], 
    [top].[VehicleID] AS [VehicleID], 
    [top].[EventDateTime] AS [EventDateTime], 
    [top].[EventEndDateTime] AS [EventEndDateTime], 
    [top].[Category] AS [Category], 
    [top].[OfficerName] AS [OfficerName], 
    [top].[OfficerId] AS [OfficerId], 
    [top].[DepartmentId] AS [DepartmentId], 
    [top].[BadgeNumber] AS [BadgeNumber], 
    [top].[DurationTicks] AS [DurationTicks], 
    [top].[CamerasCount] AS [CamerasCount], 
    [top].[C1] AS [C1], 
    [top].[DestinationStorageDeviceSerialNumber] AS [DestinationStorageDeviceSerialNumber], 
    [top].[UserFriendlyName] AS [UserFriendlyName], 
    [top].[Path] AS [Path], 
    [top].[EventFileSize] AS [EventFileSize], 
    [top].[Source] AS [Source], 
    [top].[C2] AS [C2], 
    [top].[GroupId] AS [GroupId], 
    [top].[TotalParts] AS [TotalParts], 
    [top].[Part] AS [Part], 
    [top].[ImportedOn] AS [ImportedOn]
    FROM ( SELECT [Project6].[Id] AS [Id], [Project6].[IsLocked] AS [IsLocked], [Project6].[GroupId] AS [GroupId], [Project6].[TotalParts] AS [TotalParts], [Project6].[Part] AS [Part], [Project6].[VehicleID] AS [VehicleID], [Project6].[Source] AS [Source], [Project6].[EventDateTime] AS [EventDateTime], [Project6].[EventEndDateTime] AS [EventEndDateTime], [Project6].[ImportedOn] AS [ImportedOn], [Project6].[Category] AS [Category], [Project6].[OfficerName] AS [OfficerName], [Project6].[OfficerId] AS [OfficerId], [Project6].[DepartmentId] AS [DepartmentId], [Project6].[Reid] AS [Reid], [Project6].[DurationTicks] AS [DurationTicks], [Project6].[CamerasCount] AS [CamerasCount], [Project6].[DestinationStorageDeviceSerialNumber] AS [DestinationStorageDeviceSerialNumber], [Project6].[EventFileSize] AS [EventFileSize], [Project6].[BadgeNumber] AS [BadgeNumber], [Project6].[UserFriendlyName] AS [UserFriendlyName], [Project6].[Path] AS [Path], [Project6].[C1] AS [C1], [Project6].[C2] AS [C2]
        FROM ( SELECT 
            [Project4].[Id] AS [Id], 
            [Project4].[IsLocked] AS [IsLocked], 
            [Project4].[GroupId] AS [GroupId], 
            [Project4].[TotalParts] AS [TotalParts], 
            [Project4].[Part] AS [Part], 
            [Project4].[VehicleID] AS [VehicleID], 
            [Project4].[Source] AS [Source], 
            [Project4].[EventDateTime] AS [EventDateTime], 
            [Project4].[EventEndDateTime] AS [EventEndDateTime], 
            [Project4].[ImportedOn] AS [ImportedOn], 
            [Project4].[Category] AS [Category], 
            [Project4].[OfficerName] AS [OfficerName], 
            [Project4].[OfficerId] AS [OfficerId], 
            [Project4].[DepartmentId] AS [DepartmentId], 
            [Project4].[Reid] AS [Reid], 
            [Project4].[DurationTicks] AS [DurationTicks], 
            [Project4].[CamerasCount] AS [CamerasCount], 
            [Project4].[DestinationStorageDeviceSerialNumber] AS [DestinationStorageDeviceSerialNumber], 
            [Project4].[EventFileSize] AS [EventFileSize], 
            [Project4].[BadgeNumber] AS [BadgeNumber], 
            [Extent4].[UserFriendlyName] AS [UserFriendlyName], 
            [Extent5].[Path] AS [Path], 
            CASE WHEN ([Project4].[C1] IS NULL) THEN 0 ELSE [Project4].[C2] END AS [C1], 
            CASE WHEN ( EXISTS (SELECT 
                1 AS [C1]
                FROM [RecordedEventLink] AS [Extent6]
                WHERE [Project4].[Id] = [Extent6].[LeftRecordedEventId]
            )) THEN cast(1 as bit) ELSE cast(0 as bit) END AS [C2]
            FROM    (SELECT 
                [Project2].[Id] AS [Id], 
                [Project2].[IsLocked] AS [IsLocked], 
                [Project2].[GroupId] AS [GroupId], 
                [Project2].[TotalParts] AS [TotalParts], 
                [Project2].[Part] AS [Part], 
                [Project2].[VehicleID] AS [VehicleID], 
                [Project2].[Source] AS [Source], 
                [Project2].[EventDateTime] AS [EventDateTime], 
                [Project2].[EventEndDateTime] AS [EventEndDateTime], 
                [Project2].[ImportedOn] AS [ImportedOn], 
                [Project2].[Category] AS [Category], 
                [Project2].[OfficerName] AS [OfficerName], 
                [Project2].[OfficerId] AS [OfficerId], 
                [Project2].[DepartmentId] AS [DepartmentId], 
                [Project2].[Reid] AS [Reid], 
                [Project2].[DurationTicks] AS [DurationTicks], 
                [Project2].[CamerasCount] AS [CamerasCount], 
                [Project2].[DestinationStorageDeviceSerialNumber] AS [DestinationStorageDeviceSerialNumber], 
                [Project2].[EventFileSize] AS [EventFileSize], 
                [Project2].[BadgeNumber] AS [BadgeNumber], 
                [Project2].[C1] AS [C1], 
                [SSQTAB1].[StreamNumber] AS [C2]
                FROM ( SELECT 
                    [Extent1].[Id] AS [Id], 
                    [Extent1].[IsLocked] AS [IsLocked], 
                    [Extent1].[GroupId] AS [GroupId], 
                    [Extent1].[TotalParts] AS [TotalParts], 
                    [Extent1].[Part] AS [Part], 
                    [Extent1].[VehicleID] AS [VehicleID], 
                    [Extent1].[Source] AS [Source], 
                    [Extent1].[EventDateTime] AS [EventDateTime], 
                    [Extent1].[EventEndDateTime] AS [EventEndDateTime], 
                    [Extent1].[ImportedOn] AS [ImportedOn], 
                    [Extent1].[Category] AS [Category], 
                    [Extent1].[OfficerName] AS [OfficerName], 
                    [Extent1].[OfficerId] AS [OfficerId], 
                    [Extent1].[DepartmentId] AS [DepartmentId], 
                    [Extent1].[Reid] AS [Reid], 
                    [Extent1].[DurationTicks] AS [DurationTicks], 
                    [Extent1].[CamerasCount] AS [CamerasCount], 
                    [Extent1].[DestinationStorageDeviceSerialNumber] AS [DestinationStorageDeviceSerialNumber], 
                    [Extent1].[EventFileSize] AS [EventFileSize], 
                    [Extent1].[BadgeNumber] AS [BadgeNumber], 
                    [SSQTAB1].[StreamNumber] AS [C1]
                    FROM [RecordedEvents] AS [Extent1]
                     OUTER APPLY
                    (SELECT TOP (1) 
                        [Extent2].[StreamNumber] AS [StreamNumber]
                        FROM [RecordingEventStreams] AS [Extent2]
                        WHERE [Extent1].[Id] = [Extent2].[RecordedEvent_Id]) AS [SSQTAB1]
                )  AS [Project2]
                 OUTER APPLY
                (SELECT TOP (1) 
                    [Extent3].[StreamNumber] AS [StreamNumber]
                    FROM [RecordingEventStreams] AS [Extent3]
                    WHERE [Project2].[Id] = [Extent3].[RecordedEvent_Id]) AS [SSQTAB1] ) AS [Project4]
            LEFT OUTER JOIN [StorageDriveInformations] AS [Extent4] ON [Project4].[DestinationStorageDeviceSerialNumber] = [Extent4].[SerialNumber]
            LEFT OUTER JOIN [StorageDriveInformations] AS [Extent5] ON [Project4].[DestinationStorageDeviceSerialNumber] = [Extent5].[SerialNumber]
        )  AS [Project6]
        ORDER BY [Project6].[EventDateTime] DESC
        OFFSET 0 ROWS 
    )  AS [top]

gistfile3.cs

public class RecordEventConfiguration : EntityTypeConfiguration<RecordedEvent>
{
    public RecordEventConfiguration()
    {
        Ignore(r => r.IsSplit);
        Ignore(r => r.SplitDescription);
        Ignore(r => r.Duration);
        Ignore(r => r.EncryptionLevel);
        Ignore(r => r.ThumbnailPath);
        Ignore(r => r.SourceString);
        Ignore(r => r.IsOfficerUnassignedOfficer);

        Property(r => r.StartTime).HasColumnName("EventDateTime");
        Property(r => r.EndTime).HasColumnName("EventEndDateTime");

        HasRequired(r => r.DestinationStorageDevice);
        HasMany(r => r.EventTagItems).WithRequired();
        HasMany(r => r.EventStreams).WithRequired();
        HasMany(r => r.LinkedEvents).WithMany()
            .Map(x => x.ToTable("RecordedEventLink")
                        .MapLeftKey("LeftRecordedEventId")
                        .MapRightKey("RightRecordedEventId"));
    }
}

FirstStreamNumber = x.EventStreams.Select(s => s.StreamNumber).FirstOrDefault(), is the thing causing me problems.

When the entire library of events is large (17k in my case) the query is extremely slow.

My guess is that a subquery is selecting all records in the db.

RecordedEvents
//.Include(x => x.EventStreams)
//.AsExpandable()
//.Where(where)
//.OrderBy(orderByField, ascending)
//.Skip((pageNumber - 1)*recordsPerPage)
.Take(25)
.Select(x => new
{
Id = x.Id,
Reid = x.Reid,
IsLocked = x.IsLocked,
VehicleId = x.VehicleID,
//StartTime = x.StartTime,
//EndTime = x.EndTime,
Category = x.Category,
OfficerName = x.OfficerName,
OfficerId = x.OfficerId,
DepartmentId = x.DepartmentId,
BadgeNumber = x.BadgeNumber,
DurationTicks = x.DurationTicks,
CamerasCount = x.CamerasCount,
//FirstStreamNumber = x.EventStreams.Select(s => s.StreamNumber).FirstOrDefault(),
DestinationStorageDeviceSerialNumber = x.DestinationStorageDeviceSerialNumber,
DestinationStorageDeviceName = x.DestinationStorageDevice.UserFriendlyName,
DestinationStorageDevicePath = x.DestinationStorageDevice.Path,
EventFileSize = x.EventFileSize,
Source = x.Source,
//HasLinkedEvents = x.LinkedEvents.Any(),
GroupId = x.GroupId,
TotalParts = x.TotalParts,
Part = x.Part,
ImportedOn = x.ImportedOn,
})