How to conditionally add predicate conditions in a LINQ query expression

Question

The following linq logic has 2 possible expressions that are based on the parameter values, I am looking for a way to combine the two expressions into a single expression, mainly so that I can declare and manage the join criteria once, rather than in 2 places.

1st scenario: Joins the Users and comments table based on project id and If parentId is null
2nd scenario: Joins the Users and comments table based on project id and parent id is not null

public Comment GetComments(int projectId, int parentId)
{
    List<Comment> comments= new List<Comment>();
    if(parentId==null)
    {
        comments = (from c in context.Comments
                    join users in context.Users
                    on c.CreatedBy equals users.Id
                    where c.ProjectId==projectId && c.ParentId==null
                    select new CommentModel
                    {
                        Id = c.Id,
                        Message = c.Message,
                        Date = c.Date,
                        UserName = users.UserName,
                        ProjectId=projectId,
                    }).ToList();
    }
    else
    {
        comments = (from c in context.Comments
                    join users in context.Users
                    on c.CreatedBy equals users.Id
                    where c.ProjectId==projectId && ParentId==c.parentId
                    select new CommentModel
                    {
                        Id = c.Id,
                        Message = c.Message,
                        Date = c.Date,
                        UserName = users.UserName,
                        ProjectId=projectId,
                    }).ToList();
    }
    
    return comments;
}

where is `parentId` and how should it be used, I doubt that this is the reported dup as `users` and `comments` are not both likely to have a `parentId` that means the same thing. — Chris Schaller, Jan 28 '22 at 08:06
parentId comes from the front end. If my comment is single I will use above mentioned join LINQ query. If my comments are replied comments I need to check parent comment id (ParentId). That Cause how to use same LINQ c# — Micro Kumar, Jan 28 '22 at 08:28
you can simply add **`where c.ProjectId==projectId && c.ParentId==parentId `** — Kiran Joshi, Jan 28 '22 at 08:33
I use this condition where c.ProjectId==projectId && c.ParentId==parentId . That time I am not getting single comment data. Because Only child comments have parent id. This condition is only fetched the parent id information. I need to write the code for Single comment fetching without parent id and replying to comment with parent id. If(parentId!=null) I fetch the replying comment data. Otherwise, I fetch the normal comment data. — Micro Kumar, Jan 28 '22 at 10:09
Are you describing a hierarchical structure, via a self-join. If so how deep do we need to go, or do we only return the first child to the parent, and no further descendants? — Chris Schaller, Jan 28 '22 at 10:16
Further to this, should the parentId be optional? so `int? parentId = null` ... you can simplify this problem solving process by including a table of example data and two different input scenarios and the results you are expecting. — Chris Schaller, Jan 28 '22 at 10:18
@ChrisSchaller I edited the code. In the code I called the join query two times, 1st time I check if(parentId==null) 2nd time (parentId!=null). I use the same logic in both places. parentId==null and parentId!=null only difference. Can, I make it a single LINQ. It is possible. — Micro Kumar, Jan 28 '22 at 10:34
in that case there is only a single pathway, both null and have a value are satisfied by `c.ParentId == parentId` — Chris Schaller, Jan 28 '22 at 10:47
Note: this is NOT a dupe, we are not talking about a join with multiple columns conditions! — Chris Schaller, Jan 28 '22 at 10:48
@ChrisSchaller. I will try this condition c.ParentId == parentId — Micro Kumar, Jan 28 '22 at 11:31
Not sure this will get re-opened, this is a summary of my response: _For this specific query there is no need for the two separate queries at all! The second pathway actually satisfies **BOTH** scenarios._ - If `parentId` _is_ `null`, then we still want to match on `c.ParentId == parentId` — Chris Schaller, Jan 28 '22 at 11:34
Wait! that will only work if the `parentId` parameter is _nullable_, that might be the reason for your concerns. — Chris Schaller, Jan 28 '22 at 11:37
This question is being [discussed on meta](https://meta.stackoverflow.com/q/415661/) — Hovercraft Full Of Eels, Jan 28 '22 at 11:53

Chris Schaller · Accepted Answer · 2022-01-28T12:25:39.020

In cases where there is conditional logic, similar to this we should compose the query over multiple steps. It is not necessary to try and construct a single behemoth query, nor do we need to use query syntax at all...

The first observation is that we can apply filters before the join statement, especially in this case given that the filter only applies to the principal table and is unaffected by the join.

For this specific query there is no need for the two separate queries at all if the parentId was made nullable. Then the second pathway actually satisfies BOTH scenarios:

If parentId is null, then we still want to match on ParentId==c.parentId

public List<Comment> GetComments(int projectId, int? parentId)
{
    return (from c in context.Comments
            join users in context.Users on c.CreatedBy equals users.Id
            where c.ProjectId==projectId && c.ParentId==parentId
            select new CommentModel
            {
                Id = c.Id,
                Message = c.Message,
                Date = c.Date,
                UserName = users.UserName,
                ProjectId=projectId,
            }).ToList();
}

If you can't make the parentId nullable, then presumably we use 0 or less to indicate the first pathway, not null. In that case for scenarios where you have multiple conditional sets of criteria I find it easier to work first with fluent to prepare the initial query and then you can finish with query syntax if you feel the need to:

public List<Comment> GetComments(int projectId, int parentId)
{
    var commentsQuery = context.Comments.Where(c => c.ProjectId == projectId);
    if (parentId > 0)
        commentsQuery = commentsQuery.Where(c => c.ParentId == parentId);

    return (from c in commentsQuery
            join users in context.Users on c.CreatedBy equals users.Id
            select new CommentModel
            {
                Id = c.Id,
                Message = c.Message,
                Date = c.Date,
                UserName = users.UserName,
                ProjectId=projectId,
            }).ToList();
}

ParentId is a nullable type. I will try to implement 1st method. — Micro Kumar, Jan 28 '22 at 11:46

How to conditionally add predicate conditions in a LINQ query expression

1 Answers1