Left outer join to include null values linq lambda

Question

I have one table in database named Balance and a list of dates as follows:

List<string> allDates = { "2021-01-02", "2021-01-03", "2021-01-04" }

Balance table:

Id, Amount, BalanceDate
1, 233, "2021-01-02"
2, 442, "2021-01-03

I need to fetch the records in Balance table with amount 0 for the missing dates. For example:

233, "2021-01-02"
442, "2021-01-03"
0, "2021-01-04"

I have tried the following:

balnces.GroupJoin(allDates,
       balance => balance.Date,
       d => d,
       (balance, d) => balance);

But the records are still the same (only the ones in the balance table)

any own attempts that you can post? any problem description of what you encountered? is the Balance table in a DataBase? — Mong Zhu, Jan 13 '22 at 07:53
the DataBase part is quite important because you [cannot simply join a table with a list](https://stackoverflow.com/a/20503315/5174469) — Mong Zhu, Jan 13 '22 at 08:00
@MongZhu I have added my attempt. Balance table is a database table as mentioned above — Hanady, Jan 13 '22 at 08:09
Please always mentions which type of data access you're using, for example Entity Framework + which version. Use tags to indicate this. That said, GroupJoin is equivalent to *left* outer join. The left part here is balances. If you want to see all dates, dates should be the left part. — Gert Arnold, Jan 13 '22 at 10:25

Yong Shun · Answer 1 · 2022-01-13T08:49:15.127

Assumption

Balance query had been materialized and data are returned from the database.

Solution 1: With .DefaultIfEmpty()

using System.Linq;

var result = (from a in allDates
            join b in balances on a equals b.Date.ToString("yyyy-MM-dd") into ab
            from b in ab.DefaultIfEmpty()
            select new { Date = a, Amount = b != null ? b.Amount : 0 }
            ).ToList();

Sample Program for Solution 1

Solution 2: With .ToLookup()

var lookup = balances.ToLookup(x => x.Date.ToString("yyyy-MM-dd"));

var result = (from a in allDates
            select new 
            { 
                Date = a, 
                Amount = lookup[a] != null && lookup[a].Count() > 0 ? lookup[a].First().Amount : 0
            }
            ).ToList();

Sample Program for Solution 2

J.Salas · Answer 2 · 2022-01-13T08:17:23.727

Given a data structure from database:

        private class balance
    {
        public int id { get; set; }
        public double amount { get; set; }
        public string date { get; set; }
    }

You get your data as you want (this is only a mock-up)

       List<string> allDates = new List<string> { "2021-01-02", "2021-01-03", "2021-01-04" };
        List<balance> balances = new List<balance>();
        balances.Add(new balance { id = 1, amount = 233 , date = "2021-01-02" });
        balances.Add(new balance { id = 2, amount = 442, date = "2021-01-03" });

you can get your desired result this way:

        List<balance> result = allDates.Select(d=> 
            new balance { 
                amount = 
                  balances.Any(s=> s.date == d)?
                         balances.FirstOrDefault(s => s.date == d).amount:0,
                date = d
        }).ToList();

If your default contains a 0 in amount instead a null, you can skip the .Any check

Left outer join to include null values linq lambda

2 Answers2