DLinq Tutorials - Lazy Loading and Prefetching of Object Graph - DLinq Queries
by David Hayden ( ASP.NET Developer )
Filed: DLinq Tutorials, O/R Mapping Tools
Worked my way through a number of new DLinq examples to better understand querying and how to prefetch an object graph as opposed to settling for the normal lazy loading ( aka deferred loading ) of relationships. I tossed my hand built classes mentioned in the previous DLinq Tutorial ( DLinq Query Execution and Object Identity - LINQ Tutorials ), because I started getting errors when using them to prefetch part of the object graph. Obviously, I am missing some attributes, which really just proves that handcoding the DLinq Classes is crazy. You are better off using SqlMetal.exe or DLinq Designer to create your DLinq Entities. I will continue to use my DVDLibrary / Movie Library example using Genres and Movies, but I will be using the DLinq Designer generated classes as such:
You can read through my other DLinq Tutorials thus far to catch up on what I have been playing with to date:
DLinq Prefetching
By default, DLinq will lazy load relationships just like most O/R Mappers unless you tell it differently. This means that if you query for the “SciFi” movie genre, it will only return the Genre object itself and none of the movies in the Genre.
var q =
from c in library.Genres
where c.Name == "SciFi"
select c;
If you want to include not only the “SciFi“ Genre but also the collection of movies that are in the Genre, you can use the “Including()“ query operator as such:
// Get SciFi Genre and all
// movies in SciFi
var q =
(from c in library.Genres
where c.Name == "SciFi"
select c).Including(c => c.Movies);
DLinq fires off 2 queries, one for the Genre and 1 to retrieve the collection of all movies in the Genre.
// Get Genre
exec sp_executesql N'SELECT [t0].[GenreId],
[t0].[Name], (
SELECT COUNT(*)
FROM [Movies] AS [t1]
WHERE [t1].[GenreId] = [t0].[GenreId]
) AS [Movies]
FROM [Genres] AS [t0]
WHERE [t0].[Name] = @p0
ORDER BY [t0].[GenreId]', N'@p0 nvarchar(5)',
@p0 = N'SciFi'
// Get Movies in Genre
exec sp_executesql N'SELECT [t1].[MovieId],
[t1].[GenreId], [t1].[Name]
FROM [Genres] AS [t0], [Movies] AS [t1]
WHERE ([t0].[Name] = @p1) AND ([t1].[GenreId]
= [t0].[GenreId])
ORDER BY [t0].[GenreId], [t1].[MovieId]',
N'@p1 nvarchar(5)', @p1 = N'SciFi'
DLinq Querying
Querying in DLinq is a huge topic, but here are just some neat examples of the power of the DLinq query language.
Sometimes you don't want to load entire entities when querying the database. Perhaps you only want a field or two. Here is an example of querying my DVD library for all movies in the SciFi Genre. However, in this case I just want 2 fields - MovieId and Name:
// I just want the ID and Name of all
// movies in the SciFi Genre. Don't
// pull over any other fields.
var q = from c in library.Movies
where c.Genres.Name == "SciFi"
select new {c.MovieId, c.Name };
If I just want the list of movie names in the SciFi genre, the query would even be simpler:
// I just want the names of all
// movies in the SciFi Genre.
var q = from c in library.Movies
where c.Genres.Name == "SciFi"
select c.Name;
You can join fields from different tables which gives you quite a bit of flexibility:
// Gives me a sequence of pairs
// of genre name and movie for all
// movies in the genre
var q = from c in library.Genres
from movies in c.Movies
where c.Name == "SciFi"
select new {c.Name, movies };
This just scratches the surface of the DLinq querying capabilities.
Conclusion
Hopefully this shed a little light on how to prefetch an object graph using DLinq as well as a bit on querying with DLinq for other than just entities.
Source: David Hayden ( ASP.NET Developer )
Filed: DLinq Examples and Tutorials, O/R Mapping Tools