2

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 ?

hunch_hunch
  • 2,283
  • 1
  • 21
  • 26
bugBurger
  • 6,950
  • 9
  • 32
  • 39
  • Before you get to the X and the Y of it, I believe there is a lot of merit in future readability / maintainability in creating four separate LINQ queries. Perhaps more merit in the four approach than the code duplication saving to be had by creating one generic function. – Paul Zahra Oct 10 '14 at 15:01
  • See http://stackoverflow.com/questions/3428069/how-to-map-an-entity-framework-model-to-a-table-name-dynamically I seriously wouldn't go to such an extent for only 4 duplications, now if we were talking more like say 40 duplications then my view may change. – Paul Zahra Oct 10 '14 at 15:04
  • Perhaps you could do as they suggest here http://stackoverflow.com/questions/12455389/entity-framework-get-entity-by-name – Paul Zahra Oct 10 '14 at 15:17

3 Answers3

4

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

AD.Net
  • 13,352
  • 2
  • 28
  • 47
  • You should try to work this out in a working example. I don't see how you could "use that interface to query". EF doesn't support interfaces as generic parameters in LINQ statements. – Gert Arnold Oct 10 '14 at 17:24
  • @GertArnold, I'm not really using the interface for querying, instead you're using it just to be able to access the properties to write the query. – AD.Net Oct 10 '14 at 17:41
1

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\"");
jdmneon
  • 444
  • 7
  • 12
0

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);
pkucas
  • 158
  • 6