ASP.Net Core: Display Stored Procedure result using Entity Framework

anjali60...

SOLVED CLOSED ACTIVE ANSWERED
User: anjali600
Posted: on Sep 30, 2023 12:01 AM
Forum: ASP.Net Core MVC
Answer: 1
Views: 4194

I have the following database tables:

Employee table -Database name Employee - EmployeeID is the primary key

JobTitleID    EmployeeNumber   SectionID    EmployeeId 
9               1234          2              1
2               8976          3              2
4               7654          1              3

I have another table called Job Title --Database name Employee - prmary key JobTitleID

JobTitleID    JobTitle
9               CEO
2               Developer
4               Executive

Section Table - Database name Employee- prmary key SectionID

SectionID        Section
1               manager
2               IT
3               Branch

Another table called tblEmployee that reside in another database name - Tracking - primary Key EmployeeNumber

EmployeeNumber       EmployeeName
1234                 TestEmpName1
8976                 TestEmpName2
7654                 TestEmpName3

I am using database first approach to create the context class. I need this data to populate a table in the Razor view. How can I get the EmployeeName, JobTitle, section in a LINQ query and then populate a List with this data. I tried writing a stored procedure called GetEmployeeData like this:

SELECT JobTitleID, EmployeeNumber, SectionID, JobTitle, section, EmployeeName
FROM Employee e LEFT OUTER JOIN JOBTitle j ON e.JobTitleID = j.JobTitleID
LEFT OUTER JOIN SECTION s ON e.SectionID  = s.SectionID 
LEFT OUTER JOIN  Tracking.dbo.tblEmployee t ON t.EmployeeNumber = e.EmployeeNumber

I need to populate a List with this data and then populate a table in razor view. I can go with LINQ or SQL stored procedure.

I tried doing this, but didn't work because sql query is returning JobTitle, section and emmployeeName in the sql query and that does not exists in Employee Model.

List<Employee> query = await _empContext.Set<Employee>().FromSqlRaw($"EXECUTE dbo.GetEmployeeData").ToListAsync();

I need JobTitle Section EmployeeName in a table in view.

public partial class Employee
{   
     [Key]
     public int EmployeeId{ get; set; }
     public int JobTitleId { get; set; }
     public string EmployeeNumber { get; set; }
     public int? SectionId { get; set; }
     public virtual JobTitle? JobTitle { get; set; }
     public virtual Section? Section { get; set; }
}

public partial class JobTitle
{
    public int JobTitleId { get; set; }
    public string JobTitle { get; set; } = null!;
    public virtual ICollection<Employee> Employees{ get; set; } = new List<Employee>();
}

public partial class Section
{    
    public int SectionId { get; set; }
    public string Section { get; set; } = null!;
    public virtual List<Employee> Employees { get;  } = new List<Employee>();
    public virtual ICollection<Section> Section { get; } = new List<Section>();
}

This is my partial context class. I cannot post the entire context class because I have some other columns names then can display the company Name:

public partial class EmployeeContext : DbContext
{
    public EmployeeContext()
    {
    }

    public EmployeeContext(DbContextOptions<EmployeeContext> options)
        : base(options)
    {
    }
    public virtual DbSet<JobTitle> JobTitle { get; set; }
    public virtual DbSet<Section> Section { get; set; } 
    public virtual DbSet<Employee> Employees { get; set; }
}

dharmend...

1 3

Reply
ANSWER
User: dharmendr
Replied: on Sep 30, 2023 03:37 AM
Report

Hi anjali600,

You need to add another class with the Columns returning from the Procedure.

Then use that class to call and return the result.

Refer below example.

Database

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

Download Northwind Database

Stored Procedure

-- EXEC Customers_SearchOrders ''
CREATE PROCEDURE Customers_SearchOrders
    @ContactName NVARCHAR(30)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT TOP 10 o.OrderID
		  ,c.ContactName
		  ,o.Freight
		  ,o.ShipName
    FROM Orders o
	INNER JOIN Customers c ON o.CustomerID = c.CustomerID
    WHERE c.ContactName LIKE '%' + @ContactName + '%'
	ORDER BY o.OrderID
END

Model

Customer

public class Customer
{
    public string CustomerID { get; set; }
    public string ContactName { get; set; }
    public string City { get; set; }
    public string Country { get; set; }
}

Order

public class Order
{
    public int OrderID { get; set; }
    public string CustomerID { get; set; }
    public decimal Freight { get; set; }
    public string ShipName { get; set; }
}

CustomerOrder

public class CustomerOrder
{
    [System.ComponentModel.DataAnnotations.Key]
    public int OrderID { get; set; }
    public string ContactName { get; set; }
    public decimal Freight { get; set; }
    public string ShipName { get; set; }
}

DbContext

public class DBCtx : DbContext
{
    public DBCtx(DbContextOptions<DBCtx> options) : base(options)
    {
    }

    public DbSet<Customer> Customers { get; set; }

    public DbSet<Order> Orders { get; set; }

    public DbSet<CustomerOrder> CustomerOrders { get; set; }
}

Namespaces

using System.Collections.Generic;
using System.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using System.Threading.Tasks;

Controller

public class HomeController : Controller
{
    private DBCtx Context { get; }
    public HomeController(DBCtx _context)
    {
        this.Context = _context;
    }

    public async Task<IActionResult> Index()
    {
        List<CustomerOrder> orders = await Task.Run(() => this.SearchOrders(""));
        return View(orders);
    }

    [HttpPost]
    public async Task<IActionResult> Index(string customerName)
    {
        List<CustomerOrder> orders = await Task.Run(() => this.SearchOrders(customerName));
        return View(orders);
    }

    public async Task<List<CustomerOrder>> SearchOrders(string contactName)
    {
        SqlParameter pContactName = new SqlParameter("@ContactName", contactName);
        List<CustomerOrder> orders = await this.Context.Set<CustomerOrder>()
                                .FromSql("EXECUTE Customers_SearchOrders @ContactName", pContactName).ToListAsync();

        return orders;
    }
}

View

@using Search_EF_MVC_Core.Models;
@addTagHelper*, Microsoft.AspNetCore.Mvc.TagHelpers

@model List<CustomerOrder>
@{
    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>Order ID</th>
                <th>Customer Name</th>
                <th>Ship Name</th>
                <th>Freight</th>
            </tr>
            @foreach (CustomerOrder order in Model)
            {
                <tr>
                    <td>@order.OrderID</td>
                    <td>@order.ContactName.Trim()</td>
                    <td>@order.ShipName.Trim()</td>
                    <td style="text-align:right">@order.Freight.ToString("N2")</td>
                </tr>
            }
        </table>
    </form>
</body>
</html>

Screenshot

ASP.Net Core: Display Stored Procedure result using Entity Framework

Company

Explore

Follow us

Contact Us