Entity Framework Get Table By Name

asp.net c# entity-framework linq system.reflection

Question

I'm trying to figure out how to use LINQ on a table that is runtime-selected using a string variable.

I've used reflection to come up with the following so far:

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;
}

What I have in my sample class is

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; }
}

I have the following tables in my database: "ats1," "ats2," "ats3," "atsN," each of which contains the same entity attributes as the properties listed in "AtsPlatform."

What I want to do is only this:

List<AtsPlatform> a1 = GetAtsPlatformByName("ats1");
List<AtsPlatform> a2 = GetAtsPlatformByName("ats2");
List<AtsPlatform> aN = GetAtsPlatformByName("atsN");

I could use "switch," but doing so limits the code's ability to grow and necessitates updating the code whenever a new "ats(N+1)" is formed.

My two days of research have just brought me back to square one. I'm kind of stuck.

PLEASE ASSIST! Thanks!

1
1
10/8/2013 10:03:13 PM

Popular Answer

I apologize for my delayed response while I experimented with many solutions:

First Approach: Master Table

Creating a Master Table, as proposed by @Alexw, only functions optimally if you are permitted to alter the database's design. To make this update, I'm now collaborating with the database owner. This change has to wait until the next step because of dependencies.

To test this strategy, I've developed a dummy database in the meanwhile.

Alternative #2: Raw Query

Raw query will work, as @Umair said. I made a class that manages unprocessed SQL queries.

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;
    }
}

For subsequent use, this class returns a two-dimensional list.

I added a method called "parser" to my model class.

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;
    }
}

Solution #2(b): ExecuteStoreCommand with a raw query

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;
    }

Third-party stored procedure

Here is the code for a stored procedure I made:

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 online search feature I used to see if a database table exists.

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

Conclusion:

I appreciate all the suggestions, everyone. Since Solution #2 does not have as large of an influence on database speed as Solution #3 and does not call for a redesign of the database as Solution #1, I chose to employ it.

1
10/21/2013 11:40:14 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow