In a recent blog post we took a look at how Entity Framework (version 6) handles eager loading and lazy loading of entities (or POCOs as the examples were using Code First). In this post we’ll take a look at another .NET ORM tool — LLBLGen Pro — to see how it handles eager loading of entities (or “prefetching” as it’s called with LLBLGen Pro). All of the integration tests backing these observations are openly available to check out on GitHub and are decently commented if you’d rather just let the code do the talking.
The specific goals of this post are to:
- Overview the basics of prefetching in LLBLGen Pro using the LINQ to LLBLGen Pro in conjunction with the Adapter template group
- Demonstrate the actual SQL queries that LLBLGen Pro emits in various prefetching scenarios
- Understand how and what relations are populated in these scenarios
- Take a closer look at how projections behave in terms of the above
Before we dive in, there are a few things worth mentioning about LLBLGen Pro and how it differs from the previous Entity Framework examples:
- LLBLGen Pro has multiple query systems available and my examples are using the LINQ to LLBLGen Pro method. You’ll also see the use of some LLBLGen Pro extension methods which let you do things like prefetch and filter related entities, etc.
- The method I’ve chosen does not support lazy loading of entities, although LLBLGen Pro does appear to support this if you use the “Self Servicing” method of querying.
- LLBLGen Pro uses entity classes which it generates to represent constructs in the database. These entities are not POCOs like the models I used in the Entity Framework examples.
I’ll do another blog post sometime soon to do a more strict apples to apples comparison of Entity Framework vs. LLBLGen Pro over some specific examples. My intention is not to compare the two here, but merely demonstrate how LLBLGen Pro behaves.
I used the exact same database that I used for the Entity Framework examples, but here are there three tables again for reference:
Person
Id – int – PK
Name – nvarchar
Pet
Id – int – PK
Name – nvarchar
OwningPersonId – int – FK to Person.Id
FavoritePetFoodBrandId – int – FK to PetFoodBrand.Id
PetFoodBrand
Id – int – PK
Name – nvarchar
Basic Example With No Prefetching
Here is how you could retrieve a single person — Jane — via LINQ to LLBLGen Pro using the Adapter template group:
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
LinqMetaData metaData = new LinqMetaData(adapter);
PersonEntity person = (from p in metaData.Person
where p.Name == "Jane"
select p).First();
}
As you would probably expect, this will retrieve a Person with an empty (as opposed to null) Pets collection, since Pets weren’t prefetched.
And here is the SQL query that was generated behind the scenes:
SELECT TOP(@p2) [LPA_L1].[Id], [LPA_L1].[Name]
FROM [Examples.ExampleDbContext].[dbo].[Person] [LPA_L1]
WHERE ( ( ( [LPA_L1].[Name] = @p3)))
Parameter: @p2 : Value: 1
Parameter: @p3 : Value: "Jane"
Example of Prefetching One Relationship
Here is an excerpt from an integration test showing how to retrieve Jane with all of her Pets populated:
PersonEntity result =
(from person in metaData.Person.WithPath(a => a.Prefetch(b => b.Pets))
where person.Name == "Jane"
//select person with Pets which is prefetched
select person).First();
Assert.That(result.Name, Is.EqualTo("Jane"));
//the Pets collection is actually populated here
Assert.That(result.Pets, Is.Not.Empty);
The SQL that is generated here is a little more interesting. You’ll notice that LLBLGen Pro generated two queries — one for each table.
//select the Person
SELECT TOP(@p2) [LPA_L1].[Id], [LPA_L1].[Name] FROM [Examples.ExampleDbContext].[dbo].[Person] [LPA_L1] WHERE ( ( ( [LPA_L1].[Name] = @p3)))
Parameter: @p2 : Value: 1
Parameter: @p3 : Value: "Jane"
//select all pets for that Person
SELECT [Examples.ExampleDbContext].[dbo].[Pet].[FavoritePetFoodBrandId], [Examples.ExampleDbContext].[dbo].[Pet].[Id], [Examples.ExampleDbContext].[dbo].[Pet].[Name], [Examples.ExampleDbContext].[dbo].[Pet].[OwningPersonId] FROM [Examples.ExampleDbContext].[dbo].[Pet] WHERE ( ( ( [Examples.ExampleDbContext].[dbo].[Pet].[OwningPersonId] = @p1)))
Parameter: @p1 : Value: 1.
These queries happen behind the scenes and the resulting Person object will automatically have the Pets relationship populated.
Example of a Prefetching Multiple Levels Deep
Notice in the below query we do a .SubPath to go more than one level deep:
PersonEntity result =
(from person in metaData.Person
.WithPath(a => a.Prefetch(b => b.Pets)
.SubPath(c => c.Prefetch(d => d.PetFoodBrand)))
where person.Name == "Jane"
select person).First();
Assert.IsNotEmpty(result.Pets);
Assert.That(result.Pets[0].PetFoodBrand, Is.Not.Null);
Notice that this generates three queries: one for Person, one for Pets, and one for PetFoodBrands.
//first query for the Person
SELECT TOP(@p2) [LPA_L1].[Id], [LPA_L1].[Name] FROM [Examples.ExampleDbContext].[dbo].[Person] [LPA_L1] WHERE ( ( ( [LPA_L1].[Name] = @p3)))
Parameter: @p2 : Value: 1
Parameter: @p3 : Value: "Jane"
//then query for all Pet records that have this person as the owner
SELECT [Examples.ExampleDbContext].[dbo].[Pet].[FavoritePetFoodBrandId], [Examples.ExampleDbContext].[dbo].[Pet].[Id], [Examples.ExampleDbContext].[dbo].[Pet].[Name], [Examples.ExampleDbContext].[dbo].[Pet].[OwningPersonId] FROM [Examples.ExampleDbContext].[dbo].[Pet] WHERE ( ( ( [Examples.ExampleDbContext].[dbo].[Pet].[OwningPersonId] = @p1)))
Parameter: @p1 : Value: 1
//then query for every PetFoodBrand for all pets that were retrieved in the previous query
SELECT [Examples.ExampleDbContext].[dbo].[PetFoodBrand].[BrandName], [Examples.ExampleDbContext].[dbo].[PetFoodBrand].[Id] FROM [Examples.ExampleDbContext].[dbo].[PetFoodBrand] WHERE ( [Examples.ExampleDbContext].[dbo].[PetFoodBrand].[Id] IN (@p1, @p2))
Parameter: @p1 : Value: 1
Parameter: @p2 : Value: 2
This query-per-table approach is actually quite scalable compared to the always-join approach which is prone to generating inefficient and bloated result sets.
Example of Prefetch With Filter On Related Entity
Using LLBLGen Pro you can also filter out related entities from being populated. Below is another integration test excerpt:
PersonEntity result = (from person in metaData.Person
.WithPath(a => a.Prefetch(b => b.Pets)
//prefetch PetEntities but only retrieve Fluffy
.FilterOn(c => c.Name == "Fluffy"))
where person.Name == PERSON_JANE
//select person with Pets which is explicitly prefetched
select person).First();
//only one result is retrieved
Assert.That(result.Pets.Count, Is.EqualTo(1));
//...and that result is Fluffy!
Assert.That(result.Pets[0].Name, Is.EqualTo("Fluffy"));
Below are the queries that were generated:
SELECT TOP(@p2) [LPA_L1].[Id], [LPA_L1].[Name] FROM [Examples.ExampleDbContext].[dbo].[Person] [LPA_L1] WHERE ( ( ( [LPA_L1].[Name] = @p3)))
Parameter: @p2 : Value: 1
Parameter: @p3 : Value: "Jane"
SELECT [Examples.ExampleDbContext].[dbo].[Pet].[FavoritePetFoodBrandId], [Examples.ExampleDbContext].[dbo].[Pet].[Id], [Examples.ExampleDbContext].[dbo].[Pet].[Name], [Examples.ExampleDbContext].[dbo].[Pet].[OwningPersonId] FROM [Examples.ExampleDbContext].[dbo].[Pet] WHERE ( ( ( [Examples.ExampleDbContext].[dbo].[Pet].[OwningPersonId] = @p1))
//notice that the .FilterOn criteria is applied here
AND ( [Examples.ExampleDbContext].[dbo].[Pet].[Name] = @p2))
Parameter: @p1 : Value: 1
Parameter: @p2 : Value: "Fluffy"
Example of a Simple LINQ Projection
You can of course build projections if you want to cherry pick specific fields or relationships. Here is the simplest example:
String personName = (from p in metaData.Person
where p.Name == PERSON_JANE
select p.Name).First();
Here is the SQL that was generated. Notice it only selects the Name column rather than fetching the entire row.
SELECT TOP(@p2) [LPLA_1].[Name] FROM [Examples.ExampleDbContext].[dbo].[Person] [LPLA_1] WHERE ( ( ( ( ( ( [LPLA_1].[Name] = @p3))))))
Parameter: @p2 : Value: 1
Parameter: @p3 : Value: "Jane"
Example of a LINQ Projection Pulling Back Relationships
You can also pull back multiple types of entities using a projection. Here is another integration test excerpt:
var result = (from person in metaData.Person
where person.Name == PERSON_JANE
//select Pets, even though it wasn't explicitly prefetched
select new
{
Person = person,
Pets = person.Pets
}).First();
Assert.That(result.Person, Is.Not.Null);
Assert.That(result.Pets, Is.Not.Empty);
//The Pets relationship on Person is not populated!
Assert.That(result.Person.Pets, Is.Empty);
Something important to call out here is that when using projections to pull from various tables ad-hoc and without prefetching means that the relationships on the retrieved entities will not be populated.
Here is the generated SQL:
SELECT TOP(@p4) [LPLA_1].[Id], [LPLA_1].[Name], @p2 AS [LPFA_3], 1 AS [LPFA_4] FROM [Examples.ExampleDbContext].[dbo].[Person] [LPLA_1] WHERE ( ( ( ( ( ( [LPLA_1].[Name] = @p5))))))
Parameter: @p2 : Value: 1
Parameter: @p4 : Value: 1
Parameter: @p5 : Value: "Jane"
SELECT [LPA_L1].[FavoritePetFoodBrandId], [LPA_L1].[Id], [LPA_L1].[Name], [LPA_L1].[OwningPersonId] FROM [Examples.ExampleDbContext].[dbo].[Pet] [LPA_L1] WHERE ( ( [LPA_L1].[OwningPersonId] = @p1))
Parameter: @p1 Value: 1
Example Of A Query With More Than 50 Entities In Related Table
LLBLGen Pro also has automatic tuning of queries where subsequent queries with more than 50 predicates will be consolidated into a single query with a subselect as performance can start to degrade once there are a large number of predicates. This limit is configurable at run time. Here is yet another integration test excerpt, only this time it pulls back all Persons. Trust me that there are > 50 persons in the database.
var result = (from person in metaData.Person
.WithPath(a => a.Prefetch(b => b.Pets))
//select person with Pets
select person).ToList();
And now notice that there is a single query that uses a subselect:
SELECT [Examples.ExampleDbContext].[dbo].[Pet].[FavoritePetFoodBrandId],
[Examples.ExampleDbContext].[dbo].[Pet].[Id],
[Examples.ExampleDbContext].[dbo].[Pet].[Name],
[Examples.ExampleDbContext].[dbo].[Pet].[OwningPersonId]
FROM [Examples.ExampleDbContext].[dbo].[Pet]
WHERE ( [Examples.ExampleDbContext].[dbo].[Pet].[OwningPersonId] IN
(SELECT [LPA_L1].[Id] FROM [Examples.ExampleDbContext].[dbo].[Person] [LPA_L1] ))
Wrapping Things Up
We’ve taken a look at how LLBLGen Pro handles eagerly loading relationships and have covered some of the most common scenarios. I’ll try to have a follow up post to do an apples to apples comparison of Entity Framework 6 and LLBL 4 to discuss the differences in how they generate the underlying SQL queries and how resulting entities/POCOs are presented. Stay tuned.
Musings of a Software Guy 