Populating entities two+ levels deep

Jun 2, 2012 at 2:50 AM

Here is an excerpt from your example:

"All these methods accept an optional set of navigation links between entities materializing joins that will be performed when querying.

      productRepo.First(p => p.ProductCategoryId != null , p => p.ProductCategory);


"The above syntax loads the product and its associated category in a single SQL query; This approach avoids the N+1 Selects problem, that might appears when using lazy loading."

But what if I want to load entities two levels deep such as this:

   usersRepo.GetAll(u => u.UserRoles.Roles);

This syntax is invalid, since u.UserRoles returns an ICollection in this case.

Is there a way to do it?

Coordinator
Jun 3, 2012 at 10:13 PM

Hi cbordeman,

behind the scene those methods use the DbExtensions.Include<T, TProperty> method; In the remark section you'll find different kind of usage of this method.

Tonight I do not have access to my laptop, so i cannot test if all these usage are supported with EntityFramework.Patterns, but I'll try to do it tomorrow. I'll keep you informed.

Hope this help

Coordinator
Jun 5, 2012 at 12:34 PM

cbordeman,

i've implemented your model like that :

 

    public class User
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public virtual ICollection<UserRole> UserRoles { get; set; }  
    }

    public class Role
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

    public class UserRole
    {
        public int Id { get; set; }
        public virtual User User { get; set; }
        public virtual Role Role { get; set; }
        public DateTime Since { get; set; }
    }

 

and then you can query it like that  :

        IRepository<User> repo = new Repository<User>(adpt);

        IEnumerable<User> usr = repo.GetAll(user => user.UserRoles.Select(l1 => l1.Role));

which should correspond to your needs.

For information, here is the generated SQL query :

SELECT 
[Project1].[Id] AS [Id], 
[Project1].[Name] AS [Name], 
[Project1].[C1] AS [C1], 
[Project1].[Id1] AS [Id1], 
[Project1].[Since] AS [Since], 
[Project1].[Id2] AS [Id2], 
[Project1].[Name1] AS [Name1], 
[Project1].[User_Id] AS [User_Id]
FROM ( SELECT 
	[Extent1].[Id] AS [Id], 
	[Extent1].[Name] AS [Name], 
	[Join1].[Id1] AS [Id1], 
	[Join1].[Since] AS [Since], 
	[Join1].[User_Id] AS [User_Id], 
	[Join1].[Id2] AS [Id2], 
	[Join1].[Name] AS [Name1], 
	CASE WHEN ([Join1].[Id1] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
	FROM  [dbo].[Users] AS [Extent1]
	LEFT OUTER JOIN  (SELECT [Extent2].[Id] AS [Id1], [Extent2].[Since] AS [Since], [Extent2].[User_Id] AS [User_Id], [Extent3].[Id] AS [Id2], [Extent3].[Name] AS [Name]
		FROM  [dbo].[UserRoles] AS [Extent2]
		LEFT OUTER JOIN [dbo].[Roles] AS [Extent3] ON [Extent2].[Role_Id] = [Extent3].[Id] ) AS [Join1] ON [Extent1].[Id] = [Join1].[User_Id]
)  AS [Project1]
ORDER BY [Project1].[Id] ASC, [Project1].[C1] ASC

I hope this is what you were expecting.

Jun 5, 2012 at 1:26 PM

Thank you, Fab.  Select() is what I needed.