Update entity and memory usage

Hi

I have two tables, Adverts (few million rows) and PriceTypes (twenty rows) in a one to one association/relationship.

When I update the price type in an advert like this:

var advert = unitOfWork.FindById<Advert>(advertId);
var priceType = unitOfWork.FindOne<PriceType>(Entity.Attribute("PriceTypeDisplay") == pricetypename);

advert.PriceType = priceType;

The sql output for the last line is a select statement that goes like this:

select ... from Adverts where Adverts.PriceTypeId = 123

Which if its a a popular price type then it will pull a million entities and cause an out of memory exception. How should I update the advert price type instead?

Thanks

Yes, because you are wiring up an object reference it needs to load the collection of associated entities to add the new entity into it. In this type of situation its very inefficient so you can instead wire up the association by assigning the FK id - e.g.

advert.PriceTypeId = priceType.Id;

Jeremy

Hi Jeremy

I've just tried your suggestion and the sql output remains the same. Is there some setting in the model that I should be looking out for to prevent this?

I'm using version 4.5 if that's of any help.

Allen

Hi Allen,

Do you mean 4.0 or 5.0? There is no version 4.5 of LightSpeed.

Jeremy

Hi

Sorry its 4.0.899.18717

Thanks - I suspect you will be encountering a behaviour that we have fixed a while back in the 4.0 nightlies where if you were assigning by Id but the object associated with that Id was already loaded into the UnitOfWork it would treat it as an object based assignment leading to the lazy load of the collection for wire-up. We subsequently changed this to defer the collection load until it was actually accessed.

If possible can you update to a recent nightly build and check if this resolves the issue.

Alternatively you can detach the reference object from the UnitOfWork after load by calling UnitOfWork.Detach() and then assign by Id which should avoid the problem mentioned above.

Jeremy

Hi

I've updated to the latest nightly version - it didnt help.

However if I detach the advert object, update the price type id, reattach and save then it's fine. So I think I'll stick with this solution.

Thanks

Allen