I want fetch this record using Linq in .net Core

Sandeep Kumar

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  

)

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Mohanty3y ago

Accepted 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 Kumar3y ago
how to apply paging on this linq query

Naimish Makwana3y ago

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 Kumar3y ago
SS.VersionId ==( please here showing error

and i want set where condion in one place C.FacultyID=1

Naimish Makwana3y ago

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