How to Get Left Instead of Inner Join in Linq to SQL Navigation Property Generated Query

Question

When I select from a parent object in Linq to SQL, I get all records in the parent table. However, when I also select a navigation property (from an extension table with a 1:1 relationship and a shared primary key), I get only records that have that navigation property populated. How can I use the navigation property to get all records from the parent, regardless of whether the child is populated? (i.e. a left instead of an inner join)? Can Linq to SQL navigation properties do this, or is a manual join the only option? One of the proposed answers on this post says it should be possible, and this post says that Entity Framework would handle it, but I can't get it to work with Linq to SQL.

The queries:

        TestDataContext repository = new TestDataContext();

        // No navigation property selected, returns all records from Persons
        var result = repository.Persons.Select(x => new { x.PersonID, x.Name });
        // Navigation property selected, returns only persons having a record in PersonExtensions table
        var result1 = repository.Persons.Select(x => new { x.PersonID, x.Name, x.Extension.Height });

Here are the domain classes:

[Table(Name = "Persons")]
public class Person
{
    private System.Data.Linq.EntityRef<PersonExtension> _extension = new System.Data.Linq.EntityRef<PersonExtension>();

    [Association(Name = "Persons_PersonExtensions", Storage = "_extension", ThisKey = "PersonID", OtherKey = "PersonID", IsForeignKey = true)]
    public PersonExtension Extension
    {
        get
        {
            return _extension.Entity;
        }
        set
        {
            _extension.Entity = value;
        }
    }

    [Column(Name = "PersonID", DbType = "int NOT NULL IDENTITY", IsPrimaryKey = true, IsDbGenerated = true, CanBeNull = false, UpdateCheck = System.Data.Linq.Mapping.UpdateCheck.Never)]
    public int PersonID { get; set; }

    [Column(Name = "Name")]
    public string Name { get; set; }

    [Column(Name = "Gender")]
    public string Gender { get; set; }
}

[Table(Name = "PersonExtensions")]
public class PersonExtension
{
    private System.Data.Linq.EntityRef<Person> _person = new System.Data.Linq.EntityRef<Person>();

    [Association(Name = "Persons_PersonExtensions", Storage = "_person", ThisKey = "PersonID", OtherKey = "PersonID", IsForeignKey = true)]
    public Person Person
    {
        get
        {
            return _person.Entity;
        }
        set
        {
            _person.Entity = value;
        }
    }

    [Column(Name = "PersonID", DbType = "int NOT NULL", IsPrimaryKey = true, CanBeNull = false, UpdateCheck = System.Data.Linq.Mapping.UpdateCheck.Never)]
    public int PersonID { get; set; }

    [Column(Name = "Height ")]
    public int? Height { get; set; }
}

And the datacontext:

[Database]
public class TestDataContext:DataContext
{
    public TestDataContext() : base(@"Data Source=localhost; Initial Catalog = Test; Integrated Security = True")
    {
        this.CommandTimeout = 600;
    }

    public Table<Person> Persons;
    public Table<PersonExtension> PersonExtensions;
}

Here are the table definitions:

CREATE TABLE [dbo].[Persons](
    [PersonID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NULL,
    [Gender] [nchar](10) NULL,
 CONSTRAINT [PK_Persons] PRIMARY KEY CLUSTERED 
(
    [PersonID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[PersonExtensions](
    [PersonID] [int] NOT NULL,
    [Height] [int] NULL,
 CONSTRAINT [PK_PersonExtensions] PRIMARY KEY CLUSTERED 
(
    [PersonID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[PersonExtensions]  WITH CHECK ADD  CONSTRAINT [FK_PersonExtensions_Persons] FOREIGN KEY([PersonID])
REFERENCES [dbo].[Persons] ([PersonID])
GO

ALTER TABLE [dbo].[PersonExtensions] CHECK CONSTRAINT [FK_PersonExtensions_Persons]
GO

Any help will be very much appreciated!!

Note: question above has been modified slightly from the original to make it clearer that the goal is to use navigation properties.

Answer 1

It appears that left joins in Linq to SQL navigation properties are possible, but the objects can't be set up the way I set mine up. Instead, the id of the "extension" table must be different from the id of the "main" table, and the "main" table must contain a nullable (e.g. int?) reference to the id of the extension table. It's the fact that the variable is nullable that tells Linq to SQL to create a left join instead of an inner join. Of course if I try to use the same key in both tables, that isn't possible, because the id of the main table has to be a non-nullable field in that table.

I figured this out based on this post.

Unfortunately, this is also messy, but I'm pretty sure at this point that this is the only option if I want calls to the navigation properties to generate a left join.