locked
Entity framework stored procedure issue RRS feed

  • Question

  • User-1471881183 posted

    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;

    Monday, September 23, 2019 10:27 AM

Answers

  • User753101303 posted

    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 ?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, September 23, 2019 10:43 AM