CODEWITHSUNDEEP
c#sql-serverentity-frameworkstored-procedures
Hitin
Hitin

Reputation: 442

Entity Framework stored procedure return type changed

I mostly update my data model by deleting my .EDMX file and adding it again, just to make sure I get everything I have in the database.

I ran into a strange issue, where one of my stored procedure return types has been changed after deleting and re-adding the .EDMX. The stored procedure code remains unchanged, and can be found below. I am not sure what wrong I did?

SQL Server stored procedure (never changed):

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[usp_SearchOrders]
      @orderid int = null, @statusid int = null, 
      @startdate datetime = null, @enddate datetime = null,
      @customername varchar(30) = null, @customeraddress varchar(30) = null
AS
     SELECT 
         O.OrderID Order_ID, O.OrderDate,
         OrderTotal = (SELECT SUM(od.UnitPrice - (od.UnitPrice * od.Discount)) 
                       FROM [Order Details] od 
                       WHERE od.OrderID = O.OrderID),
         Discount = (SELECT SUM(od.UnitPrice * od.Discount) 
                     FROM [Order Details] od 
                     WHERE od.OrderID = O.OrderID),
         CC.Email1, CC.Phone,
         Status = (SELECT OS.OrderStatusName 
                   FROM OrderStatus OS 
                   WHERE OS.OrderStatusID = O.OrderStatusID),
         OrderType = (SELECT OT.OrderTypeName 
                      FROM OrderType OT 
                      WHERE OT.OrderTypeID = O.OrderTypeID)
     FROM
         Orders O 
     JOIN 
         Customers C ON O.CustomerID = C.CustomerID
     JOIN 
         CustomersContactDetails CC ON C.CustomerID = CC.CustomerID
     WHERE 
         (O.OrderID = @orderid OR @orderid IS NULL)
         AND (O.OrderStatusID = @statusid OR @statusid IS NULL)
         AND (O.OrderDate >= @startdate OR @startdate IS NULL)
         AND (O.OrderDate <= @enddate OR @enddate IS NULL)
         AND (C.CustomerName LIKE @customername OR @customername IS NULL)
         AND (CC.Address LIKE @customeraddress OR @customeraddress IS NULL)
         AND (CC.City LIKE @customeraddress OR @customeraddress IS NULL)

C# code: _dbContext.usp_SearchOrders(1,1,DateTime.Now, DateTime.Now.AddDays(1),'Hitin','BH');

Before update return type: usp_SearchOrders_Result

After re-adding return type: int

I never made any complex type or did any mapping, the class usp_SearchOrders_Result was created on it own.

What could be the possible reason behind this issue?

Upvotes: 0

Views: 655

Answers (1)

Bruce Dunwiddie
Bruce Dunwiddie

Reputation: 2908

You need to have "SET NOCOUNT ON" at the top of your stored procedure.

Upvotes: 1

Related Questions