Get rid of the duplicated entities that represent the view

The main problem

Lately I was working on a problem, that was really surprising, before I understood it. I was querying the database to get some data using the Entity Framework 4. The entity was a representation of the view, which was joining a few tables. One of the information from the view was the sum of the quantities of the given item in all of the shops.

The view, that I used was more less like this one:

CREATE VIEW [dbo].[TransferItem]
AS
SELECT 
   IT.[ItemId] /*Part of PK*/
  ,IT.[ItemCode] /*Part of PK*/
  ,IT.[Name]
  ,IT.[Barcode]
  ,IT.[Description]
  ,SI.Quantity
  ,SI.ShopId /*This is not a part of PK!*/
FROM (Item IT 
LEFT JOIN ShopInventory SI ON SI.ItemId = IT.ItemId)

Table "Item" contains a lot of different information about the products and the ShopInventory contains the information about the quantities of this product in the different shops. It is important, that in the ShopInventory, there were many rows with the same product and different quantities, each for the different shop, however the ShopId was not a foreign key. For some purposes I needed to use only some of the data from both tables and it was a reason to create the view.

I have created the DTO class called Inventory which was similar to this one:

public class Inventory
    {
        public string ItemCode { get; set; }
        public string Quantity { get; set; }
        ...

        public Inventory(string itemCode, string quantity)
        {
            ItemCode = itemCode;
            Quantity= quantity;
        }
    }

Finally, in my code, I made a query, which was taking the grouped codes of products with a sum of the quantities of the given product in all shops. It looked like this

var products = transferItems.Where(ti => ti != null)
                .GroupBy(i => i.ItemId)
                .Select(g => new Inventory(
                    itemCode: g.First().ItemCode,
                    onHand: g.Sum(s => s.Quantity).ToString())
                ).ToList();

I was sure, that it should work, but unfortunately, when I run the unit test, I've found out that something was terribly wrong: for each product code I received the quantity, which was a multiplication of the number of shops in which the product was and the quantity of it in the first, found a shop.

Example: Item A was in a shop S1 (5 items), shop S2 (3 items) and shop S3 (9 items) so as a result of the query I should have had received 17 items, but I have received 15 items.

Firstly, it was amazing, but then I've found out, that the Entity Framework sees all three products as exactly the same entity because the primary key came only from the "Item" table and the ShopId was not a part of the PK. In this case EF did not look on the ShopId as a key so each row with the same ItemId was also the same entity, which caused a confusion of the EF and the described result of the query.

The solution

As I couldn't replace the ShopId with the primary key of the ShopInventory table, I had to add another unique column. In this case the easiest thing was to add a new column which looked like this:

ROW_NUMBER() OVER(ORDER BY IT.[ItemId] ASC) AS RowNo

This new column became a part of the view entity PK and the rows became unique. The view looked like below:

CREATE VIEW [dbo].[TransferItem]
AS
SELECT 
   ROW_NUMBER() OVER(ORDER BY IT.[ItemId] ASC) AS RowNo
  ,IT.[ItemId]
  ,IT.[ItemCode]
  ,IT.[Name]
  ,IT.[Barcode]
  ,IT.[Description]
  ,SI.Quantity
  ,SI.ShopId /*This is not a FK!*/
FROM (Item IT 
LEFT JOIN ShopInventory SI ON SI.ItemId = IT.ItemId)

And that's all. Using this simple trick will give you a fake PK from the DB perspective, but the rows in the entities collection will be unique.

Read More

How to set Bootstrap menu active class using ASP.NET MVC ?

Bootstrap framework is currently the most popular framework (or rather a set of themes and controls) which makes creating of the responsive websites very easy. Unfortunately, some effects on the website are not easy to reach in caseyou want to create a ASP.NET MVC web application using the partial views. One of them is setting of the specific style on the Bootstrap navbar "li" active element.

You can obviously achieve the desired style on the active "li" element of the navbar by using a Javascript function which would check what page was loaded by the address (or e.g. the content) and set the class on the element, but it is complicated and not very elegant solution.

The best way, to achieve the mentioned effect is to use the extended MenuLink method on the ASP.NET backend of the application.

Changes on the UI

Firstly, we need to use a MenuLink in the Bootstrap menu. so it would look like more-less like this:

 

            

                @Html.MenuLink("Suppliers", "Index", "Supplier")
                @Html.MenuLink("Customers", "Index", "Customer")
		@Html.MenuLink("Orders", "Index", "Order")
	    
 

In the example I have replaced ActionLinks with Menu links. Without backend changes it won't give us a result, but now we will add an Extension method o the backend.

Changes on the backend

Lets create a new class, which will hold all of the helpers, which may be used on the UI directly. In case of our problem, we will an extension method for the MVC MenuLink. The code of the method is below.

using System.Web.Mvc;
using System.Web.Mvc.Html;
using System.Web.Routing;

public static class HtmlHelpers
{
    //Extensibl method MenuLink
    public static MvcHtmlString MenuLink(this HtmlHelper htmlHelper, string linkText, string actionName, string controllerName)
    {
        //Create a tag, that will be an action link inside a "li" element
        TagBuilder builder = new TagBuilder("li")
        {
            InnerHtml = htmlHelper.ActionLink(linkText, actionName, controllerName).ToHtmlString()
        };
        //Get current routes from the context of the view.
        RouteData route = htmlHelper.ViewContext.RouteData;
        //Gets the controller and the action from the route data
        string action = route.GetRequiredString("action");
        string controller = route.GetRequiredString("controller");
        //If the current controller and action are the same as the element on the navbar menu,
        //then we need to add the class "active" on the tag.
        if (controllerName == controller && actionName == action)
        {
            builder.AddCssClass("active");
        }
        return new MvcHtmlString(builder.ToString());
    }
}

This simple and elegant solution will make application niecer and the management will be much easier. You won't need to touch the mechanism of setting of the Bootstrap active classes any more.

Read More