EntityFramework (.NET Core) left outer join by multiple conditions

Question

I need to match User and UserAction by several criteria: UserId, ActionType, Approved, and still want to keep the query as left outer join, because the action could be missing. In regular .net entity framework I would do the following:

var q = from u in db.User
        join ua in db.UserActions on { u.Id, "Approved" } equals { ua.UserId, ua.ActionType } into actions
        from ua in action.DefaultIfEmpty()
        where u.Active
        select new { User = u, Actions = ua}

For the Core version of Entity Framework, unfortunately, it doesn't work. How can I achieve the similar goal using EF for .NET Core?

Answer 1

Try this:

var q = from u in db.User
    join ua in db.UserActions on new { UserId = u.Id, ActionType = "Approved" } equals new { ua.UserId, ua.ActionType } into actions
    from ua in actions.DefaultIfEmpty()
    where u.Active
    select new { User = u, Actions = ua}

Property names of the anonymous types on either side of the join syntax need to match.