Testable Left Outer Join in LINQ to Entities

Recently I posted on Testable LINQ to Entities using the repository model and doing table joins using your query items.  I’ve found that while this works great for inner joined table queries it doesn’t work for left outer joins, in fact doing a left outer join in LINQ to Entities is quiet hard entirely.

So in my example, here is the SQL used to retrieve the data.

SELECT * FROM

Item

    LEFT OUTER JOIN ItemLocation il

        on Item.ItemCodeID = il.ItemCodeID

    LEFT OUTER JOIN WarehouseLocation wl

        on il.WarehouseLocationID = wl.WarehouseLocationID

    LEFT OUTER JOIN Warehouse w

        on wl.WarehouseID = w.WarehouseID

    LEFT OUTER JOIN Site s

        on w.SiteID = s.SiteID

In LINQ I can do this far more simply:

from item in _ItemData

select item

But when I try and look at the resulting item.ItemLocation it returns an empty list.  When using LINQ to Entities it will only load the data model for items you tell it to load for, in this case I asked for Item, but not all the ItemLocations as well.  This is shown in the screenshot below, the array of item locations has no items even though they are in the database.

I want the item to return even though I don’t necessarily have any locations, and I want to be able to get the site.

Well here is how you do a left outer join in LINQ to Entities:

var results = (from item in _DataSource

               where item.Name.Equals(name)

               // Left outer join into the item locations, warehouses and sites to calculate the data.

               let itemLoc = (from itemLocLeftOuter in item.ItemLocation

                              select new

                              {

                                  itemLocLeftOuter,

                                  itemLocLeftOuter.WarehouseLocation,

                                  itemLocLeftOuter.WarehouseLocation.Warehouse,

                                  itemLocLeftOuter.WarehouseLocation.Warehouse.Site

                              })

               select new

               {

                   item,

                   item.Class,

                   item.ItemLocation,

                   itemLoc

               }).FirstOrDefault();

Item item = results.item;

I have to join into the ItemLocations because for each item there can be many locations.  But selecting all the data I need, including joined data, my resulting item will have all it’s dependencies loaded from the database.  The screenshot of the watch below shows that my item.ItemLocation holds all the joined items now.

 

As with my previous post on testable LINQ to Entities, this code is completely testable and works very well within the repository pattern.

Unit Testing the UpdateModel method in ASP.NET MVC by Faking the Controller Context

One thing that’s not immediately obvious is that the UpdateModel method in ASP.NET MVC Beta requires a controller context to work.  In fact if you canll UpdateModel without controller context then you’re going to get an error like this:

threw exception:  System.ArgumentNullException: Value cannot be null.  Parameter name: controllerContext.

A quick search found that you need to fake the controller context to be able to unit test with UpdateModel, something Scott Gu doesn’t cover in his blog.  Never mind, Scott Hanselman to the rescue!  You will need to fake your controller context to get this to work correctly.  On Scott Hansleman’s blog he creates a MvcMockHelper class designed to mock certain aspects of the MVC environment, including the controller context.

This is the MvcMockHelpers class from Scott’s blog.

using System;

using System.Web;

using Rhino.Mocks;

using System.Text.RegularExpressions;

using System.IO;

using System.Collections.Specialized;

using System.Web.Mvc;

using System.Web.Routing;

namespace UnitTests

{

    public static class MvcMockHelpers

    {

        public static HttpContextBase FakeHttpContext(this MockRepository mocks)

        {

            HttpContextBase context = mocks.PartialMock<httpcontextbase>();

            HttpRequestBase request = mocks.PartialMock<httprequestbase>();

            HttpResponseBase response = mocks.PartialMock<httpresponsebase>();

            HttpSessionStateBase session = mocks.PartialMock<httpsessionstatebase>();

            HttpServerUtilityBase server = mocks.PartialMock<httpserverutilitybase>();

            SetupResult.For(context.Request).Return(request);

            SetupResult.For(context.Response).Return(response);

            SetupResult.For(context.Session).Return(session);

            SetupResult.For(context.Server).Return(server);

            mocks.Replay(context);

            return context;

        }

        public static HttpContextBase FakeHttpContext(this MockRepository mocks, string url)

        {

            HttpContextBase context = FakeHttpContext(mocks);

            context.Request.SetupRequestUrl(url);

            return context;

        }

        public static void SetFakeControllerContext(this MockRepository mocks, Controller controller)

        {

            var httpContext = mocks.FakeHttpContext();

            ControllerContext context = new ControllerContext(new RequestContext(httpContext, new RouteData()), controller);

            controller.ControllerContext = context;

        }

        static string GetUrlFileName(string url)

        {

            if (url.Contains("?"))

                return url.Substring(0, url.IndexOf("?"));

            else

                return url;

        }

        static NameValueCollection GetQueryStringParameters(string url)

        {

            if (url.Contains("?"))

            {

                NameValueCollection parameters = new NameValueCollection();

                string[] parts = url.Split("?".ToCharArray());

                string[] keys = parts[1].Split("&".ToCharArray());

                foreach (string key in keys)

                {

                    string[] part = key.Split("=".ToCharArray());

                    parameters.Add(part[0], part[1]);

                }

                return parameters;

            }

            else

            {

                return null;

            }

        }

        public static void SetHttpMethodResult(this HttpRequestBase request, string httpMethod)

        {

            SetupResult.For(request.HttpMethod).Return(httpMethod);

        }

        public static void SetupRequestUrl(this HttpRequestBase request, string url)

        {

            if (url == null)

                throw new ArgumentNullException("url");

            if (!url.StartsWith("~/"))

                throw new ArgumentException("Sorry, we expect a virtual url starting with \"~/\".");

            SetupResult.For(request.QueryString).Return(GetQueryStringParameters(url));

            SetupResult.For(request.AppRelativeCurrentExecutionFilePath).Return(GetUrlFileName(url));

            SetupResult.For(request.PathInfo).Return(string.Empty);

        }

    }

}

Now all I need to do is

_Mocks = new MockRepository();

_ItemRepository = _Mocks.StrictMock<IItemRepository>();

SetupTestData(_Mocks, _ItemRepository);

_Target = new StockItemMasterController(_ItemRepository);

MvcMockHelpers.SetFakeControllerContext(_Mocks, _Target);

Set the controller context to the faked context and my tests will all start magically working.

Mocking Void Methods with Rhino Mocks

I’ve finally found a need to mock a method that has a void return option.  Here is how to do it with Rhino Mocks.

// Setup the delete methods

Expect.Call(delegate { items.Delete(_TestItems[0].Item); })

    .Throw(new System.Data.ConstraintException("duplicate row"))

    .Repeat.AtLeastOnce();

You can also have it return nothing and now throw any exceptions like so.

Expect.Call(delegate { items.Delete(_TestItems[1].Item); })

    .Repeat.AtLeastOnce();

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.