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 dotnet/efcore#1862 and dotnet/efcore#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

[kobruleht]

Tried to use weitzhandler code in ASP.NET MVC Core 5 with npgsql.

Line
command.Parameters.Add(p);

throws error

ArgumentNullException: Value cannot be null. (Parameter 'value')
Npgsql.NpgsqlParameterCollection.Add(NpgsqlParameter value)

How to fix this code so that parameters with null values are also accepted ?

[Mart-Bogdan]

@kobruleht , you can't pass nulls to SqlParameters. Use DbNull.Instance or something like that.

But this should not be case with EF queries.

But you are referring to ADO.net and SqlCommand which as ALLWAYS representing nulls with DbNull since classic .Net Framework 1.0

[kobruleht]

Should if statement added which passes DbNull in parameter is null ?

I posted it also in https://stackoverflow.com/questions/65754699/get-value-from-sql-select-if-parameter-may-be-null

[Mart-Bogdan]

You could use ?? Operator to replace values with DbNull

But better to use EF method, it would convert nulls, and would be able to map back to classes

[kobruleht]

I need to run arbitrary sql queries which can return string, decimal?, bool? and other scalar values. There is no EF class for result. How to use EF method for this ? Like

var total = ctx.ExecuteScalar<decimal?>(@"select ... where p1={0}", null);

var res = ctx.ExecuteScalar<string>(@"select ... where p1={0}", null);

I found some code which injects keyless type dynamically to DbContext in OnModelCreated method. Maybe this can used if EF method is better.

[kobruleht]

I changed

command.Parameters.Add(p);

to

command.Parameters.Add(p ?? DBNull.Value);
but got exception

InvalidCastException: The value "" is not of type "NpgsqlParameter" and cannot be used in this parameter collection.
Npgsql.NpgsqlParameterCollection.Cast(object value)

[KushAlayaCare]

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();
  }
}                                                         

this does not support something like List as a return type?

[kobruleht]

I dont need List resturn type. Parameters passed to this method are object types.
It looks like Add requires NpgsqlParameter type as parameter type.

Tried your code as:

        public async Task<T> ExecScalarAsync<T>(string rawSql, params object[] parameters)
        {
            var conn = Database.GetDbConnection();
            using var command = conn.CreateCommand();
            command.CommandText = rawSql;
            if (parameters != null)
                foreach (var p in parameters)
                    command.Parameters.Add(p);
            // https://stackoverflow.com/questions/57645042/net-core-ef-cleaning-up-sqlconnection-createcommand
            //EF Core internally does that when processing commands which need open connection -open it at the beginning, close it when done.Except if it was opened externally, in which case they do nothing.
            bool wasOpen = command.Connection.State == ConnectionState.Open;
            if (!wasOpen) await command.Connection.OpenAsync();
            try
            {
                var res = await command.ExecuteScalarAsync();
                if (res == DBNull.Value || res == null)
                    return default;
                return (T)res;
            }
            finally
            {
                if (!wasOpen) command.Connection.Close();
            }

        }

But got exception:

``` The value "LV" is not of type "NpgsqlParameter" and cannot be used in this parameter collection.
System.InvalidCastException: The value "LV" is not of type "NpgsqlParameter" and cannot be used in this parameter collection.
   at Npgsql.NpgsqlParameterCollection.Cast(Object value)
   at Npgsql.NpgsqlParameterCollection.Add(Object value)
   at MyApp.ExecScalarAsync[T](String rawSql, Object[] parameters) ...