Linq Left Outer Join - DefaultIfEmpty Error

Question

There is a collection of devices types, some of which support configuration setting(s). I'm trying to get a list of all device types, and any applicable settings.

This query isn't picking up devices that have no DeviceParameters. If I add the .DefaultIfEmpty() as shown below, I get this error:

"The cast to value type 'Int64' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type."

What's the correct synatx for DefaultIfEmpty?

            var Devices = from d in dc.DeviceTypes
                      join p in dc.DeviceParameters on d.TypeID equals p.TypeID into tmpTable
                      from items in tmpTable.DefaultIfEmpty()
                      group items by d.DeviceName into g
                      select new
                      {
                          DeviceName = g.Key,
                          settings = from s in g
                                     select new
                                     {
                                         ParamName = s.ParamName,
                                         Param1 = s.Param1,
                                         Param2 = s.Param2,
                                         Param3 = s.Param3
                                     }
                      };

Answer 1

If you have defined the foreign key relationship, I think the solution is pretty straightforward, unless I'm missing something:

var Devices = dc.DeviceTypes
    .Select(p=>new 
        {
             DeviceName = p.DeviceName ,
             settings = p.DeviceParameters
                 .Select(q=>new
                 {
                     ParamName = p.ParamName,
                     Param1 = q.Param1,
                     Param2 = q.Param2,
                     Param3 = q.Param3
                 })
        });

Anyways, I would probably do the settings part this way:

settings = p.DeviceParameters.ToList()

Answer 2

I believe the issue that you are seeing with your query is that by using the DefaultIfEmpty() call you are then trying to pull values out of a null object. If you have a valid DeviceType but don't have any DeviceParameters mapped then when it is trying to materialize the settings property with this statement:

settings = from s in g
select new
{
    ParamName = s.ParamName,
    Param1 = s.Param1,
    Param2 = s.Param2,
    Param3 = s.Param3
}

It is trying to create a new object and the object for "s" is null so trying to access the property ParamName or Param1, etc. won't work. I tried the same code within LINQPad and when I removed the DefaultIfEmpty() call then everything worked.

Without knowing the properties and their types I can't be certain but like I said, based on implementing similar code in LINQPad I got similar results.