I have a problem while migrating SQL query to Entity Framework

Question

I have this SQL query but I can not transfer it to linq query Entity Framework.

SELECT 
    RB.CustomerId AS ID, C.FullName AS 'FULL NAME', 
    C.PhoneNumber AS 'PHONE NUMBER', 
    COUNT(RB.CustomerId) AS BOOKS 
FROM  
    RentedBooks RB
JOIN 
    Customers C ON RB.CustomerId = C.Id
GROUP BY  
    C.FullName, C.PhoneNumber, RB.CustomerId;

See this https://stackoverflow.com/questions/1339732/convert-sql-to-linq-to-sql — Bosco, Jun 30 '19 at 08:28

Saman Gholami · Answer 1 · 2019-07-01T04:32:41.937

1

The following code is equivalent of your query in linq:

var query = db.RentedBooks.GroupBy(x => new { x.Customer.FullName, x.CustomerId ,x.Customer.PhoneNumber })
.Select(x => new { ID = x.Key.CustomerId,
        x.Key.FullName,
        ,x.Key.PhoneNumber,Books = x.Count() });

edited Jul 01 '19 at 04:32

answered Jun 30 '19 at 10:32

Saman Gholami

3,416
7
30
71

1

What does that do? How does it answer the question? Don't just blurt out code. Explain yourself! https://stackoverflow.com/help/how-to-answer – Rob Jun 30 '19 at 12:08
@RobThis is so obvious! The equivalent query in EF. – Saman Gholami Jun 30 '19 at 12:12
I would just expand `Books = x.Count()` for the book count. Prettifying the field names (i.e. "Full Name") can be done when displaying the data. – Steve Py Jun 30 '19 at 22:20
@StevePy Yes you are right, I've edited the answer. – Saman Gholami Jul 01 '19 at 04:33

I have a problem while migrating SQL query to Entity Framework

1 Answers1