Is this possible run query SQL select statement on dynamically table name?

Question

Is this possible run query SQL select statement on dynamically table name?

.net c# ef-core-3.0 entity-framework-core sql

Question

I create dynamically table when website is active with this code:

public void CreateTable(string tableName, string field1, double field2, ...)
{
  string Raw = string.Format("CREATE TABLE [{0}] ([ID] UNIQUEIDENTIFIER NOT NULL, [{1}] NVARCHAR (MAX) NOT NULL, [{2}] FLOAT NOT NULL, ..., , CONSTRAINT[PK_{0}] PRIMARY KEY CLUSTERED([ID] ASC);", tableName, field1, field2);
  _context.Database.ExecuteSqlRaw(Raw);
  _context.SaveChanges();
}

Now I want run query SQL select something like this::

private readonly ApplicationDbContext _context;
public HomeController(ApplicationDbContext context)
{
   _context = context;
}
public void List(string tableName)
{
   string Raw = string.Format("SELECT * FROM [{0}];", tableName);
   var table = _context.Database.ExecuteSqlRaw(Raw);
   //--- Do something with table here
}

NOTE: I try using reflection like this:

var table = _context.GetType().GetProperty(tableName).GetValue(_context) as IQueryable;

But not working and return NULL.

I'm still a newbie on C#, really appreciate if you can show me example based on this code.

Xerxes Asimov

Related Questions

View more on Stack Overflow

liang.good · Accepted Answer · 4/10/2020 1:44:44 AM

you can do it with a Command with to get dynamic objects.

first create a function to get the datarow.

 public static IEnumerable<dynamic> CollectionFromSql(this DbContext dbContext, string Sql, Dictionary<string, object> Parameters)
{
    using (var cmd = dbContext.Database.GetDbConnection().CreateCommand())
    {
        cmd.CommandText = Sql;
        if (cmd.Connection.State != ConnectionState.Open)
            cmd.Connection.Open();

        foreach (KeyValuePair<string, object> param in Parameters)
        {
            DbParameter dbParameter = cmd.CreateParameter();
            dbParameter.ParameterName = param.Key;
            dbParameter.Value = param.Value;
            cmd.Parameters.Add(dbParameter);
        }

        //var retObject = new List<dynamic>();
        using (var dataReader = cmd.ExecuteReader())
        {

            while (dataReader.Read())
            {
                var dataRow = GetDataRow(dataReader);
                yield return dataRow ;

            }
        }


    }
}

Then add the data to the dynamic objects.

private static dynamic GetDataRow(DbDataReader dataReader)
{
    var dataRow = new ExpandoObject() as IDictionary<string, object>;
    for (var fieldCount = 0; fieldCount < dataReader.FieldCount; fieldCount++)
        dataRow.Add(dataReader.GetName(fieldCount), dataReader[fieldCount]);
    return dataRow;
}

you can use like this:

List<dynamic> MyList = MyDbContext.CollectionFromSql("SELECT * FROM @tableName",
        new Dictionary<string, object> { { "@tableName", tableName } }).ToList();

Is this possible run query SQL select statement on dynamically table name?

Question

Fastest Entity Framework Extensions

Popular Answer

Related Questions

Fastest Entity Framework Extensions

Performance

Expression Evaluator

Related