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
Assumption
Balance query had been materialized and data are returned from the database.
Solution 1: With
.DefaultIfEmpty()
Sample Program for Solution 1
Solution 2: With
.ToLookup()
Sample Program for Solution 2
Given a data structure from database:
You get your data as you want (this is only a mock-up)
you can get your desired result this way:
If your default contains a 0 in amount instead a null, you can skip the .Any check