Introduction

This is the first article from the series about how to improve your .NET application which uses Entity Framework. I will show you some simple tips that will boost your application. I hope that many advices you will find here can be used in any other ORM or even any other programming language. First of all let’s see how the majority of applications work.

Figure 1. Simplified model modern applications

As we can see there are many client (devices) connected to application store in server. The application server communicates with data base server. Application server is a very fast machine prepared to handle many requests from clients and to communicate with a database server. What might be interesting is the fact that for most of the time server waits for the response from a database. The main aim of this series is to reduce and optimize communication between server application and database in order to use them both more efficiently. At the beginning we should start using an asynchronous call to database (and of course any other data source). Then the thread is not held up by waiting for the response but it can start to handle another request.

Let’s get some data.

Let’s imagine we have to prepare some simple report that informs what kind of bikes are finished and are waiting in a warehouse, with the quantity lower than let’s say 50. We need their names, categories and of course the quantity itself.

First of all we need to create entity models:

public class Product
{
[Key]
public int ProductID { get; set; }
public int ProductSubcategoryID { get; set; }
public int DaysToManufacture { get; set; }
public short SafetyStockLevel { get; set; }
public short ReorderPoint { get; set; }
public string ProductNumber { get; set; }
public decimal StandardCost { get; set; }
public decimal ListPrice { get; set; }
public string Color { get; set; }
public string Name { get; set; }
public bool MakeFlag { get; set; }
public bool FinishedGoodsFlag { get; set; }
public DateTime SellStartDate { get; set; }
public DateTime ModifiedDate { get; set; }
public ProductSubcategory ProductSubcategory { get; set; }
public ProductInventory ProductInventory { get; set; }
}public class ProductSubcategory
{
[Key]
public int ProductSubcategoryID { get; set; }
public int ProductCategoryID { get; set; }
public string Name { get; set; }
public DateTime ModifiedDate { get; set; }
public ProductCategory ProductCategory { get; set; }
public ICollection<Product> Products { get; set; }
}public class ProductCategory
{
[Key]
public int ProductCategoryID { get; set; }
public string Name { get; set; }
public Guid rowguid { get; set; }
public DateTime ModifiedDate { get; set; }
}public class ProductInventory
{
public int ProductID { get; set; }
public short LocationID { get; set; }
public string Shelf { get; set; }
public byte Bin { get; set; }
public short Quantity { get; set; }
public Guid rowguid { get; set; }
public DateTime ModifiedDate { get; set; }
public Location Location { get; set; }
}public class Location
{
[Key]
public short LocationID { get; set; }
public string Name { get; set; }
public decimal CostRate { get; set; }
public decimal Availability { get; set; }
public DateTime ModifiedDate { get; set; }
}

Now we can create a database context:

public class ProdcutionContext : DbContext
{
 public DbSet<Product> Product { get; set; }
 public DbSet<ProductSubcategory> ProductSubcategory { get; set; }
 public DbSet<ProductCategory> ProductCategory { get; set; }
 public DbSet<ProductInventory> ProductInventory { get; set; }
 public DbSet<Location> Location { get; set; }protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
 {
  optionsBuilder.UseSqlServer(@"Server=.\SQLEXPRESS;Database=AdventureWorks2017;")
  .EnableSensitiveDataLogging(true);
 }protected override void OnModelCreating(ModelBuilder modelBuilder)
 {
  modelBuilder.HasDefaultSchema("Production");
  modelBuilder.Entity<ProductInventory>()
   .HasKey(o => new { o.LocationID, o.ProductID });
 }
}

Now we are ready to prepare data:

using (var context = new ProdcutionContext())
{
 var data = await context.Product
  .Include(x => x.ProductSubcategory)
  .Include(x => x.ProductSubcategory).ThenInclude(x => x.ProductCategory)
  .Include(x => x.ProductInventory)
  .Include(x => x.ProductInventory).ThenInclude(x => x.Location)
  .Where(x => x.ProductSubcategory.ProductCategory.Name.Equals("Bikes"))
  .Where(x => x.ProductInventory.Location.Name.Equals("Finished Goods Storage"))
  .Where(x => x.ProductInventory.Quantity < 50)
  .OrderBy(x => x.ProductSubcategory.Name)
  .ThenBy(x => x.Name)
  .ThenBy(x => x.ProductInventory.Quantity)
  .ToListAsync();foreach (var item in data)
 {
  Console.WriteLine($"{item.ProductSubcategory.Name} - {item.Name} - {item.ProductInventory.Quantity}");
 }
}

After running the program we get:

It looks good, let’s see what has happened in details. To check what was going on I used SQL Server Profiler:

Look how much data has been read. This is on of the most important issues which developers forget about when using any ORM. In short, when I ask a developer to prepare the list of all student names using SQL I will probably get something like:

select firstName from student;

But If ask about it in ORM like Entity Framework, I would get:

context.Students.ToList()

which, of course, gets all columns from the table. In the example above, all the columns from all the tables on the basis of which SQL query is created, are taken from the database.

Solution

The best way to fix the problem and reduce the amount of data received from server is to use the projection properly. There are two solutions. One is to use an anonymous class and the other is to prepare a special class. Let’s call it as view model. Both are good. You can use anonymous class when you do not need to pass the data forward. In most cases the best way is to prepare the class as follows:

public class StockProductViewModel
{
 public string Subcategory { get; internal set; }
 public string Product { get; internal set; }
 public short Quantity { get; internal set; }
}

Then we can modify our code retrieving data:

using (var context = new ProdcutionContext())
{
 var data = await context.Product
  .Include(x => x.ProductSubcategory)
  .Include(x => x.ProductSubcategory).ThenInclude(x => x.ProductCategory)
  .Include(x => x.ProductInventory)
  .Include(x => x.ProductInventory).ThenInclude(x => x.Location)
  .Where(x => x.ProductSubcategory.ProductCategory.Name.Equals("Bikes"))
  .Where(x => x.ProductInventory.Location.Name.Equals("Finished Goods Storage"))
  .Where(x => x.ProductInventory.Quantity < 50)
  .OrderBy(x => x.ProductSubcategory.Name)
  .ThenBy(x => x.Name)
  .ThenBy(x => x.ProductInventory.Quantity)
  .Select(x => new StockProductViewModel{
   Subcategory = x.ProductSubcategory.Name,
   Product = x.Name,
   Quantity = x.ProductInventory.Quantity
  })
  .ToListAsync();foreach (var item in data)
 {
  Console.WriteLine($"{item.Subcategory} - {item.Product} - {item.Quantity}");
 }
}

Now we can check which SQL queries are generated when we start our new code:

As we can see we get only the data we need. What is worth noticing is that fewer readings were made and it all took less time.

Summary

One of the problems with using ORM like Entity Framework is that one forgets about the proper use of projection. When we write a query in SQL we avoid using “select *“ but when we use ORM we forget about not using “select *”. To avoid the problem we should properly define “Select” section. In Entity Framework we have two solutions: either to create abstract or concrete classes that define only the fields we need.