Execute a raw SQL query

Is it possible to execute a raw SQL query from the server side using the current available objects?

Yes, using FromSql method from EF Core:

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Identity;
using Microsoft.AspNetCore.Mvc;
using Sample.Models;
using Sample.Data;
using Sample.Models.Sample;
using Microsoft.EntityFrameworkCore;

namespace Sample.Controllers
{
    [Route("api/[controller]/[action]")]
    public class ServerMethodsController : Controller
    {
        private readonly UserManager<ApplicationUser> userManager;
        private readonly RoleManager<IdentityRole> roleManager;

        private readonly SampleContext sampleContext;

        public ServerMethodsController(UserManager<ApplicationUser> userManager,  RoleManager<IdentityRole> roleManager, SampleContext sampleContext)
        {
            this.userManager = userManager;
            this.roleManager = roleManager;
            this.sampleContext = sampleContext;
        }

        public async Task<IEnumerable<ApplicationUser>> GetUsersForRole(string role)
        {
           return await this.userManager.GetUsersInRoleAsync(role);
        }

        public IEnumerable<Order> GetOrders()
        {
           return this.sampleContext.Orders.FromSql("SELECT * FROM ORDERS", new object[]{}).OfType<Order>().ToList();
        }
    }
}


2 Likes