Convert SQL into Linq query

Question

I'm quite new at Linq queries, I just want to convert my DB query into Linq.

Here is my simple SQL query:

var query = "SELECT EnrollmentDate, COUNT(*) AS StudentCount "
          + "FROM Person "
          + "WHERE EnrollmentDate IS NOT NULL "
          + "GROUP BY EnrollmentDate";

var data = db.Database.SqlQuery<EnrollmentDateGroup>(query);

It is working fine , but how could it possible to write this query in Linq, I just can't convert the group by statement into Linq. It seems somewhat tricky to convert into Linq.

Can anyone help me with this?

Marc Gravell · Accepted Answer · 2013-05-16 07:13:20Z

15

var query = from row in db.Person
            where row.EnrollmentDate != null
            group row by row.EnrollmentDate into grp
            select new {
                EnrollmentDate = grp.Key,
                Count = grp.Count()
            };

answered May 16, 2013 at 7:13

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

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

1 Comment

AKD Over a year ago

thats cool!!! @Marc Gravell, thnx for ur answer, but i was looking for Linq in lamda expression as Habib's answer. but anyways thanx 4 a quick reply

Mahmoud Gamal · Accepted Answer · 2013-05-16 07:13:11Z

11

var result = db.Person
               .Where(r=> r.EnrollmentDate != null)
               .GroupBy(r=> r.EnrollmentDate)
               .Select( group=> new 
                              {
                                 EnrollmentDate = group.Key, 
                                 Count = group.Count()
                               });

edited May 16, 2013 at 7:13

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

answered May 16, 2013 at 7:12

Habib

224k30 gold badges420 silver badges446 bronze badges

4 Comments

AKD Over a year ago

thanx for ur answer, but i am getting an error(xyzModel.SchoolContext does not contain any defenition for Person) the ques has been updated... plz see it again. thanx

Habib Over a year ago

@ashok_damani, what is the class representing your table Person in your model ? Replace that in your query

AKD Over a year ago

ur answer edited, now its working fine. but one more thing, i m writing here Select( group=> new EnrollmentDateGroup(), but as previous i tried it to convert into the List<EnrollmentDateGroup> (by adding ToList<EnrollmentDateGroup>() at tail), but didnt worked, Y ?

Habib Over a year ago

@ashok_damani, I can't see your edit, but for your answer of converting it to List<EnrollmentDateGroup> is not allowed since you are selecting an anonymous object using new keyword

Collectives™ on Stack Overflow

Convert SQL into Linq query

2 Answers 2

1 Comment

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related