Dynamically set the table name in LINQ query

Question

I am working on data warehouse application and we have 4 tables where schema is identical. Only difference between those tables is just Table Name.

Table Example:

• ps_Contractor
• ps_Employee
• ps_Union
• ps_NonUnion

Schema

• id
• hourly
• benefit
• total

Now i need to generate 4 reports based on these tables. Instead of writing 4 separate LINQ queries i would like to write single query where i can pass the table name dynamically.

The question How do i pass the table name dynamically in following LINQ query ?

var data = ( from q in _dbcontext.ps_Contractor 
join _l in _dbcontext.log on q.id equals l.tablelogid 
where q.hourly = 8
select new{
 hourly=q.hourly,
 benefit=q.benefit,
 total=q.total,
 log = l.message
}.ToList();

I have looked at all similar questions suggested by stack overflow. I do not want to use ExecuteStoreQuery.

what options do i have ?

Answer 1

Here's a way to do a dynamic function that accepts a DbSet<T> (type of database class that you want to pass as a parameter) and a specific expression to build a query on that table:

private IQueryable<T> BuildQueriedCollection<T>(Expression<Func<T, bool>> exp, DbSet<T> dbTable) where T : class
{
    var appliedQueryCollection = dbTable.AsExpandable().Where(exp);
    return appliedQueryCollection;
}

and you could call the function like so:

Expression<Func<MyClass, bool>> myExp = myList => myList... // some condition...;
var dbset = dbContext.MyTable;
var query = BuildQueriedCollection(myExp, dbset);

Answer 2

EF Core no longer has a non generic .set method but This extension class makes it easy to query your table based on a string using dynamic Linq

public static class DbContextExtensions
{
    public static IQueryable<Object> Set(this DbContext _context, Type t)
    {
        return (IQueryable<Object>)_context.GetType().GetMethod("Set").MakeGenericMethod(t).Invoke(_context, null);
    }


    public static IQueryable<Object> Set(this DbContext _context, String table)
    {
        Type TableType = _context.GetType().Assembly.GetExportedTypes().FirstOrDefault(t => t.Name == table);
        IQueryable<Object> ObjectContext = _context.Set(TableTypeDictionary[table]);
        return ObjectContext;
    }
}

}

usage:

IQueryable<Object> query = db.Set("TableName");
// Filter against "query" variable below...
List<Object> result = query.ToList();
// or use further dynamic Linq
IQueryable<Object> query = db.Set("TableName").Where("t => t.TableFilter == \"MyFilter\"");

Answer 3

If all the tables have the same columns, then I'd extract an interface out of those tables and create partial entity classes just to implement that interface, finally use that interface to query.

For example:

//entities
public partial class ps_Contractor: ICommonInterface{}
public partial class Table2 : ICommonInterface{}

in the search method I'd pass IEnumerable<ICommonInterface> or IQueryable<ICommonInterface> and apply that query on that. All you'd need to do is to pass different tables to that search method. Or you can even have kind of generic class of type ICommonInterface and use that to do the query.

public void Example(IQueryable<ICommonInterface>dataSource)
{
var data = ( from q in dataSource 
join _l in _dbcontext.log on q.id equals l.tablelogid 
where q.hourly = 8
select new{
 hourly=q.hourly,
 benefit=q.benefit,
 total=q.total,
 log = l.message
}.ToList();
}

Example(_dbcontext.ps_Contractor.AsQueryable())

This is just a sample that I tested now:

 public class Repository
{
    private List<string> GetData(IQueryable<IContractor> data)
    {
        return (from d in data select d.Name).ToList();
    }

    public List<string> GetFullTime()
    {
        using (var context = new TestDbEntities())
        {
            return GetData(context.FTContractors.AsQueryable());
        }
    }

    public List<string> GetPartTime()
    {
        using (var context = new TestDbEntities())
        {
            return GetData(context.PTContractors.AsQueryable());
        }
    }
}

Entities:

public interface IContractor
    {
        int Id { get; set; }
        string Name { get; set; }
    }

    public partial class FTContractor : IContractor
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }
public partial class PTContractor : IContractor
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

Test:

[TestMethod]
        public void Temp()
        {
            var tester = new Repository();

            var ft = tester.GetFullTime();
            var pt = tester.GetPartTime();

             Assert.AreEqual(3, ft.Count);
             Assert.AreEqual(4, pt.Count);
        }

In the database there are two tables containing just Id and Name columns