LINQ to Entities does not recognize the method

Question

This is my Code where I am fetching data.

var list = (from u in _dbContext.Users
                        where u.IsActive
                              && u.IsVisible
                              && u.IsPuller.HasValue
                              && u.IsPuller.Value
                        select new PartsPullerUsers
                               {
                                   AvatarCroppedAbsolutePath = u.AvatarCroppedAbsolutePath,
                                   Bio = u.Bio,
                                   CreateDateTime = u.CreationDate,
                                   Id = u.Id,
                                   ModifieDateTime = u.LastModificationDate,
                                   ReviewCount = u.ReviewsReceived.Count(review => review.IsActive && review.IsVisible),
                                   UserName = u.UserName,
                                   Locations = (from ul in _dbContext.UserLocationRelationships
                                                join l in _dbContext.Locations on ul.LocationId equals l.Id
                                                where ul.IsActive && ul.UserId == u.Id
                                                select new PartsPullerLocation
                                                       {
                                                           LocationId = ul.LocationId,
                                                           Name = ul.Location.Name
                                                       }),
                                   Rating = u.GetPullerRating()
                               });

Now Here is my Extension.

public static int GetPullerRating(this User source)
        {
            var reviewCount = source.ReviewsReceived.Count(r => r.IsActive && r.IsVisible);
            if (reviewCount == 0)
                return 0;

            var totalSum = source.ReviewsReceived.Where(r => r.IsActive && r.IsVisible).Sum(r => r.Rating);
            var averageRating = totalSum / reviewCount;

            return averageRating;
        }

I have check this Post LINQ to Entities does not recognize the method

And I come to know I need to use

public System.Linq.Expressions.Expression<Func<Row52.Data.Entities.User, int>> GetPullerRatingtest

But how ? Thanks

Answer 1

You can use conditionals inside LINQ to Entity queries:

AverageRating = u.ReviewsReceived.Count(r => r.IsActive && r.IsVisible) > 0 ? 
                u.ReviewsReceived.Where(r => r.IsActive && r.IsVisible).Sum(r => r.Rating) / 
                u.ReviewsReceived.Count(r => r.IsActive && r.IsVisible)
                : 0

This will be calculated by the server, and returned as part of your list. Although with 10 million rows like you said, I would do some serious filtering before executing this.

Answer 2

Code within LINQ (to Entities) query is executed within database, so you can't put random C# code there. So you should either use user.GetPullerRating() after it is retrieved or create a property if you don't want to do the calculation every time.

You can also do:

foreach (var u in list)
   u.Rating = u.GetPullerRating()

By the way, why is it extension method.