Support object/dynamic in SqlQueryValueAsync

[Misiu]

trafficstars

I have a use case where I must query a dynamic database (columns might get added/removed by another system). I have a query generator that generates raw SQL queries.

I'd like to be able to do this:

var data = await _context.SqlQueryValueAsync<IEnumerable<object>>(query, cancellationToken: cancellationToken); or var data = await _context.SqlQueryValueAsync<IEnumerable<dynamic>>(query, cancellationToken: cancellationToken);

or

var data = await _context.SqlQueryValueAsync<object>(query, cancellationToken: cancellationToken); or var data = await _context.SqlQueryValueAsync<dynamic>(query, cancellationToken: cancellationToken);

Currently, I'm getting: System.NotSupportedException: Invalid operation, supplied type is not a value type

Provide steps to reproduce

Consider a query like this: SELECT TOP 100 * from dbo.Users; or SELECT name, surname, agefrom dbo.Users where id=1234;

Provide technical details

Searching over the net I found this: https://github.com/dotnet/efcore/issues/2344#issuecomment-172641417, but ideally, this should be added to this library.

• EF Core version in use: (e.g. EF Core 6)

• Is Handlebars used: no/yes

• Is .dacpac used: no/yes

• EF Core Power Tools version: (found in About dialog - blue questionmark icon on context menu)

• Database engine: (SQL Server, Azure SQL, Postgres, Oracle, Firebird, SQLite, MySQL)

• Visual Studio version: (e.g. Visual Studio 2022)

[ErikEJ]

Try to include the source in your project and see if you can Make it work - I doubt it.

[Misiu]

I did a quick test and this works fine:

public static class DbContextExtensions
{
    public static IEnumerable<dynamic> CollectionFromSql(this DbContext dbContext, string sql)
    {
        using var cmd = dbContext.Database.GetDbConnection().CreateCommand();
        cmd.CommandText = sql;
        if (cmd.Connection != null && cmd.Connection.State != ConnectionState.Open)
            cmd.Connection.Open();

        using var dataReader = cmd.ExecuteReader();
        while (dataReader.Read())
        {
            var dataRow = GetDataRow(dataReader);
            yield return dataRow;
        }
    }

    private static dynamic GetDataRow(DbDataReader dataReader)
    {
        var dataRow = new ExpandoObject() as IDictionary<string, object?>;
        for (var fieldCount = 0; fieldCount < dataReader.FieldCount; fieldCount++)
        {
            var fieldName = dataReader.GetName(fieldCount);
            var fieldValue = dataReader.GetValue(fieldCount);
            var valueType = fieldValue.GetType();
            if (valueType == typeof(DBNull))
            {
                fieldValue = null;
            }
            if (valueType==typeof(string) && string.IsNullOrWhiteSpace(fieldValue?.ToString()))
            {
                fieldValue = null;
            }
            dataRow.Add(fieldName,fieldValue);
        }

        return dataRow;
    }
}

but it uses a different approach and isn't optimal, for example, field names could be cached.

I'll search a bit more and come back with the results.

[ErikEJ]

Any news?

[Misiu]

I was unable to use your approach. I'm generating queries by hand, but now I'm looking at Dapper because it maps results to objects much better. Not sure if I'll be able to adopt your code to handle dynamic objects and IEnumerable of dynamic objects

[ErikEJ]

OK - let's wait for EF Core 8 where mapping of complex classes will be implemented

[Misiu]

Could you please add link to EF Core issue for that?

[ErikEJ]

Sure: https://github.com/dotnet/efcore/issues/10753