EF Core 2.0 how to use SQL stored procedure

Question

I am new to EF Core 2.0 with stored procedure.

Can anyone help how to use stored procedure in my EF Core 2.0 code-first approach?

With my previous project, I had an .edmx model file, and I was using the context as below:

public IEnumerable<UserResult> GetUserResults(Entities context)
{
    if (context == null) return new List<UserResult>();
    return context.spGetUsers().Where(u => u.IsDeleted == false);
}

and the context is:

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

Thanks

Answer 1

To save someone else an hour or so...

ErikEJ's answer works perfectly but I had some extra work to do first.

Following a reverse code first migration (to an existing database with stored procedures), I had a problem where the stored procedures on an existing database did not return the standard table (e.g. list of Blog), but a different class (e.g. list of BlogTitleAndSummary) which was not in the database (and therefore the migration).

This post stated that the return must be an entity type, which I was unsure of, but another of Eriks posts pointed me in the right direction.

To get this scenario working:

I created a class of 'BlogTitleAndSummary', marked one property as the [key].

e.g.

public class BlogTitleAndSummary
{
    [Key]
    public int BlogId { get; set; }

    public string Title { get; set; }

    public string ShortSummary { get; set; }
}

Then, I added it as a DbSet on the Context e.g.

public partial class BloggingContext : DbContext
{
    public BloggingContext()
    {
    }

    public BloggingContext(DbContextOptions<BloggingContext> options)
        : base(options)
    {
    }

    // Might be best to move these to another partial class, so they don't get removed in any updates.
    public virtual DbSet<BlogTitleAndSummary> BlogTitleAndSummary { get; set; }

    // Standard Tables
    public virtual DbSet<Blog> Blog { get; set; }
    ...
}

This enabled me to use the following syntax for calling stored procedures:

NOTE: I have updated this following the comment below. Use the params in the FromSql method. Do not use string interpolation for such sql queries.

using (var ctx = new BloggingContext())
{
var dbResults = ctx.BlogTitleAndSummary.FromSql("EXEC dbo.get_bloggingSummary @UserId={0}", userId).ToList();
}

Answer 2

You can use the FromSQL method:

var blogs = context.Blogs
    .FromSql("EXECUTE dbo.GetMostPopularBlogs")
    .ToList();

https://docs.microsoft.com/en-us/ef/core/querying/raw-sql