Linq with entity Framework using a function to select columns

Question

I'm trying to reduce the amount of code in my application.

I want to turn this

 return _db.SnapshotQueues
               .Include(c => c.SnapshotDefinition)
               .Include(c => c.SnapshotDefinition.Client)
               .Include(c => c.Server)
               .Select(s => new SnapshotQueueModel()
               {
                   SnapshotQueueID = s.SnapshotQueueID,
                   SnapshotDefinitionID = s.SnapshotDefinitionID,
                   ScheduleID = s.ScheduleID,
                   DateCreated = s.DateCreated,
                   Protected = s.Protected,
                   StartTime = s.StartTime,
                   FinishTime = s.FinishTime,
                   Processed = s.Processed,
                   CoreSnapshotDatabaseName = s.CoreSnapshotDatabaseName,
                   Removed = s.Removed,
                   SnapshotID = s.SnapshotID,
                   EmailNotification = s.EmailNotification,
                   Email = s.Email,
                   ServerID = s.ServerID,
                   DateRequested = s.DateRequested,
                   Canceled = s.Canceled,
                   Active = s.Active,
                   LastSnapshotQueueActionID = s.SnapshotQueueActions.OrderByDescending(o => o.ActionDate).ThenByDescending(o => o.SnapshotQueueActionID).FirstOrDefault().ActionID,
                   LastAction = s.SnapshotQueueActions.OrderByDescending(o => o.ActionDate).ThenByDescending(o => o.SnapshotQueueActionID).FirstOrDefault().SnapshotAction.Description,
                   SnapshotLogCount = s.SnapshotGenerationLogs.Count(),

                   SnapshotDefinition = s.SnapshotDefinition.Name,
                   Server = s.Server.ServerName,
                   ScheduleName = s.Schedule.Name,
                   ClientName = s.SnapshotDefinition.Client.ClientName_Long
               }
               )
               .Where(s => s.SnapshotDefinitionID == snapshotDefinitionID)
               .OrderBy(sortFieldExpression);

into this

 return _db.SnapshotQueues
               .Include(c => c.SnapshotDefinition)
               .Include(c => c.SnapshotDefinition.Client)
               .Include(c => c.Server)
               .Select(s => _snapshotQueueModelGet(s))
               .Where(s => s.SnapshotDefinitionID == snapshotDefinitionID)
               .OrderBy(sortFieldExpression);

        private readonly Func<SnapshotQueue, SnapshotQueueModel> _snapshotQueueModelGet = s => new SnapshotQueueModel
        {
            SnapshotQueueID = s.SnapshotQueueID,
            SnapshotDefinitionID = s.SnapshotDefinitionID,
            ScheduleID = s.ScheduleID,
            DateCreated = s.DateCreated,
            Protected = s.Protected,
            StartTime = s.StartTime,
            FinishTime = s.FinishTime,
            Processed = s.Processed,
            CoreSnapshotDatabaseName = s.CoreSnapshotDatabaseName,
            Removed = s.Removed,
            SnapshotID = s.SnapshotID,
            EmailNotification = s.EmailNotification,
            Email = s.Email,
            ServerID = s.ServerID,
            DateRequested = s.DateRequested,
            Canceled = s.Canceled,
            Active = s.Active,
            LastSnapshotQueueActionID =
                s.SnapshotQueueActions.OrderByDescending(o => o.ActionDate)
                    .ThenByDescending(o => o.SnapshotQueueActionID)
                    .FirstOrDefault()
                    .ActionID,
            LastAction =
                s.SnapshotQueueActions.OrderByDescending(o => o.ActionDate)
                    .ThenByDescending(o => o.SnapshotQueueActionID)
                    .FirstOrDefault()
                    .SnapshotAction.Description,
            SnapshotLogCount = s.SnapshotGenerationLogs.Count(),
            SnapshotDefinition = s.SnapshotDefinition.Name,
            Server = s.Server.ServerName,
            ScheduleName = s.Schedule.Name,
            ClientName = s.SnapshotDefinition.Client.ClientName_Long
        };

The problem is that it passes in to SQL Server, and the database doesn't know what to do with the function. I get the error

The LINQ expression node type 'Invoke' is not supported in LINQ to Entities.

I'm using the same select list in a few places, so it would stop bugs creeping in when field are added.

Answer 1

Declare your field as Expression<Func<SnapshotQueue, SnapshotQueueModel>>:

private readonly Expression<Func<SnapshotQueue, SnapshotQueueModel>> _snapshotQueueModelGet = s => new SnapshotQueueModel
{
    SnapshotQueueID = s.SnapshotQueueID,
    SnapshotDefinitionID = s.SnapshotDefinitionID,
    ScheduleID = s.ScheduleID,
    DateCreated = s.DateCreated,
    Protected = s.Protected,
    StartTime = s.StartTime,
    FinishTime = s.FinishTime,
    Processed = s.Processed,
    CoreSnapshotDatabaseName = s.CoreSnapshotDatabaseName,
    Removed = s.Removed,
    SnapshotID = s.SnapshotID,
    EmailNotification = s.EmailNotification,
    Email = s.Email,
    ServerID = s.ServerID,
    DateRequested = s.DateRequested,
    Canceled = s.Canceled,
    Active = s.Active,
    LastSnapshotQueueActionID =
        s.SnapshotQueueActions.OrderByDescending(o => o.ActionDate)
            .ThenByDescending(o => o.SnapshotQueueActionID)
            .FirstOrDefault()
            .ActionID,
    LastAction =
        s.SnapshotQueueActions.OrderByDescending(o => o.ActionDate)
            .ThenByDescending(o => o.SnapshotQueueActionID)
            .FirstOrDefault()
            .SnapshotAction.Description,
    SnapshotLogCount = s.SnapshotGenerationLogs.Count(),
    SnapshotDefinition = s.SnapshotDefinition.Name,
    Server = s.Server.ServerName,
    ScheduleName = s.Schedule.Name,
    ClientName = s.SnapshotDefinition.Client.ClientName_Long
};