How to deal gracefully with null foreign keys in Linq to SQL?

Question

In my database, Concessions have a many-to-one relationship with Firms (each Concession has a FirmID). SqlMetal has captured this relationship and generated the appropriate classes, so that each Concession has a Firm element. I'm binding against a query (simplified here) that returns a list of Concessions along with information about the corresponding Firm:

From c as Concession in Db.Concessions _
Select _
   c.ConcessionID, _
   c.Title, _
   c.Firm.Title

The problem is that in some cases a Concession has not been assigned to a Firm (c.FirmID is null), so c.Firm is nothing and I get Object not set to an instance etc.

I can get around this by doing a join as follows:

From c As Concession In Db.Concessions _
Join f As Firm In Db.Firms On f.FirmID Equals c.FirmID _
Select _
    c.ConcessionID, _
    c.Title, _
    Firm_Title = f.Title

This doesn't throw an error when FirmID is null (Firm_Title is just an empty string), but it's not elegant: it's not object-oriented, and it doesn't leverage all of the relational intelligence that Linq to SQL has already captured.

Is there a more graceful way to deal with this situation?

Answer 1

Your 2nd example is doing an inner join. It won't return Concessions which have null FirmID. You want to do an outer join in this case:

http://msdn.microsoft.com/en-us/vbasic/bb737929.aspx#grpjoinleftout

Answer 2

Where are you getting the error?

I've just done a similar LINQ query (in VB.Net), and it worked fine (setting Title to null)

You will later have to deal with title being null, but that's not really a LINQ problem. And even that can be easily deal with in C#.

from c in Db.Concessions
select 
{   
     c.ConcessionID, 
     c.Title, 
     Title = c.Firm.Title ?? ""
}

The equivalent in Vb.net would be to use the Iif(), but I couldn't get that to work.

Answer 3

@James Curran: I'm getting the error any time the query is actually executed - e.g. if I databind a grid to it, or if I apply .ToList to it.

VB.NET has a new true ternary syntax (still more verbose than C#). This actually works:

From c As Concession In db.Concessions _
Select _
    c.ConcessionID, _
    c.Title, _
    Firm_Title = If(c.Firm IsNot Nothing, c.Firm.Title, String.Empty) _

I'm not really satisfied with this approach - it's a pain to have to do that with every field I might use from the foreign table. I'm curious to know why your query is working and mine isn't - is your DataContext generated by SqlMetal? Is the relationship inferred from the database?

Answer 4

his is working because he is selecting a null string not a null relation.

Answer 5

Deal with it in the object constructor

Public Property office() As String
            Get
                Return _office
            End Get
            Set(ByVal value As String)
                If value IsNot Nothing Then
                    _office = value
                Else
                    _office = String.Empty
                End If
            End Set
        End Property