Home > Enterprise >  How to write a SQL query in EF Core
How to write a SQL query in EF Core

Time:11-19

I want to write SQL query in EF Core. I use AddDbContextFactory service. Unfortunately, it does not have SqlQuery method. How can I write my queries?

My Startup.cs

services.AddDbContextFactory<DBContext>(options =>
    options.UseSqlServer(Configuration.GetConnectionString("ConnectionStrings")),
    lifetime: ServiceLifetime.Transient);

I inject DbContextFactory in my razor page:

@inject IDbContextFactory<DBContext> DbFactory

Here is my code:

string UserId = "a5bddfc0-ee0d-4d33-b384-60629154aad9";

using DBContext db = DbFactory.CreateDbContext();
var result = db.Database.SqlQuery<List<Post>>(@$"SELECT * FROM Posts AS P WHERE P.SenderId IN (SELECT FollowingId FROM Followers WHERE FollowerId = '{UserId}'); ");

Here is the error:

'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?)

Is there any method for writing SQL queries?

Question Update:

I encounter the error even when I use AddDbContext. I use EF Core 6.

CodePudding user response:

The SqlQuery is a method for entity framework (EF), and it does not belong to EF Core.

Add using Microsoft.EntityFrameworkCore at the top of the code file.

and here is the code:

string UserId = "a5bddfc0-ee0d-4d33-b384-60629154aad9";

using DBContext db = DbFactory.CreateDbContext();
var result = db.Posts.FromSqlRaw(@$"SELECT * FROM Posts AS P WHERE P.SenderId IN (SELECT FollowingId FROM Followers WHERE FollowerId = '{UserId}'); ").ToList();
  • Related