I've been struggling with this so long I really need to know whether you guys can help me. I tried Google, I tried your forum, the documentation, Linq in Action, LINQ Pocket reference and so on but really don't have an answer.

Data model:

---------    1  * -------------------    *   1  ------------
| Video | ------->| VideoRating   |<-----------| User   |
---------            -------------------                   -------------

VideoRating has a field called Rating which is a number showing the rating the user gave the specific video.

Video has a field called OverallRating which is the average of all the ratings (from VideoRating table) for this specific Video.

I want to retrieve a specific Video and that is all fine, no problems. However, for the fetched VideoEntity I also want it's field OverallRating to be fetched as the average of all the Ratings from table VideoRating.

I am looking for something similar to (the code below is not working, see it as pseudo code):

var videos = from video in linqMetaData.Video
                    let video.OverallRating = (from rating in linqMetaData.VideoRating
                                                                where rating.VideoId == video.VideoId
                                                                select rating.Rating).Average()
                    select (video);

I use Linq but would be happy with a non-Linq solution as well.

Worth noting, I don't want the result as a new anonymous type with two properties (1: the VideoEntity object, 2: the OverallRating). I want the VideoEntity object to have its own field OverallRating set to the average.

Very grateful if you could at least point me in some direction. The solution is probably much simpler than I think.

I think you need something like: var videos = from video in ... select new VideoEntity() { OverallRating = (from rating in linqMetaData.VideoRating where rating.VideoId == video.VideoId select rating.Rating).Average()), // rest of VideoEntity properties };

The nested query will be promoted to a scalar query in the query, so you'll get 1 query overall, which is then projected onto the VideoEntity type.

Yes that is a solution that has struck my mind. However, it means I would need to explicitly set all the properties of VideoEntity. If I go and change the database table and then regenerate all the classes with LLBLGen I would need to go in and change all my Linq-queries to reflect the changes in properties.

Isn't there a better way to just tell LINQ to "give me the VideoEntity object according to the LLBLGen generated entity class" AND ALSO "just fill in the property OverallRating for me"?

For example, how would you do it with LLBLGen code without using Linq?

In LLBLGen Pro code, we can do it by fetching a DynamicList into a DataReader, then project the results into an entityCollection.

Or by using a custom property and Extending the EntityFactory, please check the example posted here:Extending the CustomerEntityFactory

Alright I'll look into that. It just bothers me that I can't find a way to do it in a simplistic way with Linq, a one-liner or so. But that's not your fault.

Anyway, many thanks for your attention. Your time to service is top class as usual.