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

Question

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?

I can't send SQL query - because you are prematurely calling ToList(). — GSerg
– GSerg, Commented Sep 8, 2019 at 12:22
@GSerg I have wanted to explain how can i write this query by using LINQ. Already Updated — socket_
– socket_, Commented Sep 8, 2019 at 12:24
So you don't want LINQ to send this query to SQL, like it's normally supposed to? — GSerg
– GSerg, Commented Sep 8, 2019 at 12:31

Selim Yildiz · Accepted Answer · 2019-09-08 13:06:15Z

1

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();

answered Sep 8, 2019 at 13:06

Selim Yildiz

5,3906 gold badges22 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related