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.

Updating the Model Object from the Form Post Variables

We were having trouble controlling the form post variables that are used in MVC to update our model.  How do you know which fields for your model have been updated and which fields are just defaulted to null on an action?  One option is to have each of the fields being updated as a parameter to the action, but this is messy and requires a lot of manual intervention to setup your model again.

The best solution we’ve found is to use the form collection.  This post by Scott Gu on the MVC Beta release overviews how it can be done.  I’m going to go into a little more detail specific to how that might be useful.

This method is fantastic for allowing you to specify which fields have changed in the post and updating the model object like so.

public ActionResult Add(FormCollection form)

{

    ItemMaster itemMaster = new ItemMaster();

    try

    {

        TryUpdateModel(itemMaster, form);

        List<ValidationErrorResult> results = _ItemRepository.Add(itemMaster.Item);

        if (results.Count > 0)

        {

            // TODO: setup the validation failures.

            return View(typeof(Views.en.StockItemMaster.Index), itemMaster);

        }

        else

        {

            return RedirectToAction("Index/" + itemMaster.Item.Name.ToString());

        }

    }

    catch (Exception e)

    {

        HandleIt.CatchTheException(e);

        return (View(typeof(Views.en.StockItemMaster.Index), itemMaster));

    }

}

On MVC View all you need to do is specify the input fields that are to be used to update the model and the item will be updated for you.  In my case the ItemMaster is my model object for a strongly typed view.  My form looks something like this:

<table class="TableFullWidth">

<tr>

    <td class="ColumnRightAlign">

        <label class="FieldLabel" for="Item.Name">

            Part No.</label>

    </td>

    <td>

        <input name="Item.Name" style="width: 75px" value="<%= ViewData.Eval("Item.Name") %>"

            FocusOnMe="true" <%= ViewData.Eval("ItemKeyFieldSearch") %> />

    </td>

    <td colspan="4">

        <input name="Item.Description" style="width: 275px" value="<%= ViewData.Eval("Item.Description") %>" />

    </td>

</tr>

Note: See we’re not using HTML Helpers for this view.  There is a good reason for that which I’m not going into in this post.

The input tags are wrapped up in a form with the action set to the Add method.  When I call TryUpdateModel the model will update and the ModelState will be populated with the exceptions.  The following test is a great example.

FormCollection form = new FormCollection();

form.Add("Item.ItemCodeID", "string");

ResetController();

ViewResult result = (ViewResult)_Target.Save(_TestItems[0].ItemCodeID, form);

Assert.AreEqual("StockItemMaster/Index", result.ViewName);

Assert.AreEqual(_TestItems[0].Name, ((ItemMaster)result.ViewData.Model).Item.Name);

This is a problem that because the ItemCodeID field is an integer field and I’ve set a string into the form post variable.  If I breakpoint my code and investigate the result you can see the error:

You can see the error message noting that the value “string” for your form is invalid.  If we were to use UpdateModel instead of TryUpdateModel, we would be catching an exception for each of these errors. 

It’s worth noting that UpdateModel is not a very good idea because if you’re dealing with the Entity Framework then your EntityKey field is going to cause an error every time unless you happen to post a form variable with the key in it. 

You can now use the ModelState in your view to display all your validation errors specific to the database or data model you’re using.