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 stackoverflow.com/questions/1339732/convert-sql-to-linq-to-sql — Bosco
– Bosco, Commented Jun 30, 2019 at 8:28

Saman Gholami · Accepted Answer · 2019-07-01 04:32:41Z

2

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 1, 2019 at 4:32

answered Jun 30, 2019 at 10:32

Saman Gholami

3,5127 gold badges34 silver badges72 bronze badges

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

4 Comments

Rob Over a year ago

What does that do? How does it answer the question? Don't just blurt out code. Explain yourself! stackoverflow.com/help/how-to-answer

Saman Gholami Over a year ago

@RobThis is so obvious! The equivalent query in EF.

Steve Py Over a year ago

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.

Saman Gholami Over a year ago

@StevePy Yes you are right, I've edited the answer.

Collectives™ on Stack Overflow

I have a problem while migrating SQL query to Entity Framework

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related