Please give me a solution to convert below Sql query to Linq.
with cte as
(
select ca.emplid
,i.current_year
,i.course,
ROW_NUMBER() OVER (partition by ca.EmplID order by i.current_Year DESC) as Rn
from candidate ca
left outer join instructor_course_session i on ca.emplid = i.emplid
where ca.EMPLOYEE_STATUS = 'A'
and i.current_year <> 2007
and i.SESSION_ACCEPTED_FLAG = 'Y'
and i.DRAFTED_FLAG = 'Y'
order by ca.emplid
) --as cte
select * from cte where Rn = 1
Hi, if we can ignore the Rn column, then this might help you
int Rn = 1;
var data =
(from ca in candidate
join i in instructor_course_session on ca.emplid equals i.emplid
where ca.EMPLOYEE_STATUS = 'A'
and i.current_year <> 2007
and i.SESSION_ACCEPTED_FLAG = 'Y'
and i.DRAFTED_FLAG = 'Y'
order by ca.emplid
select new
{
emplid = ca.emplid,
current_year = i.current_year,
course = i.course
}
).Skip(Rn-1).Take(1);
DivakarGanta
Member
39 Points
140 Posts
convert Sql to Linq.
May 16, 2012 04:49 AM|LINK
Hi,
Please give me a solution to convert below Sql query to Linq.
Thanks,
rivdiv
All-Star
16323 Points
2595 Posts
Re: convert Sql to Linq.
May 16, 2012 05:31 PM|LINK
Haven't tried them, but you might want to take a look at this StackOverflow thread for multiple options on CTEs in LINQ: http://stackoverflow.com/questions/202912/hierarchical-data-in-linq-options-and-performance, and an article that's suggested there: http://www.scip.be/index.php?Page=ArticlesNET18
TimoYang
Contributor
3732 Points
1275 Posts
Re: convert Sql to Linq.
May 18, 2012 12:02 AM|LINK
I'm afraid that your SQL statement cannot be converted to a standard LINQ successfully——for Row_Number isn't a standard function nested in LINQ.
In my mind，To deal with this I suggest you using the triditional ADO.NET or SP+LINQ by importing them together.
sriharsha241...
Member
414 Points
109 Posts
Re: convert Sql to Linq.
May 18, 2012 05:31 AM|LINK
Hi, if we can ignore the Rn column, then this might help you
int Rn = 1; var data = (from ca in candidate join i in instructor_course_session on ca.emplid equals i.emplid where ca.EMPLOYEE_STATUS = 'A' and i.current_year <> 2007 and i.SESSION_ACCEPTED_FLAG = 'Y' and i.DRAFTED_FLAG = 'Y' order by ca.emplid select new { emplid = ca.emplid, current_year = i.current_year, course = i.course } ).Skip(Rn-1).Take(1);