How do you create additional associations in Linq-To-Sql classes?
I am trying to create an optional relationship between multiple tables. I have one table called Invoice. The invoice table contains an FK reference to the Customer table in the CustomerId field. The table of accounts also has a non-FK reference to the project, accessible via the ProjectId field.
Is there a way to configure my Linq-To-Sql classes to have an optional relationship between the Invoice and Project table?
I would like to be able to select a Project Name through this association, if there is one.
Clarification
Is it possible to set this in the dbml file? I have a partial class that extends an invoice object and would like to be able to create a property called ProjectName that wraps Invoice.Project.Name.
Just wondering if this is possible or if I have to return the left join value in the query.
source to share
I think I figured it out ... it was easier than I thought. All I need to check is to make sure the relationship is zero in the partial class that 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;
}
}
source to share
This should be done (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
source to share