Raw SQL Query without DbSet – Entity Framework Core

by

It depends if you’re using EF Core 2.1 or EF Core 3 and higher versions.

If you’re using EF Core 2.1

If you’re using EF Core 2.1 Release Candidate 1 available since 7 may 2018, you can take advantage of the proposed new feature which is Query type.

What is query type?

In addition to entity types, an EF Core model can contain query types,
which can be used to carry out database queries against data that
isn’t mapped to entity types.

When to use query type?

Serving as the return type for ad hoc FromSql() queries.

Mapping to database views.

Mapping to tables that do not have a primary key defined.

Mapping to queries defined in the model.

So you no longer need to do all the hacks or workarounds proposed as answers to your question. Just follow these steps:

First you defined a new property of type DbQuery<T> where T is the type of the class that will carry the column values of your SQL query. So in your DbContext you’ll have this:

public DbQuery<SomeModel> SomeModels { get; set; }

Secondly use FromSql method like you do with DbSet<T>:

var result = context.SomeModels.FromSql("SQL_SCRIPT").ToList();
var result = await context.SomeModels.FromSql("SQL_SCRIPT").ToListAsync();

Also note that DdContexts are partial classes, so you can create one or more separate files to organize your ‘raw SQL DbQuery’ definitions as best suits you.

If you’re using EF Core 3.0 and higher versions

Query type is now known as Keyless entity type. As said above query types were introduced in EF Core 2.1. If you’re using EF Core 3.0 or higher version you should now consider using keyless entity types because query types are now marked as obsolete.

This feature was added in EF Core 2.1 under the name of query types.
In EF Core 3.0 the concept was renamed to keyless entity types. The
[Keyless] Data Annotation became available in EFCore 5.0.

We still have the same scenarios as for query types for when to use keyless entity type.

So to use it you need to first mark your class SomeModel with [Keyless] data annotation or through fluent configuration with .HasNoKey() method call like below:

public DbSet<SomeModel> SomeModels { get; set; }

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<SomeModel>().HasNoKey();
}

After that configuration, you can use one of the methods explained here to execute your SQL query. For example you can use this one:

var result = context.SomeModels.FromSqlRaw("SQL SCRIPT").ToList();
var result = await context.SomeModels.FromSql("SQL_SCRIPT").ToListAsync();

More Related Contents:

  1. EF Core returns null relations until direct access
  2. Get SQL code from an Entity Framework Core IQueryable
  3. Entity Framework Core 2.0.1 Eager Loading on all nested related entities
  4. Dynamically access table in EF Core 2.0
  5. How to mock an async repository with Entity Framework Core
  6. Cannot access a disposed object in ASP.NET Core when injecting DbContext
  7. Can I reuse code for selecting a custom DTO object for a child property with EF Core?
  8. How to instantiate a DbContext in EF Core
  9. Map category parent id self referencing table structure to EF Core entity
  10. Entity Framework Core Using multiple DbContexts
  11. Cascade deleting with EF Core
  12. How to make EF-Core use a Guid instead of String for its ID/Primary key
  13. How to seed in Entity Framework Core 2?
  14. In-memory database doesn’t save data
  15. How do you show underlying SQL query in EF Core?
  16. Configuring Dbcontext as Transient
  17. EF Core queries all columns in SQL when mapping to object in Select
  18. Entity Framework Core: Guid Greater Than for Paging
  19. ASP – Core Migrate EF Core SQL DB on Startup
  20. Entity Framework Core – Lazy Loading
  21. How to refresh an Entity Framework Core DBContext?
  22. How do I delete multiple rows in Entity Framework Core? [duplicate]
  23. Entity Framework Core Many to Many change navigation property names
  24. EF models. Navigation properties can only participate in a single relationship
  25. ef core doesn’t use ASPNETCORE_ENVIRONMENT during update-database
  26. How to load navigation properties on an IdentityUser with UserManager
  27. How to create a password protected database?
  28. Implementing recursive property loading in EF Core
  29. How to resolve .net core build error NETDSDK1061 and warning MSB3277
  30. Entity Framework Core RC2 table name pluralization

Leave a Comment