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

varchar vs nvarchar

What are the differences between varchar and nvarchar in Microsoft SQL Server? Question quite common on the recruitment tests, but the question: when and why we should use this one or this one is not so simple.

First, let’s take a look what are varchar and nvarchar:

• nvarchar- variable-length, it takes more space than varchar, can store any Unicode data, dependent on the database collation,
• varchar - restricted to an 8-bit codepage (but not in every collation), single-byte character data, it takes less space than nvarchar, it cannot contain Unicode characters, the database collation determines which code page the data is using.

The next question which we should ask ourselves is: when we should use varchar and in which situations we should use nvarchar ?

nvarchar: - When your application need to be translated to e.g. Arabic, Mandarin, Hebrew, Japanese or other languages with 16-bit encoding, or at least there is a big possibility that it will be translated in the future. - When your application will use very small database, you can use nvarchar (for convenience) for every text column. - When you do care more about convenience than disk space, memory and effectiveness.

varchar: - When you will NEVER use Unicode. - When performance and effectiveness is important for you. - When you want to save some space and memory.

Before choosing one of those two types remember about the following things:

• Changing the data type from varchar to nvarchar and vice versa is very expensive, especially if the project is in a later phase.
• When system has a big data base and the effectiveness is important, consider using varchar – it is faster.
• You should not do this, but … if you make JOIN on the columns with varchar and nvarchar, then there is a big performance hit.
• When you are using nvarchar in stored procedures or scripts use prefix N (e.g. DECLARE @name = N'The name';) which prevent string type conversions at runtime.

Summarizing: choice between varchar and nvarchar depends on what type of application you are writing and what is important for you (effectiveness or convenience).

Read More