Linq Left Outer Join - default error

Question

There are a number of device types, some of which support configuration settings. I am trying to get a list of all types of devices and any applicable settings.

This request does not collect devices that do not have DeviceParameters. If I add .DefaultIfEmpty () as shown below, I get this error:

"An invalid value for an Int64 value occurred because the materialized value is zero. Either the general result parameter or query must use a type with a zero value.

What is the correct syntax 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 defined the foreign key relationship, I think the solution is pretty simple if I haven't missed anything:

 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 }) }); 

In any case, I will probably do this part of the settings as follows:

 settings = p.DeviceParameters.ToList() 

Answer 2

I believe that the problem that you see in your query is that by calling DefaultIfEmpty() you are trying to pull values ​​from a null object. If you have a valid DeviceType but there are no DeviceParameters mapped, then when it tries 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 tries to create a new object, and the object for "s" is null, so an attempt to access the parameter ParamName or Param1, etc. will not work. I tried the same code in LINQPad, and when I deleted the DefaultIfEmpty () call, everything worked.

Without knowing the properties and their types, I cannot be sure, but, as I said, based on the implementation of similar code in LINQPad, I got similar results.