Link to home
Start Free TrialLog in
Avatar of quest_capital
quest_capital

asked on

How can I run RAW complex query's with Entity Framework Core

I have created an API using ASP.Net Core using Entity Framework Core for the Data.
Every thing works fine with running basic query's as long as you run the query via the same table. ie. (Select * From [oneTable])

However my problem comes in when I try to run a raw query using multiple joins of tables.
See I know you need to setup an Entiy/Model for each table (which I have) however, what if you have a Raw Query with  multiple joins?

Please modify code...

See comments in:
TravelContext.cs
TravelRepository.cs

Here is my setup:

TVL_Rpt_StaySummery.cs
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Threading.Tasks;

namespace TravelData.Entities
{
    public class TVL_Rpt_StaySummery
    {
        [Key]
        public int StayID { get; set; } = 0;
        public string EmpName { get; set; } = "Herb";
        //public DateTime StartDate { get; set; }
        //public DateTime EndDate { get; set; }
        //public string PropertyName { get; set; }

    }
}

Open in new window


TravelContext.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.AspNetCore.Identity.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Migrations;

namespace TravelData.Entities
{
    //NOTE: Open cmd prompt and run (staments below) at the project root:
    //> dotnet ef migrations add any*name
    //> dotnet ef database update
    public class TravelContext : IdentityDbContext<TVL_User>
    {
        //NOTE: Open cmd prompt and run (staments below) at the project root:
        //> dotnet ef migrations add InitialDatabase
        //> dotnet ef datebase update

        private IConfigurationRoot _config;

        public TravelContext(DbContextOptions options, IConfigurationRoot config) : base(options)
        {
            _config = config;
        }

        // Build tables
        public DbSet<TVL_Employee> TVL_Employee { get; set; }
        public DbSet<TVL_Restaurant> TVL_Restaurant { get; set; }
        public DbSet<TVL_Concept> TVL_Concept { get; set; }
        public DbSet<TVL_CarCompany> TVL_CarCompany { get; set; }
        public DbSet<TVL_Department> TVL_Department { get; set; }
        public DbSet<TVL_Property> TVL_Property { get; set; }
        public DbSet<TVL_Interval> TVL_Interval { get; set; }
        public DbSet<TVL_AirTicketClass> TVL_AirTicketClass { get; set; }
        public DbSet<TVL_AccountingMethod> TVL_AccountingMethod { get; set; }
        public DbSet<TVL_Room> TVL_Room { get; set; }
        public DbSet<TVL_Destination> TVL_Destination { get; set; }
        public DbSet<TVL_FlightDetail> TVL_FlightDetail { get; set; }
        public DbSet<TVL_Stay> TVL_Stay { get; set; }
        public DbSet<TVL_TravelData> TVL_TravelData { get; set; }
        public DbSet<TVL_Rental> TVL_Rental { get; set; }

        
        // Reports: Ignore Entity from creating
        public DbSet<TVL_Rpt_StaySummery> TVL_Rpt_StaySummery { get; set; }

        protected override void OnModelCreating(ModelBuilder builder)
        {
            base.OnModelCreating(builder);

            // If I do this I get and Error: Value cannot be null. Parameter name: entityType
            // But I don't want to add and entity to my data base (A blank table with fields from multiple table)
            builder.Ignore<TVL_Rpt_StaySummery>();

        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            base.OnConfiguring(optionsBuilder);
            
            optionsBuilder.UseSqlServer(_config["Data:ConnectionString"]);
        }

    }
}

Open in new window


TravelRepository.cs
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace TravelData.Entities
{
    public class TravelRepository : ITravelRepository
    {
        private TravelContext _context;

        public TravelRepository(TravelContext context)
        {
            _context = context;
        }

        public void Add<T>(T entity) where T : class
        {
            _context.Add(entity);
        }

        public void Delete<T>(T entity) where T : class
        {
            _context.Remove(entity);
        }

        public async Task<bool> SaveAllAsync()
        {
            return (await _context.SaveChangesAsync()) > 0;
        }

        public IEnumerable<TVL_Employee> GetAllEmployees()
        {
            return _context.TVL_Employee.ToList();
        }

        public IEnumerable<TVL_Employee> GetTopEmployees()
        {
            return _context.TVL_Employee.FromSql("SELECT top 10 * FROM dbo.TVL_Employee").AsNoTracking().ToList();
        }

        public TVL_Employee GetEmployee(int id)
        {
            // Useing SQL Statments
            return _context.TVL_Employee.FromSql("SELECT top 10 * FROM dbo.TVL_Employee Where EmpID = " + id).FirstOrDefault();
            //return _context.TVL_Employee
            //  .Where(c => c.EmpID == id)
            //  .FirstOrDefault();
        }

        
        
        // Reports

        public IEnumerable<TVL_Rpt_StaySummery> GetRptStaySummery()
        {
            var sql = 
                "SELECT " +
                "sty.StayID, " +
                "emp.[Last] + ', ' + emp.[First] AS EmpName--, " +
                "--sty.[StartDate], " +
                "--sty.[EndDate], " +
                "--prp.[PropName] + ', ' + prp.[PropCity] AS PropertyName " +
                "FROM TVL_Stay sty " +
                "LEFT JOIN TVL_Employee emp ON emp.EmpID = sty.EmpID " +
                "LEFT JOIN TVL_Room rms ON rms.RoomID = sty.RoomID " +
                "LEFT JOIN TVL_Property prp ON prp.PropID = rms.PropID " +
                "WHERE (  " +
                "((sty.[StartDate])<= '1/1/2016') AND((sty.[EndDate])>= '1/1/2016' Or(sty.[EndDate]) Is Null)  " +
                "AND((DATEDIFF(DAY, [StartDate], [EndDate]))>0)  " +
                "AND((sty.RestID)<>157 And(sty.RestID)<>406)  " +
                "AND((sty.EmpID)<>1015 And(sty.EmpID)<>1468 And(sty.EmpID)<>1469 And(sty.EmpID)<>1470 And(sty.EmpID)<>1399) " +
                ") AND( " +
                "prp.[PropName] is not null AND prp.[PropCity] is not null " +
                ")";

            // Non of these work...
            return _context.TVL_Rpt_StaySummery.FromSql(sql).AsNoTracking().ToList();
            //return _context.Database.SqlQuery<TVL_Rpt_StaySummery>(sql).ToList();
            //return _context.Database.ExecuteSqlCommand<TVL_Rpt_StaySummery>(sql);
        }

    }
}

Open in new window


ITravelRepository.cs
using System.Collections.Generic;
using System.Threading.Tasks;

namespace TravelData.Entities
{
    public interface ITravelRepository
    {
        void Add<T>(T entity) where T : class;
        void Delete<T>(T entity) where T : class;
        Task<bool> SaveAllAsync();

        // Employess
        IEnumerable<TVL_Employee> GetAllEmployees();
        IEnumerable<TVL_Employee> GetTopEmployees();
        TVL_Employee GetEmployee(int id);

        // Reports
        IEnumerable<TVL_Rpt_StaySummery> GetRptStaySummery();
    }
}

Open in new window


ReportsController.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using TravelData.Entities;
using AutoMapper;
using Microsoft.AspNetCore.Authorization;
using TravelAPI.Models;


namespace TravelAPI.Controllers
{
    [Authorize]
    public class ReportsController : Controller
    {
        private ITravelRepository _repo;
        private IMapper _mapper;

        public ReportsController(ITravelRepository repo, IMapper mapper)
        {
            _repo = repo;
            _mapper = mapper;
        }


        [HttpGet("api/reports/staysummery")]
        public IActionResult Get()
        {
            var rpt = _repo.GetRptStaySummery();
            return Ok(_mapper.Map<IEnumerable<StaySummeryModel>>(rpt));
        }
    }
}

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of quest_capital
quest_capital

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial