locked
Call a stored procedure from webapi method using EF in .Net Core application --- RRS feed

  • Question

  • User-215451226 posted

    Hi,

    In my .Net Core based web application I am accessing and using a MSSQL database. Through entity framework. Initially there were only tables.
    I have now written a stored procedure and am looking for ways to call it through my EF code. I have searched some links. Some of them are suggesting things like FromSql, ExecuteSqlCommand etcetc. but I am so confused as to how to put the thing together. The trouble here is, I wrote the SP on the MSSQL db and it is not based on one specific table but yields a result based on the join on several tables as you will see below. Do I need to add something in my applicationDbContext class? If so how? Or do I add some kind of scaffolding or migration to incorporate this newly written SP into my context?

    I'll provide some of the relevant code:

    1. my SP --

    create procedure [dbo].[sp_GetAllOrders]
    @Id bigint=null
    
    as
    begin
    
    begin try
    
    select p.product_name,
    	   o.order_id,
    	   o.product_quantity,
    	   o.discount_perc,
    	   o.order_date,
    	   o.total_price,
    	   c.customer_email from Orders o	
    
    inner join Customers c on
    
    o.customer_id=c.Id
    
    inner join Products p on
    
    p.Id=o.product_id where c.Id=@Id			
    
    end try
    
    begin catch
    	select error_message(),
    		   error_severity(),
    		   error_number(),
    		   error_state()	
    end catch
    
    end


    2.  my appdbcontext --

    public class ApplicationDbContext: DbContext
        {
            public ApplicationDbContext(DbContextOptions options)
                : base(options)
            {
    
            }
    
            public DbSet<Product> Products { get; set; }
    
            public DbSet<Customer> Customers { get; set; }
    
            public DbSet<Order> Orders { get; set; }
            
        }


    Now, in a WebApi method, I am trying to call this SP and show the results. I have a sample/model class called OrderSummary which is supposed to get mapped to the data returned by the SP.

    4. OrderSummary --

    public class OrderSummary
        {
            public string product_name { get; set; }
    
            public string order_id { get; set; }
    
            public int product_quantity { get; set; }
    
            public int discount_perc { get; set; }
    
            public DateTime order_date { get; set; }
    
            public int total_price { get; set; }
    
            public string customer_email { get; set; }
        }


    So what do I do next? How to go about this task? Someone please help me step by step, as I am quite confused with this at the moment; any way I can call this SP [by passing the necessary parameter] through my instance of appdbcontext? Please show me,


    Many Thanks in advance,

    Monday, May 28, 2018 6:06 PM

Answers

  • User-215451226 posted

    Hi again,

    Yes, I found an alternate workaround and have implemented this already. I'll post the links.
    Thanks.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, June 3, 2018 4:46 PM

All replies

  • User1120430333 posted

    You have a stored procedure that is really doing nothing that you couldn't do with a Linq query on the same tables with doing a Linq projection, if the tables were on the EF virtual model. So why are you not using Linq doing an inner join and doing a projection?

    Sometimes, you may use a Linq projection or even a stored procedure with the resulting object or object graph that has nothing to do with any EF virtual object on the model. You can't use any DBSet to get the results back. 

    So what do you do is this case when the results of the query sproc or Linq query results in nothing you have on the EF virtual object model that you can use?

    You have to use custom object that's not on the EF virtual object model, like a DTO and send the DTO as an individual object or a collection of objects in a List<T> that has nothing to do with objects on the EF virtual object model. 

    https://www.codeproject.com/Articles/1050468/Data-Transfer-Object-Design-Pattern-in-Csharp

    The simple inner join example shows you how to do a Linq query that is doing a Linq projection, which is using anaymous objects. You don't want to use the anaymous objects, becuase you can't send the anaymous objects anywhere. 

    https://msdn.microsoft.com/en-us/library/gg509017.aspx

    What you want is a Linq projection that is using the custom object, like a DTO becuase you can send the DTO anywhere.

    The link shows you how to do a Linq projection that is using a custom object/custom type, a concrete object like a DTO

    https://csharp-station.com/Tutorial/Linq/Lesson02

    So what you want to do is use a Linq inner join, that is doing a Linq projection and with the Linq projection projecting out custom DTO objects.

    Monday, May 28, 2018 7:37 PM
  • User-215451226 posted

    Hi @DA924

    point noted. I will keep this suggestion in mind and develop on it.

    But consider a scenario if I have a much bigger and complex SP [I agree this SP here, is relatively small and easier]. In that case, it would be a tough problem writing it in LINQ lambda. ANd would require to be called from code through the EF like I originally was wondering. How would I do it? What are the additional code blocks I need to write in my dbcontext, model class, migrations etc, that would enable me to do the same? Can you tell me?
    It would be a huge asset to know how,

    Thanks,

    Tuesday, May 29, 2018 1:57 PM
  • User475983607 posted

    Your question is related to EF Core capabilities not Web API.

    EF Core 2.0 has limitations where it can only return entity types.

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

    Upgrade to ASP Core 2.1 RC if you must return custom result sets composed of two or more tables.

    https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-2.1

    https://docs.microsoft.com/en-us/ef/core/modeling/query-types

    Tuesday, May 29, 2018 2:14 PM
  • User1120430333 posted

    What are the additional code blocks I need to write in my dbcontext, model class, migrations etc, that would enable me to do the same? Can you tell me?

    I can't tell you about EF code first using Core and what to do. I can only tell you about EF6 DB first and from an MS SQL Server preceptive on how to execute the stored procedure. But what is happing with a complex stored procedure still results in a return result that doesn't match anything on the EF virtual object model that you can use as an object to return. So you still will need to use a custom object like a DTO and a datareader to read the returned results and populate a custom object or objects being placed in a collection foe the return.

    You're using MySQL so my advise to you is post to the MSDN EF forum, as there are top-guns in the forum that can guide you.

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=adodotnetentityframework

    Tuesday, May 29, 2018 3:04 PM
  • User-215451226 posted
    No,
    I am using MSSQL Only, not mysql.
    Thanks,
    Wednesday, May 30, 2018 7:55 AM
  • User1120430333 posted

    No,
    I am using MSSQL Only, not mysql.
    Thanks,

    Regardless, post to the MSDN EF forum for support.

    Wednesday, May 30, 2018 4:04 PM
  • User-215451226 posted

    Hi again,

    Yes, I found an alternate workaround and have implemented this already. I'll post the links.
    Thanks.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, June 3, 2018 4:46 PM