Some Agents has null in Group field. I'm trying to do LeftJoin, but receive result like InnerJoin (only Agents with not null Group)

Agents = new ObservableCollection<dynamic>((await _repository.GetAgentsAsync() ?? new Agent[] { })
            .Join(Groups.DefaultIfEmpty(), a => a.Group, g => g.ID, (a, g) =>
            new { ID = a.ID, AgentName = a.AgentName, Login = a.Login, AgentID = a.AgentID, IsDel = a.IsDel, Group = g == null ? "Empty" : $"{g.NameGroup} ({g.Num})" }));

what the trouble is?

Thanks all, I found answer https://stackoverflow.com/a/21584913/13618303

        Groups = new ObservableCollection<Group>(await _repository.GetGroupsAsync() ?? new Group[] { });
        Agents = new ObservableCollection<Agent>(await _repository.GetAgentsAsync() ?? new Agent[] { });
        AgentsGroups = new ObservableCollection<dynamic>(Agents.GroupJoin(Groups, a => a.Group, g => g.ID, (a, g) => new { Agent = a, Group = g})
            .SelectMany(ag => ag.Group.DefaultIfEmpty(), (a,g) => new { Agent = a.Agent, Group = g })
            .Select ( ag => new { ID = ag.Agent.ID, AgentName = ag.Agent.AgentName, Login = ag.Agent.Login, AgentID = ag.Agent.AgentID, IsDel = ag.Agent.IsDel, Group = ag.Group == null ? "Empty" : $"{ag.Group.NameGroup} ({ag.Group.Num})" }));

CodePudding user response：

Method syntax doesn't use Join to perform a left join style link, it uses GroupJoin. If you straight Join then the lack of an element on the right (Group) will mean that the element on the left (Agent) disappears.

A GroupJoin pairs elements on the left with multiple matching elements on the right

Left
1, John
2, Mary
3, Joe

Right
1, The Street, 1996
1, The Avenue, 2002
2, The Road, 2010

John lived in two places, mary lived in one. Joe never lived anywhere. Left.GroupJoin(Right) produces:

{1, John}, [ {1, The Street, 1996}, {1, The Avenue, 2002} ]
{2, Mary}, [ {1, The Road, 2010} ]
{3, Joe},  [ ]

GroupJoin will keep the element on the left and make the element on the right an empty sequence if there is no match

If you needed this back out as a list with repeating elements, you'd SelectMany it, which expands the list-of-lists that the person:addresses represents. I'll come back to this.

DefaultIfEmpty does not enable left join behavior; it is just a convenience that returns a sequence containing one item of the target type (and with the default value for the type), if the sequence it is called on has zero elements. In essence it's like:

public int[] DefaultIfEmpty(int[] input){
  if(input.Length == 0)
    return new int[1]; //a single integer, value 0 --> the default for int
  else
    return input;
}

You don't need it on Groups; it'd be a non op anyway, because if there are 0 Groups, converting it to a list of a single null Group will behave the same (no agent will match)

You do want DefaultIfEmpty if you plan on using SelectMany on your grouping, because SelectMany will not operate at all on a collection of 0 elements. Because Joe had no addresses, SelectMany would skip over him and he would be lost from the output

All in, this means you should execute a GroupJoin, and you don't really need a DefaultIfEmpty anywhere because you aren't doing a SelectMany/you can tolerate there being 0 Groups for an Agent in other ways.

I'll use more full names, because it's unfortunate that you're joining to a class called Group (I guess), and a groupjoin gives you a list of Agents, and each Agent in the list has a list of matching Group:

.GroupJoin(Groups, agent => agent.Group, group => group.ID, (agent, groupCollection) =>
    new {  
        agent.ID, 
        agent.AgentName,
        agent.Login,
        agent.AgentID, 
        agent.IsDel, 
        GroupNameAndNum = groupCollection.Select(g => $"{g.NameGroup} ({g.Num})").FirstOrDefault() ?? "Empty" 
    }
);

If you were using SelectMany (perhaps you have agents that have 2 groups and want them listed separately, with the agent data repeated), it would perhaps look like:

.GroupJoin(Groups, agent => agent.Group, group => group.ID, (agent, groupCollection) => new { Agent = agent, OneOrMoreGroups = groupCollection.DefaultIfEmpty() })
.SelectMany(
    agentWithGroupsCollection => agentWithGroupsCollection.OneOrMoreGroups,
    (agentWithGroupsCollection, groupsCollectionItem) =>

    new {  
        agentWithGroupsCollection.ID, 
        agentWithGroupsCollection.AgentName,
        agentWithGroupsCollection.Login,
        agentWithGroupsCollection.AgentID, 
        agentWithGroupsCollection.IsDel, 
        GroupNameAndNum = groupsCollectionItem == null ? "Empty" : $"{groupsCollectionItem.NameGroup} ({groupsCollectionItem.Num})"
    }
);