How to execute stored procedure with input and output parameters using EF?

Question

I am using EF code first approach and I have created stored procedure using migration as following:

public override void Up()
    {
        Sql(@"CREATE TYPE IdsList AS TABLE   
                    ( 
                    Id Int
                    )
                    GO

                    Create Procedure getChildIds(
                    @IdsList dbo.IdsList ReadOnly
                    )
                    As
                    Begin
                    WITH RecursiveCTE AS
                    (
                        SELECT Id
                        FROM dbo.PhysicalObjects
                        WHERE ParentId in (Select * from @IdsList)
                        --Where Id=108
                        UNION ALL

                        SELECT t.Id
                        FROM dbo.PhysicalObjects t
                        INNER JOIN RecursiveCTE cte ON t.ParentId = cte.Id
                    )
                    SELECT * FROM RecursiveCTE
                    End");
    }

    public override void Down()
    {
        Sql(@"Drop Type IdsList
                Go
                Drop Procedure getChildIds");
    }

Now If I go to sql server management studio and execute the following scripts:

Declare @Ids dbo.IdsList

Insert into @Ids
SELECT 1

Exec getChildIds @Ids

It will execute successfuly, but Now I am trying to execute that stored procedure as following:

 var idsList = new SqlParameter {ParameterName = "idsList",  Value = new int[] { 1,2,3,4,5} };

 var idParams = new SqlParameter("idParams", SqlDbType.Structured)
            {
                Direction = System.Data.ParameterDirection.Output
            };

var results = dbContext.Database.SqlQuery<int>("getChildIds @idsList, @idParams out", idsList,idParams) ;

var idsResult = (List<int>)idParams.Value;

It does not return anything.

So how I could execute stored procedure with input and output parameters of type Table?

I don't see an OUTPUT parameter in your SP; only an input Parameter (@IdsList). Your SP, instead is returning a dataset. — Thom A
– Thom A ♦, Commented Feb 19, 2018 at 12:28
I don't see any need to be using an OUTPUT parameter here though. Personally, I would instead change your application (C#) code to expect to have a DataTable returned. You could use an OUTPUT parameter, but that seems like needless complexity, which you'll likely convert to a Datatable within the application anyway. — Thom A
– Thom A ♦, Commented Feb 19, 2018 at 12:37
Honestly, I'm not very good at writing C#. I can read it fine, hence why I could see the problem, however, I've always written in VB.Net (to read, the languages aren't too dissimilar). I've not been in a position where I've needed to learn/use C#, and thus I haven't learned how to write it. — Thom A
– Thom A ♦, Commented Feb 19, 2018 at 12:40

Crowcoder · Accepted Answer · 2018-02-19 13:15:00Z

1

You will have to obtain your data a different way:

You cannot return data in a table-valued parameter. Table-valued parameters are input-only; the OUTPUT keyword is not supported.

answered Feb 19, 2018 at 13:15

Crowcoder

11.6k3 gold badges39 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Simple Code · Accepted Answer · 2018-02-19 17:19:51Z

I have solved it this way.

First of all I have updated my stored procedure to return Ids as following:

 public override void Up()
    {
        Sql(@"CREATE TYPE IdsList AS TABLE   
                    ( 
                    Id Int
                    )
                    GO

                    Create Procedure getChildIds(
                    @IdsList dbo.IdsList ReadOnly
                    )
                    As
                    Begin
                    WITH RecursiveCTE AS
                    (
                        SELECT Id
                        FROM dbo.PhysicalObjects
                        WHERE ParentId in (Select * from @IdsList)
                        UNION ALL

                        SELECT t.Id
                        FROM dbo.PhysicalObjects t
                        INNER JOIN RecursiveCTE cte ON t.ParentId = cte.Id
                    )
                    SELECT Id From RecursiveCTE
                    End");
    }

    public override void Down()
    {
        Sql(@" Drop Procedure getChildIds
               Go
               Drop Type IdsList
               ");
    }

And here how I have solved executing stored procedure using entity framework:

 var dataTable = new DataTable();
 dataTable.TableName = "idsList";
 dataTable.Columns.Add("Id", typeof(int));
 dataTable.Rows.Add(1);
 dataTable.Rows.Add(2);

  SqlParameter idsList = new SqlParameter("idsList", SqlDbType.Structured);
  idsList.TypeName = dataTable.TableName;
  idsList.Value = dataTable;

  var results = dbContext.Database.SqlQuery<int>("exec getChildIds @idsList", idsList).ToList();

I hope my code will help others having the same issue

Igor Valikovsky · Accepted Answer · 2018-06-05 04:45:42Z

0

The best solution for using user's procedures/functions and build-in functions in SQL is this library - https://github.com/Dixin/EntityFramework.Functions

It has great and easy implementation. Also it allows to follow code first approach

In your case you need to use Table-valued function

Define model

[ComplexType]
public class IdModel
{
     public int Id { get; set; }
}

Declare Type and Convention in DbContext

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Conventions.Add(new FunctionConvention<DbContext>());

    modelBuilder.ComplexType<IdModel>();

    base.OnModelCreating(modelBuilder);
}

Define function in DbContext

[Function(FunctionType.TableValuedFunction, nameof(ufnGetChildIds), Schema = "dbo")]
public IQueryable<IdModel> ufnGetChildIds([Parameter(DbType = "IdModel", Name = "IdsList")]List<IdModel> IdsList)
{
    ObjectParameter IdsListParameter = new ObjectParameter("IdsList", IdsList);

    return this.ObjectContext().CreateQuery<IdModel>(
        $"[{nameof(this.ufnGetChildIds)}](@{nameof(IdsList)})", IdsListParameter);
}

And use it from your DbContext

[TestMethod]
public void CallTableValuedFunction()
{
    using (DbContext database = new DbContext())
    {
        IQueryable<IdModel> employees = database.ufnGetChildIds(new List<IdModel> { new IdModel { Id = 1 }});
    }
}

edited Jun 5, 2018 at 4:45

answered Jun 5, 2018 at 4:14

Igor Valikovsky

5965 silver badges5 bronze badges

1 Comment

4b0 Over a year ago

Igor, please don't just post some tool or library as an answer. At least demonstrate how it solves the problem in the answer itself.

Collectives™ on Stack Overflow

How to execute stored procedure with input and output parameters using EF?

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related