“Deferred vs. Immediate Loading” or “Lazy vs. Eager Loading”, simplyfied, I hope....

Article
09/25/2008

Have you ever been thinking “When using LINQ to SQL, when does the data get loaded? Is it one huge query, or do I get a lot of database round tripping?”.

If you have thought about it once or twice, fine. If you think about it all the time, you may have developed an obsession…

In either case, the answer is………… it depends.

By default there is one request for each record needed.

As you will see below, if you want all the books for all existing authors, then there will be one trip to the database for each author.

However, sometimes it may be better (this is a design issue that I’ll leave to you J) to get all the data in one go. I.e., you pre-fetch all the data in one go.

This will have the overhead of transporting a lot of data that may not be used, but perhaps this is a better option to have the server hammered by

hundreds of selects for each call to the method performing the LINQ to SQL request. As mentioned, I’ll leave the design to you, I’ll just show you how to do this.

This topic is referred to as “Deferred vs. Immediate Loading” or “Lazy vs. Eager Loading”

So, learning by example, as ever.

First, create two tables, one Author table and one Book table and insert some data.

-- Create the Author table

CREATE TABLE LinqAuthors(AuthorId int NOT NULL, AuthorName nchar(50) NULL,

CONSTRAINT [PK_LinqAuthors] PRIMARY KEY CLUSTERED(AuthorId 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 the Books table

CREATE TABLE LinqBooks(AuthorId int NOT NULL, BookId int NOT NULL, Title nchar (50) NULL,

CONSTRAINT [PK_LinqBooks] PRIMARY KEY CLUSTERED (AuthorId ASC, BookId ASC)

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

ON [PRIMARY]) ON [PRIMARY]

-- Set up the relation, after all, books tend to always have authors.

ALTER TABLE LinqBooks WITH CHECK ADD CONSTRAINT [FK_LinqBooks_LinqAuthors] FOREIGN KEY([AuthorId])

REFERENCES LinqAuthors ([AuthorId])

ALTER TABLE LinqBooks CHECK CONSTRAINT [FK_LinqBooks_LinqAuthors]

-- Finally, insert some authors and some books

INSERT INTO LinqAuthors values (1, 'John Smith')

INSERT INTO LinqAuthors values (2, 'Michael Michealson')

INSERT INTO LinqAuthors values (3, 'Lee Enck')

INSERT INTO LinqBooks values (1, 1, 'My first book')

INSERT INTO LinqBooks values (2, 2, 'The Blue book')

INSERT INTO LinqBooks values (2, 3, 'The Red book')

INSERT INTO LinqBooks values (2, 4, 'The Green book')

INSERT INTO LinqBooks values (3, 5, 'The book about trees')

INSERT INTO LinqBooks values (3, 6, 'The book about rocks')

Fire up Visual Studio 2008 (you know this, but just in case, you need 2008 in order to use LINQ, or rather, you need .Net 3.5)

Create a new Console Application and add a .dbml (LINQ to SQL classes) file to the project.

Call it “AuthorBook.dbml”.

The designer opens, so just go to the server tab and drag and drop the tables you created above (LinqAuthors and LinqBooks).

Now, the idea is to list all Authors and their respective books. Using LINQ, this is done like so:

static void Main(string[] args)

{

using (AuthorBookDataContext dc = new AuthorBookDataContext())

{

// Get all authors from the datacontext

var authors = from a in dc.LinqAuthors select a;

// Print out all the authors

foreach (var a in authors)

{

Console.WriteLine("Author: {0}", a.AuthorName);

//...and their books.

foreach (LinqBook book in a.LinqBooks)

{

Console.WriteLine("\tBook: {0}", book.Title);

}

Run it and you should get the following output:

Author: John Smith

Book: My first book

Author: Michael Michealson

Book: The Blue book

Book: The Red book

Book: The Green book

Author: Lee Enck

Book: The book about trees

Book: The book about rocks

So, how many queries against the database?

Since this is a ‘default’ query, there is actually one query for each author. So, in this case there is four queries. One to get all the authors, and one for each call for the authors books.

Is this good? Is this bad? I do not know, but perhaps you do? And you may prefer one (heavier) query that in one go gets all authors and their books. Needed or not.

This is where the DataLoadOptions and it’s LoadWith method comes into play.

By using this, you will get all the associated records for the main target, in this case the books for the authors.

It will look like this: options.LoadWith<LinqAuthor>(b => b.LinqBooks);

The code below contains this, as well as the moving the DataContext log to the Console, it will also run the default (no LoadWith), this will show what is happening behind the scenes.

So, new code:

static void Main(string[] args)

{

Console.WriteLine("\n*** USING LoadWith<> ***\n");

using (AuthorBookDataContext dc = new AuthorBookDataContext())

{ // Write out the datacontext log to console

using (dc.Log = Console.Out)

{

// Create the DataLoadOptions

DataLoadOptions options = new DataLoadOptions();

options.LoadWith<LinqAuthor>(b => b.LinqBooks);

dc.LoadOptions = options;

// Get all authors from the datacontext

var authors = from a in dc.LinqAuthors select a;

// Print out all the authors

foreach (var a in authors)

{

Console.WriteLine("Author: {0}", a.AuthorName);

//...and their books.

foreach (LinqBook book in a.LinqBooks)

{

Console.WriteLine("\tBook: {0}", book.Title);

}

Console.WriteLine("\n*** USING DEFAULT ***\n");

using (AuthorBookDataContext dc = new AuthorBookDataContext())

{

using (dc.Log = Console.Out)

{

var authors = from a in dc.LinqAuthors select a;

foreach (var a in authors)

{

Console.WriteLine("Author: {0}", a.AuthorName);

foreach (LinqBook book in a.LinqBooks)

{

Console.WriteLine("\tBook: {0}", book.Title);

}

And you should get, I hope, the self explanatory of what is going on, output below:

*** USING LOAD WITH ***

SELECT [t0].[AuthorId], [t0].[AuthorName], [t1].[AuthorId] AS [AuthorId2], [t1].[BookId], [t1].[Title], (

SELECT COUNT(*)

FROM [dbo].[LinqBooks] AS [t2]

WHERE [t2].[AuthorId] = [t0].[AuthorId]

) AS [value]

FROM [dbo].[LinqAuthors] AS [t0]

LEFT OUTER JOIN [dbo].[LinqBooks] AS [t1] ON [t1].[AuthorId] = [t0].[AuthorId]

ORDER BY [t0].[AuthorId], [t1].[BookId]

-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30729.1

Author: John Smith

Book: My first book

Author: Michael Michealson

Book: The Blue book

Book: The Red book

Book: The Green book

Author: Lee Enck

Book: The book about trees

Book: The book about rocks

*** USING DEFAULT ***

SELECT [t0].[AuthorId], [t0].[AuthorName]

FROM [dbo].[LinqAuthors] AS [t0]

-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30729.1

Author: John Smith

SELECT [t0].[AuthorId], [t0].[BookId], [t0].[Title]

FROM [dbo].[LinqBooks] AS [t0]

WHERE [t0].[AuthorId] = @p0

-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [1]

-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30729.1

Book: My first book

Author: Michael Michealson

SELECT [t0].[AuthorId], [t0].[BookId], [t0].[Title]

FROM [dbo].[LinqBooks] AS [t0]

WHERE [t0].[AuthorId] = @p0

-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [2]

-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30729.1

Book: The Blue book

Book: The Red book

Book: The Green book

Author: Lee Enck

SELECT [t0].[AuthorId], [t0].[BookId], [t0].[Title]

FROM [dbo].[LinqBooks] AS [t0]

WHERE [t0].[AuthorId] = @p0

-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [3]

-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30729.1

Book: The book about trees

Book: The book about rocks

“Deferred vs. Immediate Loading” or “Lazy vs. Eager Loading”, simplyfied, I hope....

Additional resources