Entity Framework 6 DBContext with only a subset of all tables

Question

We have a huge database with 770 tables and want to do some performance testing with EF 6.1x.

We want to query only 5 of those 770 tables. Is it possible to create a "light" DBContext with only 5-6 entities/DBSets instead of using the full 770-tables-context?

When we use the full context, a simple query with 4 joins takes 45 seconds. Thats' 44 seconds too long. We are using code-first (reverse engineered).

The problem: When we create such a "light" version of the full context (i.e. 5 tables only), EF complains that all the other entities that are somehow related to these 5 tables have missing keys. We only map the keys, properties, relationships for those 5 tables, but not the rest.

Since the query written in LINQ only queries 5 tables, EF should simply ignore the other 765 tables, but it won't. Why not? LazyLoading=true/false doesn't seem to have any bearing on this.

Note: Obviously one could create a view in the DB that does what we do in code with a LINQ query. The question is can it be done with a "light" DbContext as above.

There's the "light" version of the context:

public class ItemLookupContext : DbContext
{
    static ItemLookupContext()
    {
        Database.SetInitializer<ItemLookupContext>( null );
    }

    public ItemLookupContext()
        : base( "Name=ItemLookupContext" )
    {
        //Configuration.LazyLoadingEnabled = true;
    }

    public DbSet<Identity> Identities { get; set; }
    public DbSet<Item> Items { get; set; }
    public DbSet<Price> Prices { get; set; }
    public DbSet<Department> Departments { get; set; }
    public DbSet<Brand> Brands { get; set; }

    protected override void OnModelCreating( DbModelBuilder modelBuilder )
    {
        modelBuilder.Configurations.Add( new IdentityMap() );
        modelBuilder.Configurations.Add( new ItemMap() );
        modelBuilder.Configurations.Add( new PriceMap() );
        modelBuilder.Configurations.Add( new DepartmentMap() );
        modelBuilder.Configurations.Add( new BrandMap() );

        //ignore certain entitities to speed up loading?
        //does not work
        modelBuilder.Ignore<...>();
        modelBuilder.Ignore<...>();
        modelBuilder.Ignore<...>();
        modelBuilder.Ignore<...>();
        modelBuilder.Ignore<...>();
    }
}

It is certainly possible and actually recommended. In larger database environments, it is often a performance increase to have multiple different contexts for subsets of tables. — DavidG, Sep 02 '14 at 09:39
some code would be helpful to understand your problem but I'd guess you look for something like http://stackoverflow.com/questions/17246069/multiple-dbcontexts-on-one-db-with-code-first-migrations ? — fuchs777, Sep 02 '14 at 09:43
is the simple query the first of the exe, that is the one query that trigger the context building ? — tschmit007, Sep 02 '14 at 09:57
@DavidG: the question is when I create a context wit ha subset of all tables, why is EF complaining about entities that aren't even in that context? — John, Sep 02 '14 at 10:02
@John I know, that's why I gave an response to your first bolded question as a comment rather than a full answer. — DavidG, Sep 02 '14 at 10:04
Was there ever any specific resolution on this beyond referring to Julie's article? — Dave, Mar 18 '16 at 20:13
@Dave I ended up using the powerful EntityFramework Reverse POCO Code First Generator to generate the classes. Hope this helps. — John, Mar 19 '16 at 07:26

score 6 · Accepted Answer · edited Aug 31 '17 at 01:33

6

what you trying to something like "Bounded Context" which is one of DDD patterns

So, you can check this article by Julie Lerman, Shrink EF Models with DDD Bounded Contexts

edited Aug 31 '17 at 01:33

TheVillageIdiot

40,053
20
133
188

answered Sep 20 '14 at 12:58

Taraman

128
2
5

score 4 · Answer 2 · answered Jun 16 '16 at 13:19

Simply just create your DBContext for your tables. To prevent Entity Framework moaning about the not mapped tables, you have switch off the db initialization in your application. Put this in your global.asax/Startup.cs

Database.SetInitializer<YourDbContext>(null);

It tells EF to stop comparing your actual DB structure against your DbContext. It also means that if someone changes your EF mapped tables, you have no chance of getting notified about that.

Masoud · Answer 3 · 2014-09-02T12:59:56.707

3

When you have a many-to-one relation between class A and class B:

public class A
{
   public B b {get; set;}
}
public class B
{
    public ICollection<A> As {get; set;} 
}

and define following DbContext, EF automatically includes DbSet<B> to the DbContext:

public class MyContext : DbContext
{
   ...
   public DbSet<A> As { get; set; }
}

So, if you want your light DbContext does not includes the related DbSets, simply use Ignore method:

public class MyContext : DbContext
{
   ...
   public DbSet<A> As { get; set; }

   protected override void OnModelCreating( DbModelBuilder modelBuilder )
   {
      modelBuilder.Ignore<B>();
   }
}

edited Sep 02 '14 at 12:59

answered Sep 02 '14 at 10:24

Masoud

8,020
12
62
123

thanks! Can it be done the other way around? Call Include() when needed and have EF ignore all DBSets by default, instead of including them? – John Sep 02 '14 at 10:28
@John, if you `Ignore` a class you could not use it with `Include()`. – Masoud Sep 02 '14 at 10:31
What I meant is this: if a table has 45 references to other DBSets, do I have to call Ignore() 45 times, or is there an easier way to EXclude a large number of related sets? – John Sep 02 '14 at 10:40
That still does not work for me. I get a runtime exception for every Ignore'd DbSet. – John Sep 02 '14 at 12:42
@John: what is the exception? – Masoud Sep 02 '14 at 12:45
It can't find any the navigation properties that I ignore via the Ignore() call. The navigation property '...' is not a declared property on type '...'. Verify that it has not been explicitly excluded from the model and that it is a valid navigation property. – John Sep 02 '14 at 12:54
@John: could you post your class code that contains ignored navigation property? – Masoud Sep 02 '14 at 14:42
I updated the question with my Ignore() calls inside OnModelCreating(). The entity classes themselves are unchanged and contain all the navigation properties I would like to ignore in this dbcontext. Any idea? Thanks! – John Sep 03 '14 at 08:11

score 0 · Answer 4 · answered Sep 03 '14 at 09:28

0

It looks like you used a tool like Entity Framework Power Tools to generate the entity classes and mappings. This would have generated a class for each table in the database, a huge context, mappings for all these classes and all possible associations. This is way too much.

First remove all classes and mappings that you don't need. Then remove all associations to removed classes in the few classes you have left, not the primitive foreign key fields. Also remove all DbSets from the context except the few you need.

This slimmed-down class model will be consistent in itself. It won't have associations to all entities in the database, but it will be possible to filter by foreign key values that refer to entities outside the context.

If you generated/created the code in any other way this is still the crux: only use navigation properties to other classes in the class model. For other references use primitive foreign key properties.

answered Sep 03 '14 at 09:28

Gert Arnold

105,341
31
202
291

I was afraid someone would suggest that. I did use the power tools, but the dbcontext I created from a few of the generated entities is only one of many dbcontexts we need. From what you wrote I think it is not possible to use the same entity classes in multiple contexts. But then would I need to create the same entity class again and again for each dbcontext? So Item1.cs for dbcontext A, Item2.cs for dbcontext B, etc.? – John Sep 03 '14 at 10:22
Yes, basically that's true. But I think you should try to create larger contexts that cover reasonably distinct aggregates of your database and that serve several task areas. Thus, you can reduce the number of overlapping classes. [EntityFramework Reverse POCO Code First Generator](https://efreversepoco.codeplex.com/documentation) may be helpful here. – Gert Arnold Sep 03 '14 at 11:25
You can use the same entity classes in different DbContexts, as long as you create a separate `EntityTypeConfiguration<...>` mapping, that has `Ignore();` for each navigation property you don't want the current DbContext to try to map. – sliderhouserules Mar 30 '16 at 23:46

Entity Framework 6 DBContext with only a subset of all tables

4 Answers4

Linked