Tyring to convert SQL to LINQ to Entities
I have a somewhat simple query in SQL that I would like to convert to Linq to Entities.
Basically, I want all of the available courses in the database, but I want the last test result for each course for a specific user if they have taken the course. They may have taken a course multiple times, but I would only want the last test result for each test. Is this possible with one Linq statement?
Here is the SQL:
select c.courseid, c.Name, ca.result
from Course c
left join CourseAttempt ca
on c.CourseId = ca.CourseId
and ca.CourseAttemptId in
(
select max(courseattemptid)
from courseattempt
where userid=1234
group by courseid
)
Here is what I have so far:
var stuff = (from c in context.Course
join ca in context.CourseAttempt.Where(a => a.userid == _userid)
on c.CourseId equals ca.Course.CourseId into jca
select new
{
courseId = c.CourseId,
name = c.Name,
开发者_如何学Go result = jca.Select(a => a.result)
}).ToList();
The problem is that result is an array of all of the results. How do I only get the last result?
This solution works:
var stuff = (from c in context.Course
join ca in context.CourseAttempt.Where(a => a.user.userid == _userid)
on c.CourseId equals ca.Course.CourseId into jca
select new
{
courseId = c.CourseId,
name = c.Name,
result = jca.OrderByDescending(a => a.CourseAttemptId).Take(1).Select(a => a.result).Cast<int>().DefaultIfEmpty(-1).FirstOrDefault()
}).ToList();
精彩评论