vb.net linq left outer join null

Question

I've got what I think is a working left outer join linq query but I'm having problems with the select because of null values in the right hand side of the join. Here is what I have so far

 Dim Os = From e In oExcel
        Group Join c In oClassIndexS On c.tClassCode Equals Mid(e.ClassCode, 1, 4)
        Into right1 = Group _
        From c In right1.DefaultIfEmpty

I want to return all of e and one column from c called tClassCode. I was wondering what the syntax would be. As you can see I'm using vb.net. Thanks.

Update.. Here is the query doing join where i get the error _message = "Object reference not set to an instance of an object."

 Dim Os = From e In oExcel
             Group Join c In oClassIndexS On c.tClassCode Equals Mid(e.ClassCode, 1, 4)
             Into right1 = Group _
             From c In right1.DefaultIfEmpty
        Select e, c.tClassCode

If i remove the c.tClassCode from the select the query runs without error. So i though perhaps i needed to do a select new but i don't think i was doing that correctly either.

Answer 1

EDIT: you need to check c for null, specifically the c after your grouping. See my updates below.

You need to do a null check on tClassCode c in your select statement. What type is tClassCode? You should be able to do a null check on the value c and if it's null cast a nullable of the respective type and returned it, otherwise return the actual value.

Since I am not sure what tClassCode is let's assume it's an integer, in which case the cast would be to a nullable integer (Integer?). With that in mind your select statement, to be added at the end of what you have so far, should resemble:

Since tClassCode is a string your code would resemble:

Select _
    e, _
    Code = If(c Is Nothing, Nothing, c.tClassCode)

Depending on what you need to do if c is null you could return String.Empty instead of Nothing:

Select _
    e, _
    Code = If(c Is Nothing, String.Empty, c.tClassCode)

Of course you are free to further expand the selection of "e" to project its specific columns by name.

The important thing to realize is that you must check c for null before using any of its properties since it might be null depending on the left outer join result for a particular result (row). Back to my earlier example, if you had another field named Priority that was an integer you would cast against a nullable:

Select _
    e, _
    Priority = If(c Is Nothing, CType(Nothing, Integer?), c.Priority)

Answer 2

Well i got it working... at least i get results without errors. Thanks for the help.. here it is.

 Dim Os = From e In oExcel
                 Group Join c In oClassIndexS On c.tClassCode Equals Mid(e.ClassCode, 1, 4)
                 Into right1 = Group _
                 From jo In right1.DefaultIfEmpty()
        Select New With {.CivilServiceTitle = e.CivilServiceTitle, .Code = If(jo Is Nothing, Nothing, jo.tClassCode)}