Specific scenarios with calculated properties defined via the PersistentAlias attribute where the performance can be improved

I don't think this issue is getting the attention it deserves! It's been 3 years and no update! Either people don't use XPO or they use it in small projects with not enough data to hit performance problems. We're using XPO in a huge database and the limitations on PersistentAlias has bitten us to the point we've resorted to direct SQLs for most of our queries.
We should be able to do this:

C#
public class MyClass  
{  
  public int Prop1 { get; set; }  
  public int Prop2 { get; set; }  
  [PersistentAlias(Prop1 * Prop2)]  
  public int Calculated { get; private set; /* or protected */ }  
}  

In the above example Prop1 and Prop2 are ordinary persistent properties. Calculated is unsurprisingly a calculated property with something special: it's not read-only!
XPO could leverage that by adding logic to fill this property at loading time. This shouldn't be too hard to implement (yes! I've checked this on XPO's source code…) as XPO can already generate correctly the expression to get the calculated property when using it in a filter.
This way, in a single round-trip, we would have all needed calculated properties completely filled with their up-to-date values without generating one extra SQL statement for each returned object multiplied by each calculated property used.

Thank you for your feedback. However, I should mention that no additional queries will be generated in your particular example, because the alias expression refers to properties of an already loaded object.

That's all well and good for the example given, but it is indeed a major problem for more complex properties, like for example where you have a Customer, Order and OrderLineItem and want to get and show the OrdersTotal on the Customer records in a list.

@Sheldmandu: Thank you for your feedback. I agree that this functionality still makes sense and we will consider it when planning future XPO releases.

We've highlighted this as well - especially when we want to put the persistentalias into a dxgrid or use them in Dashboard. Rather than getting the data in one hit, there are literally millions or queries going on. Dashboard especially it makes VERY VERY slow. The solution we have been presented is to use a linq query - but of course we have to hard code those making their use in dashboard pointless (and meaning we have to hardcode every possible query to allow customers to design their own dashboards - which simply isn't possible), and stops us using server collections in the DXGrid.
All in all, not a helpful solution. I do hope this problem is taken seriously, it greatly undermines what is otherwise a very useful ORM system.

@Andrew: Thank you for your feedback.

I agree completely. I have reported similar problems in this ticket: http://www.devexpress.com/Support/Center/Question/Details/Q523207
And I feel like the two issues raised are very closely related to one another - and I feel like AT LEAST ONE of them needs to be addressed.
Thanks!

Ej Alexandra, thank you for pointing that. I'm comment more on this on your other case.

Yes please, please, PLEASE fix this! :D

We also have problems with this. In our business applications, we make use of calculated properties alot. Using DC, we have the calculated attribute so we cannot use session.Evaluate.

Will this be solved in 14.2?

Hoping for a fix in 14.2 as well

I am afraid that this functionality will not be implemented in version 14.2. We will certainly let you know when it is implemented.

Is there any news already? Is there a way to change this behaviour on my own?

This functionality is not implemented yet. We will update this thread when anything changes.

BIG +1 VOTE from me !!!
It is a major drawback of persistent alias in hierarchical structures.

+1 for me

+1!
Not implemented on v14.2, but will be implemented on v15? Do you have 2015 roadmap?

I agree Svatopluk,
Especially when you've more compex persistent alias expressions, performance will suffer badly…

We rely heavy on XPO and the performance is causing serious issues. +1

+1 for me

+1  hope this is getting some attention after such a long time!

+1 for me

+1 for me

+1 for me

• 10 for me. ;-)

We need more detailed information on this issue.
After 3 years this is not a good answer for me -and probably for all others-  :
   <"This functionality is not implemented yet. We will update this thread when anything changes.">
Why this functionality isnt taking into account? Do you think it is not important? Is it too hard to implement? Is it necessary to change entire XPO infrastructure to implement this functionality? … etc…
We need more detailed information and we need an estimate plan when will this functionality be planned and implemented.

+1 for me

@Akin:
As we already mentioned above, we have this particular scenario on our radar. We understand it and it makes sense. It is one thing to receive feedback and a totally different thing to implement all the feedback into the product. As far as we have looked at this particular case, this optimization requires a lot of complicated changes to the product and is difficult to implement. I cannot give any exact estimate at this time, as we have not yet planned this feature for any specific product update.  Again, we appreciate your input in this regard and will take it into account for the future.

@All:
I want to ask everybody making +1 in this and other threads to make sure to provide a concrete business scenario description (preferably, in a user story notation - just dreaming) and a small sample project so as not to be misleading or confusing. Note that this particular thread is talking about a particular situation and behavior described in the reference ticket, and your real life cases can be quite different, as well as their solutions. And it does not necessary mean that you cannot approach your task in a different manner and thus avoid this XPO peculiarity. So, frankly speaking, unless your case is exactly the same as demonstrated the reference code example, making even +10 does not add any real value to such threads and only prevents you from solving real problems right away (and it also distracts our support team from helping you with your other tickets in the fastest way possible). Quite the opposite - sending us a small debuggable sample and repro instructions that help us understand your scenario enables us to provide you with the best technical solution possible for the current version. And quite often (from my almost decade experience) understanding a real case reveals other alternative options that will address a business problem even better that the original one. Thanks.

@Dennis - are there any plans for this - will it ever happen? Just hit an Performance Problem today because we implemented an persistentalias Attribute - which does all the calculations on the Server. looked good for the first Moment - but now when we load a listview with a huge amount of records - Performance is worst because for every single record the select is sent seperatly to the Server… :(

We actually eventually got round this.  Right or wrong, here's what we had to do (WPF + XPO):
standard XPO, persistentaliases everywhere (delayed, or everything is far too slow).
In the grid, create a Linq query using Dynamic Linq to only look at the properties you want to display on the grid.
Pass this as a LinqServer/instant feedback source to the grid.
Done this way Linq to Xpo has to create a proper single select query.
Then hopefully you have an ident column, and you can use a converter to read the ident and hence full object for the selected row (if you need it).
The only problem is that we have to have everything as a persistentaliases, and due to the limitations of XPO with FirstOrDefault() and OrderBy() Then(), this isn't the easiest thing to do in the world (and then there are persistent filters that you can't get anything from the session in which makes some things even more difficult)
It was either this or drop DevExpress completely and use something else.  Even with this we're still just on the edge - hopefully something will be done about the limitations in the filters and with Linq to XPO.

@Noxe, Andrew: Guys, would you please submit separate tickets and attach your test samples or at least your persistent classes code, so we can replicate and research your concrete situations and tell you for sure whether there are already solutions in the current version and whether they are ever related to the current thread? Please check out my previous comment in this thread (that starts with "@All:") for more details. Frankly speaking, there are just too many conditions and possible resolutions, which are not limited to XPQuery, Session.Prefetch and even not declaring calculated properties this way at all. With that essential information from you, our team will be in much better position to solve the exact business problem. Thanks.

Martin and Andrew the secenario you describe is what is called a SELECT N+1 where the database is hit with additional queries for each object ("row") retrieve initially. DevExpress documentation and many of XPO defaults makes it hard for us to do it "right" without actually getting to know XPO a little deeper. And XPO is NOT ALONE, this happens in nHibernate and Entity Framework too. You must "know" what you are doing to prevent that. Andrew mentioned the use of DELAYED attribute… Well, this IS one of the most common reasons for SELECT N+1 in XPO (and other ORMs). By not loading the full object graph, only loading objects on demand ("delayed"), when you actually need the "delayed" data, it will force a SELECT N+1 scenario.

What I did wast to judiciously apply the EXPLICIT attribute to prevent SELECT N+1 scenarios while still keeping the fetching of objects in control (i.e.: not fetch the entire graph on every select!). This is hard because EXPLICIT has a parameter that tells how deep in the hierarchy an object can be to be EXPLICITLY LOADED instead of DELAYED.

This affects your calculated properties if they reference delayed properties and will lead to a SELECT N+1 scenario.

You'll face this same kind of problem with any ORM. NHibernate has a nice syntax to work around it (directly from linq). I've proposed something similar to DevExpress but it has no appeal to them… So i've started adding dynamic attributes to xpo objects so that I could control explicit loading of persistent objects on a per-query/dynamic basis. But I now use it very little in favor of applying the EXPLICIT attribute judiciously and with great care.

You can't escape this kind of complexity in any ORM! The ORM can make it easier, but you MUST know about common pitfalls. Read this to know more (and see how nHibernate solves this): https://www.hibernatingrhinos.com/products/nhprof/learn/alert/selectnplusone

Sometimes you can't solve it in XPO directly, and has to use other methods: https://www.devexpress.com/Support/Center/Question/Details/T119562

(by the way I AM the same Felipe R Machado that was commenting on this case and opened the above case, but I've changed jobs and my other account got STUCK with the original employer, so I lost ALL my history and now I'm accessing the account on my new JOB… Unfortunately the one that bought the suit is my business partner and the account was created on his name - Benardo - so I'm in a kind of identity crisis here at DevExpress :-) )

And finally here I propose a mechanism for explicit loading controlled via LINQ or other similar mechanism. Note, however, I was not aware of the ExplicityLoading attribute at the time and was filled with SELECT N+1 problems: https://www.devexpress.com/Support/Center/Question/Details/Q446111

As Dennis pointed out perhaps the best way to figure out why the SELECT N+1 is happening in your scenarios is to open an individual ticket for each problem you face. I've learned that thats the best way to get to know XPO (other than read the source code, which I don't have access to anymore after changing jobs).

But I bet that it is either an improper use of Delayed or the lack of ExplicitLoading or a calculated property over a collection…

Hi Felipe,

I would like to use PersistantAlias for the loading of Counts and Maxes of specific Many-To-Many (NOT aggregated) collections that my objects contain. I haven't looked into this for at least 6 months, but I remember that there was a solution for aggregated collections, but only for them.

For me it's the Notes.Max() for last note, or Tasks.Count() but because of the N+1 problem I had to persist these values, which I really dislike.

Any thoughts on that? I don't see how to the explicit loading attribute would help (perhaps I'm misunderstanding,) but I can see how XPO could solve my problem at least, using nested sub-queries.

It's encouraging to see this ticket come to life! :)

Thanks.

@Felipe: Thank you for jumping in here. I also wanted to share more info on the SELECT N+1 problem and bring additional links to discussions on the Web like http://stackoverflow.com/questions/97197/what-is-the-n1-selects-issue, but decided to wait for concrete scenarios first.

@Denis: Our team will be more than happy to research the best technical XPO and non-XPO solutions for your business case in a separate ticket once you have had an opportunity to provide a supporting sample project/persistent classes code there, because it all depends on your classes configuration and UI requirements. Thanks in advance.

Hi Felipe!  Thanks for all of your input by the way - I looked at a great many of your issues (and shared your annoyance at some of them not being implemented).  You've haunted here much longer than I.

In the case of business logic and DXGrids, everything we do has to be server side only and optimal (i.e.  not select n+1).  It's not a case of 'ooh, this scenario'.  Everything we do has to be fast, and the way I've discovered to achieve this is to force XPO to not produce it's full classes, but dynamic classes. To do this we use Linq/Dynamic Linq, and it traverses persistentaliases server side without doing Select N+1 queries as a result.  Not only that but as we only get the data we want it is much quicker.
This is the generic solution we needed.  I can't be coming back with X scenarios (X being a very large number), it's just got to work and work fast.  Explicitloading, prefetch, delay (and if we don't delay persistentaliases its sooo slowwww) don't really interest, the above works.
If we had FirstOrDefault, Order, Then functionality our queries would be much easier to code and understand (there is a work around we can use, but horrendously adds extra joins everywhere).
Try it, most of our XPCollections are now gone, we just have IQueriables everywhere.
Just one small point, Linq doesn't like '.' in property names it retrieves, so use the "as" to call those properties a name without any '.' in.
No point me carrying on though, without this we would have ditched XPO a long time ago.

@Andrew:
>>Try it, most of our XPCollections are now gone, we just have IQueriables everywhere.
Have you considered using XPView (DataAccessMode = DataView in XAF) in your particular case? This would solve problems with multiple queries in many scenarios as well. I am looking forward to hearing from you.

Hi Dennis,

Yes we were going to be using XPViews - but we spotted Linq + Dynamic Linq, got it working and never looked back!

I'm sure using an IQueryable with dynamic classes in effect does under the hood what XPView does, but we can just use it everywhere (business logic, DXGrids with LinqInstantFeedback), and all the filtering etc looks nice in the code in Linq.

As I say, we've got no problem it all works fine.  When FirstOfDefault(), OrderBy(), Then() works we'll be even happier, and if we can get session variables into XPO custom filtering we'll actually be able to do everything we need.

We don't use XAF by the way - love to, but we've got this horrendous legacy system that we're recoding and just have to be in control of every aspect.  The database is nasty too, so we can't make standard XPO joins and have to use persistentalias everywhere (with compound keys).  Still, it's slowly being done.

Andrew