Use a variable table name for Entity Framework SqlQuery to fetch data into entity Class

Question

I want to convert string var to entity framework class but I don't now how. I have searched a lot but can not found anything

public JsonResult GetReports(string tbl)
{
    using (Context db = new Context())
    {
        // *** Problem is that tbl is string and I want to Cast tbl 
        //     to Users Class OR eny of  my other Class 
        var result= db.Database.SqlQuery<tbl>("SELECT * FROM Users");
        return Json(new { data = result});
    }
}

public partial class Users
{
    public int ID { get; set; }
    public string Name { get; set; }
    public string Family { get; set; }
    public Nullable<bool> Gender { get; set; }
    public string UserName { get; set; }
    public string Password { get; set; }
    public string Email { get; set; }
    public string Mobile { get; set; }      
    public bool Deleted { get; set; }
}

The reason of way I want to do something like that is I want to crete some dynamic method and tbl can be any of my table names (As a string)

tbl is string var it is the problem i want to create dynamic method and i have table name as string i want to convert string varable to Users Class — Pooria.Shariatzadeh
– Pooria.Shariatzadeh, Commented Oct 29, 2017 at 13:36
You can’t do that by default. You can create a switch/case structure that takes the string and creates the correct class instance. But you have to know them beforehand. — Sami Kuhmonen
– Sami Kuhmonen, Commented Oct 29, 2017 at 13:37
@SamiKuhmonen its not bad idea I'm thinking about it before but i wonder if its have better solution — Pooria.Shariatzadeh
– Pooria.Shariatzadeh, Commented Oct 29, 2017 at 13:43

StuartLC · Accepted Answer · 2017-10-30 05:58:44Z

1

Generic Approach

Given that you have strongly named classes (like Users), there should be no reason to resort to weak or dynamic approaches.

For example, if the requirement is to build a generic repository Query capability, which DRY's up the data access code and possibly introduces common cross cutting concerns like logging, instrumentation, possibly retries, and exception handling, then you can use a generic wrapper around SqlQuery<T>:

public async Task<IEnumerable<T>> TypedQuery<T>(string myQuery)
{
    try
    {
        using (var db = new DbContext(ConnString))
        {
            return await db.Database
                .SqlQuery<T>(myQuery)
                .ToListAsync()
                .ConfigureAwait(false);
        }
    }
    catch (Exception ex)
    {
        // Log error here
        // Consider if the query can be retried
        // Consider whether the exception should be propogated or swallowed
        // Should the return type extended to indicate success / failure
        return Enumerable.Empty<T>();
    }
}

Which you can invoke like so, using the generic type parameter to indicate the type of the target POCO (Users in this case):

var repo = new MyRepo();
var myUsers = await repo.TypedQuery<Users>("SELECT Id, Name FROM Users");

The benefit is that myUsers is a strongly typed collection.

Dynamic Approach

In theory it is possible to use the Type overload of SqlQuery to answer your original question. You can either pass in a System.Type directly, or I guess you could map some other key abstraction to the type, such as this:

private static readonly IReadOnlyDictionary<string, Type> MyTypeMap 
= new Dictionary<string, Type>
{
    { "users", typeof(Users)},
    { "foo", typeof(Foo)},
    ... other type maps here
};

public async Task<IEnumerable<object>> WeakQuery(string someType, string someQuery)
{
    Type typeOfMyClass;
    if (MyTypeMap.TryGetValue(someType, out typeOfMyClass))
    {
        using (var db = new DbContext(ConnString))
        {
            return await db.Database.SqlQuery(typeOfMyClass, someQuery)
                .ToListAsync()
                .ConfigureAwait(false);
            // The problem now is that you get an untyped DbRawSqlQuery
        }
    }
    return Enumerable.Empty<object>();
}

The problem is though is because of the weak typing that you would need to either re-cast the result, or work with dynamic, which will make for very fragile code when used, such as :

var repo = new MyRepo();
dynamic x = await repo.WeakQuery("users", "SELECT Id, Name, Gender FROM Users");
Assert.IsNotNull(x);
Assert.IsTrue(Enumerable.First(x).Name == "Foo");

Try to avoid SELECT * - instead, select all of the columns explicitly to map into the Users class.

edited Oct 30, 2017 at 5:58

answered Oct 29, 2017 at 13:43

StuartLC

108k18 gold badges225 silver badges294 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Pooria.Shariatzadeh Over a year ago

tanx for reply. i read bout post before but i don't want use any of dose because its not work for me i want to create dynamic method and i need to tbl var be a generic class or something like that

StuartLC Over a year ago

This doesn't make sense to me - you have a strong, well defined class Users, so why on earth would you want to go dynamic? SqlQuery<T> is a generic overload, so you are free to build your own generic wrapper around it. The dynamic workaround links above use the other overload, viz SqlQuery(Type, ..)

StuartLC Over a year ago

I've updated the answer. in my opinion, the whole point of SqlQuery is like Dapper, to be quickly able to deserialize rows into a strongly typed object. If you resort to weak typing, you are probably better off with a SqlDataReader and pulling out the fields by ordinal value.

Pooria.Shariatzadeh Over a year ago

Tanx I left my workstation but I think that is my answer. I let you now tomorrow if it's worked for me :)

Pooria.Shariatzadeh Over a year ago

thank you very much. i learn tow things from you. bout approach are good and handy i use second one but remember the first one

Collectives™ on Stack Overflow

Use a variable table name for Entity Framework SqlQuery to fetch data into entity Class

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related