Calculations inside LINQ query

Hello!

How can I perform correctly calculations in LightSpeed using LINQ?

For example

var something = context.Photo.Select(photo => new {

Id = photo.Id,
......
Rating = photo.Ratings.Where(rating => rating.IdPhotoId == photo.Id).Avg(rating => rating.Rate), (just an example without checking to describe what I'm trying to do)
......
Date = photo.Date

 

}

 Thanks in advance

Unfortunately LightSpeed does not currently support this scenario because it requires performing an operation on a table other than the main one being queried.  LightSpeed's projection support is limited to selecting individual columns or properties.  Sorry.

A possible workaround is to do this in two stages:

var intermediate = context.Photo.Select(photo => new {
  Id = photo.Id,
  Ratings = photo.Ratings,
  Date = photo.Date
};  // this performs the database query using LightSpeed
var results = intermediate.Select(photo => new {
  Id = photo.Id,
  Rating = photo.Ratings.Where(rating => rating.PhotoId == photo.Id).Average(rating => rating.Rate),
  Date = photo.Date
});  // this performs the averaging client-side

Not very elegant, though, and rather inefficient because it hauls back the entire Ratings collection when all you want is the average rating; but may be viable if your data sets aren't too large.

[quote user="ivan"]Unfortunately LightSpeed does not currently support this scenario because it requires performing an operation on a table other than the main one being queried.  LightSpeed's projection support is limited to selecting individual columns or properties.  Sorry.[/quote]

Do you plan to add this functionality? If yes than can I know ETA if there is one?

It's something we'd like to do, but there's a fair bit of infrastructural work we'd need to do first, so it's not even planned in at the moment.  We'll keep monitoring customer feedback and prioritise accordingly.