Update linq query results

Question

The following code has a join on the Users table (wrong) that gives me only the records where AssignedUserId is not null. I need all of the records in Request regardless of the AssignerUserId, and then add the User.Name when the AssignedUserId is not null.

var query = from r in _context.Request
    join st in _context.ServiceType on r.ServiceTypeId equals st.ServiceTypeId
    join u in _context.Users on r.UserId equals u.UserId
    select new RequestDto
    {
        RequestId = r.RequestId,
        UserId = r.UserId,
        FirstName = r.FirstName,
        //...
        ServiceType = st.ServiceName,
        AssignedUserId = r.AssignedUserId,
        AssignedUser = u.Name
    };

return query.ToList();

How do I get the User.Name for only the records where the AssignedUserId is not null?

Question is unclear... what do you mean *Users table (wrong)*? What is `r.AssignedUserId` compared to `r.UserId`? — Gilad Green, Dec 21 '17 at 18:06
Is there a mistake here "on r.UserId equals u.UserId" ? . Maybe It would be "on r.AssignedUserId equals u.UserId" ? — lucky, Dec 21 '17 at 18:09
by (wrong) I meant that this query is only returning the records where AssignedUserId is not null — P-Finny, Dec 21 '17 at 18:25

Gilad Green · Accepted Answer · 2017-12-21T18:31:46.703

1

What you want is a left join - to retrieve the data from the requested but to join the Users table only when possible:

var query = from r in _context.Request
    join st in _context.ServiceType on r.ServiceTypeId equals st.ServiceTypeId
    join u in _context.Users on r.AssignedUserId equals u.UserId into ju
    from u in ju.DefaultIfEmpty()
    select new RequestDto
    {
        // ....
        AssignedUserId = r.AssignedUserId,
        AssignedUser = u?.Name
    };

As for your exception from the comments see an expression tree lambda may not contain a null propagating operator.

Replace u.Name with u == null ? null : u.Name

edited Dec 21 '17 at 18:31

answered Dec 21 '17 at 18:13

Gilad Green

36,708
7
61
95

I'm not sure what the ? in u?.Name does.... When I try this solution I get an error, "An expression tree lambda may not contain a null propagating operator." When I take out the ? I'm still just getting the records that contain an AssignedUserId. – P-Finny Dec 21 '17 at 18:27
Also, in reviewing the results set, my AssignedUserId is actually equal to 0. It is not null. There is no UserId = 0. How do I use this left join and lookup Users only when AssignedUserId > 0? – P-Finny Dec 21 '17 at 18:29
Same query... how can I also put the datediff of AssignedDate and DateTime.Now into AssignedElapsed? ie; AssignedElapsed = DateDiff(r.AssignedDate.Value, DateTime.Now) – P-Finny Dec 21 '17 at 19:12
@P-Finny - it is a followup question and unfortunately I should not be answering it here.. Please search online for similar questions or references and if you can't solve it then you can post a new question – Gilad Green Dec 21 '17 at 19:15

score 0 · Answer 2 · answered Dec 21 '17 at 18:23

You want to perform left join

        var query = from r in _context.Request
            join st in _context.ServiceType on r.ServiceTypeId equals st.ServiceTypeId
            join u in _context.Users on r.AssignedUserId equals u.UserId into ps
            from u in ps.DefaultIfEmpty()
            select new RequestDto
            {
                RequestId = r.RequestId,
                UserId = r.UserId,
                FirstName = r.FirstName,
                ServiceType = st.ServiceName,
                AssignedUserId = r.AssignedUserId,
                AssignedUser = u?.Name
            };

I am assuming that join with Users condition is "r.AssignedUserId". Also, It could be better to use navigation properties instead of manual joins.

score -1 · Answer 3 · edited Dec 21 '17 at 18:19

-1

Try

AssignedUser = r.AssignedUserId == null ? null : u.Name

If you don't want AssignedUser to be null you can set it to String.Empty instead.

edited Dec 21 '17 at 18:19

Gilad Green

36,708
7
61
95

answered Dec 21 '17 at 18:08

Edu001

26
4

You can simplify it to `AssignedUser = r.AssignedUserId ?? u.Name` – FCin Dec 21 '17 at 18:21
@FCin: What you are suggesting is not the same thing. What you wrote reads: If r.AssignedUserId is null use u.Name otherwise use r.AssignedUserId. Not what the user is asking. see: https://stackoverflow.com/questions/446835/what-do-two-question-marks-together-mean-in-c – Edu001 Dec 21 '17 at 19:51

Update linq query results

3 Answers3