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
TravelContext.cs
TravelRepository.cs
ITravelRepository.cs
ReportsController.cs
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; }
}
}
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"]);
}
}
}
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);
}
}
}
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();
}
}
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));
}
}
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.