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

How to get database column property for entity?

Why getting the column property for the entity is a problem?

When we use Entity Framework, we often want to get some information about some columns, that are a part of the database table, which is behind the entity. The most elegant way of getting this information is to use a partial class which contains the decorated (with attributes) properties. It may look like below:

[Column("Description")]
[Required(ErrorMessage = "Description is mandatory!")]
[StringLength(255, MinimumLength = 5, ErrorMessage = "The description must contain more than 5 and less then 255 characters!")]
public string Description{ get; set; }

This solution is the most elegant and easiest to use. You can use e.g. the messages in the higher layers of your application and use the lenght in validation of the objects also on the higher layers of the application. Unfortunatly, it is hard to use this mechanism in the application that has a big database and you have very limited time to make a change.

Quick solution

If you have the problem as stated above, you can use a little bit different solution - get access to the properties by the Reflection. The main idea is to access the properties of the entity by its names and types represented as strings like below:

        public static object GetInfoAboutColumn<TypeOfEntity>(ObjectContext objectContext, 
                              Expression<Func<TypeOfEntity, string>> column, 
                              string typeName, 
                              string propertyName)
        {
            object resultValue = null;
            Type entType = typeof(TypeOfEntity); //we need to know the type of the entity, so we know what we should look for
            string columnName = ((MemberExpression)column.Body).Member.Name; //Get the name of the column (field) in entity
            if (objectContext != null)
            {
               // Get collection of items from the context.
               ReadOnlyCollection<GlobalItem> globalItems = objectContext.MetadataWorkspace.GetItems(DataSpace.CSpace);
               if (globalItems != null)
               {
                 // Get properties of the given type and name from the given entity.
                 var allPropertiesOfType = 
                                 GetAllPropertiesOfType<TypeOfEntity>(typeName, globalItems, columnName, entType);
                 IEnumerable<object> propertyResults = 
                                 allPropertiesOfType.Select(sel => sel.TypeUsage.Facets[propertyName].Value).ToList();
                 if (propertyResults.Any())
                 {
                     resultValue = propertyResults.First(); // Get the value which we were looking for.
                 }
               }
            }
            return resultValue;
        }

The most interesting of this solution is the mechanism of looking for the value, that we are interested in the collection of the entities. It can be done by the LINQ query as below:

        private static IEnumerable<EdmProperty> GetAllPropertiesOfType<TypeOfEntity>(string typeName, 
                                                     ReadOnlyCollection<GlobalItem> globalItems, 
                                                     string columnName, 
                                                     Type entType)
        {
            IEnumerable<EdmProperty> allPropertiesOfType = globalItems
                .Where(m => m.BuiltInTypeKind == BuiltInTypeKind.EntityType)
                .SelectMany(meta => ((EntityType) meta)
                    .Properties
                    .Where(p => p.Name == columnName
                                && p.TypeUsage.EdmType.Name == typeName
                                && p.DeclaringType.Name == entType.Name));
            return allPropertiesOfType;
        }

You can test it for example by trying to get "MaxLength" property of any entity. Cheers!

Read More