Convert SQL query to Entity Framework which used AggregateFunctions and Where clause

How can I convert this query to Entity Framework?

SQL query:

SELECT Fullname, SUM(CoinCount+DiamondCount) AS GeneralPoint
FROM Students, Groups
WHERE Students.GroupId = Groups.Id AND Groups.Name = 'FSDA_1813_az'
GROUP BY Fullname
ORDER BY GeneralPoint

Entity:

public class Student
{
    public int Id { get; set; }
    public int GroupId { get; set; }
    public string Fullname { get; set; }
    public Nullable<int> DiamondCount { get; set; }
    public Nullable<int> CoinCount { get; set; }
    public string Phone { get; set; }
    public string Address { get; set; }
    public string Education { get; set; }
    public string Email { get; set; }
    public System.DateTime Birthdate { get; set; }
    public string Username { get; set; }
    public string Password { get; set; }
    public Nullable<System.DateTime> LastVisited { get; set; }
    public string Facebook { get; set; }
    public string Linkedin { get; set; }
    public string SocialMedia { get; set; }
    public byte[] Photo { get; set; }

    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<Comment> Comments { get; set; }
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<Exam> Exams { get; set; }
    public virtual Group Group { get; set; }
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<Point> Points { get; set; }
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<StudentHomework> StudentHomeworks { get; set; }
}

UserRepository:

public ICollection<Student> GetUsersForGroup(string group)
{
    using (var db = new EFContext())
    {
        var temp = db.Students.Where(x => x.Group.Name == group).ToList();

        // I have to sort students in group by their DiamondCount+CoinCount as new GeneralCount
        temp = temp.OrderBy(x => );
    }
}

I have to sort students for their general point (DiamondCount+CoinCount). But I can’t send LINQ query by using Entity Framework. How can I do this?

Answer

Not tested but it should work:

 var result = db.Students.Where(x => x.Group.Name == group)
            .GroupBy(g => g.Fullname)
            .Select(i => new
            {
                FullName = i.Key,
                GeneralPoint = i.Sum(s => s.DiamondCount + s.CoinCount)
            })
            .OrderBy(o => o.GeneralPoint)
            .ToList();

Advertisement

Answer