CODEWITHSUNDEEP
c#linq
AboutDev
AboutDev

Reputation: 1923

LINQ: Get the Parent object properties and a single child property as a flat structure

I have a list of addresses contained in a parent object called Branch. Branch may or may not have these addresses defined and I need to get a flat hierarchy or these branch and address.

var x = from p in CurrentBranchList 
        where p.ScheduledForDeletion == false
        from c in p.Addresses 
        where c.ScheduledForDeletion == false && c.AddressTypeId == 3
        select new
        {
          BranchId = p.BranchId,
          Name = p.Name,
          Address = (c == null) ? "" : c.Address1 + " " + c.Address2,
          City = (c == null) ? "" : c.City,
          State = (c == null) ? 0 : c.StateId
        };

The above is what I tried but if the Address is missing I get no information about the Branch...I'm still trying to figure out how to get this going with Linq. In SQL I would have just left joined the two tables to get that info.

Can anyone help me on this...I'm sure it's a pretty easy thing. Thanks. PS. I know this is very similar to (Linq query to return a flatened list of parent child) but in that one the child always exists.

EDIT - WORKING SOLUTION The following is code that seemed to work for me. I can't go against a database for the source because the objects contained in the CurrentBranchList are edited in memory and persistence is performed in a single operation.

var x = from p in CurrentBranchList
        join c in CurrentBranchList.SelectMany(b => b.Addresses) 
          on p.EntityId equals c.EntityId into ur 
        where p.ScheduledForDeletion == false      
        from u in ur.DefaultIfEmpty() 
        select new
        {
          BranchId = p.BranchId,
          Name = p.Name,
          Address = (u == null) ? "" : u.Address1 + " " + u.Address2,
          City = (u == null) ? "" : u.City,
          State = (u == null) ? 0 : u.StateId
        };

Thank you for your help. Those links really helped me understand what needed to happen.

I also tried Daniel Brückner's solution and that appears to be more elegant and require less typing. :-) Seems to work in the couple scenarios I tried.

Here's what that looks like.

var xx = CurrentBranchList.SelectMany(b => b.Addresses.DefaultIfEmpty().Select(a => new 
        {
          BranchId = b.BranchId,
          Name = b.Name,
          Address = (a == null) ? "" : a.Address1 + " " + a.Address2,
          City = (a == null) ? "" : a.City,
          State = (a == null) ? 0 : a.StateId
        }));

Upvotes: 4

Views: 3241

Answers (4)

Denis Troller
Denis Troller

Reputation: 7501

Take a look at this post which shows how to use the DefaultIfEmtpy construct to carry out a LEFT JOIN.

Not the most discoverable feature of Linq I'm afraid.

Upvotes: 2

AkshayM
AkshayM

Reputation: 317

Sorry, maybe I am very late - but I encountered a similar problem and could not find a concrete answer even 5.5 years after this post. My solution (not optimized - but working) : 

public class YearDayBill
{
    public string Name;
    public int YearDay;
}

public class EatOutDaysOfYear
{
    public string Name;
    public List<int> YearDays;
}

public static class Program
{
    static void Main()
    {
        var eatouts = new List<EatOutDaysOfYear>
        {
            new EatOutDaysOfYear {Name = "amit", YearDays = new List<int>() {59, 37, 31, 17, 29}},
            new EatOutDaysOfYear {Name = "prakash", YearDays = new List<int>() {6, 18, 13}},
            new EatOutDaysOfYear {Name = "sarvo", YearDays = new List<int>() {9, 7, 47, 56, 82, 96}},
            new EatOutDaysOfYear {Name = "akshay", YearDays = new List<int>() {8, 5, 2, 4}}
        };

        // query to get properties of parent ('Name') and single child element 
        var bills = eatouts
            .SelectMany(a => a.YearDays
                .Select(b => new YearDayBill {Name = a.Name, YearDay = b})) 
            .OrderBy(d => d.Name)     // optional 
            .ThenBy(e => e.YearDay)   // optional 
            .ToList();

        bills.ForEach(a => Console.WriteLine(string.Concat(a.Name, " | ", a.YearDay)));
    }
}

Upvotes: 0

Daniel Br&#252;ckner
Daniel Br&#252;ckner

Reputation: 59705

IQueryable<Branch> branches = GetBranches();

var result = braches.
   SelectMany(b => b.Addresses.
      DefaultIfEmpty().
      Select(a => new { Branch = b, Address = a }));

Upvotes: 8

Adam Robinson
Adam Robinson

Reputation: 185703

You need a left outer join rather than an inner join. Here's how.

Upvotes: 2

Related Questions