Left join with LINQ Error

I'm new to linq so would appreciate your help with getting this left join query to work.

I have "List hours" (a list of hours 0-23) that I'd like to left join with "var hourlyData" (a processed linq query containing aggregated data). My a开发者_如何转开发ttempt at the left join is "var reportData".

DataSet ds = GetDataSet(sSql);
var stats = ds.Tables[0].AsEnumerable();

var hourlyData = from stat in stats
                 group stat by stat.Field<int>("Hour") into g
                 select new
                 {
                     Hour = g.Key,
                     Value = g.Key.ToString(),
                     D1 = g.Sum(stat => stat.Field<int>("D1")),
                     D2 = g.Sum(stat => stat.Field<int>("D2")),
                     D3 = g.Sum(stat => stat.Field<decimal>("D3"))
                 };


List<int> hours = new List<int>();
for (int i = 0; i < 24; i++)
{
    hours.Add(i);
}


var reportData = from hour in hours.AsEnumerable()
                 join stat in hourlyData.AsEnumerable()
                     on hour equals stat.Hour 
                 into sts2
                 from stat2 in sts2.DefaultIfEmpty()
                 select new
                 {
                     Hour = hour,
                     Value = hour,
                     D1 = stat2.D1 != null ? stat2.D1 : 0,
                     D2 = stat2.D2 != null ? stat2.D2 : 0,
                     D3 = stat2.D3 != null ? stat2.D3 : 0
                 };

---

The code above produces this error:

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

Source Error:

Line 135:                            into sts2
Line 136:                         from stat2 in sts2.DefaultIfEmpty()
Line 137:                         select new
Line 138:                         {
Line 139:                             Hour = hour,
 ...

Thanks!

---

It's giving you an NRE because sts2.DefaultIfEmpty() is returning null. In those cases, you will need to make your D1, D2, and D3 assignments in your select statement have sane defaults if null:

D1 = stat2 != null ? stat2.D1 : 0,
D2 = stat2 != null ? stat2.D2 : 0,
D3 = stat2 != null ? stat2.D3 : 0,

---

Since you are doing a left join and stat2 can be null, you'll have to deal with the case where it can be null.

Try

select new
{
    Hour = hour,
    Value = hour,
    D1 = stat2 != null ? stat2.D1 : 0,
    D2 = stat2 != null ? stat2.D2 : 0,
    D3 = stat2 != null ? stat2.D3 : 0
};

NOTE: 0 might not be appropriate in your case; use what's appropriate.

---

Take a peek at this: http://msdn.microsoft.com/en-us/vcsharp/aa336746