.Net Linq DataSet Problems (left outer joins)

Question

I have 3 sql tables SourceKeys, Channels, and ChannelTypes. These tables are queried and their data is stored in datatables in my dataset.

What I need is this:

SELECT ...
FROM ChannelTypes ct 
LEFT OUTER JOIN Channels ch ON ct.channelTypeID = channelTypeID
LEFT OUTER JOIN SourceKeys sk ON ch.channelID = sk.channelID

but in linq form... I'm a bit new to linq and read a bunch of msdn articles but just need some help to get over the hump.

var sourceKeyQuery = 
    from ct in ds.Tables["ChannelTypes"].AsEnumerable()
     join ch in ds.Tables["Channels"].AsEnumerable()
     on ct.Field<int>("channelTypeID") equals ch.Field<int>("channelTypeID") into gj1
     from channels in gj1.DefaultIfEmpty()
     join sk in ds.Tables["SourceKeys"].AsEnumerable()
     on channels.Field<int>("channelID") equals sk.Field<int>("channelID") into gj2
     from sourceKeys in gj2.DefaultIfEmpty()
     orderby ct.Field<string>("channelType"), 
             channels.Field<string>("channel"),
             sourceKeys.Field<string>("sourceKeys")
     select (sourceKeys == null)?null:sourceKeys.Field<int?>("sourceKeyID");

The error I'm getting says channels.Field<int>("channelID") cannot be null... i tried using <int?> but it didnt work.

Answer 1

FWIW, I find that code more difficult to understand than a simple call to DataTable.Select, which also allows filtering as well as sorting, by parent or child tables if needed.

var keys = ds.Tables["SourceKeys"].Select(...)
foreach (var key in keys)
{
    int id = key["sourceKeyID"];
    ...
}

Of course that assumes you have DataRelations defined between the tables.

The only thing I'm not 100% sure you could do is sort by channel in the Select method, since that table appears to be a sibling of source keys, rather than a parent/child relationship.

EDIT: the other approach you may want to consider: if you do have (nested) data relations, then perhaps DataSet.GetXml() and then query that using LinqToXml?

Answer 2

I had a few things mixed up... looks like this will work.

var sourceKeyQuery = from ct in ds.Tables["ChannelTypes"].AsEnumerable()
                     join ch in ds.Tables["Channels"].AsEnumerable()
                     on ct.Field<int>("channelTypeID") equals ch.Field<int>("channelTypeID") into g_ch
                     join sk in ds.Tables["SourceKeys"].AsEnumerable()
                     on ct.Field<int>("channelTypeID") equals sk.Field<int>("channelID") into g_ct
                     from ch in g_ch.DefaultIfEmpty()
                     from sk in g_ct.DefaultIfEmpty()
                     select new 
                     {
                         channelTypeID = ct.Field<int>("channelTypeID"),
                         channelType = ct.Field<string>("channelType"),
                         channelID = (ch == null)?null:ch.Field<int?>("channelID"),
                         channel = (ch == null)?String.Empty:ch.Field<string>("channel"),
                         sourceKeyID = (sk == null)?null:sk.Field<int?>("sourceKeyID"),
                         sourceKey = (sk == null)?String.Empty:sk.Field<string>("sourceKey")
                     };