Entity Framework Get Table By Name

Question

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

In my model class, I have:

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

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"

What I would like to do is simply:

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

I could use "switch" but this makes the code less expandable and requires update whenever new "ats(N+1)" gets created.

My 2 days of research lead me nowhere but back to ground zero. I'm quite stuck.

PLEASE HELP! Thanks!

Answer 1

Sorry for my late response as I wondered off trying out different solutions:

Solution #1: Master Table

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.

Solution #2: Raw Query

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

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

Solution #2(b): Raw Query using 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;
    }

Solution #3: Stored Procedure

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

Conclusion:

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.

Answer 2

I don't think what you are doing will work like that. You should create an entity based on a single 'master' table, eg. Ats

Once you have done this you will have a property on your Entities class called Ats. You can now use this property to select the entities using a raw SQL query like this.

var atsName = "ats1";

using (var context = new Entities())
{
    var blogs = context.Ats.SqlQuery(string.Format("SELECT * FROM {0}", atsName)).ToList();
}

Alternatively you could try this (I am assuming the property type is DBSet as you haven't specified it in the question)

var platform = propertyInfo.GetValue(ctx, null) as DBSet<Ats>;

atsPlatform = platform.Select(ats => new A new AtsPlatform { RequestNumber = ats.RequestNumber, NumberOfFail = ats.NumberOfFail, NumberOfFailWithCR = ats.NumberOfFailWithCR, NumberOfTestCase = ats.NumberOfTestCase }).ToList();     

return atsPlatform;

Answer 3

Instead of reflection, how about using the SqlQuery function?

So

List<AtsPlatform> GetAtsPlatformByName(int index)
{
    using (var ctx = new Entities())
    {
        return ctx.Database.SqlQuery<AtsPlatform>("SELECT * FROM dbo.ats" + index)
                           .ToList();
    }
}

Also, there is no change tracking on the entities using the SqlQuery method on the Database object (which is ok in your case I suppose since the AtsPlatform class only contains primitive properties).

For changes tracking you will need to use the DbSet SqlQuery method, and may need to mix some reflection in.