CODEWITHSUNDEEP
asp.net-mvclinqasp.net-mvc-4asp.net-mvc-3linq-to-entities
Xtremcool
Xtremcool

Reputation: 185

How to get Average rating in entity framework

Hi I have below code as 

var query = (from R in db.Registrations
            join c in db.Campus
            on R.CampusId equals c.CampusId
            from tsr in db.TutorStudentRequests.Where(t => t.TutorId == R.RegistrationId).DefaultIfEmpty()
            where R.UserTypeId == 2 && tsr.StatusId!=3
            orderby R.Name ascending
            select new
            {
                RegistrationId = R.RegistrationId,
                Name = R.Name,
                Email = R.Email,
                Phone = R.Phone,
                Password = c.CampusName,
                IsGPA = R.IsGPA,
                IsActive = R.IsActive,
                StripeId = R.StripeId,
                CreatedOn = R.CreatedOn,
                UserTypeId =tsr.StudentReviewRating
            })
        .ToList();

I have Registration table having single row and I also have another table TutorStudentRequests which have multiple rows. How can I get average of column name StudentReviewRating present in TutorStudentRequests table?

Structure may be seem as:

Registration Table

RegistrationId   Name   Email 
   1             abc    [email protected]
   2             xyz    [email protected]

TutorStudentRequests Table

Id  TutorId(RegistrationId of F.k.)   StudentReviewRating
 1    1                                    5
 2    1                                    2
 3    1                                    1
 4    2                                    3

I want UserTypeId data as average of StudentReviewRating for each TutorId

Tried

var query = (from R in db.Registrations
        join c in db.Campus
        on R.CampusId equals c.CampusId
        from tsr in db.TutorStudentRequests.Where(t => t.TutorId == R.RegistrationId).DefaultIfEmpty()
        where R.UserTypeId == 2 && tsr.StatusId != 3
        group R by new
        {
            R.RegistrationId,
            R.Name,
            R.Email,
            R.Phone,
            c.CampusName,
            R.IsGPA,
            R.IsActive,
            R.StripeId,
            R.CreatedOn,                      
        } into groupings
        select new
        {
            RegistrationId = groupings.Key.RegistrationId,
            Name = groupings.Key.Name,
            Email = groupings.Key.Email,
            Phone = groupings.Key.Phone,
            Password = groupings.Key.CampusName,
            IsGPA = groupings.Key.IsGPA,
            IsActive = groupings.Key.IsActive,
            StripeId = groupings.Key.StripeId,
            CreatedOn = groupings.Key.CreatedOn,
            Average = groupings.Average(p=>Convert.ToDecimal(p.StudentReviewRating))
        });

But it is saying Registration does not contain a definition for 'StudentReviewRating'..

What is wrong?

Upvotes: 1

Views: 1571

Answers (2)

Sultan
Sultan

Reputation: 700

Hi you can Use this to have all you want about math here https://numerics.mathdotnet.com/

Upvotes: 0

Dan Dohotaru
Dan Dohotaru

Reputation: 3079

how about grouping by tutor, calculate the average as total ratings/number of ratings

var query = from request in data
            group request by request.TutorId into groupings
            let total = groupings.Sum(p=>p.StudentReviewRating)
            let number = groupings.Count()
            let average = (decimal)total/number
            select new 
            {
                TutorId = groupings.Key,
                Summary = new 
                {
                    Total = total,
                    Number = number,
                    Average = average
                }
            }

and results would look like this for the given test case

TutorId Summary
1   Total   8
    Number  3
    Average 2.66
2   Total   3
    Number  1
    Average 3

Edit extra joins and extended group by

var query = from registration in registrations
            join request in requests 
                on registration.RegistrationId equals request.TutorId
            group request by new 
            {
                registration.RegistrationId,
                registration.Name, 
                request.TutorId,
            } into groupings
            select new 
            {
                RegistrationId = groupings.Key.RegistrationId,
                TutorId = groupings.Key.TutorId,
                Average = groupings.Average(p=>p.StudentReviewRating)
            }

Upvotes: 1

Related Questions