View generation speed

Topics: EF Runtime
Sep 19, 2012 at 7:17 PM

Hi. The view generation takes a long time. In out project we have just 300 entities and already 80 seconds on a powerful system. Also, it just uses one cpu, which is not a surprise for a very complex algorithm. The msdn site assures that the algorithm used is exponential in the worst case, and their own model with 1000+ tables takes more than an hour.

What part of the view generation takes exponential time? Is the algorithm already expressed in a more abstract manner? Or does one need to read the entire view generation code (and of course some accompanying code, to know the data structures, preprocesses etc)? I really like to be able to make my move on the code, but I really hope for some information better that the raw source code.

Sep 19, 2012 at 7:18 PM

Also, please note that the power tools throws exceptions for many users, so view generation for Code first is impossible for them (including me!)

Sep 19, 2012 at 8:23 PM

Another option is to use T4 templates for generating views for CodeFirst apps. They are on the VS Gallery. Here is the blog post the describes how to use them:

Sep 19, 2012 at 8:28 PM

The foundations of view generation are outlined in this paper:

Sep 20, 2012 at 6:35 AM

Thank. I'll look to know if the T4 generator works for me. Also, thank you for pointing to the paper. That's exactly what I'm looking for.

Sep 26, 2012 at 11:16 AM

Ok. I read your paper. It always feels good to know about the efforts that underlie a working system. Especially, the emphasis on a formal approach to ensure a lossless roundtrip. I suggest that you put a link to this paper somewhere that more people may see it and better appreciate the system they use.

BTW, as I responded to your blog post, I've been unable to generate views (we building EF6 an use it), so I still need to improve my speed. I have two suggestions:

  1. Allowing some kind of runtime view generation without checking the roundtrip condition (It is implied from the paper that it takes most of the view generation time). Most of our developer work does not include changing the model. Even when they do change the model, most of their work is about adding a simple property, or a very simple table. It was great if we could disable this, and have a nightly, or hourly job make sure the view is in good conditions. In other words, roundtrip condition checking be removed from every first run of the application and delegated to the development team (trust us, we can handle such a task!)
  2. I profiled the view generation, and 81% of the time was spent in ViewGenContext.CreateConstraintsForForeignKeyAssociationsAffectingThisWarapper(). Most of the time elapsed in this code is spent in Enumerating an IEnumerable, obtained by filtering EdmEntityContainer.BaseEntitySets. The moral of this for me is that a simple ReadOnlyCollection is not appropriate to keep this data, and a better data structure will yield a 5X speed boost. Fortunately, this collection is not used in many places among the code and I'll be able to understand all queries we perform on the list. I'll inform you if any improvements are made.
Oct 17, 2012 at 10:13 AM


The problem with CreateConstraintsForForeignKeyAssociationsAffectingThisWarapper() is that allForeignKeyAssociationSets and oneToOneForeignKeyAssociationsForThisWrapper are not materialized, the last query: oneToOneForeignKeyAssociationSetsForThisWrapper makes unnecessarily large loops inside unnecessarily large loops.

I put .ToList() after the query that generates allForeignKeyAssociationSets. Also, merged the first and second queries that make oneToOneForeignKeyAssociationsForThisWrapper and appended it with another ToList(). finally I had to add .ToList() to the third query as well.

These changes cut the total time spent in this method from 70 seconds to about 120ms. The difference was much greater that what I expected, so I actually had to ask my colleagues to verify I did not break anything.

The complete code for the updated method is:


private void CreateConstraintsForForeignKeyAssociationsAffectingThisWarapper(
            FragmentQueryKB rightKB, MemberDomainMap rightDomainMap)
            //First find the entity types of the sets in these cell wrappers.
            var entityTypes = m_cellWrappers.Select(it => it.RightExtent).OfType<EntitySet>().Select(it => it.ElementType);
            //Get all the foreign key association sets in these entity sets
            var allForeignKeyAssociationSets =
                    m_entityContainerMapping.EdmEntityContainer.BaseEntitySets.OfType<AssociationSet>().Where(it => it.ElementType.IsForeignKey).ToList();
            //Find all the foreign key associations that have corresponding sets
            var oneToOneForeignKeyAssociationsForThisWrapper = allForeignKeyAssociationSets.Select(it => it.ElementType).Where(
                            it => (it.AssociationEndMembers.All(endMember => endMember.RelationshipMultiplicity == RelationshipMultiplicity.One))).ToList();
            //Find all the 1:1 associations from the above list
            //oneToOneForeignKeyAssociationsForThisWrapper =
            //        oneToOneForeignKeyAssociationsForThisWrapper.Where(
            //                it => (it.AssociationEndMembers.All(endMember => endMember.RelationshipMultiplicity == RelationshipMultiplicity.One)));
            //Filter the 1:1 foreign key associations to the ones relating the sets used in these cell wrappers.
            oneToOneForeignKeyAssociationsForThisWrapper =
                            it => (it.AssociationEndMembers.All(endMember => entityTypes.Contains(endMember.GetEntityType())))).ToList();

            //filter foreign key association sets to the sets that are 1:1 and affecting this wrapper.
            var oneToOneForeignKeyAssociationSetsForThisWrapper =
                    allForeignKeyAssociationSets.Where(it => oneToOneForeignKeyAssociationsForThisWrapper.Contains(it.ElementType));

            //Collect the facts for the foreign key association sets that are 1:1 and affecting this wrapper
            foreach (var assocSet in oneToOneForeignKeyAssociationSetsForThisWrapper)
                rightKB.CreateEquivalenceConstraintForOneToOneForeignKeyAssociation(assocSet, rightDomainMap);


Oct 17, 2012 at 10:42 AM

Some statistics and an apology: we have about 400 entities, prior to this code change, our first query took 67047 ms from which 63579 ms was spent in the given method, now it takes 4058 ms, 117 ms of which is in the aforementioned method.

The first and last line of the changed code I submitted contains the Time-keeping code I wrote for myself, sorry. 

Oct 17, 2012 at 4:41 PM

Hi Alireza,

This looks really promising and we would love to consider it for EF6. Would it be possible for you to contribute it?



Oct 18, 2012 at 7:30 AM

Hello Andrew.

I did it now. the relevant pull request is:


Nov 15, 2012 at 5:36 PM

Thanks @Alireza!  I came across that issue a while ago, but didn't have the time or understanding to fix it:  Nice work.  Now if only they'd backport the patch to EF5...

Nov 19, 2012 at 8:51 AM

Hi, the fix made by Alireza works like a charm view generation time is decreased in my case from 25 min to 2!

Is there any chances to have an update of EF5 with those changes??


Nov 19, 2012 at 9:46 AM

That's good news, I'm happy to hear that.

I hope that other areas of code that affect startup time be treated as well. Currently, there are certain features of EF that make it very appealing in serious projects, while some unexpected gotchas tend to prevent that.

I wish that EF team used to spend more time on fixing such problems, rather than adding new fancy features.

Mar 7, 2013 at 8:54 PM
Is there a non-code first (i.e. ObjectContext) T4 view generator?
Aug 9, 2013 at 1:49 PM
Not for EF6. For EF5 and earlier you can use EdmGen.exe form the developer command prompt.
Oct 24, 2013 at 2:18 AM
@joshmouch - I recently published T4 templates for generating views for EF5 Model/Database First apps on VS Gallery. See this post for more details.