2

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)?

Aleks Andreev
  • 7,016
  • 8
  • 29
  • 37
Cliff C
  • 143
  • 1
  • 10
  • You need to create a generic interface using because the database tables have different class mapping in Entity Model but have similar structure. See following : https://stackoverflow.com/questions/50136396/generic-entity-base-class – jdweng Apr 22 '19 at 09:57
  • Thanks very much for your fast response. I have previously created a generic version which side-steps the string tablename GetType code. However, using generics does not solve the combination problem - which is now, how to convert generic table structures(class mappings for Prices2018, Prices 2017 etc) to a common table structure (class mapping for Prices) without writing specific conversion code i.e. with something like Linq's Cast extension?. – Cliff C Apr 22 '19 at 10:42
  • What about inheritiance like following : https://stackoverflow.com/questions/26295377/entity-framework-table-per-class-inheritance – jdweng Apr 22 '19 at 11:14
  • @jdweng - I am using EF 6 so not exactly sure how I would enable the inheritance in the EF generated code. – Cliff C Apr 22 '19 at 13:54
  • You are using DbContext which is Entity and project contains mapping data between the c# classes and the database tables. The link is showing how to change c# classes that map to database. – jdweng Apr 22 '19 at 14:06
  • @jdweng - thank you for your continuing responses. if I am reading the contents of the link correctly I would need to duplicate the code (or parts of it) for each of my current tables (Prices1997, Prices 1998 etc.. 20 tables and growing). This is the issue with my current code which works but is repetitive and therefore verbose and needs to be amended every year. – Cliff C Apr 22 '19 at 14:49
  • If you had a base class for prices then all you would need to do is create a new table each year that inherits the Price Class. You would then need some method in the test case to select a table class by year. You can get a table by string name or use GetSchema() to get all the tables. – jdweng Apr 22 '19 at 14:57
  • Sorry for no recent response from me (2 days of Bank Holiday in the UK). I will revisit when I catch up. Thanks for all the suggestions. – Cliff C Apr 23 '19 at 17:13

1 Answers1

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.

App-Devon
  • 253
  • 2
  • 10
  • Thanks for the suggestion. This solution is more or less what I have been doing to date. However, I was hoping to find a solution that was less verbose i.e. something like the Cast extension (which doesn't work with different mappings). Thanks again. – Cliff C Apr 23 '19 at 17:15
  • This seems like the least verbose option to me. In my experience the only alternative to this is using Inheritance (which is much more code), or writing explicit casts as no implicit cast exists (also much more code). This could always be abstracted into a method and then it becomes a one-liner. – App-Devon Apr 23 '19 at 18:55
  • If you consider having to set 26 values it is screaming for a more elegant solution of some sort ;-). It is unfortunate that the Linq Cast extension does work in this situation. Thanks for your feedback – Cliff C Apr 24 '19 at 09:27
  • The LINQ Cast extension only works for [primitive types](https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/entity-data-model-primitive-data-types) & enums. It was not made to map class properties. – App-Devon Apr 24 '19 at 22:35
  • apologies I meant to say (and repeat) that Cast doesn't work - this is what first made me raise the issue (see the yellow text associated with my original submission). I was hoping there is another way around it. I am going to accept your answer as this is more or less what I have come up with originally before submission. Many thanks again,.and to others. – Cliff C Apr 25 '19 at 05:20
  • @CliffC I have updated my answer, I think I found a better solution for you. Let me know if it works out. – App-Devon Apr 25 '19 at 17:51
  • Conveniently I am already using NewtonSoft's Json.Net. I will give your solution a go and let you know. If this works it will save me a number of lines of code that I currently repeat for 20 year's tables (and growing), not to mention the need to amend my code each year. Thanks for your digging on this one. – Cliff C Apr 26 '19 at 05:12
  • Just a thought, but why a new table each year instead of a prices table that includes year as a column? – App-Devon Apr 26 '19 at 06:03
  • The reason for a separate table each year is performance. The current 20 tables have some where around 6 million records. As queries will be biased towards more recent (financial) data, I made a design decision to split the Prices into separate tables. This will improve query performance by reducing the search space - related to the bias for more recent data. I should have explained this in my original submission. – Cliff C Apr 26 '19 at 06:58