在动态LINQ查询设置表名 [英] Dynamically set the table name in LINQ query

问题描述

我工作在数据仓库应用程序，我们有4个表，其中架构是相同的。 。这些表之间唯一的区别仅仅是表名

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.

表示例：

• ps_Contractor


• ps_Employee


• ps_Union


• ps_NonUnion

• ps_Contractor
• ps_Employee
• ps_Union
• ps_NonUnion

模式

• 标识


• 小时


• 好处


• 总

• id
• hourly
• benefit
• total

现在我需要生成4份报告基于这些表。而不是写4个独立的LINQ查询我想编写一个查询在那里我可以动态传递的表名。

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.

如何在下面的LINQ查询动态传递的表名的问题？

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();

我已经看过所有堆栈溢出提出类似的问题，我不希望使用ExecuteStoreQuery。

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

我有什么选择呢？

推荐答案

如果所有表具有相同的列，那么我会提取一个接口出这些表，并创建部分实体类只是为了实现该接口， 。最后使用这个接口来查询

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.

例如：

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

在搜索方法我会通过的IEnumerable< ICommonInterface> 或的IQueryable< ICommonInterface> 和应用上该查询。所有你需要做的是不同的表传递到搜索方法。
或你甚至可以有一种泛型类类型的 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

这篇关于在动态LINQ查询设置表名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！