I want fetch this record using Linq in .net Core

Question

I m writing a Api in .net core To fetch Below record as shown in image

How can i write this query in linq in simple way

My sql query

select SS.VersionId,SS.ContentId,SS.CourseName,SS.DeptName from (SELECT C.ContentTypeId,C.Id,V.VersionId,V.VersionName,C.CourseName,C.DeptName,V.ContentId
FROM  [dbo].[ContentDetails] C inner join [dbo].[VersionDetails] V on C.Id=V.ContentId  
WHERE  C.FacultyID=1 
)SS where SS.VersionId in
(
select Max(V.VersionId)
  FROM [dbo].[ContentDetails] C inner join [dbo].[VersionDetails] V on C.Id=V.ContentId  where C.FacultyID=1
group by V.ContentId  

)

Amit Mohanty · Answer

Check this var query = from c in db.ContentDetails join v in db.VersionDetails on c.Id equals v.ContentId where c.FacultyID == 1 select new { v.VersionId v.ContentId c.CourseName c.DeptName }; var result = from cv in query where (from c in db.ContentDetails join v in db.VersionDetails on c.Id equals v.ContentId where c.FacultyID == 1 group v by v.ContentId into g select g.Max(x => x.VersionId)).Contains(cv.VersionId) select new { cv.VersionId cv.ContentId cv.CourseName cv.DeptName };

Sandeep Kumar · Answer

how to apply paging on this linq query

Naimish Makwana · Answer

Try below code with where condition on one place. List contentDetails = dbContext.ContentDetails.Where(c => c.FacultyID == 1).ToList(); List versionDetails = dbContext.VersionDetails.Where(v => contentDetails.Select(c => c.Id).Contains(v.ContentId)).ToList(); var query = from SS in ( from C in contentDetails join V in versionDetails on C.Id equals V.ContentId select new { C.ContentTypeId C.Id V.VersionId V.VersionName C.CourseName C.DeptName V.ContentId } ).ToList() where ( from C in contentDetails join V in versionDetails on C.Id equals V.ContentId group V by V.ContentId into g select g.Max(x => x.VersionId) ).Contains(SS.VersionId) select new { SS.VersionId SS.ContentId SS.CourseName SS.DeptName }; Thanks

Sandeep Kumar · Answer

SS.VersionId ==( please here showing error and i want set where condion in one place C.FacultyID=1

Naimish Makwana · Answer

Hello Sandip Plese try below. var query = from SS in ( from C in dbContext.ContentDetails join V in dbContext.VersionDetails on C.Id equals V.ContentId where C.FacultyID == 1 select new { C.ContentTypeId C.Id V.VersionId V.VersionName C.CourseName C.DeptName V.ContentId } ) where SS.VersionId == ( from C in dbContext.ContentDetails join V in dbContext.VersionDetails on C.Id equals V.ContentId where C.FacultyID == 1 group V by V.ContentId into g select g.Max(x => x.VersionId) ) select new { SS.VersionId SS.ContentId SS.CourseName SS.DeptName }; Thanks Naimish