如果我只知道从中获取数据的表名和列名，我如何从实体框架获取数据？

I have a table Parameters which contains columns Id, Name, TableName, ColumnName among others. The database also contains multiple 'series data' tables which have several, possibly hundreds of columns including a Timestamp column. I do not own the db schema. So I cannot change this.

我有一个表参数,其中包含列Id,Name,TableName,ColumnName等。该数据库还包含多个“系列数据”表,这些表包含几个,可能包含数百个列,包括Timestamp列。我不拥有db模式。所以我无法改变这一点。

The user must provide the Name of the Parameter, a StartTime and an EndTime by means of the GUI. The business logic must return a series of data.

用户必须通过GUI提供参数名称,StartTime和EndTime。业务逻辑必须返回一系列数据。

How can I get the data that is in the column and table whose names are param.TableName and param.ColumnName?

如何获取名称为param.TableName和param.ColumnName的列和表中的数据?

I am currently using a switch statements like this:

我目前正在使用这样的switch语句:

public List<double> GetData
                    (string paramName, DateTime startTime, DateTime endTime)
{
    using (var context = new MyEntities())
    {
        var param =
            (from p in context.Parameters where p.Name == paramName select p)
            .FirstOrDefault();

        switch (param.TableName)
        {
             case "Table1":
                 return GetTable1Data(columnName, startTime, endTime);
                 break;
             case "Table2":
                 return GetTable2Data(columnName, startTime, endTime);
                 break;
             default:
                 throw new Exception();
        }
    }
}

private List<double> GetTable1Data
   (MyEntities context, string columnName, DateTime startTime, DateTime endTime) {

    List<double> data = new List<double>();

    switch (columnName)
    {
        case "Parameter1":
            (from d in context.Table1 where d.Timestamp >= startTime && d.Timestamo <= endTime
            order by d.Timestamp select d).ForEach(x => data.Add(d.Parameter1));
            break;
        case "Parameter2":
            (from d in context.Table1 where d.Timestamp >= startTime && d.Timestamo <= endTime
            order by d.Timestamp select d).ForEach(x => data.Add(d.Parameter2));
            break;
       case "Parameter3":
           (from d in context.Table1 where d.Timestamp >= startTime && d.Timestamo <= endTime
           order by d.Timestamp select d).ForEach(x => data.Add(d.Parameter3));
           break;
       default:
           throw new Exception("");
    }

    return data;
}

private List<double> GetTable2Data(MyEntities context, string columnName, DateTime startTime, DateTime endTime)
{
    List<double> data = new List<double>();

    switch (columnName)
    {
        case "Parameter1":
            (from d in context.Table2 where d.Timestamp >= startTime && d.Timestamo <= endTime
            order by d.Timestamp select d).ForEach(x => data.Add(d.Parameter1));
            break;
        case "Parameter2":
            (from d in context.Table2 where d.Timestamp >= startTime && d.Timestamo <= endTime
            order by d.Timestamp select d).ForEach(x => data.Add(d.Parameter2));
            break;
        case "Parameter3":
            (from d in context.Table2 where d.Timestamp >= startTime && d.Timestamo <= endTime
            order by d.Timestamp select d).ForEach(x => data.Add(d.Parameter3));
            break;
        default:
             throw new Exception("");
    }

    return data;

}

This duplication seems very wrong.

这种重复似乎非常错误。

Is there a better way?

有没有更好的办法?

1 个解决方案

#1

You could build the query using the information you have in string form and then execute it like this:

您可以使用字符串形式的信息构建查询,然后执行它:

return db.ExecuteStoreQuery<double>(
    "SELECT {0} FROM {1} WHERE [Timestamp] BETWEEN {2} AND {3}",
    columnName,
    tableName,
    startTime,
    endTime).ToList();

Pete