What is more advisable? Perform the query through a list or directly in the database?

Question

I have to perform a consultation where I need to do 4 INNER JOIN and still use WHERE in various fields.

What is most advisable?

1. Conduct the consultation in this way?

using (DbContexto db = new DbContexto())
{
    var query = (from t1 in db.tabela1
                 join t2 in db.tabela2 on t1.id equals t2.fk_t1
                 join t3 in db.tabela3 on t1.idt3 equals t3.fk_t1
                 where (t1.v1 = 1) && (t2.v2 = 2) && (t3.v3 = 3)
                 select t1
             );
}

2. Or that?

List listaTabela1 = new List();
List listaTabela2 = new List();
List listaTabela3 = new List();

using (DbContexto db = new DbContexto())
{
    listaTabela1 = (from t1 in db.tabela1 select t1).ToList();
    listaTabela2 = (from t2 in db.tabela2 select t2).ToList();
    listaTabela3 = (from t3 in db.tabela3 select t3).ToList();
    var query    = (from t1 in listaTabela1 
                    join t2 in listaTabela2 on t1.id equals t2.fk_t1
                    join t3 in listaTabela3 on t1.idt3 equals t3.fk_t1
                    where (t1.v1 = 1) && (t2.v2 = 2) && (t3.v3 = 3)
                    select t1
                   );
}

Answer 1

The most advisable way depends on your final goal with the code. Only by the text around the code would it be easy to dismiss the question as opinion dependent. By looking more carefully at the two alternatives, however, I believe that the first is more advisable.

The reason is this: in the first way, you will execute a single query in the database to get the data you want. The server is able to create a well-defined search strategy and, if everything is OK on the SQL side, efficiently returns what you want.

In the second option you have three queries to the bank without any filtering criteria. The complete contents of the tables will be read on the disk, allocated in the server memory of the database, then goes to the memory space of your application and finally you discard what you do not want. Every time your search result does not contain all the records of the three tables, you will have done operations as well... For lack of a better word, useless operations.

Note also that the complexity of its joins must be something approximate of an algorithm O(n m o). In people’s language, this means that the running time of your program does not grow proportionally to the amount of data involved... The growth of the runtime is exponential, and can be proportional to the cube of the amount of elements it will work with (can be even worse than that). I’m actually seeing two Inner joins in your example - with four Inner joins, we are talking about the fifth power. It is essential to optimize so that the minimum of elements is obtained from the database.

Keep the first option, unless you want to take advantage of items that are not part of the query result for something else.