Testable LINQ to Entities with Table Joins

I’m enjoying LINQ to Entities and the Entity Framework, but sometimes it feels like someone forgot to write up the documentation.  It’s nice to have the product, but it can be very hard to use without adequate training or documentation.  But I suppose it’s very new and I’ll just have to wait for more people to jump on board.

In the mean time, I came across a problem.  We are following the repository model design pattern in our data access objects and when using LINQ to Entities we define the table that the model is concerned with as a private member from the Entity Model.  This allows us to use TDD and override this private member with our own queryable datasets for testing.

So your data repository will look something like this:

public class SecurityUserRepository : ISecurityUserRepository

{

    private IQueryable<rfSysAction> _rfSysActionData;

    private MyEntities _Entities;

    public SecurityUserRepository()

    {

        _Entities = new MyEntities();

        _rfSysActionData = _Entities.rfSysAction;

    }

    public SecurityUserRepository(IQueryable<rfSysAction> sysAction)

    {

        _rfSysActionData = sysAction;

    }

}

Nice code, easy to test, but what if I want to join several tables together to get a set of data back?  For example, my user is part of several security groups, my security groups are linked to my MVC actions and my actions are defined in a table specifying their names. 

Well it’s actually not that hard.

Most often when trying to write this join you’ll get examples that make you reference the entity model to get all the tables you want to join.  This works fine, but it’s not good for me because I have only mocked up a copy of my root object.

Then I found this great example showing how it can be done.  So if I want to return an action table record for a specific action, controller and user my LINQ should look like this:

IQueryable<rfSysAction> query = 

    from sa in _rfSysActionData

        from sas in sa.rfSysActionSecurity

        from usg in sas.rfSecurityGroup.UserSecurityGroup

    where usg.UserID == userIdparam 

        && sa.Controller == controller

        && sa.Name == action

    select sa;

This query will find all security actions and then limit the selection to those for the user, controller and action specified.  This is what my model looks like for the tables in the above query:

The code is simple and makes sense when you know what to do, but now that I have it, how do I test it.  With rfSysAction as my base table, all I have to do is mock this up and add in the child table data like this:

private IQueryable<rfSysAction> GetTestUserSecurityGroupData()

{

    List<rfSysAction> data = new List<rfSysAction>

    {

        new rfSysAction()

        {

            ActionID = 1,

            Controller = "controllera",

            Name = "actiona",

            rfSysActionSecurity = new EntityCollection<rfSysActionSecurity>()

            {

                new rfSysActionSecurity()

                {

                    rfSecurityGroup = new rfSecurityGroup()

                    {

                        Name = "groupa",

                        UserSecurityGroup = new EntityCollection<UserSecurityGroup>()

                        {

                            new UserSecurityGroup()

                            {

                                UserID = 1

                            }

                        }

                    }

                }

            }

        }

    };

    return data.AsQueryable();

}

And then use the override constructor to set the data:

ISecurityRepository target = new SecurityRepository(GetTestUserSecurityData());

There you have it, testable LINQ to Entities and I haven’t had to overwrite several table object.