In this article I will explain with an example, how to execute RAW SQL using Entity Framework Core 2.1 framework in ASP.Net Core MVC.
The RAW SQL Query will be executed using the FromSql function of the Entity Framework Core 2.1 framework in ASP.Net Core MVC.
Note: For beginners in ASP.Net Core MVC and Entity Framework, please refer my article  ASP.Net Core: Simple Entity Framework Tutorial with example. It covers all the information needed for connecting and configuring Entity Framework.
 
 
Database
Here I am making use of Microsoft’s Northwind Database. You can download it from here.
Download and install Northwind Database
 
 
Database Context
The very first step is to create Database Context.
Note: For beginners in Database Context, please refer my article ASP.Net Core: Simple Entity Framework Tutorial with example. It covers all the information needed for connecting Database Context.
 
Inside the class, first inherit the EntityFrameworkCore namespace and then inherit the DbContext class.
Then using Dependency Injection, a Constructor is created DbContextOptions are passed as parameter and also the Constructor of base class i.e. DbContext class is inherited.
A DbSet Collection property of Customer Entity is created, which will be later used for holding the Data fetched from SQL Server Database Table.
Finally, there is a method SearchCustomers which is used to execute the RAW SQL and return its result.
The SQL Query is executed using the FromSql function. The FromSql function accepts the following two parameters:
1. SQL Query – SQL Query to be executed.
2. Parameters – One or more objects of SqlParameter class.
Note: The FromSql function belongs to the Microsoft.EntityFrameworkCore namespace.
 
using System.Linq;
using System.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
 
namespace Query_EF_MVC_Core
{
    public class DBCtx : DbContext
    {
        public DBCtx(DbContextOptions<DBCtx> options) : base(options)
        {
        }
 
        public DbSet<Customer> Customers { get; set; }
 
        public IQueryable<Customer> SearchCustomers(string contactName)
        {
            string sql = @"SELECT TOP 10 *
                            FROM Customers
                            WHERE ContactName LIKE '%' + @ContactName + '%'";
            SqlParameter pContactName = new SqlParameter("@ContactName", contactName);
            return this.Customers.FromSql(sql, pContactName);
        }
    }
}
 
 
Controller
The Entity Framework is now configured and hence now we can create a Controller and write code to fetch the records from the Customers Table of the Northwind Database.
The Controller consists of two Action methods.
Action method for handling GET operation
Inside the Index Action method, the SearchCustomers method of the Database Context class is called and an empty string is passed as parameter.
Finally, the returned results are converted into Generic List collection and returned to the View.
 
Action method for handling POST operation
When the Form is submitted, the value of the Customer Name TextBox is submitted to this Action method and the received value is passed as parameter to the SearchCustomers function.
Finally, the returned results are converted into Generic List collection and returned to the View.
public class HomeController : Controller
{
    private DBCtx Context { get; }
    public HomeController(DBCtx _context)
    {
        this.Context = _context;
    }
 
    public IActionResult Index()
    {
        List<Customer> customers = this.Context.SearchCustomers("").ToList();
        return View(customers);
    }
 
    [HttpPost]
    public IActionResult Index(string customerName)
    {
        List<Customer> customers = this.Context.SearchCustomers(customerName).ToList();
        return View(customers);
    }
}
 
 
View
Inside the View, in the very first line the generic list of Customer Entity is declared as Model for the View.
The View consists of an HTML Form with following ASP.Net Tag Helpers attributes, a HTML TextBox and a Submit Button.
asp-action – Name of the Action. In this case the name is Index.
asp-controller – Name of the Controller. In this case the name is Home.
method – It specifies the Form Method i.e. GET or POST. In this case it will be set to POST.
When the Search button is clicked, the Form gets submitted.
For displaying the records, an HTML Table is used. A loop will be executed over the Model which will generate the HTML Table rows with the Customer records.
@using Query_EF_MVC_Core;
@addTagHelper*, Microsoft.AspNetCore.Mvc.TagHelpers
 
@model List<Customer>
@{
    Layout = null;
}
 
<!DOCTYPE html>
 
<html>
<head>
    <meta name="viewport" content="width=device-width"/>
    <title>Index</title>
</head>
<body>
    <form method="post" asp-controller="Home" asp-action="Index">
        <span>Customer Name:</span><input type="text" name="CustomerName"/>
        <input type="submit" value="Search"/>
        <br/>
        <br/>
        <table cellpadding="0" cellspacing="0">
            <tr>
                <th>CustomerID</th>
                <th>ContactName</th>
                <th>City</th>
                <th>Country</th>
            </tr>
            @foreach (Customer customer in Model)
            {
                <tr>
                    <td>@customer.CustomerID</td>
                    <td>@customer.ContactName</td>
                    <td>@customer.City</td>
                    <td>@customer.Country</td>
                </tr>
            }
        </table>
    </form>
</body>
</html>
 
 
Screenshots
HTML Table displaying Top 10 records
ASP.Net Core: Execute RAW SQL with Entity Framework Core 2.1
 
HTML Table displaying Filtered records
ASP.Net Core: Execute RAW SQL with Entity Framework Core 2.1
 
 
Downloads
Download Code