Hidden merging of the entities collections

Merge two collection of the entities?

Sometimes in our applications we have problems with the performance of the operations, that are performed on the collections of the entities. Usually the problem is caused by a lot of joins between tables. If we don't want to build big, time consuming queries that return a lot of data, we can hit database a few more times and join the returned collections.

In this case, when we can get some objects in one hit (using Entity Framework) and after that, take the children (objects connected with the navigation properties) using another query. After that we can merge both collections by setting the correct values of the navigation properties in any of those two collections.

Faster solution

However, if we do both of operations using one ObjectContext in one scope, we do not need to set the navigation properties manually. The Entity Framework will do this for us automatically.

In the example below we pass the collection of the entities without connected navigation properties to the method, then we get the id's (or any other identification fields from the entities) and we take the data from the included table with full product data. In this case we do not need to merge the collections. As a result of this method we will receive the collection of ProductSets with attached products with all the full data included.

Example

        public static void AttachDespatchItemsToDespatchPackages(List<ProductSet> listToWhichWeWantToAttachObjects, 
               ObjectContext ctx)
        {
            List<int> productsSetsIds = listToWhichWeWantToAttachObjects
                .SelectMany(d => d.ProductSets.Select(dp => dp.ProductSetId))
                .Distinct()
                .ToList();
            List<Product> productsForProductSet = new List<Product>();
            if (productsSetsIds.Any())
            {
                productsForProductSet = ctx.Products
                    .Include("FullProductData")
                    .Where(p => productsSetsIds.Contains(p.ProductSetId))
                    .ToList();
            }
        }

Read More

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