sql server entity framework WPF database first procedure with select returns list

Question

I have a problem with stored procedures in SQL Server that implement business logic on DB level where no return value is expected. In WPF application, database first approach, importing these procedures (EF) I can see return value as collection of scalars (bool, decimal etc). Problem seems to be in usage of SELECT instead of SET example:

CREATE PROCEDURE [dbo].[some_name](@UserID INT, @InvoiceID INT)
AS
BEGIN
    DECLARE @KeyAccountID INT = dbo.User_GetKeyAccount(@UserID)
    DECLARE @InvoiceStatusID INT = CAST(dbo.SPSOmeOtherName_GetValue('Invoice_XXXXX', @KeyAccountID) AS INT)
    DECLARE @ConsiderAccounts BIT = 0
    DECLARE @TotAmountTaxAccounts DECIMAL(18,2) = 0, @TaxAmountAccounts DECIMAL(18,2) = 0
    DECLARE @ProjectID INT, @OfferID INT, @ProjectExtraID INT, @RelatedType INT

    SELECT @ConsiderAccounts = invc.ConsiderAccount,
                    @RelatedType = invc.RelatedType, 
                    @ProjectID = invc.ProjectID,
                    @OfferID = invc.OfferID,
                    @ProjectExtraID = invc.ProjectExtraID
    FROM dbo.Invoice AS invc        
    WHERE invc.InvoiceID = @InvoiceID

DECLARE @TotInvItems DECIMAL(18,2) = 0, @TotInvItemsTax DECIMAL(18,2) = 0
    SELECT @TotInvItems = Sum(iit.TotalPrice),
                 @TotInvItemsTax = Sum(ISNULL(tc.Amount,0)*iit.TotalPrice)
    FROM dbo.InvoiceItem AS iit

    LEFT JOIN dbo.TaxCode AS tc ON iit.TaxCodeID = tc.TaxCodeID
    WHERE iit.InvoiceID = @InvoiceID

    IF ISNULL(@ConsiderAccounts,0) = 1
    BEGIN
        SELECT @TotAmountTaxAccounts = Sum(invc.TotAmountTax),
                     @TaxAmountAccounts = Sum(invc.TaxAmount)
        FROM dbo.Invoice AS invc
        WHERE ((invc.ProjectID = InvoiceID)
                     AND (invc.InvoiceID <> @InvoiceID)
                     AND (((@RelatedType = 2) AND (invc.OfferID = @OfferID))
                              OR ((@RelatedType = 3) AND (invc.ProjectExtraID = @ProjectExtraID))))
    END

UPDATE invc
    SET     invc.TotAmount =                            ISNULL(@TotInvItems * (1 - invc.Discount) + invc.RestAmount,0),
                invc.TotAmountTax =                     ISNULL(@TotInvItems * (1 - invc.Discount) + invc.RestAmount,0)
                                                                            + ISNULL(@TotInvItemsTax * (1 - invc.Discount),0),
                invc.TotAmountWoAccountsTax = ISNULL(@TotInvItems * (1 - invc.Discount) + invc.RestAmount,0)
                                                                            + ISNULL(@TotInvItemsTax * (1 - invc.Discount),0)
                                                                            - ISNULL(invc.TotalAccount,0),
                invc.TaxAmount =                            ISNULL(@TotInvItemsTax * (1 - invc.Discount),0)
                                                                            - ISNULL(@TaxAmountAccounts,0)


    FROM dbo.Invoice AS invc 
    WHERE invc.InvoiceID = @InvoiceID

END

If user tries to execute them, at some point I get error -> New transaction is not allowed because there are other threads running in the session. If I read output in WPF application from stored procedure to a variable with .ToList();, the error will not present itself. I suppose calling ToList() empty enumerator returned from SQL and transaction is "closed".

However the problem is that I am not expecting any return value and I am afraid that converting all SP from SELECT to SET might result in much longer execution time of SP. I can try to change result mapping in browser model of edmx (Visual Studio), but does this have side effects?

So the question is how to use SELECT in SQL and not to receive lists in EF?

Thanks, Roman

If you have just one value or what amounts to one row of data to obtain then [using OUTPUT variables](https://contrivedexample.com/2015/05/14/output-parameters-are-the-most-efficient-way-to-retrieve-a-single-row-from-a-stored-procedure/) is more efficient than `SELECT` so don't worry about increased execution times. This is assuming I understand the question, it is not entirely clear. — Crowcoder, Oct 03 '18 at 11:47

score 0 · Answer 1 · answered Oct 03 '18 at 13:43

0

that I am not expecting any return value

Then just call the sproc with ExecuteSqlCommand instead.

answered Oct 03 '18 at 13:43

ΩmegaMan

29,542
12
100
122

sql server entity framework WPF database first procedure with select returns list

1 Answers1