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)

CodePudding user response：

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

CodePudding user response：

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