I have a stored procedure GetVideo in a SQL Server 2019 database that does this:

select 
    [video.id] = vid,
    [video.title] = title
from 
    videos
for json path, root('x');

Which returns this JSON:

{
    "x": 
    [
    {
        "video": 
        {
            "id": 11,
            "title": "Forest Gump"
        }
    }, 
    {
        "video": 
        {
            "id": 22,
            "title": "Merry Christmas"
        }
    }
    ]
}

However using C# and EF Core 3.1 generates an error:

Sequence has no elements

public class Sp
{
    public List<video> XV {get; set;}

    public class video
    {
        public int id {get; set;}
        public string title {get; set;}
    }
}

public async Task<ActionResult<IEnumerable<Sp>>> X()  // Task<IActionResult> same error
{
    // this line throws "Sequence has no elements", same for .ToArrayAsync()
    var v = await _context.Sp.FromSqlRaw("dbo.GetVideo").ToListAsync(); 
    return Ok(a);
}

Update

1. My DbContext class does have this line omitted in original question: public virtual DbSet<Sp> Sp { get; set; }.

2. .FromSqlRaw() always works for calling a stored procedure. That is, it works without for json path, root('x') in stored procedure along with builder.Entity<SP>().HasNoKey().

Unfortunately I'm locked in with JSON being returned from SQL Server and EF Core version 3.1. At the moment I'm wondering if DbSet<Sp> is right because it may expects table-like returns therefore Sequence contains no elements?

I need to find a way to call a stored procedure which returns JSON instead of table-like data.

CodePudding user response：

I think you probably need to break your _context.FromSqlRaw() and ToListAsync() into two separate lines and make sure that the first one is not returning an empty list. I'm not familiar with the FromSqlRaw, but "sequence contains no elements" is probably the result of trying to do something to an IEnumerable when the referenced collection is empty.

var v = await _context.Sp.FromSqlRaw("dbo.GetVideo");
if (v.Any()) return Ok(await v.ToListAsync());
return NotFound();

CodePudding user response：

First of all, from what you shared it looks like class Video should be your entity class (note I'm calling it Video with a capital V because that is recommended in all coding guidelines).

You did not share it with us, but please make sure you have this in your DB Context class:

public DbSet<Video> Videos { get; set; }

To make this easier, place class Video outside of the wrapper class SP, then maybe even remove class SP.

After that I see 2 options:

Option 1: change the stored procedure

... to contain just SELECT vid AS id, title FROM videos.

That should return exactly the data that _context.Videos.FromSqlRaw() expects. Note: it definitely does not expect JSON. For more info, see Microsoft Learn: Using SQL queries with EF Core.

The code to run the SP would become something like this:

var list = await _context.Videos.FromSqlRaw("dbo.videos").ToListAsync();

Option 2: drop the stored procedure, forget about writing SQL (my favourite option)

... and do just this:

var list = await _context.Videos.ToListAsync();

This is what normal EF Core code would and should do. No stored procedures are needed. Writing your own SQL is almost never needed, because EF Core will generate proper SQL for you on the fly.

One more thing: you will now run into the problem that the database table uses vid while class Video uses id. The solution would be to change either vid to id (in the DB Table), or change id to vid (in the Video class).

It is a ground rule of EF Core that property names of Entity classes match with column names in the database tables. Maybe there are ways to make it work if they don't match, but I wouldn't know if there are, and it's just easiest and clearest if they are the same.