I've got the following audit log table:

CREATE TABLE [dbo].[Audit](
    [AuditID] [bigint] IDENTITY(1,1) NOT NULL,
    [MasterTableName] [varchar](100) NOT NULL CONSTRAINT [DF_Audit_MasterTableName]  DEFAULT (''),
    [MasterRecordID] [uniqueidentifier] NOT NULL,
    [TableName] [varchar](100) NOT NULL CONSTRAINT [DF_Audit_TableName]  DEFAULT (''),
    [RecordID] [uniqueidentifier] NOT NULL,
    [UserID] [uniqueidentifier] NULL,
    [OrganizationContactID] [uniqueidentifier] NULL,
    [EmployeeContactID] [uniqueidentifier] NULL,
    [Workstation] [nvarchar](100) NOT NULL CONSTRAINT [DF_Audit_Workstation]  DEFAULT (''),
    [AuditDate] [datetime] NOT NULL,
    [ColumnName] [varchar](100) NOT NULL CONSTRAINT [DF_Audit_ColumnName]  DEFAULT (''),
    [Description] [nvarchar](200) NOT NULL,
    [AuditType] [char](1) NOT NULL,
    [OldValue] [sql_variant] NULL,
    [NewValue] [sql_variant] NULL,
 CONSTRAINT [PK_Audit] PRIMARY KEY NONCLUSTERED 
(
    [AuditID] ASC
)

I'd like to write a Linq query that returns the most recent audit record for each order (MasterRecordID), per organization. It should run similar to this:

   SELECT    MAX(AuditID) AS MaxAuditID, MasterRecordID, OrganizationContactID
   FROM      dbo.Audit
   GROUP BY MasterRecordID, OrganizationContactID')

The following Linq query should do the trick:

   var groupedResult =
          from a in context.PPPMetaData.Audit
          group a.AuditId by new { OrderId = a.MasterRecordId, ThirdPartyContactId = a.OrganizationContactID }
            into mostRecentChanges
            select new
            {
              MostRecentChangeId = mostRecentChanges.Max(),
              OrderId = mostRecentChanges.Key.OrderId,
              ThirdPartyContactId = mostRecentChanges.Key.ThirdPartyContactId
            };

but I run into an Null reference exception in the method QueryExpressionBuilder.HandleAggregateExpressionReferencingGroupBy.

   var resultArray = groupedResult.ToArray();

I tried replacing the Max() aggregate by First(), but results in a InvalidCastException, like so:

{"Unable to cast object of type 'System.Linq.Expressions.ParameterExpression' to type 'SD.LLBLGen.Pro.LinqSupportClasses.ExpressionClasses.SetExpression'."} 

It seems as if the construct I am using to do the grouping is not supported.

Any tips?

Thanks

Ries

PS: I validated the query using a local expression, that works fine:

        var data = new[] 
                   { new { AuditId = 1, MasterRecordId = 2, OrganizationContactID = 3},
                     new { AuditId = 2, MasterRecordId = 2, OrganizationContactID = 3},
                     new { AuditId = 3, MasterRecordId = 3, OrganizationContactID = 3}
                   };

        var localQuery =
          from a in data
          group a.AuditId by new { OrderId = a.MasterRecordId, ThirdPartyContactId = a.OrganizationContactID }
            into mostRecentChanges
            select new
            {
              MostRecentChangeId = mostRecentChanges.Max(),
              OrderId = mostRecentChanges.Key.OrderId,
              ThirdPartyContactId = mostRecentChanges.Key.ThirdPartyContactId
            };

        var localGroup = localQuery.ToArray()

This should work (specify the field you wan to get MAX):

var groupedResult =
     from a in context.PPPMetaData.Audit
         group a by new { OrderId = a.MasterRecordId, ThirdPartyContactId = a.OrganizationContactID }
          into mostRecentChanges
     select new
     {
          MostRecentChangeId = mostRecentChanges.Max(a => a.AuditId),
          OrderId = mostRecentChanges.Key.OrderId,
          ThirdPartyContactId = mostRecentChanges.Key.ThirdPartyContactId
     };