Queries in Asp.net mvc with Entity framework

Question

I’m starting a project in ASP.NET MVC 5 with Entity Framework 6, along with studies in mvc. This to have a better learning, and I came across the following doubt.

In the controller have the method Index, that literally returns a "select * from usuario" of the database:

// GET: Usuario 
public ActionResult Index() 
{
    return View(db.usuario.ToList()); 
}

My question, which I couldn’t find, or maybe because I wasn’t looking properly, is what do I do if I need a specific darling? For example "select * from usuario where status = 'ativo'". How do I set this up?

Answer 1

Basically using extension methods (or in English, Extension Methods). The complete list of them is here, but try to use the link as a reference, after you have mastered the general aspect of how to write a complete command.

I will give some equivalent examples, as well as explanations that are pertinent to the understanding, since I have never found a manual or tutorial that is didactic enough.

How to Read a Predicate Expression (alias "lambda expression")

It is a common difficulty for programmers who have a certain seniority in object-oriented languages to have difficulty understanding how a predicate expression works.

Suppose the expression of Example 1:

db.usuario.SingleOrDefault(x => x.Nome == "Fulano");

This:

x => x.Nome == "Fulano"

It is a Predicate Expression, and should be read as:

"For every object x within this collection, such that the property Name of x equals 'So-and-so'."

Or another example (Example 2):

db.usuario.Select(x => x.Nome);

The expression:

x => x.Nome

It should be read as:

"For every object x within this collection, consider the property Name of x."

---

Example 1: select * from Usuario where Nome = "Fulano";

I am assuming that only one record will be returned. For this, the method SingleOrDefault meets the need:

var resultado = db.usuario.SingleOrDefault(x => x.Nome == "Fulano");

---

Example 2: select Nome from Usuario;

Select selects some property of the object and returns an iterator. This iterator must be soon after transformed into a list to be used.

var resultado = db.usuario.Select(x => x.Nome).ToList();

---

Example 3: select Nome, Idade from Usuario;

Same principle as the previous example, but an anonymous object with two properties is mounted.

var resultado = db.usuario.Select(x => new { x.Nome, x.Idade }).ToList();

---

Example 4: select Nome as UsuarioNome, Idade as UsuarioIdade from Usuario;

Analogous to example 3, specifying the properties names of the anonymous object.

var resultado = db.usuario.Select(x => new { UsuarioNome = x.Nome, UsuarioIdade = x.Idade }).ToList();

---

Example 5: select * from Usuario order by Nome;

Just use the method OrderBy:

var resultado = db.usuario.OrderBy(x => x.Nome).ToList();

---

Example 6: select * from Usuario order by Nome desc;

Analogous to example 5 with another method, OrderByDescending:

var resultado = db.usuario.OrderByDescending(x => x.Nome).ToList();

---

Example 7: select * from Usuario order by Nome desc, Idade;

ThenBy is a cumulative method to OrderBy:

var resultado = db.usuario.OrderByDescending(x => x.Nome).ThenBy(x => x.Idade).ToList();

There is also the version ThenByDescending.

---

Example 8: select * from Usuario where Nome like '%Fulano%';:

Similar to Example 1, only exchanging the equal operator for Contains:

var resultado = db.usuario.Where(x => x.Nome.Contains("Fulano")).ToList();

---

Example 9: select * from Usuario group by CategoriaId;:

Use the method GroupBy.

var resultado = db.usuario.GroupBy(x => x.CategoriaId).Select(grupo => new { Chave = grupo.Key, Valores = grupo.ToList()}).ToList();

---

Example 10: select * from Usuario where UsuarioId in (1, 2, 3);

var listaDeIds = new List<int> { 1, 2, 3};
var resultado = db.usuario.Where(x => listaDeIds.Contains(x.UsuarioId)).ToList();

---

Example 11: select u.* from Usuario u inner join Categoria c ... ;

The Join is one of the most complicated operators to understand. Most of the time, its use is unnecessary, but assuming it is really necessary, I will give an example assuming that a User has a Category, and that the Model Usuario has a property called CategoriaId which is a foreign key to the Model of Categoria:

var resultado = db.Usuario.Join(db.Categoria,       // A tabela que será unida ao resultado
                                u => u.CategoriaId, // A chave estrangeira da tabela em questão. Nossa tabela em questão é 'Usuario'
                                c => c.CategoriaId, // A chave primária da tabela que será unida
                                // Dados os dois conjuntos, Usuario representado por u e Categoria por c, 
                                // Este argumento produz o retorno do Join, criando um objeto anônimo 
                                // com as propriedades desejadas.
                                (u, c) => new { NomeUsuario = u.Nome, NomeCategoria = c.Nome }).ToList();

---

Example 12: Aggregate

Aggregate has no equivalent in any and all database system. It is important to explain the concept of it before.

Suppose a ratio of 1 to N, or then from N to N. In our example, suppose the User now has permissions per screen (I will invent a Model called UsuarioPermissao, which is an associative table between Usuario and Permissao), which is stated in Model Usuario as follows:

public virtual ICollection<UsuarioPermissao> UsuarioPermissoes { get; set; }

For example make sense, I’ll match the Aggregate with another operator, called SelectMany. SelectMany is analogous to Select, but serves for a set of objects.

Suppose you would like to return all permissions of all users, their permissions being in a single line, separated by a comma (or so by a semicolon, whatever). The method Aggregate do it like this:

var resultado = db.Usuario.SelectMany(p => p.UsuarioPermissoes).
                          .Aggregate("",     // String inicial, chamada de 'acumulador'
                                     // A construção abaixo considera 'str' como a String acumulada e 'usuarioPermissao' como o registro atual da iteração
                                     (str, usuarioPermissao) => str + ", " + usuarioPermissao.Permissao.Nome).ToList();