CODEWITHSUNDEEP
c#linqgenericsentity-framework-6
Pooria.Shariatzadeh
Pooria.Shariatzadeh

Reputation: 301

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

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)

Upvotes: 1

Views: 1692

Answers (1)

StuartLC
StuartLC

Reputation: 107247

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.

Upvotes: 1

Related Questions