Why does linq-2-sql create extra unnecessary objects?

Question

I have a simple Parent Child table in a database like so

CREATE TABLE [Parent](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](256) NOT NULL)    
ALTER TABLE [Parent] ADD CONSTRAINT [PK_Parent_Id] PRIMARY KEY ([Id])    

CREATE TABLE [Child](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ParentId] [int] NOT NULL,
    [Name] [nvarchar](256) NOT NULL)    
ALTER TABLE [Child] ADD CONSTRAINT [PK_Child_Id] PRIMARY KEY ([Id])
ALTER TABLE [Child] ADD CONSTRAINT [FK_Child_Parent_ID] 
    FOREIGN KEY([ParentId]) REFERENCES [Parent] ([Id])

The data that I have in them is

Parent Table

Id  Name
1   John

Child Table

Id ParentId  Name
1     1    Mike
2     1    Jake
3     1    Sue
4     1    Liz

These tables are mapped to Parent and Child C# objects using the Linq-2-SQL designer in Visual Studio with no non standard options.

I made a simple test program to query all child with their parents

public partial class Parent
{
    static int counter = 0;
    //default OnCreated created by the linq to sql designer
    partial void OnCreated()
    {
        Console.WriteLine(string.Format("CreatedParent {0} hashcode={1}",
            ++counter , GetHashCode()));
    }
}

class Program
{
    static void Main(string[] args)
    {
        using (var db = new SimpleDbDataContext())
        {
            DataLoadOptions opts = new DataLoadOptions();
            opts.LoadWith<Child>(c => c.Parent);
            db.LoadOptions = opts;
            var allChildren = db.Childs.ToArray();
            foreach (var child in allChildren)
            {
                Console.WriteLine(string.Format("Parent name={0} hashcode={1}",
                    child.Parent.Name, child.Parent.GetHashCode()));

            }
        }
    }
}

The output of the above program is

CreatedParent 1 hashcode=53937671
CreatedParent 2 hashcode=9874138
CreatedParent 3 hashcode=2186493
CreatedParent 4 hashcode=22537358
Parent name=John hashcode=53937671
Parent name=John hashcode=53937671
Parent name=John hashcode=53937671
Parent name=John hashcode=53937671

As you can see a Parent object was created for every Child in the database only to be discarded eventually.

Questions:

1. Why does Linq-2-Sql create these unnecessary extra Parent objects ?
2. Are there any options to avoid creation of extra Parent objects ?

Answer 1

This is a side-effect of the way, LoadWith is implemented. LINQ to SQL converts your query internally to:

from c in children
select { Child = c, Parent = c.Parent }

As you can see, we are loading the Parent once for every child (an inner join). This effect is not normally visible because of the identity map. ORMs make sure that entity objects are never duplicate by (table, primary key). This comes in handy when you do updates.

LINQ to SQL reads the result set returned from the server (it contains the same Parent N times!) and materializes it into objects. Only after materialization is done, the identity map does its job and discards the duplicate parent instances.

The same effect works for all queries returning the same entity multiple times.