How to call a stored procedure that returns a list<int> using .net core entity framework

user_2462563 0
Viewed 1701

I am migrating a .Net Framework project to .Net Core. In my .Net Framework code I call a stored procedure and it returns a list<int> in a datatable:

 DataTable dt = new DLDistinctWarehousePackingOperation().CreatePacking(packingId, userId, 
 previousOrderId, totalAwaitingOrdersAllowed);
        List<int> items = new List<int>();
        foreach (DataRow dr in dt.Rows)
        {
            items.Add(int.Parse(dr["orderid"].ToString()));
        }
        return items;

This is the .Net Core code I have come up with until now:

List<int> items = dbContext.FromSql("CreatePacking")
            .Select(t => new List<int> { t.OrderId.Value })
            .ToList();

But it doesn't work. The problem is that an object needs to follow dbContext - like this dbContext.MyObject.FromSql - but in this case it should just be a List of ints - the stored procedure is not connected to any specific object. And how can I return a List<int>? or perhaps it has to be converted into a class? Thanks in advance. enter image description here

asp.net-core .net-core .net c#
3 Answers

Assume the stored procedure name is CreatePacking, define the property in your DbContext. For example

public class MyContext : DbContext
{
    ...............
    
    [NotMapped]
    public DbSet<CreatePackingResult> CreatePackingResult { get; set; }
    
    ...............
    ...............
}

And then define the result class

 public class CreatePackingResult
 {
      public int OrderId { get; set;}
 }

Finally

 List<int> items = dbContext.CreatePackingResult.FromSqlRaw("EXECUTE [dbo].[CreatePacking]")
                  .Select(t => t.OrderId)
                  .ToList();
user_17802550

Below is a simple demo,you can check it.

Simple class:

public class Student
{
    public int Id { get; set; }
    public string Name { get; set; }
}

Procedure

 CREATE PROCEDURE GetStudent
     -- Add the parameters for the stored procedure here
     @Name VARCHAR(50)
    AS
    BEGIN
     -- Insert statements for procedure here
     SELECT * FROM Student WHERE [Name] = @Name
    END
    GO

Get item:

 var user = "xx"
 List<int> items = _context.Student.FromSqlRaw($"EXECUTE dbo.GetStudent{user}").ToList().SelectMany(t => new List<int> { t.Id }).ToList();
user_139730370

The procedure doesn't really produces a list of numbers, it returns a table with one column of integer.

So you should get back an object with one integer property.

user_123729920
Related