lovettjay
asked on
Search Filter using lamba or linq - group by & take top record for each group.
Hello,
I am having some difficulties with getting the correct statement in lambda or linq.
SQL Query
select count(me.schools_id) as numOfStudent, schools_name, members_exams_examdate
from members_exams as me
join schools as s on s.schools_id = me.schools_id
where members_exams_examdate = '8/19/16'
group by s.schools_id, members_exams_examdate, schools_name
This returns
+----------------------+-- ---------- ---------- -----+---- ---------- ---------- ---------- -------+
|numOfStudent | School Name | members exams examdate |
| 1 | School A | 8/19/2016 |
| 4 | School B | 8/19/2016 |
| 7 | School C | 8/19/2016 |
| 21 | School D | 8/19/2016 |
+-----------------------+- ---------- ---------- ------+--- ---------- ---------- ---------- -------+
Controller: Try1
Try2: using FirstorDefault
Return Error: Error 1 'System.Linq.IOrderedEnume rable<Data .members_e xams>' does not contain a definition for 'FirstorDefault' and no extension method 'FirstorDefault' accepting a first argument of type 'System.Linq.IOrderedEnume rable<Data .members_e xams>' could be found (are you missing a using directive or an assembly reference?)
Try3:
Error: The model item passed into the dictionary is of type 'System.Data.Entity.Infras tructure.D bQuery`1[< >f__Anonym ousTypef`1 [System.Nu llable`1[S ystem.Int1 6]]]', but this dictionary requires a model item of type 'System.Collections.Generi c.IEnumera ble`1[Data .members_e xams]'.
In the view I would like to group the records and display only the top record for each school. Just like to table above.
I have tried a number of different things but no success.
Thanks for the help.
I am having some difficulties with getting the correct statement in lambda or linq.
SQL Query
select count(me.schools_id) as numOfStudent, schools_name, members_exams_examdate
from members_exams as me
join schools as s on s.schools_id = me.schools_id
where members_exams_examdate = '8/19/16'
group by s.schools_id, members_exams_examdate, schools_name
This returns
+----------------------+--
|numOfStudent | School Name | members exams examdate |
| 1 | School A | 8/19/2016 |
| 4 | School B | 8/19/2016 |
| 7 | School C | 8/19/2016 |
| 21 | School D | 8/19/2016 |
+-----------------------+-
Controller: Try1
public ActionResult Index(string searchBy, string search)
{
DateTime convertedSearch;
DateTime.TryParse(search, out convertedSearch);
if (searchBy == "members_exams_examdate")
{
return View(from x in db.members_exams
where x.members_exams_examdate == (convertedSearch)
group x by x.schools_id
into groups
select groups.OrderBy(p => p.school.schools_name).First());
}
return View();
}
Return Error: The method 'First' can only be used as a final query operation. Consider using the method 'FirstOrDefault' in this instance instead.Try2: using FirstorDefault
select groups.OrderBy(p => p.school.schools_name).FirstorDefault());
Return Error: Error 1 'System.Linq.IOrderedEnume
Try3:
return View(from x in db.members_exams
where x.members_exams_examdate == (convertedSearch)
group x by x.schools_id into y
select new
{
schools_id = y.Key
});
Error: The model item passed into the dictionary is of type 'System.Data.Entity.Infras
In the view I would like to group the records and display only the top record for each school. Just like to table above.
I have tried a number of different things but no success.
Thanks for the help.
Can you given me the final output you need , I will write that with SQL Query itself.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
by the way...
row_number() over(order by count(*) DESC) as rn
will always result in a single rn = 1 (i.e. one row)
& you might can include other "tie breaker" column(s) in the ordering
if there is an equal count of students over more than one school, then instead you coud use
rank() over(order by count(*) DESC) as rn
and the rn=1 would apply to more then one school (i.e. rank number of 1)
your call
row_number() over(order by count(*) DESC) as rn
will always result in a single rn = 1 (i.e. one row)
& you might can include other "tie breaker" column(s) in the ordering
if there is an equal count of students over more than one school, then instead you coud use
rank() over(order by count(*) DESC) as rn
and the rn=1 would apply to more then one school (i.e. rank number of 1)
your call