Binding to a Grid eager loads unbound columns

Hello:

Visual Studio 2013 with LightSpeed 5, April 7 nightly. Infragistics Windows Forms UI toolkit but the same thing happens in .NET DataGridView. .NET 4.0.

I am tracing down a performance problem in my application and have uncovered a problem I am hoping to get some guidance on solving.

For this conversation, I have two entities defined in my model: Dog and Color with Color being a nullable property of Dog. The One-To-Many Association has the Eager Load properties set to FALSE.

The Color table is a VARCHAR "Description" column and a few other VARCHAR and INT columns describing the 'Color'.

My application has an editor for Colors which is a Form with a Grid drawn on it and an OK button. The grid displays the single "Description" column. I fetch all my Color entities (7 of them) in to a BindingList like so:

BindingList colorList = new BindingList(work.Find());

I bind to the Grid like so: theColorsGrid.DataSource = _colorList;

I enabled the LightSpeed TraceLogger and discovered the source of my performance problem as I get more and more dogs. The assignment of my BindingList to DataSource fetches all the dogs attached to each Color in _colorList. There are several thousand Dogs and so the application pauses.

I haven't defined any columns manually in the grid; I just bind it like above. After the binding I hide columns I don't need. I Have to do it after because the binding CREATES the columns. Maybe this is wrong on my part?

Because I don't need the Dogs Collection when editing Color entities, I'd like to stop the fetching of all the Dogs in the Collection but don't know how to do that.

I swear this didn't happen before :)

Any suggestions would be most appreciated.

Thank you in advance.

Lionel

You will want to specifically choose which properties to bind rather than have it fetch the data for all columns. Because it is binding the Dog's collection it then triggers a lazy load (database fetch) of all dogs associated with each Color entity.

e.g. Have a look at this example: http://stackoverflow.com/questions/1180004/binding-to-bindinglistt-choose-what-to-bind

Jeremy

Thank you, Jeremy.

None of the suggestions there work with an Infragistics Grid which ultimately is where I need to be. It appears that Infragistics grids load all the columns (thereby firing the load) but only provide for a way to HIDE a column. Of course that doesn't do any good when the lazy load is the problem.

It doesn't look like I can use their tools directly with a Lightspeed entity list. It seems I have to create a proxy class with only needed columns, then bind that to the grid, then deal with all the synchronization after editing.

Oh well, I had hoped to avoid writing gobs of code to do that and just use the two great tools togther, but apparently it can't be done without some intermediary.

I appreciate your time and the links and suggestions very much.

Thank you,

Lionel

Hello:

A follow-up:

I removed Infragistics from the equation and am simply using a DataGridView.

I set my "Collection Visibility" to 'private' and added attribute [System.ComponentModel.Bindable(false)] to the "Collection Attributes" of my model. Shouldn't that prevent the collection from lazy loading when binding to a DataGridView?

I sort of figured it would... but it didn't. The logger still reported retrieving all the back reference.

Thank you again.

Lionel

Hello:

Do you have any suggestions on the above reply?

I can't seem to get my collection back references to NOT load and I'm at a bit of a brick wall.

Any suggestions to avoid this would be most welcome.

Thank you!

Lionel

The lazy loads will only occur if the control is dereferencing those properties. Did you try applying the solution in the Stack Overflow article above to the DataGridView? An alternative approach would be to detach the entities from the UnitOfWork using UnitOfWork.Detach(), bind them and then attach them later using UnitOfWork.Attach() if they need to be persisted. This shouldn't be needed however if you are able to explicitly describe the columns to be bound and therefore what properties the control should be inspecting.

Jeremy

Yes, and like I wrote:

I set my "Collection Visibility" to 'private' and added attribute [System.ComponentModel.Bindable(false)] to the "Collection Attributes" of my model. Shouldn't that prevent the collection from lazy loading when binding to a DataGridView?

Yet still the lazy loading occurs. I'm unsure why "bind and detach and attach" are in the conversation here. I'm simply trying to retrieve some rows, bind it to a grid and ignore the collection properties, yet I get a bazillion rows being lazy loaded for no apparent reason. They aren't displayed, I've got them "private" and I've done everything you suggested.

To your points:

The lazy loads will only occur if the control is dereferencing those properties Right, and I'm not dereferencing, hence this entire conversation.

Did you try applying the solution in the Stack Overflow article above to the DataGridView? Yes, see above.

The suggestion on detach and attach is to remove the association to the Unit Of Work which prevents any lazy loading from occurring. As mentioned previously the lazy loads will be being triggered by the control accessing the association property, so the only way to prevent this from occurring is to instruct the control not to bind those properties (and thus not access them). I would have expected the suggestion I made to achieve that but obviously not.. Im afraid I don't have any more suggestions I can give at this point but if you are able to send through a small repro project I can look at debugging whats causing the property access etc and see if that can lead to a solution.

Jeremy

Thank you, Jeremy. I appreciate your time very much.

Attached is the repro I've been exploring and trying to figure out how to avoid loading up the collection when I bind to a DataGridView.

In there, you will see I've got the "Collection Visibility" to 'private' and added attribute [System.ComponentModel.Bindable(false)] to the "Collection Attributes" of my model. With the logger activated, I can see that during binding, the collection is loaded and that's what I'm trying to avoid.

The repro is a single Form, the model it is using and all the code is in the Form button click.

This is just trying to diagnose; long-term I'd like my collection visibility to be public and the collection bindable but for now I'm happy just to get performance back.

I appreciate any suggestions and again, thank you for your time.

Lionel

Thanks Lionel,

Ill have a look into this and get back to you.

Jeremy

Hi Lionel,

Ok, didn't take too long to track this down. This is the same issue as is described here: http://www.mindscapehq.com/forums/thread/1566549 - because the grid control is calling BeginEdit on the Color objects it in turn has to load the children to ensure any changes on those are in the transaction scope leading to the lazy load.

You can disable this behaviour by setting this compatibility option:

var context = new LightSpeedContext<LightSpeedModel1UnitOfWork>("SQLite3")
{
  CompatibilityOptions = LightSpeedCompatibilityOptions.EnableCandidateFixes
};

Jeremy

Hi Jeremy:

That made a WORLD of difference - THANK YOU!

I appreciate all you did with this, your pointers, and your direction.

Thank you and best regards.

Lionel