how to write linq query of the below sql query

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
            };

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 

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

Please go through my sql query

my query returns two but in linq returning only one raw

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