Skip to content

Instantly share code, notes, and snippets.

@kmcginnes
Last active August 29, 2015 14:14
Show Gist options
  • Save kmcginnes/9f9c473b5170dd32f1d0 to your computer and use it in GitHub Desktop.
Save kmcginnes/9f9c473b5170dd32f1d0 to your computer and use it in GitHub Desktop.
RecordedEvent List
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,
});
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]
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"));
}
}
@latish
Copy link

latish commented Jan 30, 2015

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,
})

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment