1

First of all, I've searched for solutions but maybe I've not done it well because I might be missing some concepts.

So my problem: I'm developing an application to work with a database, using Entity Framework. I create the model using "EF Designer from database", import all tables, functions and stored procedures. The problem comes next. When C# generates the code, it also generates class like "storedProcedureName_Result" which, as the name says, is the result of the stored procedure. C# generates code like:

 public virtual ObjectResult<uSP_ListSpecialities_Result> uSP_ListSpecialities()
    {
        return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<uSP_ListSpecialities_Result>("uSP_ListSpecialities");
    }

and without the result : 

public virtual ObjectResult<Nullable<int>> uSP_ListAvailableMedicBi()
        {
            return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<Nullable<int>>("uSP_ListAvailableMedicBi");
        }

I need to use those results but C# does not generate those results to all the stored procedures. And that's my problem, I've no idea why.

e.g : This Stored Procedure generates the result class:

CREATE PROCEDURE uSP_ListSpecialities
AS
BEGIN
    SELECT  id, nome
    FROM Especialidade 
END

it generates an uSP_ListSpecialities_Result class

but this one doesn't:

CREATE PROCEDURE uSP_ListAvailableMedicBi
AS
BEGIN
    SELECT P.biPessoa
    FROM Pessoa P
        LEFT JOIN Medico M
            ON( M.biPessoa = P.biPessoa)
    WHERE M.biPessoa IS NULL
END

I ran both on SQLSMS and both returned what's supposed.

Thank you in advance

Improve this question
5
  • From what I remember, when importing the sp through the designer, you can select Create new complex type. Commented Aug 26, 2015 at 11:55
  • @Ric You're right but why someone would want to generate complex result for a procedure which just returns Nullable<int> ? Commented Aug 26, 2015 at 11:59
  • No idea, doesn't make too much sense, there maybe a reason? Commented Aug 26, 2015 at 12:01
  • @Ric I also found that, the thing is, the complex type does not have the result on the combo box Commented Aug 26, 2015 at 12:04
  • I don't want a nullable, that's the point, I want a result class that has a property which is biPessoa Commented Aug 26, 2015 at 12:04

2 Answers 2

Reset to default
1

uSP_ListAvailableMedicBi Procedure returns Nullable Integer so what's the point for generating result class if it's just a standard .NET type ?

EDIT.

According to Your comment. It does return a table for You. It just didn't create a complex type since it's no needed. Snippet below will help You:

public List<int> GetAvailableMedicBiList()
{
    using(var ctx = YourEntities())
    {
        return (List<int>)ctx.uSP_ListAvailableMedicBi().ToList();
    }
}
Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

My point is, uSP_ListAvailableMedicBi is supposed to return a table with the P.biPessoa attribute. uSP_ListSpecialities generates a result. Ok, I did not specify the return parameter, but why does one generate a result and the other one generate a nullable?
Then do as suggested and select the SP in the model browser and create new complex type.
@DanielS. because first procedure returns table with more than one fields so it had to create a type which holds those fields. The second procedure return just a simple int so why should it create a class for it - it makes no sense
@MajkeloDev yes, that makes sense. As I said, I'm missing some concepts :) Just another thing. It returns a nullable, but biPessoa is Primary Key from the table Pessoa. Where should I specify that I don't want the nullable value? Because, if I don't specify it i will have a List<int?> and there's no need since I know it will have a value
If it's a PK it shouldn't create nullable type - that's strange. Go to sql client You are using and make sure that Pessoa.biPessoa is a "int not null" type.
|
1

I created a dummy sp returning an int and get the following:

enter image description here

You can see here that I've selected Complex as returns collection of and I clicked at the bottom Create New Complex Type which created the object for me, which is what I assume you want.

Improve this answer

7 Comments

OP got what he wanted but i can't find a reason for it.
On your SP, you defined the value as an OUTPUT?
The sp was more simple than yours, just a SELECT 1 - that's all.
Oh, alright. But has @MajkeloDev said, there's no need to generate a class. But thank you for the explanation!
You could technically create a class that has the property of your column, but for just a single column being returned may not warrant such complexity, the built in types should suffice - the call is yours to make.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.