how to write linq query of the below sql query

Question

Declare @FacultyName Nvarchar(100)
SELECT C.ContentTypeId,C.Id,V.VersionId,V.VersionName,C.CourseName,C.DeptName,V.ContentId,V.VersionNote
FROM [dbo].[ContentDetails] C inner join [dbo].[VersionDetails] V on C.Id=V.ContentId where V.VersionId in (
select Max(V.VersionId)
FROM [dbo].[ContentDetails] C inner join [dbo].[VersionDetails] V on C.Id=V.ContentId where c.FacultyName like @FacultyName
group by V.ContentId

) order by V.VersionId desc

Naimish Makwana · Answer

Try with below. var query = dbContext.ContentDetails .Join(dbContext.VersionDetails, c => c.Id, v => v.ContentId, (c, v) => new { c, v }) .Where(x => x.v.VersionId == ( from c2 in dbContext.ContentDetails join v2 in dbContext.VersionDetails on c2.Id equals v2.ContentId where c2.FacultyName.Contains(FacultyName) group v2 by v2.ContentId into g select g.Max(v2 => v2.VersionId) ).Max()) .OrderByDescending(x => x.v.VersionId) .Select(x => new { x.c.ContentTypeId, x.c.Id, x.v.VersionId, x.v.VersionName, x.c.CourseName, x.c.DeptName, x.v.ContentId, x.v.VersionNote }).ToList();

Naimish Makwana · Answer

Hello Sandeep, Try below. var query = from c in dbContext.ContentDetails join v in dbContext.VersionDetails on c.Id equals v.ContentId where v.VersionId == ( from c2 in dbContext.ContentDetails join v2 in dbContext.VersionDetails on c2.Id equals v2.ContentId where c2.FacultyName.Contains(FacultyName) group v2 by v2.ContentId into g select g.Max(v2 => v2.VersionId) ).Max() orderby v.VersionId descending select new { c.ContentTypeId, c.Id, v.VersionId, v.VersionName, c.CourseName, c.DeptName, v.ContentId, v.VersionNote }; Thanks Naimish Makwana

Jignesh Kumar · Answer

Hello sandeep, Please use below query, var facultyName = "your_faculty_name"; var contentDetailsQuery = from c in context.ContentDetails join v in context.VersionDetails on c.Id equals v.ContentId where v.VersionId == (from subV in context.VersionDetails join subC in context.ContentDetails on subV.ContentId equals subC.Id where subC.FacultyName.Contains(facultyName) group subV by subV.ContentId into g select g.Max(v => v.VersionId)).FirstOrDefault() orderby v.VersionId descending select new { c.ContentTypeId, c.Id, v.VersionId, v.VersionName, c.CourseName, c.DeptName, v.ContentId, v.VersionNote };

Naimish Makwana · Answer

Use below query as you want. string FacultyName = "Soumoyajyoti"; var query = dbContext.ContentDetails .Join(dbContext.VersionDetails, c => c.Id, v => v.ContentId, (c, v) => new { c, v }) .GroupBy(x => x.v.ContentId) .Select(g => g.OrderByDescending(x => x.v.VersionId).FirstOrDefault()) .Where(x => x.c.FacultyName.StartsWith(FacultyName ?? string.Empty)) .OrderByDescending(x => x.v.VersionId) .Select(x => new { x.c.ContentTypeId, x.c.Id, x.v.VersionId, x.v.VersionName, x.c.CourseName, x.c.DeptName, x.v.ContentId, x.v.VersionNote }); Thanks

Sandeep Kumar · Answer

I want like search like this Declare @FacultyName Nvarchar(100)='Soumoyajyoti' SELECT C.ContentTypeId,C.Id,V.VersionId,V.VersionName,C.CourseName,C.DeptName,V.ContentId,V.VersionNote FROM [dbo].[ContentDetails] C inner join [dbo].[VersionDetails] V on C.Id=V.ContentId where V.VersionId in ( select Max(V.VersionId) FROM [dbo].[ContentDetails] C inner join [dbo].[VersionDetails] V on C.Id=V.ContentId where c.FacultyName LIKE ISNULL(@FacultyName, '') + '%' group by V.ContentId ) order by V.VersionId desc

Sandeep Kumar · Answer

Please go through my sql query my query returns two but in linq returning only one raw