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

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

  • Comments 1

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.

GO

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

REFERENCES LinqAuthors ([AuthorId])

GO

ALTER TABLE LinqBooks CHECK CONSTRAINT [FK_LinqBooks_LinqAuthors]

GO

-- 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

 

More on this:

“Deferred versus Immediate Loading (LINQ to SQL)”

http://msdn.microsoft.com/en-us/library/bb399393.aspx

“How to: Control How Much Related Data Is Retrieved (LINQ to SQL)”

http://msdn.microsoft.com/en-us/library/bb882681.aspx

 

".NET Framework Class Library - DataContext.Log Property"

http://msdn.microsoft.com/en-us/library/system.data.linq.datacontext.log.aspx

".NET Framework Class Library - DataLoadOptions Class"

http://msdn.microsoft.com/en-us/library/system.data.linq.dataloadoptions.aspx

".NET Framework Class Library - DataLoadOptions.LoadWith Method"

http://msdn.microsoft.com/en-us/library/system.data.linq.dataloadoptions.loadwith.aspx   

 

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

Leave a Comment
  • Please add 1 and 8 and type the answer here:
  • Post
  • Really expounded the issue at hand with your explanation. Thanks for this!

Page 1 of 1 (1 items)