I've got the following query:
ATable
.GroupBy(x=> new {FieldA = x.FieldAID, FieldB = x.FieldBID, FieldC = x.FieldCID})
.Select(x=>new {FieldA = x.Key.FieldA, ..., last_seen = x.OrderByDescending(y=>y.Timestamp).FirstOrDefault().Timestamp})
results in:
SqlException: Invalid column name 'FieldAID' x 5
SqlException: Invalid column name 'FieldBID' x 5
SqlException: Invalid column name 'FieldCID' x 1
I've worked out it has to do with the last query to Timestamp because this works:
ATable
.GroupBy(x=> new {FieldA = x.FieldAID, FieldB = x.FieldBID, FieldC = x.FieldCID})
.Select(x=>new {FieldA = x.Key.FieldA, ..., last_seen = x.OrderByDescending(y=>y.Timestamp).FirstOrDefault()})
The query has been simplified. The purpose is to group by a set of variables and then show the last time this grouping occured in the db.
I'm using Linqpad 4 to generate these results so the Timestamp gives me a string whereas FirstOrDefault gives me the whole object which isn't ideal.
Update
On further testing I've noticed that the number and type of SQLException is related to the class created in the groupby clause.
So,
ATable
.GroupBy(x=> new {FieldA = x.FieldAID})
.Select(x=>new {FieldA = x.Key.FieldA, last_seen = x.OrderByDescending(y=>y.Timestamp).FirstOrDefault()})
results in
SqlException: Invalid column name 'FieldAID' x 5
