Stored Procedure in dot net core

Question

Below is my method, which calls a stored procedure.

public List<int> GetActivityListforUser(string userId)
{
    IList<int> results = new List<int>();            
    context.LoadStoredProc("dbo.GetRegionOrganizationActivities")
            .WithSqlParam("userId", userId)
            .ExecuteStoredProc((handler) =>
            {    
                results = handler.ReadToList<int>().ToList();
            });

    return results.ToList();
}

My stored procedure dbo.GetRegionOrganizationActivities, returns only one column Id, which is the required result up on passing the parameter userId.

My issue is in the following line:

return results.ToList();

I can see all the list that comes from the stored procedure, but all int values are 0. The list count matches with stored proc result count, but values should be example: 1,2,3, etc. it shows 0,0,0, etc.

Can any one provide some insight on what I am doing wrong?

what is the data type of the column? Could be that you are using the wrong .net type — Nkosi, Jul 25 '18 at 19:18

score 0 · Accepted Answer · answered Jul 25 '18 at 19:50

It seems you are using the Snickler.EFCore nuget package with EF Core.

Short answer: You'll have to create a complex object to allow this library to map your result set, something like:

public class RegionOrganizationActivity
{
    public int Id { get; set; }
}

then you can use this to extract your list of integers:

public List<int> GetActivityListforUser(string userId)
{
    IList<RegionOrganizationActivity> results = new List<RegionOrganizationActivity>();            
    context.LoadStoredProc("dbo.GetRegionOrganizationActivities")
            .WithSqlParam("userId", userId)
            .ExecuteStoredProc((handler) =>
            {    
                results = handler.ReadToList<RegionOrganizationActivity>();
            });

        return results.Select(activity => activity.Id).ToList();
    }

Explanation:

handler.ReadToList<int>() won't work here because the ReadToList<T> implementation only supports complex types. It is assuming it needs to create an instance of T and try to match properties to the columns.

Because there are no properties to match when T is int, the latter part fails. This explains why you are getting all values of 0: the only thing the method manages to do is an Activator.CreateInstance<int>() which will return 0.

Their other helper method ReadToValue only supports a single result value, which won't work either.

This means you can't use this library to map your result straight to a List<int>. So you'll need to use complex object to match the result set.

bajivali shaik · Answer 2 · 2018-07-25T20:09:28.283

-1

This is my final method.

public List<int> GetActivityListforUser(string userId)
    {

        List<ActivityId> results = new List<ActivityId>();
        context.LoadStoredProc("dbo.GetRegionOrganizationActivities")
                .WithSqlParam("userId", userId)
                .ExecuteStoredProc((handler) =>
                {
                    results = handler.ReadToList<ActivityId>().ToList();
                });
        List<int> finalresult = new List<int>();

         finalresult = results.Select(a=>a.Id).ToList();

        return finalresult.ToList();

    }

    public  class ActivityId
    {
        public int Id { get; set; }
    }

edited Jul 25 '18 at 20:09

answered Jul 25 '18 at 19:57

bajivali shaik

81
2
13

This is extremely inefficient code. Why are you calling `ToList()` on a `List`? You should have just used `return results.Select(x => x.Id).ToList()`, as the answer by @RuudKobes shows, and avoid those two unnecessary `List` allocations – Camilo Terevinto Jul 25 '18 at 22:07

Stored Procedure in dot net core

2 Answers2

Linked