Error CS1061: 'DbSet<T>' does not contain a definition for 'FromSql' and no extension method 'FromSql' accepting a first argument of type 'DbSet<T>'

Question

I am trying to call view or store procedure using asp.net core 2.1 on mac os webapi.

using System;
using System.Linq;
using Auth.Database;
using Microsoft.EntityFrameworkCore;

public virtual IQueryable<T> ExecuteStoreProcView(string viewProcName)
{
    IQueryable<T> queryResult = _entities.Set<T>().FromSql(viewProcName).AsQueryable();
    return queryResult;
}

Getting the below error

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

I am developing webapi using entity framework on mac os.

Research some of the queries in below link :- Raw SQL Query without DbSet - Entity Framework Core

Raw SQL Query without DbSet - Entity Framework Core

https://forums.asp.net/t/1886501.aspx?System+Data+Entity+DbSet+Entities+User+does+not+contain+a+definition+for+FirstOrDefault+

https://docs.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.relationalqueryableextensions.fromsql?view=efcore-2.1

But not able to find the error solution. Can anyone please let me know what I, am missing.

Answer 1

install Microsoft.EntityFrameworkCore.Relational nuget package and then add the using statement to the class

using Microsoft.EntityFrameworkCore;

Answer 2

This might have changed since Umer answered. It is now in the Microsoft.EntityFrameworkCore namespace. But you will need to reference the package.

So...

dotnet add package Microsoft.EntityFrameworkCore.Relational

And add this line...

using Microsoft.EntityFrameworkCore;

Answer 3

In the latest version, Microsoft renamed this method to:

FromSqlInterpolated()

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

So you'll have to find/replace all the code using .FromSql to .FromSqlInterpolated. They discuss the motivation in the above document, although I have to say the length of the new method name is unwelcome.

What the other answers say is still necessary - you'll need to have installed the Nuget package Microsoft.EntityFrameworkCore.Relational, and, have added a using statement for Microsoft.EntityFramework.

Answer 4

It solved for me with this using this line:

using Microsoft.EntityFrameworkCore;

Answer 5

Hope this might be useful as it's related to the above issue and is likely that others will have the same problem. I was not able to access .FromSql inside of a service that resided in a seperate service project from the Web.Api (MVC Project) I was able to access the .FromSql from within a controller in the Web.Api project. What confused me was that there is no direct reference in the web.api project for Microsoft.EntityFrameworkCore.Relational The Microsoft.EntityFrameworkCore.SqlServer nuget package is referenced by the EntityFramework project. By adding a reference to the service project the services are now able to use the .FromSql. It's been a long day but I still don't know why the web.api works but the service project needs a direct reference.

Answer 6

In Net core 6, the method name has changed to FromSqlRaw.

So, you need to install the bellow package if you haven't already:

PM > Install-Package Microsoft.EntityFrameworkCore.Relational

and also use FromSqlRaw instead:

using Microsoft.Data.SqlClient; //parameters must come from this namespace and not System.Data.SqlClient

var param1Value= new SqlParameter("Param1", "value1");
context.SomeEntity
       .FromSqlRaw("EXECUTE dbo.StoredProcedureName @Param1", param1Value)
       .ToList();

Answer 7

Add this line on top of the file where you are getting this error. FromSql is an extension method and is implemented in Microsoft.EntityFrameworkCore.Relational assembly.

using Microsoft.EntityFrameworkCore.Relational;