0

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..

  • Does this answer your question? [Linq: adding conditions to the where clause conditionally](https://stackoverflow.com/questions/10884651/linq-adding-conditions-to-the-where-clause-conditionally) – tailoxyn Sep 16 '22 at 01:15
  • It is impossible to help you without knowing the relationship in your DbContext. – Alexander Petrov Sep 17 '22 at 15:37

0 Answers0