Left join with LINQ Error

Question

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 attempt 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!

I'm still getting an error. Maybe this offers more insight: [NullReferenceException: Object reference not set to an instance of an object.] _Ch.<Page_Load>b__b(<>f__AnonymousType12 <>h__TransparentIdentifier0, <>f__AnonymousType05 stat2) in System.Linq.<SelectManyIterator>d__313.MoveNext() +303 System.Collections.Generic.List1..ctor(IEnumerable1 collection) +327 System.Linq.Enumerable.ToList(IEnumerable1 source) +58 — mike
– mike, Commented Sep 20, 2011 at 21:20
The actual code is throwing an exception in a ToList() call. Is there any code after this? Also you could strip out all the AsEnumerable() calls except for on the datatable for readability and your hours setup could simply be var hours=Enumerable.Range(0,24) — Bob Vale
– Bob Vale, Commented Sep 20, 2011 at 22:10
Thanks for your help and the .range shortcut! The simple answer was changing "D1 = stat2.D1 != null ? stat2.D1 : 0,..." to "D1 = stat2 != null ? stat2.D1 : 0,". — mike
– mike, Commented Sep 20, 2011 at 22:31

CassOnMars · Accepted Answer · 2011-09-20 21:01:28Z

0

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,

answered Sep 20, 2011 at 21:01

CassOnMars

6,1812 gold badges34 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

CassOnMars Over a year ago

I did notice that above you have var hourlyData but in your join clause you have join stat in hourlyStats.DefaultIfEmpty(). Are you using another data set elsewhere?

mike Over a year ago

"var stats = ds.Tables[0].AsEnumerable();" is data pulled from sql db. var hourlyData performs aggregate functions on that stats table.

CassOnMars Over a year ago

Right, but look at your var reportData = from hour in hours.AsEnumerable() join stat in hourlyStats.AsEnumerable(). It says hourlyStats, not hourlyData

mike Over a year ago

Sorry, thanks for catching that. I modified the variable names before posting my question. That isn't the source of the issue.

CassOnMars Over a year ago

Ah, okay. I'm not sure what else to see there.

|

Bala R · Accepted Answer · 2011-09-20 20:59:01Z

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.

answered Sep 20, 2011 at 20:59

Bala R

109k23 gold badges203 silver badges212 bronze badges

Comments

Kai CriticallyAcclaimed Cooper · Accepted Answer · 2011-09-20 20:59:28Z

0

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

answered Sep 20, 2011 at 20:59

Kai CriticallyAcclaimed Cooper

9782 gold badges15 silver badges27 bronze badges

Collectives™ on Stack Overflow

Left join with LINQ Error

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related