Eager Loading Of Related Entities in LLBLGen Pro

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.

Try Not To Reference Your DbContext In Entity Framework Code First Migrations

I recently ran into a problem where a developer was trying to pull down the latest version of the NemeStats codebase (https://github.com/jakejgordon/NemeStats) was getting the below error when running the Entity Framework code first migrations via “update-database”. The error was:

The model backing the ‘NemeStatsDbContext’ context has changed since the database was created. Consider using Code First Migrations to update the database (http://go.microsoft.com/fwlink/?LinkId=238269).

The most common proposed solution to this error is to add the following line to your Global.asax.cs Application_Start():

Database.SetInitializer<MyContext>(null);

However, in my case I was getting the error because I had a migration that actually referenced my DbContext directly rather than using the Sql method that is available in Entity Framework migrations. The problem with this is that the developer was starting with a fresh database that was on say, version 1 but the most current version of the code corresponded to say, version 15 of the database. When the offending migration runs and references the DbContext directly it is assuming the DbContext has models that were in place when the migration was written. When a developer runs “update-database” with the current version of the models and custom code runs, the models could be wildly out of sync with the code that was in the migration and the above error is thrown. Let’s take a look at the following example to illustrate the problem.

Migration 1

A new Player table is created with an Id and Name column:

public override void Up()
{
    CreateTable(dbo.Player", c => new
    {
        Id = c.Int(nullable: false, identity: true),
        Name = c.String(),
    }).PrimaryKey(t => t.Id);
}

Migration 2

This migration uses the DbContext directly to update the Player table, rather than using Sql(…). This will work fine so long as the models associated with ExampleDbContext never change from this point forward. Not likely!

public override void Up()
        {
            using (ExampleDbContext dbContext = new ExampleDbContext())
            {
                Player playerToUpdate = (from player in dbContext.Players
                                 where player.Name == "Foo"
                                 select player).FirstOrDefault();

                if (playerToUpdate != null)
                {
                    playerToUpdate.Name = "Bar";
                    dbContext.SaveChanges();  
                }
            }
        }

Migration 3

This migration makes a change to the Player model/table by adding another column called DateRegistered. This migration is listed just to demonstrate that the Player model backing the ExampleDbContext will have an extra property in the most current version of the code.

public override void Up()
{
    AddColumn("dbo.Player", "DateRegistered", c => c.DateTime());
}

What Happens When You Run These Migrations With a Fresh Database

The first migration will run just fine and will create the Player table.

The second migration will fail and give the error:

The model backing the ‘ExampleDbContext’ context has changed since the database was created. Consider using Code First Migrations to update the database (http://go.microsoft.com/fwlink/?LinkId=238269).

The problem is that the current version of the code has a Player model with Id, Name, and DateRegistered. When you first access the ExampleDbContext in Migration 2, the database doesn’t yet have the DateRegistered column (since this is added in Migration 3 which hasn’t run yet). Entity Framework compares the models with the database and realizes that they are not in sync, and throws this error.

Conclusion and How To Avoid This Problem

The short answer is that you should really try to avoid referencing your DbContext directly in a migration as it will eventually cause problems when your model changes.  Whenever you have to pull down the project with a fresh database your migrations will fail when you run update-database. If you need to access or manipulate the database you should use the built-in methods that are on the DbMigration class which all migrations extend. In the example above, rather than updating the player using the DbContext, I should have updated it using the Sql method in my migration as follows:

Sql(“UPDATE Player Set Name = ‘Bar’ WHERE Name = ‘Foo’;”);

There may actually be times when you have a good reason to want to run code and reference your DbContext in a migration, but this is a topic for another post.

Hopefully this helps someone else who runs into this problem!