LLBLGen Pro, Entity Framework 4.1, and the Repository Pattern

In this post, I will use the model-first approach in LLBGen Pro designer and some custom templates to generate repository classes for LLBLGen entities. I will create a simple membership API with some table-per-type inheritance, build some code-generation templates that extend the out-of-the-box EF templates in LLBLGen to model our API after the repository pattern, make use of SQL stored procedures, extend the generated code with some extension methods; and introduce more surprises along the way.

Introduction

If you’ve been hanging out in the .NET universe, you’ve probably heard of LLBLGen Pro. It’s one of my favorite power tools. If you’re new to LLBLGen, visit http://www.llblgen.com. LLBLGen is an ORM mapping tool, similar to CodeSmith Tools and DataObjects.Net, other excellent power tools. One of the unique things I like about LLBLGen is that it supports an array of persistence frameworks, such as the home-grown, extremely powerful LLBLGen SelfServicing and Adapter frameworks, the Entity Framework (EF, STE, and POCO), Linq to Sql, NHibernate and FluentNHIbernate. LLBLGen supports every major relational database vendor I have ever worked with (MS Access, SQL Server, Oracle, PostgreSql, Firebird, IBM DB2, MySql, and Sybase). LLBLGen Pro supports both a database-first approach, and a model-first approach, and the next version (v3.5) will support the Code-First approach. It also has an advanced templating and code-generation engine and designer that: allows you to rename entities and entity fields; configure your own database mappings; supports advanced EF data models; allows you to create model-only relationships; create typed views on stored procedures and new and existing entity relationships; lets you create code generation libraries of your own, and override any template you want that comes out-of-the-box. And that's just the beginning. In this post, I’d like to have fun with LLBLGen Pro, so I’m going to:

• Use the model-first approach in LLBGen Pro designer to create a simple membership API with some table-per-type inheritance;
• Build some code-generation templates that extend the out-of-the-box EF templates in LLBLGen to model our API after the repository pattern;
• Make use of SQL stored procedures;
• Extend the generated code with some extension methods; and
• Introduce more surprises along the way.

But first, a word about the repository pattern. Recently I’ve observed some backlash against it, and I don’t want that to distract you.

Repository Pattern (NIH)

This pattern almost needs no introduction. If you are new to this pattern, or itching for some reading, check out the following links:

• The Repository Pattern
• Repository (summary)
• Can anyone recommend a good tutorial on repository pattern usage, in C#?
• The repository pattern explained and implemented
• DDD: The Repository Pattern
• Entity Framework 4 POCO, Repository and Specification Pattern
• The Repository Pattern with Linq to Fluent NHibernate and MySQL

The repository pattern is very popular, and there is no single implementation. If you’re curious, here are sample open-source applications that show different versions and usages of this pattern.

• EF4PRS
• KiGG
• Domain Oriented N-Layered .NET 4.0 Sample App
• NerdDinner
• nopCommerce
• Orchard
• TruckTracker
• Umbraco

A hot debate

Abstracting your data access behind clean repository and unit of work interfaces seems simple, but before you go down this road, check your motivations. If you do this because you think one day you’ll want to “swap” out your DAL implementation with another--for instance, go from EF, to NHibernate, or LLBLGen Entity Adapter Framework, or custom DAO--step back and mull the decision a bit more. ORM designers have carefully crafted many features for you to use. Abstracting all of them into your repository façade for a hypothetical future technology shift begs the question: Why are you designing with the current technology in the first place? Check out some interesting voices in the debate: Repository is the new Singleton, The false myth of encapsulating data access in the DAL, Review: Microsoft N Layer App Sample, part VII-Data Access Layer is GOOD for you, Farewell to Repositories (I think). For example, if you use the LLBLGen Adapter framework, building your own Repository layer on top of it doesn’t make sense. This adapter framework has an incredible predicate and deep-fetch system built-in to it. It has a unit of work, and appropriately separates entities from the persistence framework so you can create a rich n-tier, manager, service layer, or whatever you like. So building a repository layer on top of an existing ORM framework may simply duplicate existing functionality. Don’t do it just because you can, or because you’re a purist. Ok, so now, if the fear has been put into you, good. At least you’re going to make a conscious decision to use or not use this pattern in your application. All that said, I still personally like using this pattern more often then not. The repository pattern is easy to implement, and with a code-generation framework like LLBLGen, you can build into your repositories all kinds of things, customizations, thanks to the rich designer and some creative thinking. Using the pattern also helps me think through my implementation a little more, and thinking is fun, and that makes it worth it to me. I almost always use the repository pattern when developing against SharePoint lists for example, because in SharePoint, lists are all over the place, and I can use a simple repository interface (and the Service Locator Pattern) to easily read/write to the lists, regardless of which site the list is in. By creating a factory for the unit of work and the repository, I can also use the repository pattern to read/write data to different persistence stores, which I have had to do on occasion. So all that to say, have fun with it.

Case Study

Building the model

LLBLGen supports two different types of paradigms: “Database First”, and “Model First” code generation. The first approach consists of working with an existing database, and modeling your entity graph on top of it. The second approach consists of modeling your entities within the LLBLGen modeling tool, and working with LLBLGen generated SQL scripts to create and alter your database schema (a migrations approach that relies on SQL). LLBLGen handles creating update scripts for you, as it tracks the state of your model as you model it over time. You can also generate a complete “CREATE” script for your database at any point in time. Check out some intro videos to LLBLGen: http://www.llblgen.com/pages/videos.aspx, and an older video by the wizard of data access Frans Bouma (a principal developer of LLBLGen): http://weblogs.asp.net/fbouma/archive/2010/04/28/llblgen-pro-v3-0-with-entity-framework-v4-0-12m-video.aspx.

Use-case

We are going to create a User entity, Group entity, and Role entity that all inherit from an abstract Principal base entity. In our scenario, the Id and Name of a Principal is always unique. The User entity will have some unique fields of it’s own. Every principal (regardless of type) can have settings (similar to a dictionary of properties for that principal), and preferences, although preferences can be shared between principals. Finally, principals are containers that can contain other principals (regardless of type), and can be contained within any number of other principals (regardless of type).

The Model

Using the LLBLGen Pro modeling tool, I come up with the following.

By making the Principal entity a base entity for Group, User, and Role, I can model the many-to-many relationship between Principal and Preferences, and the one-to-many relationship between Principal and Settings as such, and these relationships are passed on to the child entities. Creating the container relationships so that any principal can contain other principals is a little more complex because recursion is involved. In order to handle recursive queries of varying depths, we'll incorporate some stored procedures with CTE queries into our LLBLGen Pro generated model.

Generating code

The first step in generating code is to determine what persistence framework we’re going to use. So we validate the integrity of the model using the menus: Project > Validate. Then we fix any errors that show up in the error list. Then we map the model to a persistence store using the menus: Project > Add Relational Model Data Storage for a Database. We pick SQL Server. Once we’ve chosen our database type, we can either manually map all the fields to a schema, or we can let the tool do it all for us. In our case we will let the tool do it for us, and we can do that using: Project > Automap Unmapped Entities. Finally, we generate our Visual Studio solution using the menus: “Project > Generate Source Code...”. On the “Task queue to execute” tab, we’ll pick the SD.EntityFramework.v4 (Proxy friendly POCO entities) preset (which is the template bundle we’re going to use), and then we click “Start generator (Normal)”. Here is our solution in the LLBLGen Pro Entity Explorer, and the generated code in Visual Studio.

LLBLGen Pro Entity Explorer		Visual Studio Solution Explorer

At this point, this is essentially a similar output as what the EF designer would produce along with the T4 POCO templates. One of the benefits of LLBLGen here is the extent to which you can customize the entities in the designer, and then quickly update the code. LLBLGen can easily handle hundreds of entities. Well, we’ve just started to have fun, now let’s get creative.

Building Repository Classes with LLBLGen

Our repository implementation will consist of an IUnitOfWork interface in the “Model” project (which is NOT persistence aware), and a UnitOfWork class (in our case the EF context class) which will manage adds/edits/deletes of entities and coordinate updates against the persistence store. You can read about the Unit of Work pattern here. The implementation will also involve generating a base IRepository interface and a set of I<entity>Repository interfaces for each entity within the “Model” project, and their implementations in the “Persistence” project. Let’s generate a folder within each of these projects called “RepositoryClasses”. Building out code generation templates in LLBLGen involves a couple things:

1. Creating a templatebindings file that represents a virtual bundle of all our custom templates, with each template’s physical mapping to disk
2. Creating the templates themselves that generate the code.
3. Creating a preset file that houses the execution instructions of which templates to fire, in which order, and the parameters to pass to the templates. This includes instructions to create folders to contain the classes and add those folders to the Model and Persistence projects.

Upon re-generating the code, the visual studio solution looks as follows. Notice the new directories and repository classes.

Generating the database

To build the database, I’ll use the “Project > Generate Database Schema Create Script (DDL SQL)…” menus, and run the SQL output in SQL Management studio. After creating the database schema, in the LLBLGen Pro designer I will lync the database schema to the model we have created so that LLBLGen can keep them in sync. Built-in to the templates, I have added code that will generate methods in the IUnitOfWork interface and it’s implementation to call the stored procedures in the database, using the connection information belonging to the EF context. We will now create some stored procedures that use CTE recursion to query for which users, groups, roles are members of each other. The CTE query to retrieve members of a principal for example looks like this:

CREATE PROCEDURE [dbo].[ChildPrincipalMemberships]
  @parentId int,
  @childType int,
  @recurse bit
AS
  SET NOCOUNT ON
    IF (@recurse = 1)
    BEGIN
        ;WITH child_memberships_CTE (ParentPrincipalId, ChildPrincipalId, Level) AS
        (
             – Anchor cte definition
             SELECT pm.ParentPrincipalId, pm.ChildPrincipalId, 0 AS Level
             FROM PrincipalMembers AS pm
             WHERE pm.ParentPrincipalId = @parentId
             UNION ALL
             – Recursive cte definition
             SELECT pm.ParentPrincipalId, pm.ChildPrincipalId, cte.Level + 1
             FROM PrincipalMembers AS pm
             INNER JOIN child_memberships_CTE AS cte ON pm.ParentPrincipalId = cte.ChildPrincipalId
        )
        SELECT d.Id, Principal.Name, Principal.Description, d.Email, d.FullName, d.Birthdate, child_memberships_CTE.Level, 1 as MemberType
        FROM [User] as d
            INNER JOIN Principal ON d.Id = dbo.Principal.Id
            INNER JOIN child_memberships_CTE ON Principal.Id = child_memberships_CTE.ChildPrincipalId
        WHERE (@childType = 1 OR @childType = 0)
        UNION ALL
        SELECT d.Id, Principal.Name, Principal.Description, NULL as Email, NULL as FullName, NULL as Birthdate, child_memberships_CTE.Level, 2 as MemberType
        FROM [Group] as d
            INNER JOIN Principal ON d.Id = dbo.Principal.Id
            INNER JOIN child_memberships_CTE ON Principal.Id = child_memberships_CTE.ChildPrincipalId
        WHERE (@childType = 2 OR @childType = 0)
        UNION ALL
        SELECT d.Id, Principal.Name, Principal.Description, NULL as Email, NULL as FullName, NULL as Birthdate, child_memberships_CTE.Level, 3 as MemberType
        FROM [Role] as d
            INNER JOIN Principal ON d.Id = dbo.Principal.Id
            INNER JOIN child_memberships_CTE ON Principal.Id = child_memberships_CTE.ChildPrincipalId
        WHERE (@childType = 3 OR @childType = 0)
    END
    ELSE
    BEGIN
        SELECT d.Id, Principal.Name, Principal.Description, d.Email, d.FullName, d.Birthdate, 0 as Level, 1 as MemberType
        FROM [User] as d
            INNER JOIN Principal ON d.Id = dbo.Principal.Id
            INNER JOIN PrincipalMembers ON Principal.Id = PrincipalMembers.ChildPrincipalId
        WHERE PrincipalMembers.ParentPrincipalId = @parentId AND (@childType = 1 OR @childType = 0)
        UNION ALL
        SELECT d.Id, Principal.Name, Principal.Description, NULL as Email, NULL as FullName, NULL as Birthdate, 0 as Level, 2 as MemberType
        FROM [Group] as d
            INNER JOIN Principal ON d.Id = dbo.Principal.Id
            INNER JOIN PrincipalMembers ON Principal.Id = PrincipalMembers.ChildPrincipalId
        WHERE PrincipalMembers.ParentPrincipalId = @parentId AND (@childType = 2 OR @childType = 0)
        UNION ALL
        SELECT d.Id, Principal.Name, Principal.Description, NULL as Email, NULL as FullName, NULL as Birthdate, 0 as Level, 3 as MemberType
        FROM [Role] as d
            INNER JOIN Principal ON d.Id = dbo.Principal.Id
            INNER JOIN PrincipalMembers ON Principal.Id = PrincipalMembers.ChildPrincipalId
        WHERE PrincipalMembers.ParentPrincipalId = @parentId AND (@childType = 3 OR @childType = 0)
    END

Now, we can just refresh the model in the LLBLGen designer and ask it to create calling methods for these new stored procedures. After re-generating the code, the calling methods now appear as part of our unit of work.

public interface IUnitOfWork : ISql, IDisposable
{
    void Commit();
    // Incorporate stored procedure calls in the unit of work (or create a separate interface for these custom actions)
    ///<summary>Calls the stored procedure ‘[dbo].[ChildPrincipalMemberships]‘ and returns its results in a single DataSet</summary>
    ///<param name=”parentId”>Parameter mapped onto the stored procedure parameter ‘@parentId’</param>
    ///<param name=”childType”>Parameter mapped onto the stored procedure parameter ‘@childType’</param>
    ///<param name=”recurse”>Parameter mapped onto the stored procedure parameter ‘@recurse’</param>
    ///<returns>A single DataSet with all result data returned by the called stored procedure</returns>
    DataSet GetChildPrincipalMembershipsResults(System.Int32 parentId, System.Int32 childType, System.Boolean recurse);
    ///<summary>Calls the stored procedure ‘[dbo].[ParentPrincipalMemberships]‘ and returns its results in a single DataSet</summary>
    ///<param name=”childId”>Parameter mapped onto the stored procedure parameter ‘@childId’</param>
    ///<param name=”parentType”>Parameter mapped onto the stored procedure parameter ‘@parentType’</param>
    ///<param name=”recurse”>Parameter mapped onto the stored procedure parameter ‘@recurse’</param>
    ///<returns>A single DataSet with all result data returned by the called stored procedure</returns>
    DataSet GetParentPrincipalMembershipsResults(System.Int32 childId, System.Int32 parentType, System.Boolean recurse);
    // Not a real big fan of this method, but need it for EF support
    IObjectSet<TEntity> CreateSet<TEntity>() where TEntity : class;
}

Using the Code

We are ready to use the code. I’ve attached a console project to the download that shows how to use the generated code. The console project in the download provides samples for the following:

1. Using the repositories with an IoC framework (I created a real simple IoC implementation for the purpose of this demo in the download, I would use one of the popular DI containers most likely in a real implementation) - so don't gag when you see IoC.Resolve<IABC> below.
2. Deleting entities specifically and using predicates, including Cascade deletes.
3. Creating new entities (with 1 to many and many to many associated entities) as part of the same transaction.
4. Auto-incrementing behavior upon UnitOfWork commit.
5. Eager loading of related entities (both with Paths, and Lambdas).
6. Running SQL directly against the underlying persistence store.
7. Executing statically typed stored procedures.

class Program
{
  static void Main(string[] args)
  {
    IoC.Initialize();
    using (var uow = IoC.Resolve<IUnitOfWork>())
    {
      var preferenceRepository = IoC.Resolve<IPreferenceRepository>(uow);
      var groupRepository = IoC.Resolve<IGroupRepository>(uow);
      var roleRepository = IoC.Resolve<IRoleRepository>(uow);
      var userRepository = IoC.Resolve<IUserRepository>(uow);
      var memberRepository = IoC.Resolve<IPrincipalMemberRepository>(uow);
      //————————————————————————————————
      // TEST 1 -  Delete all records
      //————————————————————————————————
      // When deleting, to enable Cascade delete, you must load related entities into the context (ARGHH…)
      // OR, just modify your SQL/DATABASE to implement ON CASCADE DELETE
      //————————————————————————————————
      preferenceRepository.DeleteMulti(preferenceRepository.FetchAll(new[] { Preference.Includes.CascadeDeleteDependencies }));
      // OR, use the DeleteMulti with predicate argument, which will automatically delete dependencies based on the
      // UseCascadeDelete flag in LLBLGen designer (equivalent to method above)
      groupRepository.DeleteMulti(x => x.Id > 0);
      roleRepository.DeleteMulti(x => x.Id > 0);
      userRepository.DeleteMulti(x => x.Id > 0);
      memberRepository.DeleteMulti(x => x.Id > 0);
      //————————————————————————————————
      // TEST 2 – Create some records
      //————————————————————————————————
      // Create a couple records, along with some 1 to many, and many to many related items all at once
      //————————————————————————————————
      var user1 = User.CreateUser(“someone”, “[email protected]”, “”, “Some One”, DateTime.Now.AddYears(-20));
      user1.Settings.Add(new Setting { Name = “Doctor”, Value = “Good” }); // add 1 to many related item
      user1.Preferences.Add(new Preference { Name = “EyeColor”, Value = “Blue” }); // add many to many related item
      var user2 = User.CreateUser(“noone”, “[email protected]”, “”, “No One”, DateTime.Now.AddYears(-40));
      user2.Settings.Add(new Setting { Name = “Doctor”, Value = “Evil” }); // add 1 to many related item
      user2.Preferences.Add(new Preference { Name = “EyeColor”, Value = “Green” }); // add many to many related item
      userRepository.CreateMulti(new[] {user1, user2});
      uow.Commit(); // Commit all this to the database
      Console.WriteLine(“PASS – Delete and Create”);
      //————————————————————————————————
      // TEST 3 – AutoIncrementing behavior test
      //————————————————————————————————
      // Check that the AutoIncrementing IDs for objects and related items were all created and are now active
      // on committed objects
      //————————————————————————————————
      if (user1.Id > 0 &&
        user1.Settings.First().Id > 0 &&
        user1.Preferences.First().Id > 0 &&
        userRepository.Count() > 0)
      {
        Console.WriteLine(“PASS – AutoIncrementing IDs”);
      }
      else
      {
        Console.WriteLine(“FAIL – AutoIncrementing IDs”);
      }
      //————————————————————————————————
      // TEST 4 – Inside UOW prefetch test of committed related item
      //————————————————————————————————
      // A related item that was committed within a Unit of Work should not have to be eager loaded
      // on subsequent fetches, as it should live in the context
      //————————————————————————————————
      if (userRepository.FetchUsingEmail(“[email protected]”).Name == “someone” &&
        userRepository.FetchUsingName(“someone”).Email == “[email protected]” &&
        userRepository.FetchUsingName(“someone”).Settings.Count == 1 &&
        userRepository.FetchUsingName(“someone”, new[] { User.Includes.Paths.Settings }).Settings.Count == 1)
      {
        Console.WriteLine(“PASS – Inside UOW eager loading behavior after commit”);
      }
      else
      {
        Console.WriteLine(“FAIL – Inside UOW eager loading behavior after commit”);
      }
    }
    //————————————————————————————————
    // TEST 5 – Eager loading tests
    //————————————————————————————————
    // 5a – No eager loading
    //————————————————————————————————
    using (var uow = IoC.Resolve<IUnitOfWork>())
    {
      // Create a user
      var userRepository = IoC.Resolve<IUserRepository>(uow);
      var test5a = userRepository.FetchUsingName(“someone”).Settings.Count == 0;
      Console.WriteLine((test5a ? “PASS” : “FAIL”) + ” – No eager loading test”);
    }
    //————————————————————————————————
    // 5b – Eager loading with lambdas
    //————————————————————————————————
    using (var uow = IoC.Resolve<IUnitOfWork>())
    {
      // Create a user
      var userRepository = IoC.Resolve<IUserRepository>(uow);
      var users = userRepository.FetchUsingName(“someone”, new[] { User.Includes.OneToMany.Settings, User.Includes.ManyToMany.Preferences });
      var test5b = users.Settings.Count == 1 && users.Preferences.Count == 1;
      Console.WriteLine((test5b ? “PASS” : “FAIL”) + ” – Eager loading with lambdas”);
    }
    //————————————————————————————————
    // 5c – Eager loading with paths
    //————————————————————————————————
    using (var uow = IoC.Resolve<IUnitOfWork>())
    {
      // Create a user
      var userRepository = IoC.Resolve<IUserRepository>(uow);
      var users = userRepository.FetchUsingName(“someone”, new object[] { User.Includes.Paths.Settings, User.Includes.Paths.Preferences, User.Includes.Paths.Preferences_Principals, User.Includes.Paths.Settings_Principal });
      var test5c = users.Settings.Count == 1 && users.Preferences.Count == 1 && users.Preferences.First().Principals.Count > 0;
      Console.WriteLine((test5c ? “PASS” : “FAIL”) + ” – Eager loading with paths”);
      var test5a = userRepository.FetchUsingName(“someone”).Settings.Count == 0;
      Console.WriteLine((test5a ? “FAIL” : “PASS”) + ” – No eager loading test (for already fetched item with eager load)”);
    }
    //————————————————————————————————
    // TEST 6 – Run some SQL directly against the database
    //————————————————————————————————
    using (var uow = IoC.Resolve<IUnitOfWork>())
    {
      var count = (int)uow.ExecuteScalarQuery(CommandType.Text, “SELECT count(Id) FROM [User]“);
      Console.WriteLine((count > 0 ? “PASS” : “FAIL”) + ” – SQL test”);
    }
    //————————————————————————————————
    // TEST 7 – Execute statically typed stored procedure call
    //————————————————————————————————
    using (var uow = IoC.Resolve<IUnitOfWork>())
    {
      var groupRepository = IoC.Resolve<IGroupRepository>(uow);
      var roleRepository = IoC.Resolve<IRoleRepository>(uow);
      var userRepository = IoC.Resolve<IUserRepository>(uow);
      var membershipRepository = IoC.Resolve<IPrincipalMemberRepository>(uow);
      var groups = new List<Group>();
      var roles = new List<Role>();
      var users = new List<User>();
      for (var i = 1; i < 11; i++)
      {
        groups.Add(Group.CreateGroup(“Sample Group “ + i));
        roles.Add(Role.CreateRole(“Sample Role “ + i));
        users.Add(User.CreateUser(“user” + i, “user” + i + “@company.com”, “”, “User “ + i, null));
      }
      groupRepository.CreateMulti(groups);
      roleRepository.CreateMulti(roles);
      userRepository.CreateMulti(users);
      // commit so that IDs are updated
      uow.Commit();
      // Create a bunch of hierarchical memberships
      var principalMembers = new[]
        {
           PrincipalMember.CreatePrincipalMember(users[0].Id, groups[0].Id),
           PrincipalMember.CreatePrincipalMember(users[0].Id, groups[1].Id),
           PrincipalMember.CreatePrincipalMember(users[0].Id, groups[2].Id),
           PrincipalMember.CreatePrincipalMember(users[0].Id, groups[3].Id),
           PrincipalMember.CreatePrincipalMember(users[0].Id, groups[4].Id),
           PrincipalMember.CreatePrincipalMember(users[0].Id, groups[5].Id),
           PrincipalMember.CreatePrincipalMember(users[0].Id, groups[6].Id),
           PrincipalMember.CreatePrincipalMember(users[1].Id, groups[0].Id),
           PrincipalMember.CreatePrincipalMember(users[1].Id, groups[2].Id),
           PrincipalMember.CreatePrincipalMember(users[1].Id, groups[4].Id),
           PrincipalMember.CreatePrincipalMember(users[1].Id, groups[8].Id),
           PrincipalMember.CreatePrincipalMember(users[1].Id, groups[9].Id),
           PrincipalMember.CreatePrincipalMember(groups[1].Id, roles[0].Id),
           PrincipalMember.CreatePrincipalMember(groups[1].Id, roles[2].Id),
           PrincipalMember.CreatePrincipalMember(groups[2].Id, roles[2].Id),
           PrincipalMember.CreatePrincipalMember(groups[2].Id, roles[3].Id),
           PrincipalMember.CreatePrincipalMember(users[1].Id, roles[4].Id),
           PrincipalMember.CreatePrincipalMember(users[1].Id, roles[8].Id),
           PrincipalMember.CreatePrincipalMember(roles[6].Id, roles[5].Id),
           PrincipalMember.CreatePrincipalMember(users[7].Id, roles[5].Id),
           PrincipalMember.CreatePrincipalMember(users[8].Id, roles[6].Id)
        };
      membershipRepository.CreateMulti(principalMembers);
      uow.Commit();
      // Extension method sample: role 5 contains user 8 and role 6, role 6 contains user 7
      var role5 = roles[5];
      if (role5.GetMembers(PrincipalType.Any, false).Count() == 2 && // no recursion, any type of principal
        role5.GetMembers(PrincipalType.User, false).Count() == 1 && // no recursion, only users
        role5.GetMembers(PrincipalType.User, true).Count() == 2) // recurse, only users
      {
        Console.WriteLine(“PASS – Called typed stored procedure with parameters”);
      }
      else
      {
        Console.WriteLine(“FAIL – Problem with typed stored procedure call”);
      }
    }
    Console.WriteLine(“ALL DONE”);
    Console.Read();
  }
}

Extending the code

One creative way of using the stored procedures is to use extension methods on the entities. See for example the following extension method on the principal entity that uses the stored procedure defined earlier to retrieve it’s members:

public enum PrincipalType
{
    Any = 0,
    User = 1,
    Group = 2,
    Role = 3
}
public static class ExtensionMethodSamples
{
    public static IEnumerable<Principal> GetMembers(this Principal principal, PrincipalType constraintType, bool recurse)
    {
        var principals = new List<Principal>();
        using (var uow = IoC.Resolve<IUnitOfWork>())
        {
            var dt = uow.GetChildPrincipalMembershipsResults(principal.Id, (int)constraintType, recurse).Tables[0];
            foreach (var row in dt.Rows.Cast<DataRow>())
            {
                switch(Convert.ToInt32(row["MemberType"]))
                {
                    case 1:
                        principals.Add(User.MapUser(
                            row.Field<int>(0),
                            row.Field<string>(1),
                            row.Field<string>(3),
                            row.Field<string>(2),
                            row.Field<string>(4),
                            row.Field<DateTime?>(5)));
                        break;
                    case 2:
                        principals.Add(Group.MapGroup(
                               row.Field<int>(0),
                               row.Field<string>(1),
                               row.Field<string>(2)));
                        break;
                    case 3:
                        principals.Add(Role.MapRole(
                               row.Field<int>(0),
                               row.Field<string>(1),
                               row.Field<string>(2)));
                        break;
                    default:
                        break;
                }
            }
        }
        return principals;
    }
    public static IEnumerable<Principal> GetMemberships(this Principal principal, PrincipalType constraintType, bool recurse)
    {
        var principals = new List<Principal>();
        using (var uow = IoC.Resolve<IUnitOfWork>())
        {
            var dt = uow.GetParentPrincipalMembershipsResults(principal.Id, (int)constraintType, recurse).Tables[0];
            foreach (var row in dt.Rows.Cast<DataRow>())
            {
                switch (Convert.ToInt32(row["MemberType"]))
                {
                    case 1:
                        principals.Add(User.MapUser(
                            row.Field<int>(0),
                            row.Field<string>(1),
                            row.Field<string>(3),
                            row.Field<string>(2),
                            row.Field<string>(4),
                            row.Field<DateTime?>(5)));
                        break;
                    case 2:
                        principals.Add(Group.MapGroup(
                               row.Field<int>(0),
                               row.Field<string>(1),
                               row.Field<string>(2)));
                        break;
                    case 3:
                        principals.Add(Role.MapRole(
                               row.Field<int>(0),
                               row.Field<string>(1),
                               row.Field<string>(2)));
                        break;
                    default:
                        break;
                }
            }
        }
        return principals;
    }
}

You could use extension methods to add / remove members as well, or just use the IPrincipalMemberRepository class to create the record directly.

Conclusion

I hope this has been entertaining and that you have found this post useful. I’d love to hear your comments. The entire set of templates, the LLBLGen project file, and the visual studio solution are available for download below. Use the supplied download as you wish, the intent for it is to help you have fun with LLBLGen, the Entity Framework, and the Repository Pattern. [button link="https://skydrive.live.com/redir?resid=30FD0C7F694C1B3F!308&authkey=!AHl-fvb0d-2-93M" color="primary" target="_blank" size="large" title="Code Download" icon_before="download"]Code Download[/button] .net, entity framework, llblgen