How do you create optional associations in Linq-To-Sql classes?

Question

I am trying to creating an optional association between a couple of tables. I have one table called Invoice. The Invoice table has a FK reference to the Customer table through the CustomerId field. The Invoice table also has a not enforced FK reference to the Project able through the ProjectId field.

Is there anyway to set up my Linq-To-Sql classes to have an optional association between the Invoice and the Project table?

I'd like to be able to pick off the Name of Project through this association, if one is available.

Clarification

Is it possible to set this up in the dbml file? I have a partial class that extends the Invoice entity and would like to be able to create a property called ProjectName that wrap Invoice.Project.Name.

Just wondering if this is even possible or if I would have to return the left join value in the query.

Answer 1

This should do it (VB syntax):

Dim results = _
    From i in context.Invoice _
    Join c in context.Customer On i.CustomerId Equals c.CustomerId _
    Group Join p in context.Project On i.ProjectId Equals p.ProjectId Into g = Group _
    Select i.InvoiceName, c.CustomerName, ProjectName = (From x In g Select x.Name).SingleOrDefault

Answer 2

I think I figured this out ... it was way simpler than I thought. All I need to check to see if the relations is null in partial class, which extends the LinqToSql class.

Something like:

   public string ModifiedByName
    {
        get
        {
            if (this.ModifiedByUser == null)
                return string.Empty;
            else
                return this.ModifiedByUser.FirstName + " " + this.ModifiedByUser.LastName;
        }
    }

    public string CreatedByName
    {
        get
        {
            if (this.CreatedByUser == null)
                return string.Empty;
            else
                return this.CreatedByUser.FirstName + " " + this.CreatedByUser.LastName;
        }
    }