Im newbie on EF core (Im using EF 6) can anyone give me some clue on my need, as follow bellow ;
I have query & its working good ;
public async Task<IActionResult> GetCommentsWithReplies([FromRoute] Guid POST_ID, [FromRoute] string STATUS, [FromQuery] PaginationFilter paginationFilter)
{
var validFilter = new PaginationFilter(paginationFilter.PageNumber, paginationFilter.PageSize);
var pagedData = dbContext.Comments
.Join(dbContext.Users,
c => c.User_Id,
u => u.User_Id,
(c, u) => new { c, u })
.Where(x => x.c.Post_Id == POST_ID && x.c.Parent_Id == null && x.c.Status == STATUS)
.OrderByDescending(x => x.c.Created_At)
.Select(x => new Comment_Main
{
Comment_Id = x.c.Comment_Id,
Parent_Id = x.c.Parent_Id,
Post_Id = x.c.Post_Id,
Content = x.c.Content,
User_Id = x.c.User_Id,
Status = x.c.Status,
First_Name = x.u.First_Name,
Last_Name = x.u.Last_Name,
Posted_At = x.c.Posted_At,
Created_At = x.c.Created_At,
User_Name = x.u.User_Name,
Divisi = x.u.Divisi,
Departemen = x.u.Departemen,
Comment_Replies = (dbContext.Comments
.Join(dbContext.Users,
c => c.User_Id,
u => u.User_Id,
(c, u) => new { c, u })
.Where(y => y.c.Parent_Id == x.c.Comment_Id && y.c.Status == STATUS)
.OrderBy(y => y.c.Created_At)
.Select(y => new Comment_Replies
{
Comment_Id = y.c.Comment_Id,
Parent_Id = y.c.Parent_Id,
Post_Id = y.c.Post_Id,
Content = y.c.Content,
User_Id = y.u.User_Id,
Status = y.c.Status,
First_Name = y.u.First_Name,
Last_Name = y.u.Last_Name,
Posted_At = y.c.Posted_At,
Created_At = y.c.Created_At,
User_Name = y.u.User_Name,
Divisi = y.u.Divisi,
Departemen = y.u.Departemen
})).AsEnumerable()
}).AsEnumerable()
.Skip((validFilter.PageNumber - 1) * validFilter.PageSize)
.Take(validFilter.PageSize);
var totalRecords = dbContext.Comments
.Join(dbContext.Users,
c => c.User_Id,
u => u.User_Id,
(c, u) => new { c, u })
.Where(x => x.c.Post_Id == POST_ID && x.c.Parent_Id == null && x.c.Status == STATUS)
.Select(x => x.c.Comment_Id)
.Count();
var totalPages = Convert.ToInt32(Math.Ceiling((double)totalRecords / (double)paginationFilter.PageSize));
return Ok(new PagedResponse<List<Comment_Main>>(pagedData.ToList(), validFilter.PageNumber, validFilter.PageSize, totalPages, totalRecords));
}
The models like here ;
public class Comment_Main
{
public Guid Comment_Id { get; set; }
public Guid? Parent_Id { get; set; }
public Guid Post_Id { get; set; }
public string Content { get; set; }
public Guid User_Id { get; set; }
public string Status { get; set; }
public string? First_Name { get; set; }
public string? Last_Name { get; set; }
public DateTime? Posted_At { get; set; }
public DateTime? Created_At { get; set; }
public string? User_Name { get; set; }
public string? Divisi { get; set; }
public string? Departemen { get; set; }
public IEnumerable<Comment_Replies>? Comment_Replies { get; set; }
}
public class Comment_Replies
{
public Guid Comment_Id { get; set; }
public Guid? Parent_Id { get; set; }
public Guid Post_Id { get; set; }
public string Content { get; set; }
public Guid User_Id { get; set; }
public string Status { get; set; }
public string? First_Name { get; set; }
public string? Last_Name { get; set; }
public DateTime? Posted_At { get; set; }
public DateTime? Created_At { get; set; }
public string? User_Name { get; set; }
public string? Divisi { get; set; }
public string? Departemen { get; set; }
}
As we can see that di query has nested query, how can I make some conditioning the 'where' clause on the main query AND the nested query?
Please for any suggestion, thank you. Regards..