ORM « Musings of a Software Guy

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.

	Eager Loading Of Rel… on Entity Framework Eager vs. Laz…
	jakejgordon on Entity Framework Eager vs. Laz…
	jakejgordon on Entity Framework Eager vs. Laz…
	Julie Lerman on Entity Framework Eager vs. Laz…
	Shawn Wildermuth (@S… on Entity Framework Eager vs. Laz…

Musings of a Software Guy

A blog about software development

Tag Archives: ORM

Eager Loading Of Related Entities in LLBLGen Pro