How to use EF .SqlQuery expression in EFCore?

Question

I'm refactoring a code and I have this question.

public class MovimentoManualListRepository : RepositoryBase<MovimentoManualList>, IMovimentoManualListRepository
{
    ConsultarContext _context;
    public MovimentoManualListRepository(ConsultarContext context) : base(context)
    {
        _context = context;
    }

    public List<MovimentoManualList> Listar()
    {
        return _context.Database
            .SqlQuery<MovimentoManualList>("ListarMovimentacao")
            .ToList();
    }
}

I get an error:

Severity Code Description Project File Line Suppression State Error CS1061 'DatabaseFacade' does not contain a definition for 'SqlQuery' and no accessible extension method 'SqlQuery' accepting a first argument of type 'DatabaseFacade' could be found (are you missing a using directive or an assembly reference?)

https://learn.microsoft.com/en-us/ef/core/querying/raw-sql – Herohtar Oct 20 '19 at 02:31 — Herohtar, Oct 20 '19 at 02:31

score 2 · Accepted Answer · edited Oct 20 '19 at 06:20

2

Do you mean FromSqlRaw.?

Basic raw SQL queries

You can use the FromSqlRaw extension method to begin a LINQ query based on > a raw SQL query. FromSqlRaw can only be used on query roots, that is directly on the DbSet<>.
var blogs = context.Blogs
   .FromSqlRaw("SELECT * FROM dbo.Blogs")
   .ToList();

The following executes a stored procedure

var blogs = context.Blogs
    .FromSqlRaw("EXECUTE dbo.GetMostPopularBlogs")
    .ToList();

Please note that:

it can be mixed and matched it with Linq queries
there are limitations

Please see Raw SQL Queries.

edited Oct 20 '19 at 06:20

marc_s

732,580
175
1,330
1,459

answered Oct 20 '19 at 02:34

tymtam

31,798
8
86
126

Can EFCore run raw Query useing context.Database? So we can select query with return data type c# maybe dateTime, or string or int, or Guid? – toha May 03 '23 at 05:14
1

Yes, with `context.Database.SqlQuery`, please see: https://learn.microsoft.com/en-us/ef/core/querying/sql-queries#querying-scalar-non-entity-types – tymtam May 03 '23 at 07:55
oke thanks This feature was introduced in EF Core 7.0. My EF Version is v5.0.17 which is that feature is still not available yet at my EF version – toha May 03 '23 at 08:19
You probably know this but EF Core is 'End of life'. Are you running on Core 3.1? – tymtam May 03 '23 at 23:00
.NET Core 5. Yes this is out of support but we still use this version – toha May 04 '23 at 01:46
Do you mean .NET 5 or EF Core 5? I understand that this depends on your team's circumstances but you may consider upgrading from .NET5 to .NET6 which supports EF Core 7. – tymtam May 04 '23 at 02:28
Ok I know this .NET 6 is LTS and promising enough. but our company still use VS 2019. We do not have license for vs2022. We can use VS Code but another developer maybe have difficulties when changing to VS Code. There are some big projects here, and taking so much time, if converting that to new .NET version. maybe later when we need new app we will use .net 6 – toha May 05 '23 at 06:37

How to use EF .SqlQuery expression in EFCore?

1 Answers1

Linked