How to use stored procedure in .net core 3.0

.net c# entity-framework-core

Question

Having confusion to use stored procedure in .net core because I have applied code-first approach with Entity Framework Core I did not find such as option to put an procedure, if say in mvc 5 there had kinds of way to go in .net core .edmx data model not finding yet.

IList<int> r = new List<int>();            

context.LoadStoredProc("dbo.GetProc")
        .ExecuteStoredProc((handler) =>
        {    
            r = handler.ReadToList<int>().ToList();
        });
1
0
1/22/2019 8:23:36 PM

Accepted Answer

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

10
1/11/2018 4:25:40 PM

Popular Answer

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();
}


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow