Speeding Up Remote Server Access

Jan 3, 2011 at 5:17 AM

This may be more of an Entity Framework question than an nHydrate question per se, but I thought I'd try here first since I'm using nHydrate in the project. As you'll see from the question I am not an expert in either EF or nHydrate. In fact, I'm working on my first project using either.

I've written an app for a small database (7 tables, less than 300 records -- none larger than a few hundred bytes each -- in all the tables; the largest table has only 249 records). Running against SqlServer 2008 locally it's quite snappy. But running against a remote hosted database it's quite slow on startup. After that, it's similarly snappy.

While there aren't a lot of records in the database, there are a number of relationships in it which I access repeatedly. Something tells me that I'm being sloppy in the way I'm accessing stuff, causing far more round trips to the server than I really need to. It feels like if I could simply download all the data into the eight tables at once and cache the results I'd be a lot better off.

So... what are best practices as regards minimizing server hits so far as using Entity Framework/nHydrate against a remote server?

- Mark

Jan 3, 2011 at 4:46 PM
Edited Jan 3, 2011 at 4:48 PM

You can lazy load or eager load in EF. When using nHydrate and walking relationships you are lazy loading so there are multiple round trips. If you wish to minimize database access look at eager loading with EF. The nHydrate syntax is a little better than standard EF in that you can specify walks with LINQ instead of strings. So if you wanted to walk all order for loaded customers and wanted to do this in one call your syntax would be something like this. This assume you have a Customer and related Order table. The "Include" method will ensure that there is a single database call.

using (AAAEntities context = new AAAEntities())

    var q = from x in context.Customer.Include(z => z.OrderList)
    select x;

Jan 3, 2011 at 11:00 PM

Thanks. I'm running into a problem using .Include(), however, because one of the associations I want to eagerly load is a many-to-many relation. Specifically, if I do this:

var x = from c in filterContext.Clip.Include(z => z.FkDramatisList)
            select c;

then when I iterate x I get an error to the effect that the object Clip does not include a navigation property FkDramatisList.

Looking into the generated code, that's because FkDramatisList is defined on the class ClipInclude. It points to the "linking" table of the many to many join:

Clip record definition:


Personae record definition:


Dramatis record definition (linking table):


nHydrate is kind enough to hide all the many-to-many plumbing behind the scenes, but either a different syntax for .Include() is required, or these kinds of associated relationships can't be .Included().

Suggestions? Would an explicit join work?

- Mark

Jan 3, 2011 at 11:41 PM

Some more information. There appears to be a bug in the code generator for the ObjectInclude class definitions.

The definition for my Clip class includes the following:

/// <summary>
/// The back navigation definition for walking Clip->Personae (role: 'Fk')
/// </summary>
[EdmRelationshipNavigationPropertyAttribute("Olbert.OFMEditor.EFDAL.Entity", "Dramatis", "FkPersonaeList")]
public virtual EntityCollection<Olbert.OFMEditor.EFDAL.Entity.Personae> FkPersonaeList
		EntityCollection<Olbert.OFMEditor.EFDAL.Entity.Personae> retval = ((IEntityWithRelationships)this).RelationshipManager.GetRelatedCollection<Olbert.OFMEditor.EFDAL.Entity.Personae>("Olbert.OFMEditor.EFDAL.Entity.Dramatis", "FkPersonaeList");
		if (!retval.IsLoaded && this.EntityState != System.Data.EntityState.Added && this.EntityState != System.Data.EntityState.Detached)
		return retval;

This is the navigation property that walks through the many-to-many linking table I described above (i.e., the relationship in the database is Clip <->Dramatis <-> Personae, but the Dramatis table gets hidden by nHydrate).

The definition of the ClipInclude class created by the code generator originally included the following:

/// <summary>
/// This is a mapping of the relationship with the Dramatis entity. (Role: 'fk')
/// </summary>
[Association(ThisKey = "ID", OtherKey = "clip_id")]
public Olbert.OFMEditor.EFDAL.ContextIncludeTree.DramatisInclude FkDramatisList { get; private set; }

The Entity Framework gacked on FkDramatisList at runtime whenever I would try to enumerate an include that referenced it (like in the example I cited in an earlier post in this thread).

Since I don't know what I'm doing :) I figured I'd try the only thing I could think of, which was to change the name of FkDramatisList to FkPersonaeList, the latter being a defined navigation property in the Clip class.

Wonder of wonders, it seems to have solved my problem, although I'm still testing. My app doesn't crash when enumerating the LINQ query using the Include(), and overall the app seems reasonably snappy again, even when calling the remote database.

So I think there's a bug in how the code generator names the properties in the ObjectInclude classes when the association it's related to has an associated table.

- Mark

p.s. Before changing the property name I upgraded to the 1/3/2011 release to see if that would solve the problem. It didn't, so if I'm right about the property naming bug I think it's still present.

Jan 4, 2011 at 1:33 AM

We know of some issues with N:M tables and I am glad you brought it up. This has forced a clean up. Now in the next version you will be able to eager load (with the "Include" syntax) these table types. For example image you have a Customer, Feature, and CustomerFeature intermediary. In the next version you will be able to do this:

var q1 = (from x in context.Customer.Include(z => z.FeatureList) select x).ToList();

Notice the context.Customer has a direct path to FeatureList (and vice versa) so you can define this relation walk before a query. I think this should fix what your issue.

Jan 4, 2011 at 1:58 AM

Thanks. Although if the problem was more than a simple one of naming the property I don't see how I was able to fix it in the generated code...