Return text sql to model in the Entity framework

Question

I have the parent class and the state class:

[Table("tb_pais")]
    public class PaisModel
    {
        [Key]
        [Column("id")]
        public int Id { get; set; }

        [Column("nome")]
        [Required(ErrorMessage = "O campo nome é obrigatório")]
        public string Nome { get; set; }

        [Column("sigla")]
        [Required(ErrorMessage = "O campo sigla é obrigatório")]
        public string Sigla { get; set; }
}

[Table("tb_estado")]
    public class EstadoModel
    {

        [Key]
        [Column("id")]
        public int Id { get; set; }

        [Column("nome")]
        [Required(ErrorMessage = "O campo nome é obrigatório")]
        public string Nome { get; set; }

        [Column("sigla")]
        [Required(ErrorMessage = "O campo sigla é obrigatório")]
        public string Sigla { get; set; }

        [Column("id_pais")]
        [ForeignKey("Pais")]
        public int IdPais { get; set; }

        public virtual PaisModel Pais { get; set; }
}

With using the following sql and returning the data in the virtual variable Paismodel?

var sql = "SELECT A.id, A.nome, A.sigla, A.id_pais as IdPais from 
 dbo.tb_estado A " +
"INNER JOIN dbo.tb_pais B ON (A.id_pais = B.id) " +
 filtroWhere +
 paginacao;

ret = db.Database.SqlQuery<EstadoModel>(sql).ToList();

Answer 1

Simple example of using Entity Framework (EF) and where dynamic.

Context

public class Conexao : DbContext
{
    public Conexao()
        : base("Conexao")
    {
        Database.SetInitializer<Conexao>(null);
    }

    public DbSet<Pais> Pais { get; set; }
    public DbSet<Estado> Estado { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {            
        modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
    }
}

Models

Detail about the RU, if the name of the Model is equal to the column name in the database, it is not necessary to put the identifier Column. The same goes for table.

Parents

[Table("tb_pais")]
public class Pais
{
    [Key]
    public int Id { get; set; }

    [Required(ErrorMessage = "O campo nome é obrigatório")]
    public string Nome { get; set; }

    [Required(ErrorMessage = "O campo sigla é obrigatório")]
    public string Sigla { get; set; }
}

State

[Table("tb_estado")]
public class Estado
{
    [Key]
    public int Id { get; set; }

    [Required(ErrorMessage = "O campo nome é obrigatório")]
    public string Nome { get; set; }

    [Required(ErrorMessage = "O campo sigla é obrigatório")]
    public string Sigla { get; set; }

    [Column("id_pais")]
    public int IdPais { get; set; }

    [ForeignKey("IdPais")]
    public virtual Pais Pais { get; set; }
}

System.Linq.Dynamic

To add dynamic items from select, where and related you can use the `System.Linq.Dynamic.

You can install it by nugget https://www.nuget.org/packages/System.Linq.Dynamic/

Example of use

Let me give you a simple example of how to use.

In it is the Montawhere, which mounts the where dynamically. The Cidade.Descricao does the where in the parent class, because the OperacaoLocalidade has a FK for the class CIDADE.

using site.Dominio.Contexto;
using site.Dominio.DTO;
using site.Dominio.Entidades;
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Linq.Dynamic;
using System.Web;
using System.Web.Mvc;
namespace site.Dominio.Repositorios
{
    public class OperacaoLocalidadeRepositorio
    {
        private Conexao db { get; set; }
        private UsuarioLogadoDTO usuario { get; set; }


        private string MontaWhere()
        {
            var where = String.Format("Apagado = \"N\" and VisaoID == {0}", usuario.VisaoID);
            return where;
        }

        private string MontaWhereFiltro(GridFiltroDTO parametros)
        {
            var where = "";
            if (!String.IsNullOrEmpty(parametros.filtro))
            {
                where += where.SQLWhereInteger("OperacaoLocalidadeID", parametros.filtro);
                where += where.SQLWhereMergeOr("Sigla.ToLower().Contains(@0)");
                where += where.SQLWhereMergeOr("OperacaoLocalidadeUF.Estado.Sigla.ToLower().Contains(@0)");
                where += where.SQLWhereMergeOr("OperacaoLocalidadeUF.Estado.Descricao.ToLower().Contains(@0)");
                where += where.SQLWhereMergeOr("Cidade.Descricao.ToLower().Contains(@0)");
            }
            return where;
        }

        private string MontaWherePessoa(int? pessoa)
        {
            var where = "";
            if (pessoa != null)
            {
                where = String.Format("PessoaID == {0}", pessoa);
            }
            return where;
        }

        private string MontaOrderBy(GridFiltroDTO parametros)
        {
            return String.IsNullOrEmpty(parametros.orderna) ? "Sigla" : parametros.orderna + " " + parametros.ordernaTipo;
        }

        private int TotalDeRegistros(GridFiltroDTO parametros, UsuarioLogadoDTO usuarioLogado)
        {
            return
                db.OperacaoLocalidade
                .Where(MontaWhere())
                .Where(MontaWhereFiltro(parametros), parametros.filtro)
                .Where(MontaWherePessoa(usuarioLogado.PessoaID))
                .Count();
        }

        public GridResultadoDTO Grid(GridFiltroDTO parametros)
        {
            return new GridResultadoDTO(
                db.OperacaoLocalidade
                    .Where(MontaWhere())
                    .Where(MontaWhereFiltro(parametros), parametros.filtro)
                    .Where(MontaWherePessoa(usuario.PessoaID))
                    .Select(s => new {
                        s.OperacaoLocalidadeID,
                        s.Pessoa.Nome,
                        s.Sigla,
                        Estado = s.Estado.Descricao,
                        Cidade = s.Cidade.Descricao,
                        s.Status,
                        Fixo = s.Fixo == "S" ? "Sim" : "Não"
                    })
                    .OrderBy(MontaOrderBy(parametros))
                    .Skip(parametros.itensParaIgnorar)
                    .Take(parametros.itensPorPagina)                    
                    .ToArray(), TotalDeRegistros(parametros, usuario));
        }

        public SelectList ComboBox(int? pessoa) 
        { 
            return 
                new SelectList(
                    db.OperacaoLocalidade
                    .Where(MontaWhere())
                    .Where(MontaWherePessoa(pessoa))
                    .OrderBy(o => o.Sigla)
                    .Select(s => new ItemComboBoxDTO { Key = s.OperacaoLocalidadeID, Texto = s.Sigla + " [ " + s.Cidade.Descricao + " / " + s.Cidade.Estado.Sigla + " ] " }).ToArray(), 
                    "Key", "Texto"); 
        }

        public OperacaoLocalidadeRepositorio(Conexao conexao, UsuarioLogadoDTO usuarioLogado)
        {
            db = conexao;
            usuario = usuarioLogado;
        }
    }
}