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.