C# library to get an IQueryable<dynamic> from raw sql string

Question

I'm developing the data backend for a DevExtreme (DE) DataGrid. The DE library provides a method to return data to the grid given an IQueryable to get data. I have a situation where the data columns aren't known at compile time, but I can construct a SQL query string to get them. Unfortunately, EFCore must have a defined type in order to query data (although it seems like they might add this feature in the future https://github.com/dotnet/efcore/issues/10753). It seems like the obvious way to do this would be to find a library that could take a SQL string and return an IQueryable<dynamic>. Is there another ORM that could do this, or a way to do it with existing .NET features?

I also know DE can accept an IEnumerable<dynamic>, but I don't want to load all my data into memory at once. Giving DE an IQueryable allows it to request data by page/filter criteria.

Answer 1

may be this will be useful, if you are looking for dynamic SQL query. Apologize me if its wrong.

  public async Task<DetailsRecords> createString(SearchModel searchModel)
     {
     
     StringBuilder queryString = new StringBuilder();
                Dictionary<string, dynamic> keyValuePairs = new Dictionary<string, dynamic>();
                var sqlParameters = new List<MySqlParameter>();
                string a = searchModel.MaleFirstName;
               
    
                string mLastName = (string.IsNullOrEmpty(searchModel.MaleLastName)) ? "emptynull" : searchModel.MaleLastName;
                string mFirstName = (string.IsNullOrEmpty(searchModel.MaleFirstName)) ? "emptynull" : searchModel.MaleFirstName;
                string femaleFirstName = (string.IsNullOrEmpty(searchLicenseModel.FemaleFirstName))?"emptynull":searchModel.FemaleFirstName;
                string fLastName = (string.IsNullOrEmpty(searchModel.FemaleLastName))?"emptynull":searchModel.FemaleLastName;
                int year = (searchModel.Year <= 0) ? 0 : searchModel.Year;
                
                var rawQuery = new StringBuilder("select * from Marriagedetails where ");
    
                keyValuePairs.Add(nameof(searchModel.MaleLastName), mLastName);
                keyValuePairs.Add(nameof(searchModel.MaleFirstName), mFirstName);
                keyValuePairs.Add(nameof(searchModel.FemaleLastName), fLastName);
                keyValuePairs.Add(nameof(searchModel.FemaleFirstName), femaleFirstName);
                keyValuePairs.Add(nameof(searchModel.Year), year);
    
                foreach(var v in keyValuePairs)
                {
                    if(v.Key!="Year" && v.Value!="emptynull")
                    {
                        if(queryString.Length>1)
                        {
                            queryString.Append(" and ");
                        }
                        var parameterName = $"@"+v.Key;
                        var condition = string.Format("Upper("+v.Key+")" + "={0}", parameterName);
                        queryString.Append(condition);
    
                        MySqlParameter mySqlParameter = new MySqlParameter();
                        mySqlParameter.ParameterName = parameterName;
                        mySqlParameter.Value = Convert.ToString(v.Value);
                        mySqlParameter.MySqlDbType = MySqlDbType.VarChar;
                        sqlParameters.Add(mySqlParameter); 
                    }
                    else if(v.Key=="Year" && v.Value>0)
                    {
                         if(queryString.Length>1)
                        {
                            queryString.Append(" and ");
                        }
                        var parameterName = $"@" + v.Key;
                        var condition = string.Format(v.Key + "={0}", parameterName);
                        queryString.Append(condition);
    
                        MySqlParameter mySqlParameter = new MySqlParameter();
                        mySqlParameter.ParameterName = parameterName;
                        mySqlParameter.Value =Convert.ToInt32(v.Value);
                        mySqlParameter.MySqlDbType = MySqlDbType.Int32;
                        sqlParameters.Add(mySqlParameter);
                    }
                }
                queryString.Append(" order by year");
                rawQuery.Append(queryString);
                var count = await _marriage_LicenseContext.Marriagedetails
                    .FromSql(rawQuery.ToString(), sqlParameters.ToArray())
                    .CountAsync();

 var result = await _Context.details
              .FromSql(rawQuery.ToString(), sqlParameters.ToArray())
              .Include(x=>x.document)
              .Skip(pageNumber*pageSize)
              .Take(pageSize)
              .ToListAsync();
                    
                    }

Answer 2

It is possible to create a DbContext per query type (see this issue comment). Which would work for an anonymous type, but still requires you to know the result set at compile time.

EF Core does expose an API for executing raw sql with no results. With a little work, it is possible to expose and reuse their internal database query abstractions;

        // Expose EF Core internal ExecuteReaderAsync, used for reading database results
        // Roughly based on https://github.com/dotnet/efcore/blob/v5.0.3/src/EFCore.Relational/Extensions/RelationalDatabaseFacadeExtensions.cs#L377
        public static Task<RelationalDataReader> ExecuteReaderRawAsync(this DatabaseFacade db, string sql, IEnumerable<object> parameters, CancellationToken cancellationToken = default)
        {
            var dependencies = ((IDatabaseFacadeDependenciesAccessor)db).Dependencies as IRelationalDatabaseFacadeDependencies;
            var cmd = dependencies.RawSqlCommandBuilder
                .Build(sql, parameters);

            return cmd.RelationalCommand
                .ExecuteReaderAsync(new RelationalCommandParameterObject(
                    dependencies.RelationalConnection,
                    cmd.ParameterValues,
                    null,
                    ((IDatabaseFacadeDependenciesAccessor)db).Context,
                    dependencies.CommandLogger
                ), cancellationToken);
        }

        public static Task<RelationalDataReader> ExecuteReaderRawAsync(this DatabaseFacade db, string sql, CancellationToken cancellationToken = default)
            => ExecuteReaderRawAsync(db, sql, Enumerable.Empty<object>(), cancellationToken);

        public static Task<RelationalDataReader> ExecuteReaderInterpolatedAsync(this DatabaseFacade db, FormattableString sql, CancellationToken cancellationToken = default)
            => ExecuteReaderRawAsync(db, sql.Format, sql.GetArguments(), cancellationToken);

That way EF Core will be responsible for managing the connection lifetime. But you'll still need to step through the results, or convert them to an IEnumerable yourself.

Since the whole point of EF Core is to provide an abstraction for saving and loading objects from the database. Implementing a dynamic query feels like an anti-feature that they will never implement.