skip to Main Content

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)

2

Answers


  1. 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

    Login or Signup to reply.
  2. 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

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search