Execute Raw SQL query using Entity Framework in ASP.Net MVC

Hi sunnyk21,

Check this example. Now please take its reference and correct your code.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

Model

public class CustomerDetails
{
    public string Id { get; set; }
    public string Name { get; set; }
    public string Address { get; set; }
    public string CompanyName { get; set; }
    public string City { get; set; }
    public string Country { get; set; }
    public string Phone { get; set; }
}

Controller

public class HomeController : Controller
{
    // GET: /Home/
    public ActionResult Index()
    {
        return View();
    }

    public ActionResult LoadData()
    {
        List<CustomerDetails> customers = new List<CustomerDetails>();
        NorthwindEntities entities = new NorthwindEntities();
        using (EntityConnection conn = new EntityConnection(entities.Connection.ConnectionString))
        {
            conn.Open();
            using (EntityCommand cmd = new EntityCommand("SELECT c.CustomerID,c.ContactName,c.Address,c.CompanyName,c.City,c.Country,c.Phone FROM NorthwindEntities.Customers as c", conn))
            {
                using (DbDataReader rdr = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
                {
                    while (rdr.Read())
                    {
                        customers.Add(new CustomerDetails
                        {
                            Id = rdr["CustomerID"].ToString(),
                            Name = rdr["ContactName"].ToString(),
                            Address = rdr["Address"].ToString(),
                            CompanyName = rdr["CompanyName"].ToString(),
                            City = rdr["City"].ToString(),
                            Country = rdr["Country"].ToString(),
                            Phone = rdr["Phone"].ToString()
                        });
                    }
                }
            }
            conn.Close();
        }
        return Json(new { data = customers }, JsonRequestBehavior.AllowGet);
    }
}

View

<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" />
<link rel="stylesheet" href="https://cdn.datatables.net/1.10.19/css/dataTables.bootstrap.min.css" />
<script type="text/javascript" src="https://code.jquery.com/jquery-3.3.1.js"></script>
<script type="text/javascript" src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/1.10.19/js/dataTables.bootstrap.min.js"></script>
<link rel="stylesheet" href="https://cdn.datatables.net/responsive/2.2.3/css/responsive.bootstrap.min.css" />
<script type="text/javascript" src="https://cdn.datatables.net/responsive/2.2.3/js/dataTables.responsive.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/responsive/2.2.3/js/responsive.bootstrap.min.js"></script>
<script type="text/javascript">
    $(document).ready(function () {
        $(document).on("click", ".opencomment", function () {
            var mycomment = $(this).data('id');
            $(".modal-body #commentdesc").html(mycomment);
        });

        $('#FeedbackDetails').DataTable({
            "processing": true,
            "ajax": {
                "url": "/Home/LoadData",
                "type": "GET",
                "datatype": "json"
            },
            "lengthMenu": [[5, 10, 25, 50, 100, -1], [5, 10, 25, 50, 100, "All"]],
            "autoWidth": true,
            "responsive": true,
            "lengthChange": true,
            "ordering": true,
            "fnRowCallback": function (nRow, aData, iDisplayIndex) {
                var oSettings = this.fnSettings();
                $("td:first", nRow).html(oSettings._iDisplayStart + iDisplayIndex + 1);
                return nRow;
            },
            "columns": [
                { "data": "Id", "autoWidth": true },
                { "data": "Name", "name": "User Name", "autoWidth": true },
                { "data": "Address", "name": "Email ID", "autoWidth": true },
                { "data": "CompanyName", "name": "Organization", "autoWidth": true },
                { "data": "City", "name": "Comment", "autoWidth": true },
                { "data": "Country", "name": "Designation", "autoWidth": true },
                { "data": "Phone", "name": "Contact No", "autoWidth": true }
            ],
            columnDefs: [{
                targets: 3,
                render: function (data, type, row, meta) {
                    if (type === 'display' && data.length > 15) {
                        return '<span title="' + data + '">' + data.substr(0, 8) + '...<a href="" data-id="' + data + '" data-toggle="modal" class="opencomment" data-target="#myModal">Show More</a>';
                    }
                    else {
                        return data;
                    }
                }
            }],
            "language": {
                "emptyTable": "No Events Found Related To This User"
            }
        });
    });
</script>
<div>
    <div class="container" style="margin-top: 10px">
        <table id="FeedbackDetails" class="ui celled table">
            <thead>
                <tr>
                    <th>S.No</th>
                    <th>Name</th>
                    <th>Address</th>
                    <th>CompanyName</th>
                    <th>City</th>
                    <th>Country</th>
                    <th>Phone</th>
                </tr>
            </thead>
        </table>
    </div>
    <div id="myModal" class="modal fade" role="dialog">
        <div class="modal-dialog">
            <div class="modal-content">
                <div class="modal-header">
                    <h4 class="modal-title">
                        Feedback Comment</h4>
                </div>
                <div class="modal-body">
                    <p id="commentdesc"></p>
                </div>
                <div class="modal-footer">
                    <button type="button" class="btn btn-danger" data-dismiss="modal">Close</button>
                </div>
            </div>
        </div>
    </div>
</div>

Screenshot