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?)
Take help from this link https://docs.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.