Converting an SQL with Left Join , Group and Sum to a Linq Query

Question

I am trying to replicate the below SQL in C# & Linq , but I am not too experienced with Linq beyond the basics.

SELECT SUM(coalesce(cd.minutesspent,0))
FROM timelabels t
LEFT JOIN challengedetails cd on cd.createddate = t.date 
GROUP BY t.date

This would give me a sum of minutes for every date, or zero if there were null entries. Pretty straightforward I think.

// timelabels structure 
DateTime start = DateTime.Now.AddDays(-14);
DateTime end = DateTime.Now;
List<DateTime> timelabels =  new List<DateTime>();
for (var dt = start; dt <= end; dt = dt.AddDays(1))
{
    timelabels.Add(dt);
}

// ChallengeDetails structure & sample data
class ChallengeDetails(){
    DateTime? Createddate
    int? MinutesSpent 
}
List<ChallengeDetails> ChallengeDetails = new List<ChallengeDetails >();
  List<ChallengeDetails> ChallengeDetails = new List<ChallengeDetails>();
            ChallengeDetails.Add(new ChallengeDetails { MinutesSpent = 44, Createddate = DateTime.Now.AddDays(-10) });
            ChallengeDetails.Add(new ChallengeDetails { MinutesSpent = 31, Createddate = DateTime.Now.AddDays(-7) });
            ChallengeDetails.Add(new ChallengeDetails { MinutesSpent = 13, Createddate = DateTime.Now.AddDays(-3) });
            ChallengeDetails.Add(new ChallengeDetails { MinutesSpent = 77, Createddate = DateTime.Now.AddDays(-2) });


// The Actual Code 
List<string> timedata = (from a in timelabels
                         join al in ChallengeDetails on a equals al.Createddate into All
                         from al in All.DefaultIfEmpty()
                         group al by a into grouped
                         select grouped.Sum(m => m.MinutesSpent ?? 0).ToString()
                        ).ToList();

Looking at this it should work - I have DefaultIfEmpty which should replicate the left join. Within the Sum I have '?? 0' which should be the fallback for any dates that dont have a ChallengeDetail.

But I get System.NullReferenceException: 'Object reference not set to an instance of an object.' m was null. I shouldnt have gotten this as I have null entries covered or have I got this all wrong ?

First, do this with the data layer you're using (which one?). LINQ to object is different than LINQ to a SQL provider. Second thing that probably applies: use navigation properties, not `join`. Doing this the ORM will 1. Cancel null references because it's SQL and 2. Figure out left joins all by itself. — Gert Arnold, Apr 25 '21 at 13:44
Try following : select grouped.Sum(m => (m == null) ? 0 :m.MinutesSpent).ToString() — jdweng, Apr 25 '21 at 17:28
Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. Which LINQ are you using: LINQ to SQL / EF 6.x / EF Core 2.0 / 2.1 / 3.x / 5.x / 6.x? Which database provider? — NetMage, Apr 26 '21 at 20:12
Sometimes `al` will be `null` in LINQ to Objects (from `DefaultIfEmpty()`) in which case `m` will be `null` and `m.MinutesSpent` will be a `NullReferenceException`. You need `m?.MinutesSpent ?? 0` for LINQ to Objects. This is not the same as LINQ to a database. — NetMage, Apr 26 '21 at 20:15
@NetMage You were right about m being null - ` m?.MinutesSpent ?? 0 ` takes care of it . Your SQL to LINQ Recipe is exactly what I am looking for as well ! — Jamie McManus, Apr 26 '21 at 21:31

score 1 · Answer 1 · answered Apr 25 '21 at 19:36

1

Under the section "Actual Code" just add a parenthesis in Sum function, as below It should take care of null , even before Sum works.

select grouped.Sum(m => (m.MinutesSpent ?? 0)).ToString()

answered Apr 25 '21 at 19:36

Giopet · Answer 2 · 2021-04-27T14:22:31.110

This will do for you, using LINQ - Lambda Expressions:

var TotalMinutesSpentGroupedList = timelabels
         .GroupJoin(challengedetails,
                    t => date,
                    cd => createddate,
                    (t,cd) => new 
                    { 
                       MinutesSpent = cd.minutesspent ?? 0,
                       CreateDate = cd.createddate ?? DateTime.Now
                    })
         .SelectMany(tcd=> challengedetails.DefaultIfEmpty(),
                     (t, cd) => new 
                     { 
                        MinutesSpent = cd.minutesspent ?? 0,
                        CreateDate = cd.createddate ?? DateTime.Now
                     })
         .Select(s => new 
                     { 
                        MinutesSpent = cd.minutesspent ?? 0,
                        CreateDate = cd.createddate ?? DateTime.Now
                     })
         .GroupBy(g => g.createddate).Select(item => item.Sum(s => s.minutesspent));

Converting an SQL with Left Join , Group and Sum to a Linq Query

2 Answers2