Why does linq-2-sql create extra unnecessary objects?
.net, c#, linq, linq-to-sql, orm
Solution
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.
Problem
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: - Why does Linq-2-Sql create these unnecessary extra `Parent` objects ? - Are there any options to avoid creation of extra `Parent` objects ?