Linq C# Joining null values on navigational properties

Question

I have these classes:

class Group 
{
    public int GroupID { get; set; }
    public string PlaceOfMeeting { get; set; }
    public int? PublisherID { get; set; }
    public IEnumerable<Publisher> Publishers { get; set; }
    public Publisher Publisher { get; set; }
}

class Publisher 
{
    public int PublisherID { get; set; }
    public int GroupID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string MiddleName { get; set; }
    public int Gender { get; set; }
    public string Nationality{ get; set; }
}

and i want to select using Linq to Dataset like this:

QUERY

SELECT TOP 1000 [Groups].[GroupID],
    [Groups].[PlaceOfMeeting],
    [Groups].[PublisherID],
    [publisher].[MiddleName],
    [publisher].[Gender],
    [publisher].[Nationality]
FROM [dbo].[Groups]
left outer join Publisher
on Publisher.PublisherID = Groups.PublisherID

OUTPUT

GroupID PlaceOfMeeting PublisherID MiddleName Gender Nationality

1 Angola 1 Kojo 1 Ghanaian

2 Estate 2 Joe 1 Ghanaian

3 Banchem 3 Kofi 1 Ghanaian

4 Kwabedu NULL NULL NULL NULL

I Want to do This using Linq to Dataset or Object

I tried something like this but am getting ArgumentNullException @ line 18 and the message was Value cannot be null Because some of the record values passed to Publisher are null

  List<ValueObjects.Group> GetAllGroups() {

        DataTable Groups = HelperClass.GetTable("Groups", connectionString);
        DataTable Publishers = HelperClass.GetTable("Publisher", connectionString);
        DataTable Contacts = HelperClass.GetTable("Contacts", connectionString);
        var groups = Groups.AsEnumerable();
        var publishers = Publishers.AsEnumerable();


        var group = from g in groups
                     join p in Publishers.AsEnumerable() on g.Field<int?>("PublisherID") equals p.Field<int?>("PublisherID") into g_p
                     from pub in g_p.DefaultIfEmpty()
                     select new Group()
                     {
                         GroupID = g.Field<int>("GroupID"),
                         PlaceOfMeeting = g.Field<string>("PlaceOfMeeting"),
                         Publisher = g.Field<int?>("PublisherID"),
                         Publisher = new Publisher()
                         {
                             PublisherID = pub.Field<int>("PublisherID"),
                             GroupID = pub.Field<int>("GroupID"),
                             FirstName = pub.Field<string>("FirstName"),
                             LastName = pub.Field<string>("LastName"),
                             MiddleName = pub.Field<string>("MiddleName"),
                             Gender = (Gender)pub.Field<int>("Gender"),
                             
                             Nationality = pub.Field<string>("Nationality"),
                         },
                         Publishers = from p in publishers.DefaultIfEmpty()
                                      where p.Field<int>("GroupID") == g.Field<int>("GroupID")
                                      join c in Contacts.AsEnumerable().DefaultIfEmpty() on p.Field<int>("PublisherID") equals c.Field<int>("PublisherID")
                                      select new Publisher
                                      {
                                          PublisherID = p.Field<int>("PublisherID"),
                                          GroupID = p.Field<int>("GroupID"),
                                          FirstName = p.Field<string>("FirstName"),
                                          LastName = p.Field<string>("LastName"),
                                          MiddleName = p.Field<string>("MiddleName"),
                                          Gender = (Gender)p.Field<int>("Gender"),
                                          
                                          Nationality = p.Field<string>("Nationality"),
                                          Contacts = new Contacts
                                          {
                                              Phone1 = c.Field<string>("Phone1"),
                                              Phone2 = c.Field<string>("Phone2")
                                          },
                                      }
                     };

        return group.ToList();
    }

So you have a List of Group and a List of Publisher in your code? You need to join the two Lists using Left Outer Join. See msdn : https://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b — jdweng, Aug 09 '16 at 12:01
Possible duplicate of [LEFT OUTER JOIN in LINQ](http://stackoverflow.com/questions/3404975/left-outer-join-in-linq) — Dmitry Egorov, Aug 09 '16 at 12:14
Please show the exact exception message and indicate where it is thrown in your code. You can [edit](http://stackoverflow.com/posts/38849928/edit) your question. — Gert Arnold, Aug 10 '16 at 09:42

score 0 · Answer 1 · answered Aug 10 '16 at 20:36

This means that sometimes publisher is null, because of the "outer join", i.e. the join ... into (GroupJoin). Replace the part ...

Publisher = new Publisher()
{
    ...
}

... by ...

Publisher = pub == null ? default(Publisher) : new Publisher()
{
    PublisherID = pub.Field<int>("PublisherID"),
    GroupID = pub.Field<int>("GroupID"),
    FirstName = pub.Field<string>("FirstName"),
    LastName = pub.Field<string>("LastName"),
    MiddleName = pub.Field<string>("MiddleName"),
    Gender = (Gender)pub.Field<int>("Gender"),
    Nationality = pub.Field<string>("Nationality"),
},

Linq C# Joining null values on navigational properties

1 Answers1