Generate SQL Server Select Statement from a dynamically created table.

[BringerOD]

I am pretty sure this is something I should be able to figure out on my own. But reading through all the documentation I can't seem to figure it out. So, sorry about this in advance.

I want to create a query using a dynamically created table. We create our tables in SQL Server using specific SQL Server create commands, not Entity Framework. I was able to use DynamicProperty and DynamicClassFactory to create a class. What I don't know is, how create a query and run the query and get a result back from SQL Server, using the DynamicClass.

I currently create all my queries by hand, since the tables are created at runtime, but I would prefer to use IQuerable and Linq.

Do I have to use entity framework? Right now I am directly running queries using "Microsoft.Data.SqlClient", opening up a SqlConnection etc.

This is as far as I got. We have a class called pTable that has all the properties and types.

 var props = new DynamicProperty[pTable.Properties.Count];

for (var index = 0; index < pTable.Properties.Count; index++)
 {
    var pProperty = pTable.Properties[index];

    props[index] = pProperty.Type switch
    {
        "integer" => new DynamicProperty(pProperty.Uid.ToString(), typeof(int)),
        "boolean" => new DynamicProperty(pProperty.Uid.ToString(), typeof(bool)),
        "number" => new DynamicProperty(pProperty.Uid.ToString(), typeof(decimal)),
        "string" => pProperty.StringSubType switch
        {
            "Date" => new DynamicProperty(pProperty.Uid.ToString(), typeof(DateTime)),
            "UniqueId" => new DynamicProperty(pProperty.Uid.ToString(), typeof(Guid)),
            _ => new DynamicProperty(pProperty.Uid.ToString(), typeof(string))
        },
        _ => props[index]
    };
 }

Type type = DynamicClassFactory.CreateType(props);

Thanks in advance,

Bryan

[JonathanMagnan]

Hello @BringerOD ,

This library allows you to make dynamically what you can do in type-safe mode.

It doesn't create or generate any SQL query. For this, you need an ORM such as Entity Framework.

So in short, this library doesn't do what you want (it doesn't generate any SQL at all). However, if you want an example that works with your scenario with Entity Framework, provide us a runnable example ([email protected] if you want to keep it private) that creates your type, and we will try to make it works with EF6 or EF Core depending on your preference.

Best Regards,

Jon

---

Performance Libraries
context.BulkInsert(list, options => options.BatchSize = 1000);
Entity Framework Extensions • Entity Framework Classic • Bulk Operations • Dapper Plus

Runtime Evaluation
Eval.Execute("x + y", new {x = 1, y = 2}); // return 3
C# Eval Function • SQL Eval Function

[BringerOD]

Johnathan, thanks for quick replay. In our case, we are just going to write the necessary SQL. Would be nice if there was a LINQ to SQL for dynamically created objects.