CODEWITHSUNDEEP
c#sql-serverentity-frameworkentity-framework-6
Kyle McClellan
Kyle McClellan

Reputation: 993

Entity framework split model into multiple tables programmatically

I'm using Entity Framework 6 Code First to store a large POCO model in a database. The model happens to have 1000s of properties (don't ask lol**), meaning it must be split into multiple SQL tables (the column limit is 1024). I know this is normally done by specifying the individual columns like so:

modelBuilder.Entity<HugeEntity>.Map(m =>
{
    m.Properties(e => new { e.Prop1, e.Prop2 });
    m.ToTable("HugeEntity1");
}.Map(m =>
{
    m.Properties(e => new { e.Prop3, e.Prop4 });
    m.ToTable("HugeEntity2");
};

I'm wondering if there's any way to do this without having to specify the properties individually. Ideally it could partition an entity automatically based on a given column limit (viz. 1000).

Even if there is no standard way, what is the easiest hack to get this to work? The properties on the model are subject to change, so I would really like to avoid listing them exhaustively in more than one place.

Any advice appreciated!

**CONTEXT: This is a domain model, representing user entered data that should be captured on a certain web page. It is also exposed in a WebAPI. My team considered a key/value pair approach, but decided this would make the data more difficult to consume by future BI applications that hit the WebAPI.

Upvotes: 3

Views: 1045

Answers (2)

Kyle McClellan
Kyle McClellan

Reputation: 993

Figured out a way to do it. I had to employ Linq Expressions and the "dynamic" keyword:

    private static void SplitIntoTables<T>(DbModelBuilder modelBuilder, IReadOnlyCollection<PropertyInfo> properties, int columnLimit) where T : class
    {
        var numberOfTables = Math.Ceiling((properties.Count + (double)columnLimit / 2) / columnLimit);
        var paramExp = Expression.Parameter(typeof(T));

        var tableIndex = 0;
        foreach (var tableGroup in properties.GroupBy(p => p.Name.GetHashCode() % numberOfTables))
        {
            var expressions = tableGroup.Select(p => Expression.Lambda(
                typeof(Func<,>).MakeGenericType(typeof(T), p.PropertyType),
                Expression.Property(paramExp, p), paramExp));

            modelBuilder.Entity<T>().Map(m =>
            {
                foreach (var exp in expressions)
                {
                    m.Property((dynamic) exp);
                }
                m.ToTable($"{typeof(T).Name}_{++tableIndex}");
            });
        }
    }

Upvotes: 1

pwilcox
pwilcox

Reputation: 5763

You say "don't ask" to us. But your biggest problem is that you're saying it to yourself. If your model is getting to have even more than 50 fields, then you need to ask what's going on. I wonder if it's not worth taking a breath and revisiting some more abstract concepts in computing. I would start with Database Normalization. 1K dynamic properties tells me that you're in desperate need of this.

And by the way, "Database Normalization" concepts aren't specific to SQL databases per se. You should be normalizing your POCO models just the same where you can. Granted, there are some non-relational concepts in OO languages. But no excuse for the extremes you're describing.

Another paradigm might be Domain Driven Design. I'm less fluent in this myself, so that's why I'm not saying you should start there. But from the beginnings I have in implementing it I'd say the learning curve has been worth it.

I should be careful not to condescend. I don't exactly have all my tables in the highest normal forms myself. But I'll tell you that in the areas I don't, the headaches are stronger.

Upvotes: 5

Related Questions