Link to home
Start Free TrialLog in
Avatar of lovettjay
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
	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();
			}

Open in new window

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

Open in new window


Return Error: Error      1      'System.Linq.IOrderedEnumerable<Data.members_exams>' does not contain a definition for 'FirstorDefault' and no extension method 'FirstorDefault' accepting a first argument of type 'System.Linq.IOrderedEnumerable<Data.members_exams>' could be found (are you missing a using directive or an assembly reference?)      


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

Open in new window


Error: The model item passed into the dictionary is of type 'System.Data.Entity.Infrastructure.DbQuery`1[<>f__AnonymousTypef`1[System.Nullable`1[System.Int16]]]', but this dictionary requires a model item of type 'System.Collections.Generic.IEnumerable`1[Data.members_exams]'.

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.
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Can you given me the final output you need , I will write that with SQL Query itself.
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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