SelectMany lost parent, if child is null

Question

Using selectmany to flatten list, How to get parent object, when child object is a null ?

I need to show list :

"Payer: Payer A, Status: Active"
"Payer: Payer A, Status: Top"
"Payer: Payer A, Status: Fast"
"Payer: Payer B, Status: "--"

// Parent class
public class Payer
{
    public string Name { get; set; }
    public List<Status> Status { get; set; }
}

// Child class
public class Status
{
    public string Name { get; set; }
}

static void Main(params string[] args)
{
    // Lets build some example-data
    List<Payer> payers = new List<Payer>()
    {
        new Payer()
        {
            Name = "Payer A",
            Status = new List<Status>()
            {
                new Status() { Name = "Active" },
                new Status() { Name = "Top" },
                new Status() { Name = "Fast" }
            }
        },
        new Payer()
        {
            Name = "Payer B",
            Status = new List<Status>()
            {
                // Payer B got no Status
            }
        }
    };

    var payerStatuses = payers.SelectMany
    (
        payer => payer.Status, // Select the Children
        (payer, stat) => new { Name = payer.Name, Status = stat.Name } // Tell Linq what to take from parent (payer) and what to take from child (status)
    );

    // let's see what we got
    foreach (var payerStatus in payerStatuses)
    {
        Console.WriteLine("Payer: {0}, Status: {1}", payerStatus.Name, payerStatus.Status);
    }
    // Result:
    // "Payer: Payer A, Status: Active"
    // "Payer: Payer A, Status: Top"
    // "Payer: Payer A, Status: Fast"
    //  But I need payer B too!

   }

If you want to improve your question you're free to take the example-code out of my answer (if i understood your question correctly o_O). — kara, Feb 23 '18 at 07:05

kara · Answer 1 · 2018-02-23T07:45:38.087

I'm doing a guess what you want to do. You want to have a Payer-Status-Object. You want to see three objects for payer B non for A, correct?

You should not mix the linq-styles. Here some example how to use SelectMany:

// Parent class
public class Payer
{
    public string Name { get; set; }
    public List<Status> Status { get; set; }
}

// Child class
public class Status
{
    public string Name { get; set; }
}

static void Main(params string[] args)
{
    // Lets build some example-data
    List<Payer> payers = new List<Payer>()
    {
        new Payer()
        {
            Name = "Payer A",
            Status = new List<Status>()
            {
                new Status() { Name = "Active" },
                new Status() { Name = "Top" },
                new Status() { Name = "Fast" }
            }
        },
        new Payer()
        {
            Name = "Payer B",
            Status = new List<Status>()
            {
                // Payer B got no Status
            }
        }
    };

    var payerStatuses = payers.SelectMany
    (
        payer => payer.Status.DefaultIfEmpty(), // Select the Children. If no status, we want an empty list
        (payer, stat) => new { Name = payer.Name, Status = stat == null ? null : stat.Name } // Tell Linq what to take from parent (payer) and what to take from child (status). check if status is not null, because we receive an empty status-list for payers without status
    );

    // let's see what we got
    foreach (var payerStatus in payerStatuses)
    {
        Console.WriteLine("Payer: {0}, Status: {1}", payerStatus.Name, payerStatus.Status);
    }
    // Expected:
    // "Payer: Payer A, Status: Active"
    // "Payer: Payer A, Status: Top"
    // "Payer: Payer A, Status: Fast"
    // "Payer: Payer B, Status: "
}

Modified the example. You should modify your question. It's hard to read what you want. Add example class and the output you want to have to your question. — kara, Feb 23 '18 at 07:45
@kara This answer above worked a treat for a parent with one child, but how would you modify the code if class Status has a child (that can also be null)? — Angela, Jan 29 '20 at 14:17
Similar :) Post a Question. Editing this one will make it to be not readable. — kara, Jan 30 '20 at 15:12

score 0 · Answer 2 · answered Feb 23 '18 at 08:45

So you have Payers, and every Payer has zero or more PayerStatuses.

I need to use selectmany, so I can get rows as per statuses

No idea what this means. You start mentioning the solution and then you start saying what you want, but the solution does not work? Maybe next time first specify what you want, then what you tried, then tell us why that does not work.

Besides that you didn't inform us about what you want, you also forgot to write your Payer and your PayerStatus classes .

Do you have a class "Payer with this statuses", like you would have when you'd use entity framework? Or do you only have two separate tables?

And if you only have two separate tables, how do you indicate to which Payer a PayerStatus belongs? Does every PayerStatus belong to exactly one Payer (one-to-many relation), or are there PayerStatusses that may belong to several Payers (many-to-many) or maybe there are PayerStatusses that belong to no Payer at all?

Let's assume that your relation is the most common: you have a one-to-many relation between Payers and PayerStatuses: every Payer has zero or more Payerstatuses; every PayerStatus belongs to exactly one Payer.

In tables this is usually solved using primary and foreign keys:

class Payer
{
    public int Id {get; set;}  // primary key

    ... // other Payer properties
}

class PayerStatus
{
    public int Id {get; set;}  // primary key

    // every PayerStatus belongs to exactly one Payer using foreign key:
    public int PayerId {get; set;}

    ... // other properties
}

If you've got tables like this, then to get "Every Payer with his PayerStatuses" you'd do a GroupJoin.

// your two tables: Payers and Statuses:
IEnumerable<Payer> payers = ...
IEnumerable<PayerStatus> statuses = ...

// GroupJoin these two tables:
var prayersWithTheirStatusses = payers.GroupJoin(statusses,
    payer => payer.Id,              // from each payer take the Id
    status => status.PayerId,       // from each status take the PayerId

    (payer, statusses) =>  new      // when they match, make a new object:
    {
        // take only the Payer properties you plan to use, for instance:
        PayerId = payer.Id,
        Name = payer.Name,
        Reputation = payer.Reputation,
        ...

        // from all payer's statuses, take only the properties you plan to use
        Statusses = statusses.Select(status => new
        {
            Description = status.Description,
            StatusValue = status.Value,
            ...
        })
        .ToList(),
    }
}

This way you get every Payer with all his statuses, even if the Payer has no status at all.

So if you have the following Tables:

Payers
Id = 10, Name = A
Id = 11, Name = B
Id = 12, Name = C

PayerStatusses
Id = 21, PayerId = 10, ...
Id = 22, PayerId = 11, ...
Id = 23, PayerId = 10, ...

The linq statement above will give you something like

Payer 10, name A with Status list containing data from statuses 21 and 23
Payer 11. name B with Status list with one element containing data from status 22
Payer 12, name C with empty status list

Quite often, people don't want the Payer with his statuses, but one row per a Payer and one of his statuses. So they like the following result

10 - A - status 21
10 - A - status 23
11 - B - status 22
12 - C - (null)

I never found a proper use case where you'd prefer this above the GroupJoin. I think this is still in demand because people think in the limitations of SQL statements that doesn't have a join to get "Payers with their statuses", and so they take the next best thing, a Left Outer Join, as is answered several times on SO:

Continuing after the GroupJoin:

.SelectMany(payer => payer.Statusses.DefaultIfEmpty(),
    (payer, status) => new
    {
        // payer properties:
        PayerId = payer.PayerId,
        PayerName = payer.Name,
        Reputation = payer.Reputation,

        // status properties:
        StatusDescription = status.Description,
        StatusValue = status.Value,
    });

SelectMany lost parent, if child is null

2 Answers2