Programming tips: Entity Framework (any ORM) is NOT the GOD of Data! You need to know how to work with it

Introduction

In my professional life, I've worked with different ORMs and of course the best ones were Hibernate and Entity Framework. They will save almost half of the time that you had to spend for your software by handling the requests to DB so you just need to know how to work with objects instead of relational data. However, they are no gods! They will do the same thing that you are telling them, They cannot work miracles because they don't know what is your intention! They are simply some software systems!

The problem

A simple question can result to the answer. Take an individual who knows Object Oriented and give him this classes.

Code:

class A

{

 public string Status{get;set;}

 public List<B> Bs{get;set;}

}



class B

{

 public string Status{get;set;}

 public List<C> Cs{get;set;}

}



class C

{

 public string Status{get;set;}

 public string Name{get;set;}

 }

Now ask him to give you name of cs that are in status 'BLBlah' he can find in B with Status 'Blah' and are in As with Status 'BlahBlah'. You will end up with a code like this:

Code:

var results = new List<string>();

Var selectedAs = context.As.Where(a=> a.Status== "BlahBlah" );

foreach(a in selectedAs)

{

     var selectedBs = a.Bs.Where(b=>b.Status=="Blah");

     foreach(b in selectedBs)

    {

      var selectedNames = b.Cs.Where(c=>c.Status=="BLBlah").Select(c=>c.Name).ToList();

       results.AddRange(selectedNames);

     }

}

return results;

Pretty straight forward, huh? He rocks! and the ORM rocks! Right?!
Well he just added a lot of overhead to your DB!

What is wrong with this code?

The way you've asked your query! Lets say there are 100 As with wanted status and for each of them you have 100 Bs with wanted status.
You've asked your ORM to find those 100 A, then for each of them you've asked it to find Bs and then for each one find Cs.
It is completely fine if you are working with your memory, but this code means :
1 call for As + (100 calls for Bs * 100 Call for Cs * (1 projection + addrange)) = 100001

So you've sent 10,001 requests to DB for a simple 3 layer select! How fast can it be?!!!

Lets See another Code

So now you might say that it is because of the foreachs I wrote, but that is not the case.
Lets say I want to convert the structure above to this one and then use it somewhere.

Code:

Calss ConvertedA

{

 public string Status{get;set;}

 public List<string> BStatuses{get;set;}


public List<string> CNames{get;set;}


}

With simple Object Oriented view you will probably end up with this code (With no foreach):

Code:

List<ConvertedA> GetConvertedList()

{

  var As= context.As;

  return context.As.Select(a=> new ConvertedA()

  {

   Status = a.Status,

  BStatuses = a.Bs.Select(b=>b.Status).ToList()

  CNames= a.Bs.SelectMany(b=>b.Cs).Select(c=>c.Name)

  };

}

OMG! What a wonderful query! right?! So Simple! But again, you are sending lots of requests to DB! why?
1  selectAs+ 100 Select Bs+ 100Bs*100Cs = 10,101 requests!

The Solution

There are 2 solutions to this problem. One is from the view of Software Architect, and the other from the view of programmer.

The programmer

Write the best query
As Mahdi Hasheminejad in the comments, there are many cases that you can select the correct data with one query. It is pretty useful and of course is the best way to solve the problem. As he mentioned, the query can be written like this:

var results = context.As
.Where(a => a.Status == "BlahBlah").SelectMany(a => a.Bs)
.Where(b => b.Status == "Blah").SelectMany(b => b.Cs)
.Where(c => c.Status == "BLBlah");

And the result will be translated to this query:
SELECT
[Extent3].[Status] AS [Status],
[Extent3].[Name] AS [Name],
FROM [A] AS [Extent1]
INNER JOIN [B] AS [Extent2] ON ...
INNER JOIN [C] AS [Extent3] ON ...
WHERE (N'BlahBlah' = [Extent1].[Status]) AND (N'Blah' = [Extent2].[Status]) AND (N'BLBlah' = [Extent3].[Status])

Load needed data in memory
If you cannot handle your request with a good query, load your data first! A good example for this case is when you need to compare something with the result of something out of your DB, like when you need to call a service.
Like for the first example, you can say:
var Cs = context.Cs.Where(c=>c.Stauts="Blah").ToList();
Var Bs = context.Bs..Where(b=>b.Status=="Blah").ToList();

Now use these 2 lists inside your foreach and compare them with their Ids.


The Architect

ORMs simply map tables to related objects in memory. But, there are 2 ways of  working with relations. Lazy loading (which is the default in most ORMS) and Eager Loading.
Lazy loading simply means that ORM will wait for you to ask for something, and then it will load the data. For instance :
var a = context.As.First();
This will only load 1 A object from the memory and nothing more.
Now if you write:
Var bs = a.Bs.ToList()
Your ORM will send another request to fetch the Bs.
This is exactly what most codes needs. But in some cases, we know that the a is not usable without their Bs. So the architect can decide to use Eager loading for that relation. So when you say
var a = context.As.First();

Your ORM will retries your A and all Bs that are related to it.

*The Eager loading, is not a good solution for 90% of the times. It depends to nature of your data! So don't use it perfunctory.

Advanced Programming: An easy way to manage critical zones by locking

So, sometimes people might ask what is the critical zone, or why shall we manage it?
Well, on multi-thread apps like web applications, it is possible that a thread leaves a method and go to sleep and another thread change something that has an impact on those values.
The simplest example that I can come up is an "IncreaseAndSave" method like this:

Code:

void IncreaseAndSave()
{
     var value= ReadValueFromDB();
     Value++;
     // The thread1 goes to sleep
      SaveValueInDB(Value);
}

Simple scenario:
Lets say we have a value "10" in our DB and 2 threads that call our method almost at the same time. The first one will try to run the code, it reads the value from DB and increase it so Value will be "11" in our memory. Then the thread will go to sleep and thread2 will run the process. It will read value "10" again from DB since T1 didn't save it and will increase it to "11". Now both threads will call save method and we will end up with "11" instead of "12".
So the zone that we call a critical zone is from reading to saving Data in the DB.

Simplest Solution
Well there is a reserved word in C# (and almost all other programming languages) for lock. The functionality is very simple. it is like a safe box. When you lock it, no one else can enter or lock it until you leave the safe box and then someone else can take over.
The only important note is that you have to lock the same object :D (safe box)

Code:

void IncreaseAndSave()
{
     lock(_object)
     {
      var value= ReadValueFromDB();
      Value++;
      // The thread1 goes to sleep
       SaveValueInDB(Value);
      }
}

Make it a bit more complex
So, we saw the problem when 2 threads want to access a critical section in a method but what if our critical zone is inside 2 different methods?
For instance, if we have a decrease method, one thread may decrease the value in DB while the other one still didn't save its value.
Of course the solution is to lock both stuff with the single object (the same safe-box)

Make even more Complex
We saw the code for locking some part of codes, but the drawback of that code is that the lock area cannot be accessed by multiple threads even though they might not have an impact on each other.

For instance, take updated function below as an example. The thread thr1 will try to increase the value for key =1 so it will lock the process and goes to sleep, then thr2 will try to increase the value for key=2 but since the area is already locked by thr2, it has to wait.
It is pretty clear that in a simple web application, there will be a long queue for just saving stuff and etc, and it will be slow like if it is running on a computer with only 1 CPU and 1 thread.
So what can we do to make it efficient?

Code:

void IncreaseAndSave(int key)
{
  lock(object)
     {
       var value= ReadValueForKeyFromDB(key);
       Value++;
       // The thread1 goes to sleep
       SaveValueInDB(key,Value);
      }
}

Solution
We need a class to return the lock for each key. The idea is to have a bank of  safe boxes instead of only 1. But we have to pay attention that the bank is the one who is responsible for finding the mutual interest. In the other word, on runtime the thread will ask the LockContainer(banker) about the safe-box with special key and then it will try to lock the safe-box (lock).
It can simply be done by code below:

Code:

  public class LockContainer
    {
        private BlockingCollection<object> lockItems { get; set; }

        private object LocalLockItem { get; set; }


        public LockContainer()
        {
            lockItems = new BlockingCollection<object>();
            LocalLockItem = new object();
        }

        public object GetLockItem(string str)
        {
            lock (LocalLockItem)
            {
                if (!lockItems.Any(li => (string)li == str))
                {
                    lockItems.Add(str);
                }
                return lockItems.First(li => (string)li == str);
            }
        }
    }

* Implementing the process required a list, but at the same time, adding items to list and reading them would make another critical zone, but fortunately, .net has a thread safe list called BlockingCollection which you can use with multiple threads.

How to use the code
You will need to create the container somewhere. If your critical zone is only inside a class, use it as a property of that class and make it static.
If you need to lock different areas in different classes for a key, implement a singleton design pattern (which can be found in here) and return the lock container. Then you can use it easily:

Code:

  LockContainer locks = new LockContainer();
-------------------

void IncreaseAndSave(int key)
{
  lock(locks.GetLockItem(key))
     {
       var value= ReadValueForKeyFromDB(key);
       Value++;
       // The thread1 goes to sleep
       SaveValueInDB(key,Value);
      }
}

  

Using Exceptions, good or bad?!

Last year when I started to discuss this matter with my friends, I was almost sure that they all love exceptions, but I've found out that I was wrong! So lets could be the drawback of the exceptions and shall we use them or not.

First, lets say an exception is like a sword. You can use it when something happens to protect your code from unwanted results, but at the same time cut you if someone throws it and no one catch it.
Also, no one will use a sword to peel an apple! will you?! :D

Advantages
Well, I myself cannot imagine how hard it would be to write a clean code without exceptions. :) You will prevent unwanted effects with only one line of code. There is no need to return message, value, etc. it could be logged very easily and your log can contain every detail that you may need. specially the call stack! By using typed exceptions (who won't?!) you can choose different reactions and by using inheritance, you can have even more control.

Disadvantages
handling lots of exceptions is hard. You have to have a good idea about what is going on in your project. As I said, it is a sword, if you use it in a small code, like throwing an exception and handling it inside a class (or even worst - inside a method :| )
If you don't know what you are doing, you will most probably end up with a very ugly code that will be very hard to maintain

Where to throw an exception

An exception has to be thrown in these cases:
1- When there is a special situation in your code.
Think of normal scenarios that you've seen until now, like when memory is not accessible, network is down or etc. These situations have a meaning in their criteria, but at the same time they will tell make the user (code) to handle them properly.It is important to notice that it is not only connected to a low level code, when your code is about bank accounts you can have exceptions like "the balance cannot be negative" or "insufficient funds", etc. or for a method that handles shipment it can be "Product already shipped" or "The stock is empty", etc.

2- Where your domain ends as a wrapper
Basically speaking, you may need to have lots of different events in your code, but in many cases you don't want to return the complete details to the one who consumes your methods/services/package. For instance, you may have different rules in a banking system that you need to manage internally, but when there is a relation to other system, it just needs to know that an exception from "Specific Type" happened, and maybe a message and a code for the reference, there is no need for additional data.

Where to handle Exceptions
Well this one is obvious when you know the first part,
Of course on any outer edge of your domain/service you can decide if you want to take an action on your specific exceptions. If you have created an exception to stop the whole system, then you have to let it go, otherwise you have to handle the exception and take a suitable action.
A long sword can only handled by a strong man while even a girl can handle a short and light sword.

Also, if you are interacting with a human, you have to make sure that no exception reaches him. You just need some messages as a hint/error for the customer, and nothing more.