Execute non-query procedure not working asp.net core

Question

I want to execute a stored procedure which returns three values (Email, Name, CompanyID) and get one parameter (CompanyID) but it's not working.

I have created a class with these properties and a stored procedure which returns the data. By it is showing DatabaseFacade error.

My code is:

 List<MyClass> AppUser = new List<MyClass>(); //Class with three properties
 SqlParameter param1 = new SqlParameter("@CompanyID", CompanyID);
 AppUser = _context.Database.SqlQuery<CC>("GetUserAndRolesForCompany", param1).ToList();

Showing this error: I have include System.Linq

'DatabaseFacade' does not contain a definition for 'SqlQuery' and no extension method 'SqlQuery' accepting a first argument of type 'DatabaseFacade' could be found (are you missing a using directive or an assembly reference?)

Possible duplicate of [How to run stored procedures in Entity Framework Core?](http://stackoverflow.com/questions/28599404/how-to-run-stored-procedures-in-entity-framework-core) — Panagiotis Kanavos, Nov 04 '16 at 17:08
That sounds like a compilation error. It's not that the procedure isn't working, you are using the wrong syntax. The correct method is `FromSql` as shown in the duplicate question — Panagiotis Kanavos, Nov 04 '16 at 17:09
This returns DbSet but why use it for returning a list from Stored Procedure can there is any way to return list from SP? — Fahad, Nov 04 '16 at 17:56

score 1 · Answer 1 · answered Jun 28 '18 at 06:53

Take help from this link https://learn.microsoft.com/en-us/ef/core/querying/raw-sql

In Core 2.0 or EntityFramework 7 does not support SqlQuery feature which was available in previous version of ef 6.

Below is the example how you can execute sp in EntityFramework 7.

 public List<UserDetails> GetUserDetailsUsingSP(int LoggedInID)
        {
            var loggedInUser = new SqlParameter("Id", LoggedInID);

            return WidServices
                .FromSql("EXECUTE WID_Services_GetAll  @Id ", loggedInUser)
                .FirstOrDefault();
        }

Note : Add this method where you are adding your DbSet under your main db context class and then call this method by instantiating your dbContext.

Execute non-query procedure not working asp.net core

1 Answers1

Linked