Entityframework.docs: Explain how keyless types can be used to execute raw SQL query for "non-entities"

[Mart-Bogdan]

In EF6 there were method to call raw sql for non entity https://docs.microsoft.com/en-us/ef/ef6/querying/raw-sql#writing-sql-queries-for-non-entity-types

It's seems impossible in EF Core, and RelationalDatabaseFacadeExtensions.ExecuteSqlCommand (eg context.Database.ExecuteSqlCommand ) don't allow selects, only non-query statements.

Should I create new DbSet for non entity dynamically? or other workarounds?

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: 7e1289d5-8364-18a4-3445-6bba05c6854b
• Version Independent ID: 2680e647-f64a-7991-f821-3b5e2a845960
• Content: Raw SQL Queries - EF Core
• Content Source: entity-framework/core/querying/raw-sql.md
• Product: entity-framework
• GitHub Login: @rowanmiller
• Microsoft Alias: divega

[Mart-Bogdan]

It this Context.Query<KnowlageNodeRecurcive>().FromSql(" XXXX ").ToListAsync(); valid way of doing this?

[Mart-Bogdan]

@ajcvickers @Paul-Dempsey it's seems improper way.

I'm getting "Cannot create a DbSet for 'KnowlageNodeRecurcive' because this type is not included in the model for the context."

If data type not registered in context, as table.

Perhaps there are no way to use this, and we should be stuck with raw ADO SqlCommand. :-(

[ajcvickers]

@Mart-Bogdan See https://github.com/aspnet/EntityFrameworkCore/issues/1862 and https://github.com/aspnet/EntityFrameworkCore/issues/10753

[Mart-Bogdan]

@ajcvickers thank you so much!!! It helped.

I've added builder.Query<..> into OnModelCreating.

For other who would be interested and would be reading this thread.

P.S. should this issue be opened? It's related to updating documentation.

[odahcam]

I'm getting an Exception:

An exception of type 'System.MissingMethodException' occurred in Havan.MinhaHavan.Infra.dll but was not handled in user code: 'Method not found: 'System.Linq.IQueryable`1<!!0> Microsoft.EntityFrameworkCore.RelationalQueryableExtensions.FromSql(System.Linq.IQueryable`1<!!0>, System.String, System.Object[])'.'

But I have no idea of what that means. 😕

[odahcam]

I totally agree, to get my raw query working I had to make configurations on OnModelCreating hook and create models and it feels totally wrong.

[weitzhandler]

Here's how you can achieve this:

public static async Task<T> ExecuteScalarAsync<T>(this DbContext context, string rawSql,
  params object[] parameters)
{
  var conn = context.Database.GetDbConnection();
  using (var command = conn.CreateCommand())
  {
    command.CommandText = rawSql;
    if (parameters != null)
      foreach (var p in parameters)
        command.Parameters.Add(p);
    await conn.OpenAsync();
    return (T)await command.ExecuteScalarAsync();
  }
}

[ajcvickers]

Duplicate of #410