Queries in asp.net mvc with entity framework

Basically using Extension Methods (or in English, Extension Methods ). The full list of them is here , but try to use the link as a reference, once 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 ("lambda expression")

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

Suppose the expression from 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 Name property of x is equal to 'Other'."

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 Name property of x."

Example 1:% w / w%

I'm guessing only one record will be returned. To do this, the method select * from Usuario where Nome = "Fulano"; satisfies the need:

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

Example 2:% w / w%

SingleOrDefault selects some property of the object and returns an iterator. This iterator should be just after it has been turned into a list to be used.

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

Example 3:% w / w%

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

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

Example 4:% w / w%

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

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

Example 5:% w / w%

Just use the select Nome from Usuario; :

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

Example 6:% w / w%

Analogous to example 5, with another method, Select :

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

Example 7:% w / w%

select Nome, Idade from Usuario; is a cumulative method to select Nome as UsuarioNome, Idade as UsuarioIdade from Usuario; :

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

There is also the select * from Usuario order by Nome; .

Example 8: OrderBy :

Similar to Example 1, just changing the equal operator by select * from Usuario order by Nome desc; :

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

Example 9: OrderByDescending :

Use the method select * from Usuario order by Nome desc, Idade; .

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

Example 10:% w / w%

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

Example 11:% w / w%

The ThenBy is one of the most complicated operators to understand. Most of the time, its use is unnecessary, but supposing it is really necessary, I will give an example assuming a User has a Category, and that the Model OrderBy has a property called ThenByDescending which is a foreign key to the Model of select * from Usuario where Nome like '%Fulano%'; :

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:% w / w%

Contains has no equivalent at all and any database system. It's important to explain the concept of it before.

Assume a relation from 1 to N, or from N to N. In our example, suppose that the user now has permissions per screen (I am going to invent a Model named select * from Usuario group by CategoriaId; , which is an associative table between GroupBy and select * from Usuario where UsuarioId in (1, 2, 3); ), which is declared in Model select u.* from Usuario u inner join Categoria c ... ; as follows:

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

For example, making sense, I'll combine Join with another operator, called Usuario ". CategoriaId is analogous to Categoria , but serves for a set of objects.

Suppose you'd like to return all permissions for all users, and their permissions on a single line, separated by a comma (or by a semicolon, whatever). The Aggregate method does this 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();