How to process dynamic tablenames

Question

I have a number of tables which I need to combine into one structure using Linq

I need to process a number of tables that have the same structure but different table names (financial data spread across 20 years). I can create code to access the various table contents dynamically:

string tableName = "Trading.DataSources.Prices2017";
var results = DbContext.Set(Type.GetType(tableName));

However, when I try to Cast the results from one table into a common table (Price which has the same table structure) by using Cast:

var newResults = results.AsQueryable().Cast<Price>().ToList();

I get the following error:

"System.NotSupportedException: 'Unable to cast the type 'Trading.DataSources.Prices2017' to type 'Trading.DataSources.Price'. LINQ to Entities only supports casting EDM primitive or enumeration types.'"

I can obviously do this casting with my own conversion method. However, this results in multiple version of the same block of code to cater for the different tables and every year, when I have a new set of prices data, I have to amend my code to cater for the new year's table name.

Is there a way to deal with this dynamically (or generically)?

Answer 1

Make a new model class SharedPrices. This class will contain all of the same values that these tables have

var newResults = results.select(r => new SharedPrices{value1 = r.value1, value2 = r.value2}).ToList();

for my example all Prices tables only have:

• value1
• value2

These should be substituted for your actual class structure.

I did a little more digging for you, and I would like to give @Tyler-Long credit for his answer here. His answer essentially points out that you can use JSON (De)serialization in order to reflect a class into another without having to write a cast so long as they have the exact same properties, like so:

using Newtonsoft.Json;

string tableName = "Trading.DataSources.Prices2017";
var tableType = Type.GetType(tableName);
var results = DbContext.Set(tableType);
Price newResults = JsonConvert.DeserializeObject<tableType>(JsonConvert.SerializeObject(results.ToList()));

I think that this will provide you with the ability to convert your tables into a single price model without having to explicitly write conversion logic. The only downside is it adds a dependency to your project on the Newtonsoft.json package (which can be pulled down through nuget). I have personally used this package before though, and it is dependable.