Home / Questions / 319085
Accepted
Huntzberger
Asked: 2020-12-31 15:11:06 +0800 CST 2020-12-31 15:11:06 +0800 CST

How to add a custom query in Entity Framework?

  • 772

I am new asn ASP.NET MVC 5 and I am trying to move a project from ASP.NET C# to MVC, but in my old project I have very long queries involving up to 5 tables with operations.

This is the query I need:

enter image description here

Model. This is my main table 

namespace Sistema.Models
{
    using System;
    using System.Collections.Generic;

    public partial class Visitas
    {
        public int Id { get; set; }
        public short IdUser { get; set; }
        public string Codigo { get; set; }
        public short Campo { get; set; }
        public System.DateTime Fecha { get; set; }
        public string Estatus { get; set; }
        public string Comentarios { get; set; }
    }
}

Context class. The other tables are the ones I need in my query.

namespace Sistema.Models
{
    using System;
    using System.Data.Entity;
    using System.Data.Entity.Infrastructure;
    
    public partial class MyBd : DbContext
    {
        public MyBd()
            : base("name=MyBd")
        {
        }
    
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            throw new UnintentionalCodeFirstException();
        }
    
        public virtual DbSet<Semanas> Semanas { get; set; }
        public virtual DbSet<Usuarios> Usuarios { get; set; }
        public virtual DbSet<Campos> Campos { get; set; }
        public virtual DbSet<Visitas> Visitas { get; set; }
    }
}

What I try now is to use Json and Ajax

public JsonResult VisitasList()
{
var visitas = bd.Visitas.SqlQuery("select * from . . . .");
return Json(visitas, JsonRequestBehavior.AllowGet);
}

and with ajax

<script src="~/js/Jquery/jquery-3.1.0.min.js"></script>
<script type="text/javascript">
    $(document).ready(function () {
        $.getJSON("/Visitas/VisitasList", function (data) {
            console.log('Obtuve los datos');
            $.each(data, function (idx, opt) {
                $('#dataTableVisitas').append('<tr><td>' + opt.colum1 + '</td><td>' + opt.colum2 + '</td><td>' + opt.colum3 + '</td></tr>' + opt.colum4 + '</td></tr>');                
            });
        }, 'json');
    });
</script>

But it throws me the following error

The data reader is incompatible with the specified 'myContext.Visitas'. A member of the type, 'IdVisita', does not have a corresponding column in the data reader with the same name.

c#

3 Answers

  1. Best Answer

    Try something like this:

    using (var ctx = new SchoolDBEntities())
{
    //Get student name of string type
    string studentName = ctx.Database.SqlQuery<string>("Select studentname from Student where studentid=1")
                            .FirstOrDefault();

    //or
    string studentName = ctx.Database.SqlQuery<string>("Select studentname from Student where studentid=@id", new SqlParameter("@id", 1))
                            .FirstOrDefault();
}

    Only instead of a . FirstOrDefault();you use a.ToList();

    Reference: https://www.entityframeworktutorial.net/EntityFramework4.3/raw-sql-query-in-entity-framework.aspx (although it says 4.3, the version is 6).

    • 0

  2. I'm still trying to figure out a way to Force an Entity Framework to force a Query to return a dynamic, I think this would answer the conformance issue.

    namespace Sistema.Models
{
    using System;
    using System.Collections.Generic;

    public partial class EntidadVista
    {
        public int Campo1 { get; set; }
        public int Campo2 { get; set; }
        public int Campo3 { get; set; }
    }
}

    and in the SQL create the following:

    CREATE VIEW EntidadVista as 
Select Campo1 , Campo2, Campo3 FROM (
  SELECT pablito, clavo ,un , clavito FROM la calva INNER JOIN de un calvito
)

    This complements the previous example using the view (views are ANSI and are supported by many engines).

    class EntidadVista miRegistroEntidadVista = ctx.Database.SqlQuery<EntidadVista>("Select Campo1 , Campo2, Campo3 from EntidadVista  where 1=1").FirstOrDefault();
    • 0

  3. as I understand... You only need to query the data from a table with EF right?

    If so... It's as simple as doing the following:

    public List<Visitas> VisitasList() {
    return bd.Visitas.ToList();
}

    Or if you want it filtered:

    public List<Visitas> VisitasList(int idUser) {
    return bd.Visitas.Where(x => x.IdUser == idUser).ToList(); // Filtras las visitas de un usuario
}

    You do not need to return a JSON, and in the front part, you will be able to retrieve it perfectly.

    I hope I have helped you, greetings!

    • 0