CODEWITHSUNDEEP
entity-frameworklinq-to-entitiesentity-framework-5
emelious
emelious

Reputation: 67

Entity Framework : map duplicate tables to single entity at runtime?

I have a legacy database with a particular table -- I will call it ItemTable -- that can have billions of rows of data. To overcome database restrictions, we have decided to split the table into "silos" whenever the number of rows reaches 100,000,000. So, ItemTable will exist, then a procedure will run in the middle of the night to check the number of rows. If numberOfRows is > 100,000,000 then silo1_ItemTable will be created. Any Items added to the database from now on will be added to silo1_ItemTable (until it grows to big, then silo2_ItemTable will exist...)

ItemTable and silo1_ItemTable can be mapped to the same Item entity because the table structures are identical, but I am not sure how to set this mapping up at runtime, or how to specify the table name for my queries. All inserts should be added to the latest siloX_ItemTable, and all Reads should be from a specified siloX_ItemTable.

I have a separate siloTracker table that will give me the table name to insert/read the data from, but I am not sure how I can use this with entity framework...

Thoughts?

Upvotes: 1

Views: 870

Answers (3)

Grigorii
Grigorii

Reputation: 87

Possible solution for this problem may be using context initialization with DbCompiledModel param:

var builder = new DbModelBuilder(DbModelBuilderVersion.V6_0);
builder.Configurations.Add(new EntityTypeConfiguration<EntityName>());
builder.Entity<EntityName>().ToTable("TableNameDefinedInRuntime");

var dynamicContext = new MyDbContext(builder.Build(context.Database.Connection).Compile());

For some reason in EF6 it fails on second table request, but mapping inside context looks correct on the moment of execution.

Upvotes: 0

emelious
emelious

Reputation: 67

As mentioned in my comment, to solve this problem I am using the SQLQuery method that is exposed by DBSet. Since all my item tables have the exact same schema, I can use the SQLQuery to define my own query and I can pass in the name of the table to the query. Tested on my system and it is working well.

See this link for an explanation of running raw queries with entity framework: EF raw query documentation

If anyone has a better way to solve my question, please leave a comment.

[UPDATE]

I agree that stored procedures are also a great option, but for some reason my management is very resistant to make any changes to our database. It is easier for me (and our customers) to put the sql in code and acknowledge the fact that there is raw sql. At least I can hide it from the other layers rather easily.

[/UPDATE]

Upvotes: 0

Satish
Satish

Reputation: 3100

You could try to use the Entity Inheritance to get this. So you have a base class which has all the fields mapped to ItemTable and then you have descendant classes that inherit from ItemTable entity and is mapped to the silo tables in the db. Every time you create a new silo you create a new entity mapped to that silo table. 

 [Table("ItemTable")]
public class Item
{
    //All the fields in the table goes here
}

[Table("silo1_ItemTable")]
public class Silo1Item : Item
{

}

[Table("silo2_ItemTable")]
public class Silo2Item : Item
{

}

You can find more information on this here

Other option is to create a view that creates a union of all those table and map your entity to that view.

Upvotes: 2

Related Questions