How to make a complex SQL query in . NET MVC

Question

I would like to know how to make a query, using multiple fields, multiple tables with Inner joins in ASP.NET MVC without having to put the SQL string in the code, using the Entity Framework.

At the moment, without using the string in the code, I only know how to make basic queries in the base.

Answer 1

Suppose a Model of Product:

public class Produto 
{ 
    [Key]
    public Guid ProdutoId { get; set; }
    public Guid CategoriaId { get; set; }
    public Guid FabricanteId { get; set; }

    [Required]
    public String Descricao { get; set; } 
    [Required]
    public Decimal Preco { get; set; }

    public virtual Categoria Categoria { get; set; }
    public virtual Fabricante Fabricante { get; set; }
}

Notice that Produto has two relationships: Categoria and Fabricante. Usually the Entity Framework is already configured to bring all data alone when prompted by the code. This is an implementation that uses lazy load. In this implementation, when selecting a Produto, Categoria and Fabricante receive provisional objects (dynamic proxies). These objects, when asked to provide the value of some property (for example, the name of a Categoria), are replaced by objects from the Model proper. In this process, selections or searches occur in local cache, depending on the situation.

Basic Selections

The sentence below:

var produtos = context.Produtos.ToList();

is the equivalent of:

select * from Produtos

context is a variable representing the context of its application, this class object derived from DbContext.

Selecting Dependent Entities

To also bring the manufacturers and categories of each product, the idea is to use the following command:

var produtos = context.Produtos
               .Include(p => p.Fabricante)
               .Include(p => p.Categoria)
               .ToList();

Which is equivalent to:

select * from Produtos
inner join Fabricantes on Fabricantes.FabricanteId = Produtos.FabricanteId
inner join Categorias on Categorias.CategoriaId = Produto.CategoriaId

The sentence Include(), unlike the lazy load, forces the Entity Framework to bring the data immediately. That is, an SQL is generated with the joins specified above.

To set conditions in your selection, use Where():

var produtos = context.Produtos
               .Include(p => p.Fabricante)
               .Include(p => p.Categoria)
               .Where(p => p.Categoria.Nome == "Calçados")
               .ToList();

For compound conditions, just use logical operators such as && or else ||:

var produtos = context.Produtos
               .Include(p => p.Fabricante)
               .Include(p => p.Categoria)
               .Where(p => p.Categoria.Nome == "Calçados" && p.Fabricante.Nome != "Adidas")
               .ToList();

Cardinality N

Suppose now that Fabricante is implemented as follows:

public class Fabricante 
{ 
    [Key]
    public Guid FabricanteId { get; set; }

    [Required]
    public String Nome { get; set; }

    public virtual ICollection<Produto> Produtos { get; set; }
}

Just like a Produto belongs to a Fabricante, Fabricante has several Produtos. To ICollection above specifies this relationship.

To select a Fabricante and their Produtos, use the following sentence:

var fabricantes = context.Fabricantes.Include(f => f.Produtos).ToList();

Persisting Data

To insert a new Produto, the sequence of steps to follow does this job:

var produto = new Produto 
{
    ProdutoId = Guid.NewGuid(),
    Nome = "Tênis de Corrida",
    Preco = 200,
    Fabricante = context.Fabricantes.SingleOrDefault(f => f.Nome == "Adidas"),
    Categoria = context.Categorias.SingleOrDefault(c => c.Nome == "Calçados")
};

context.Produtos.Add(produto);
context.SaveChanges();

Note that you do not enter Ids directly. Instead, they populate objects that have all the information that the Entity Framework needs to perform the insertion.

Similarly, an update is made as follows:

var produto = context.Produtos.SingleOrDefault(p => p.Nome == "Tênis de Corrida");

produto.Preco = 250;
context.Entry(produto).State = EntityState.Modified;
context.SaveChanges();