CODEWITHSUNDEEP
sql-serverentity-frameworkstored-procedures
Vector
Vector

Reputation: 747

Generate stored procedures for Entity Framework with Reverse POCO generator

I'm using Entity Framework Reverse POCO Generator 2.19.0 to work with an existing database

Everything is fine until I add a sproc that does a SELECT from multiple tables (including temporal ones) and I expect the tool (POCO Generator) to generate an Entity to store the returned values from that sproc but it justs generates a function that returns an Int instead of the expected object.

How should I structure the sproc or what kind of SELECT the poco generator expects?

Here's the procedure

CREATE PROCEDURE [dbo].[GetAccountStatement] --Historial de transacciones
@MembershipID BIGINT
, @pagina INT = 1
, @npag INT = 100
AS
DECLARE @sql NVARCHAR(MAX),
        @offset INT,
        @NumeroTransacciones BIGINT,
        @AccountID BIGINT

SELECT
    @offset = (@pagina - 1) * @npag

SET FMTONLY OFF

SELECT
    transactionCheck_Rel.CheckID,
    AmountTotal AS Accrual,
    0 AS Redemption INTO #TransactionAux_t4
FROM transactionCheck_Rel
JOIN [transaction]
    ON [transaction].TransactionID = transactionCheck_Rel.TransactionID
    AND TransactionTypeID = 4

SELECT
    transactionCheck_Rel.CheckID,
    0 AS Accrual,
    AmountTotal AS Redemption INTO #TransactionAux_t3
FROM transactionCheck_Rel
JOIN [transaction]
    ON [transaction].TransactionID = transactionCheck_Rel.TransactionID
    AND TransactionTypeID = 3


SELECT
    #TransactionAux_t4.CheckID,
    #TransactionAux_t4.Accrual,
    ISNULL(#TransactionAux_t3.redemption, 0) AS redemption INTO #TransactionAux
FROM #TransactionAux_t4
LEFT JOIN #TransactionAux_t3
    ON #TransactionAux_t3.CheckID = #TransactionAux_t4.CheckID
ORDER BY #TransactionAux_t4.CheckID

INSERT INTO #TransactionAux
    SELECT
        #TransactionAux_t3.CheckID,
        #TransactionAux_t3.Accrual,
        ISNULL(#TransactionAux_t3.redemption, 0) AS redemption
    FROM #TransactionAux_t3

SELECT
    #TransactionAux.CheckID,
    [dbo].[check].StoreID,
    store.SystemName,
    dbo.[check].NetAmount,
    account.AccountID,
    #TransactionAux.Accrual,
    #TransactionAux.redemption,
    account.InitialBalance,
    account.FinalBalance,
    dbo.[transaction].TransactionID,
    dbo.[transaction].BusinessDate,
    dbo.membershipAccount_Rel.MembershipID

FROM dbo.account
LEFT JOIN dbo.assignCheck
    ON assignCheck.AccountID = account.AccountID
LEFT JOIN [dbo].[check]
    ON [check].CheckID = assignCheck.CheckID
LEFT JOIN #TransactionAux
    ON #TransactionAux.CheckID = [check].CheckID
JOIN dbo.[transaction]
    ON [transaction].AccountID = account.AccountID
JOIN dbo.transactionDetail
    ON transactionDetail.TransactionID = [transaction].TransactionID
LEFT JOIN transactionCheck_Rel
    ON transactionCheck_Rel.TransactionID = [transaction].TransactionID
LEFT JOIN dbo.store
    ON store.StoreID = [check].StoreID
JOIN dbo.membershipAccount_Rel
    ON membershipAccount_Rel.AccountID = account.AccountID
ORDER BY membershipAccount_Rel.MembershipID ASC OFFSET @offset ROWS FETCH NEXT @npag ROWS ONLY

Upvotes: 0

Views: 2582

Answers (1)

BBauer42
BBauer42

Reputation: 3657

This is what Simon Hughes, the author of the reverse POCO generator states in the discussion forum for the project as a response to this question.

Question:

"I have Temp Table using in Stored PRoc. But the class is not generated due to using this temp table in SP"

Answer:

"Nothing that can be done in the generator. What you can do however is to change the stored procedure into a table valued function, and define the table you are returning. I'm sure I recently mentioned this somewhere and gave an example."

I have looked through the discussion forum further and have yet to find the example he references. You may be better off asking your question directly on that forum. I have asked questions there before and Simon is very responsive.

This SO post may also be helpful.

Upvotes: 2

Related Questions