实体框架获取表按名称 [英] Entity Framework Get Table By Name
问题描述
我要寻找方法做对LINQ通过字符串变量在运行系统中选择一个表。
I am looking for ways to do LINQ on a table selected in runtime via string variable.
这是我到目前为止使用反射：
This is what I have so far using reflection:
private Entities ctx = new Entities();
public List<AtsPlatform> GetAtsPlatformByName(string atsPlatformName)
{
List<AtsPlatform> atsPlatform = null;
System.Reflection.PropertyInfo propertyInfo = ctx.GetType().GetProperty(atsPlatformName.ToLower());
var platform = propertyInfo.GetValue(ctx, null);
// it fails here highlighting "platform" with error that reads "Error 1 Could not find an implementation of the query pattern for source type 'System.Data.Objects.ObjectQuery'. 'Select' not found. Consider explicitly specifying the type of the range variable 'ats'."
atsPlatform = ((from ats in platform select new AtsPlatform { RequestNumber = ats.RequestNumber, NumberOfFail = ats.NumberOfFail, NumberOfFailWithCR = ats.NumberOfFailWithCR, NumberOfTestCase = ats.NumberOfTestCase }).ToList());
return atsPlatform;
}
在我的模型类，我有：
public class AtsPlatform
{
public string Name { get; set; }
public string RequestNumber { get; set; }
public Int32? NumberOfFail { get; set; }
public Int32? NumberOfTestCase { get; set; }
public Int32? NumberOfFailWithCR { get; set; }
}
在数据库，我有以下表格：ATS1，ATS2，ATS3..ATSN，其中他们每个人都有相同的实体领域，如AtsPlatform
In Database, I have the following tables: "ats1", "ats2", "ats3" .. "atsN" where each of them has the same entity fields as the properties defined in "AtsPlatform"
我想要做的仅仅是：
List<AtsPlatform> a1 = GetAtsPlatformByName("ats1");
List<AtsPlatform> a2 = GetAtsPlatformByName("ats2");
List<AtsPlatform> aN = GetAtsPlatformByName("atsN");
我可以使用开关，但是这使得code扩展度较少，并且需要更新，每当新的ATS（N + 1）被创建。
I could use "switch" but this makes the code less expandable and requires update whenever new "ats(N+1)" gets created.
我2天的研究使我无处但回到地面零。我很坚持。
My 2 days of research lead me nowhere but back to ground zero. I'm quite stuck.
请帮助！谢谢！
推荐答案
对不起，我的反应迟缓，因为我不知道过尝试不同的解决方案：
Sorry for my late response as I wondered off trying out different solutions:
解决方案1：主表
由于建议的@Alexw，创建一个主表只适用，如果你被允许改变数据库的设计是最好的。我目前正在与店主分贝合作，使这一变化。由于依赖，这种变化有可能等到下一个阶段。
As suggested by @Alexw, creating a Master Table works the best ONLY if you are allowed to change the design of the db. I'm currently working with the db owner to make this change. Due to dependencies, this change has to wait till next phase.
同时，我创建模拟分贝行使这一做法。
Meanwhile, I've created mock db to exercise this approach.
解决方案2：原始查询
的建议由@Umair，原始查询将做的工作。我创建了处理原始的SQL查询的类。
As Suggested by @Umair, raw query will do the job. I've created a class that handles raw sql query.
public class AtsRawQuery
{
private string ConnetionString = "";
public AtsRawQuery(string connectionString)
{
this.ConnetionString = connectionString;
}
public List<List<string>> Query(string queryString)
{
List<List<string>> results = null;
MySqlConnection conn = null;
MySqlDataReader rdr = null;
try
{
conn = new MySqlConnection(this.ConnetionString);
conn.Open();
MySqlCommand cmd = new MySqlCommand(queryString, conn);
rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
results = new List<List<string>>();
while (rdr.Read())
{
List<string> curr_result = new List<string>();
for (int columnIndex = 0; columnIndex <= rdr.FieldCount - 1; columnIndex++)
{
curr_result.Add(rdr.GetString(columnIndex));
}
results.Add(curr_result);
}
}
}
catch (MySqlException ex)
{
Console.WriteLine(ex.Message);
return null;
}
finally
{
if (rdr != null)
{
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
}
return results;
}
}
本类返回后消费2维列表。
This class returns a 2 dimension list for later consumption.
在我的模型类，我添加了一个解析器方法：
In my model class, I added a parser method:
public class AtsPlatform
{
public string Name { get; set; }
public string RequestNumber { get; set; }
public Int32? NumberOfFail { get; set; }
public Int32? NumberOfTestCase { get; set; }
public Int32? NumberOfFailWithCR { get; set; }
public void Parse(string name, string requestNumber, string numberOfFail, string numberOfTestCase, string numberOfFailWithCR)
{
Int32 temp;
this.Name = name;
this.RequestNumber = requestNumber;
this.NumberOfFail = (Int32.TryParse(numberOfFail, out temp)) ? Int32.Parse(numberOfFail) : 0;
this.NumberOfTestCase = (Int32.TryParse(numberOfTestCase, out temp)) ? Int32.Parse(numberOfTestCase) : 0;
this.NumberOfFailWithCR = (Int32.TryParse(numberOfFailWithCR, out temp)) ? Int32.Parse(numberOfFailWithCR) : 0;
}
}
解决方案＃2（B）：原始查询使用ExecuteStoreCommand
public List<AtsPlatform> GetAtsPlatformByName(string atsPlatformName)
{
List<AtsPlatform> atsPlatforms = null;
string stm = String.Format("SELECT RequestNumber, NumberOfFail, NumberOfTestCase, NumberOfFailWithCR FROM {0}", atsPlatformName);
atsPlatforms = new List<AtsPlatform>();
foreach (AtsPlatform ats in ctx.ExecuteStoreQuery<AtsPlatform>(stm))
{
atsPlatforms.Add(ats);
}
return atsPlatforms;
}
解决方案3：存储过程
我创建一个存储过程，这里是code：
I've created a stored procedure and here is the code:
DELIMITER $$
CREATE PROCEDURE `UnionAtsTables`()
BEGIN
DECLARE atsName VARCHAR(10);
DECLARE atsIndex INT;
SET atsIndex = 1;
SET @qry = '';
WHILE atsIndex > 0 DO
SET atsName =concat('ATS',atsIndex);
IF sf_is_table(atsName) = 1 THEN
Set @temp_qry = CONCAT('SELECT *, ''', atsName ,''' As TestPlatform FROM ', atsName, ' WHERE RequestNumber <> ''''' );
If @qry = '' THEN
SET @qry = @temp_qry;
ELSE
SET @qry = CONCAT(@qry, ' UNION ', @temp_qry);
END IF;
ELSE
SET atsIndex = -1;
END IF;
SET atsIndex = atsIndex + 1;
END WHILE;
DROP TABLE IF EXISTS ats_all;
SET @CreateTempTableQuery = CONCAT('CREATE TEMPORARY TABLE ats_all AS ', @qry ,'');
PREPARE stmt1 FROM @CreateTempTableQuery;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
ALTER TABLE ats_all DROP COLUMN ExecOrder;
ALTER TABLE ats_all ADD ExecOrder INT PRIMARY KEY AUTO_INCREMENT;
ALTER TABLE ats_all auto_increment = 0;
END
下面是函数我在网上找到的检查，如果表中存在分贝
Here is the function I found online that checks if table exists in db.
DELIMITER $$
CREATE FUNCTION `sf_is_table`(`in_table` varchar(255)) RETURNS tinyint(4)
BEGIN
/**
* Check if table exists in database in use
*
* @name sf_is_table
* @author Shay Anderson 08.13 <http://www.shayanderson.com>
*
* @param in_table (table name to check)
* @return TINYINT (1 = table exists, 0 = table does not exist)
*/
# table exists flag
DECLARE is_table BOOLEAN DEFAULT FALSE;
# table count
DECLARE table_count INT DEFAULT 0;
# database name
SET @db = NULL;
# set database name
SELECT
DATABASE()
INTO
@db;
# check for valid database and table names
IF LENGTH(@db) > 0 AND LENGTH(in_table) > 0 THEN
# execute query to check if table exists in DB schema
SELECT COUNT(1) INTO table_count
FROM information_schema.`TABLES`
WHERE TABLE_SCHEMA = @db
AND TABLE_NAME = in_table;
# set if table exists
IF table_count > 0 THEN
SET is_table = TRUE;
END IF;
END IF;
RETURN is_table;
END
结论：
感谢大家对你的建议。
我决定使用方案＃2，因为它不造成太大的影响，以分贝性能溶液＃3和它不需要分贝重新设计作为溶液＃1
Thank you everyone for your suggestions. I decided to use Solution #2 since it does not cause as much impact to the db performance as Solution #3 and it does not require db redesign as Solution #1.
这篇关于实体框架获取表按名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
