c# - 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

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