Interview Questions for Programmers by MK #7

Home

Question #7
You have the following code that uses Entity Framework to retrieve data from Northwind database. Firstly it finds customers that are from London and then process their orders. All data model classes were generated with the code first from database approach. Unfortunately, this code contains a bug that can lead to performance problems. Identity this problem and propose a fix.

using (var ctx = new NorthwindContext())
{
   var londoners = ctx.Customers.Where(e => e.City == "London");
   foreach (var londoner in londoners)
   {
      foreach (var o in londoner.Orders)
      {
         foreach (var d in o.OrderDetails)
         {
            //....
         }
      }
   }               
}

Answer #7
This code is a classical example of N+1 select problem where too many queries are sent to a database. The first query will be sent to a database in order to find customers from London. Then for each customer another query will be sent to read orders. Finally, for each order another query will be sent to retrieve details of a given order. Instead, all data could be retrieved by sending only one query. To do so we need to tell EF that we want to read customers together with their orders and orders details. It can be achieved with Include method.

using (var ctx = new NorthwindContext())
{
   londoners = ctx.Customers.Include("Orders").Include("Orders.Order_Details").Where(e => e.City == "London");
   ...
}

A quick test will show that originally 53 queries are sent to a database and after a fix only 1.

Report from the battlefield #3 - IEnumerable vs IQueryable

Home

Sometime ago I was reviewing the data access layer that was based on Entity Framework. I found a code which immediately attracted my attention. The simplified version is shown below.

public IList<Product> GetAll()
{
   return ctx.Products.Select(p => new Product() { ... }).ToList(); 
}
...
var numberOfProducts = GetAll().Count();

GetAll method is pretty simple because it just reads products from a database. The result returned from this method is used to count number of products in the database. Although it is simple it contains a serious bug. The problem is that it uses ToList method to return a list of products. It causes that ALL products must be retrieved from the database in order to return them in the form of a list. In other words there is no deferred execution here.

If we work with a local database and the number of products is small it shouldn't be a problem. However, this kind of code might lead to difficult to analyse performance problems. For example if our application uses a remote database and/or there are thousands of products. The desired behaviour is that products are counted by a database engine. So let's try to make a fix:

public IEnumerable<Product> GetAll()
{
   return ctx.Products.Select(p => new Product() { ... });
}
...
var numberOfProducts = GetAll().Count();

Now it looks much more better, doesn't it? GetAll doesn't use ToList and returns IEnumerable. interface. Unfortunately this solution is far from being perfect. In comparison to the first version, the only difference is the moment when all products are retrieved from the database. This time it will happen when Count method is executed. Why? Before I'll explain let's see the correct solution:

public IQueryable<Product> GetAll()
{
   return ctx.Products.Select(p => new Product() { ... });
}
...
var numberOfProducts = GetAll().Count();

This time I used IQueryable instead of IEnumerable. This small change is crucial. It causes that no products are read from a database. Entity Framework "sees" that we only wanto to count number of products and an appropriate query is sent to a database. In other words LINQ To Entities is used.

The situation is completely different when we work with IEnumerable. In order to understand a difference we have to realise one thing. Count method for IEnumerable is something different than Count method for IQueryable. With IEnumerable we use LINQ To Objects and LINQ To Objects operates on objects in memory, it cannot communicate with a database. It is why all products must be read from a database if we want to count them.

Now someone inquiring can say that for virtual methods it shouldn't matter if we have variables of type IEnumerable or of type IQueryable if these variables points the same objects. After all C# is an object oriented language that supports polymorphism etc. Well, it is all true but only for virtual methods and Count is not a virtual method. It is an extension method and extension methods don't support polymorphism.

Sandbox Database Manager

Home

My colleague Tomasz Moska published very nice tool that makes management of development MSSQL sandbox databases very easy. It is called Sandbox Database Manager and you can download it here or from GitHub.

Why is it worth recommending? Try to imagine yourself situation like this. A tester found a bug in the application. In order to reproduce it you need a copy of his database from a system test environment. With Sandbox Database Manager you can make a copy of this database and restore it on a selected server with just a few clicks. Another click or two and you have a snapshot created. Thanks to that you are be able to revert the database to its original state at any time. Now let's assume that this database contains hundreds of tables and you don't know all of them. To investigate a problem you want to run an application and see which tables (probably dozens of them) will be updated and how. Sandbox Database Manager also supports this scenario because it'll allow you to track data changes at the column level.

These are only a few features of Sandbox Database Manager. It can do much more, for example to run the same query against many databases or compare data between two databases. I can guarantee that Sandbox Database Manager is a really, really helpful tool because I use it in my day to day work. I recommend it without any hesitation. What is the best you can use it completely for free!

Report from the battlefield #2 - amount of data matters a lot

Home

In the next post from the Report from the battlefield series I'll wrote about a serious mistake that is quite common according to my experience. I'm thinking about a situation when a developer assumes that all data from a database can be processed on the client side. I'll give you 2 examples that I encountered during my reviews.

Case 1

A developer was asked to implement the paging functionality. He created a single page Web application. It looked nice and the paging was working correctly at first glance. I decided to check how it was implemented under the hood. I examined a web service that was used by the application and I was surprised. Why? I didn't find a web method responsible for returning pages. The next step was to dig into a java script code. Unfortunately, I discovered that the paging was implemented only on the client side i.e. the application initially downloaded all data from a database (via Web Service).

Case 2

In the another project the paging was implemented correctly on the server side but a developer made a more subtle mistake. An application had a shopping cart function. Of course it was possible to add and remove products to and from a cart. To do so a web service used by the application had a method GetCart. This method was responsible for retrieving a current content of a cart from a database.

However, it was strange that this method returned only identifiers of products. What's more there was no GetProductDetails web method. It made me curious how the application displays products details to users only knowing its identifiers. It turned out that at the initiation the application was reading details of all products from a database. Having all products on the client side it was easy to find details of a product based on its identifier.

Summary

In both cases applications were fast enough because of a small amount of data. In the case of real-life databases they will not. I think that we should always be prepared for the worst case. Especially, when we participate in a recruitment and we want to show ourselves from the best side. An evaluator shouldn't guess whether we know something or not.

Merry Christmas!

Home

Source: own resources, Authors: Agnieszka and Michał Komorowscy

Giving wishes in a foreign language can be challenging so my wishes will be simple but very sincere. I wish you a Merry, Peaceful Christmas and an Amazing 2016. Let it be at least as good as the past year.

Best wishes,
Michał Komorowski