i have an web api project using .net 4.7.2, then created model as .edmx file and added one stored procedure. below is the complete SP but, then i start create controller that time im receiving exception as like below. may i know what is the solution for
this?
ALTER PROCEDURE usp_CustomerCard_getList
(@inpClientID INT = NULL ) AS BEGIN SELECT C.clientID AS [Key], CC.MasterCardID, CC.number, cc.expiry, SUBSTRING(cc.expiry, 0, 3) ExpMonth, SUBSTRING(cc.expiry, 3, 2) ExpYear, CC.holder CardHolderName, P.Title, P.firstName FirstName, P.familyName LastName, '' AS Company, A.line1 StreetAddress1, A.line2 StreetAddress2, '' StreetAddress3, A.town City, A.region Province, A.postCode PostalCode, LKPCOU.name Country, A.tel1 HomePhone, A.tel2 WorkPhone, A.tel3 MobilePhone, A.Fax, A.email Email, CAST(GETDATE() AS CHAR(30)) + ' request created date ' AS Comments FROM dbo.uvw_MasterCard_getlist CC LEFT OUTER JOIN tblClient C ON CC.clientID = C.clientID AND C.clientTypeID IN(1, 2, 3, 101, 102) INNER JOIN tlnkClient_person CP ON C.clientID = CP.clientID AND CP.isDefault = 1 INNER JOIN tblPerson P ON P.personID = CP.personID INNER JOIN tlnkPerson_Address PA ON P.personID = PA.personID AND PA.isDefault = 1 INNER JOIN tblAddress A ON PA.addressID = A.addressID INNER JOIN tlkpCountry LKPCOU ON A.countryID = LKPCOU.countryID WHERE(@inpClientID IS NULL OR C.clientID = @inpClientID) AND LEN(LTRIM(RTRIM(CC.expiry))) = 4 AND CONVERT(CHAR(10), EOMONTH(CONVERT(DATETIME, CAST(SUBSTRING(cc.expiry, 0, 3) AS CHAR(2)) + '/01/' + CAST(SUBSTRING(cc.expiry, 3, 2) AS CHAR(2)))), 111) >= CONVERT(CHAR(10), GETDATE(), 111) ORDER BY c.clientID ASC, CC.MasterCardID ASC; END;
Member
289 Points
1336 Posts
Entity framework stored procedure issue
Sep 23, 2019 10:27 AM|winseealn@hotmail.com|LINK
hello,
i have an web api project using .net 4.7.2, then created model as .edmx file and added one stored procedure. below is the complete SP but, then i start create controller that time im receiving exception as like below. may i know what is the solution for this?
ALTER PROCEDURE usp_CustomerCard_getList
(@inpClientID INT = NULL
)
AS
BEGIN
SELECT C.clientID AS [Key],
CC.MasterCardID,
CC.number,
cc.expiry,
SUBSTRING(cc.expiry, 0, 3) ExpMonth,
SUBSTRING(cc.expiry, 3, 2) ExpYear,
CC.holder CardHolderName,
P.Title,
P.firstName FirstName,
P.familyName LastName,
'' AS Company,
A.line1 StreetAddress1,
A.line2 StreetAddress2,
'' StreetAddress3,
A.town City,
A.region Province,
A.postCode PostalCode,
LKPCOU.name Country,
A.tel1 HomePhone,
A.tel2 WorkPhone,
A.tel3 MobilePhone,
A.Fax,
A.email Email,
CAST(GETDATE() AS CHAR(30)) + ' request created date ' AS Comments
FROM dbo.uvw_MasterCard_getlist CC
LEFT OUTER JOIN tblClient C ON CC.clientID = C.clientID
AND C.clientTypeID IN(1, 2, 3, 101, 102)
INNER JOIN tlnkClient_person CP ON C.clientID = CP.clientID
AND CP.isDefault = 1
INNER JOIN tblPerson P ON P.personID = CP.personID
INNER JOIN tlnkPerson_Address PA ON P.personID = PA.personID
AND PA.isDefault = 1
INNER JOIN tblAddress A ON PA.addressID = A.addressID
INNER JOIN tlkpCountry LKPCOU ON A.countryID = LKPCOU.countryID
WHERE(@inpClientID IS NULL
OR C.clientID = @inpClientID)
AND LEN(LTRIM(RTRIM(CC.expiry))) = 4
AND CONVERT(CHAR(10), EOMONTH(CONVERT(DATETIME, CAST(SUBSTRING(cc.expiry, 0, 3) AS CHAR(2)) + '/01/' + CAST(SUBSTRING(cc.expiry, 3, 2) AS CHAR(2)))), 111) >= CONVERT(CHAR(10), GETDATE(), 111)
ORDER BY c.clientID ASC,
CC.MasterCardID ASC;
END;
All-Star
47700 Points
17659 Posts
Re: Entity framework stored procedure issue
Sep 23, 2019 10:43 AM|PatriceSc|LINK
You likely need to update the EDMX by hand to tell the "Key" column is a primary key (the query returns a single row for each clientID value ?)
Or you are reading only and don't intent to then update the same entity ?