Devexpress xaf listview how top 10 record only năm 2024

Question

This example demonstrates how to prevent a user from invoking a Detail View of an object in a List View.

Nội dung chính Show

Implementation Details
Additional Information
The scenario (is quite artificial but will serve the purpose)
The N+1 problem
Linq to the rescue?
Linq to the rescue with IQueryable
PersistentAlias can perform better?
PersistentAlias performs better with the correct DataAccessMode
InstantFeedbackView
Techniques that overcome the N+1 problems
CQRS pattern - XPO stored aggregates
Use the database at your advantage - Views
Totally unsupported - There will be dragons - N+N query version
Summary - Lessions learned
Bonus round

To allow users to edit objects directly in a List View, enable the and functionality.

Implementation Details

Call the Frame.GetController method to access an instance of the ListViewProcessCurrentObjectController class and disable the ListViewProcessCurrentObjectController.ProcessCurrentObjectAction property.

Additional Information

This example applies only when an XAF application displays a Detail View of an object after a user double-clicks this object in a List View, or focuses an object in a List View and presses Enter. To handle other scenarios, extend the controller's code. For example, if you do not want your XAF application to invoke a Detail View after a user clicks the New button to create an object in a List View, handle the NewObjectViewController.ObjectCreating event and set the ObjectCreatingEventArgs.ShowDetailView property to false.

We all know (or not) that using an abstraction over any kind of datasource can lead to performance problems. That's nothing new and is quite common with ORMs.

Let's look at the business problem we need to solve first before discussing why N+1 happens and what we can do to avoid it. It's a quite common problem with relational databases, an especially when dealing with any ORM. So we need to be careful to avoid it and there are several options we have when dealing with that kind of problem.

The scenario (is quite artificial but will serve the purpose)

Okay to be clear, this is an rather easy one and you don't want to go all the way down this performance optimization route (esp. for this example) but it's universal, easy to understand and applies to all 1+N aggregate calculation problems.

Let's imagine you have a simple

18.09.20 16:22:13.906 Executing sql 'select N0."Oid",N0."Name",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOffer" N0 where N0."GCRecord" is null'  
18.09.20 16:22:13.908 Result: rowcount = 300, total = 11996, N0.{Oid,Guid} = 4800, N0.{Name,String} = 4796, N0.{OptimisticLockField,Int32} = 1200, N0.{GCRecord,Int32} = 1200  
18.09.20 16:22:13.913 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {a786412c-907d-4b36-beb6-004395d38e9d}  
18.09.20 16:22:13.927 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:13.931 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {cf656095-dcd6-46f1-b830-00af607693a9}  
18.09.20 16:22:13.949 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
.  
.  
.  
.  
18.09.20 16:22:20.036 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {0be207ec-9430-44e7-964c-fd684303d051}  
18.09.20 16:22:20.053 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:20.057 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {3b48cf0a-d610-4586-8dfb-fde802777653}  
18.09.20 16:22:20.078 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:20.122 Executing sql 'select top 1 count(*) from "dbo"."SlowOffer" N0 where N0."GCRecord" is null'  
18.09.20 16:22:20.126 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4  
18.09.20 16:22:20.127 Executing sql 'select top 1 count(*) from "dbo"."SlowOfferItem" N0 where N0."GCRecord" is null'  
18.09.20 16:22:20.170 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4

4 and

18.09.20 16:22:13.906 Executing sql 'select N0."Oid",N0."Name",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOffer" N0 where N0."GCRecord" is null'  
18.09.20 16:22:13.908 Result: rowcount = 300, total = 11996, N0.{Oid,Guid} = 4800, N0.{Name,String} = 4796, N0.{OptimisticLockField,Int32} = 1200, N0.{GCRecord,Int32} = 1200  
18.09.20 16:22:13.913 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {a786412c-907d-4b36-beb6-004395d38e9d}  
18.09.20 16:22:13.927 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:13.931 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {cf656095-dcd6-46f1-b830-00af607693a9}  
18.09.20 16:22:13.949 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
.  
.  
.  
.  
18.09.20 16:22:20.036 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {0be207ec-9430-44e7-964c-fd684303d051}  
18.09.20 16:22:20.053 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:20.057 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {3b48cf0a-d610-4586-8dfb-fde802777653}  
18.09.20 16:22:20.078 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:20.122 Executing sql 'select top 1 count(*) from "dbo"."SlowOffer" N0 where N0."GCRecord" is null'  
18.09.20 16:22:20.126 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4  
18.09.20 16:22:20.127 Executing sql 'select top 1 count(*) from "dbo"."SlowOfferItem" N0 where N0."GCRecord" is null'  
18.09.20 16:22:20.170 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4

5 class that are in a

18.09.20 16:22:13.906 Executing sql 'select N0."Oid",N0."Name",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOffer" N0 where N0."GCRecord" is null'  
18.09.20 16:22:13.908 Result: rowcount = 300, total = 11996, N0.{Oid,Guid} = 4800, N0.{Name,String} = 4796, N0.{OptimisticLockField,Int32} = 1200, N0.{GCRecord,Int32} = 1200  
18.09.20 16:22:13.913 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {a786412c-907d-4b36-beb6-004395d38e9d}  
18.09.20 16:22:13.927 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:13.931 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {cf656095-dcd6-46f1-b830-00af607693a9}  
18.09.20 16:22:13.949 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
.  
.  
.  
.  
18.09.20 16:22:20.036 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {0be207ec-9430-44e7-964c-fd684303d051}  
18.09.20 16:22:20.053 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:20.057 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {3b48cf0a-d610-4586-8dfb-fde802777653}  
18.09.20 16:22:20.078 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:20.122 Executing sql 'select top 1 count(*) from "dbo"."SlowOffer" N0 where N0."GCRecord" is null'  
18.09.20 16:22:20.126 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4  
18.09.20 16:22:20.127 Executing sql 'select top 1 count(*) from "dbo"."SlowOfferItem" N0 where N0."GCRecord" is null'  
18.09.20 16:22:20.170 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4

6 relationship.

  Offer                       OfferItem  
+-----------------+         +-----------------+  
| OfferId         |         | OfferItemId     |  
| Name (str)      |         |                 |  
|                 |         |  Hours (int)    |  
|                 +-------->+  FK_Offer       |  
|                 |         |                 |  
|                 |         |                 |  
+-----------------+         +-----------------+

We want to calculate the sum of the hours and display them in the offer's

18.09.20 16:22:13.906 Executing sql 'select N0."Oid",N0."Name",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOffer" N0 where N0."GCRecord" is null'  
18.09.20 16:22:13.908 Result: rowcount = 300, total = 11996, N0.{Oid,Guid} = 4800, N0.{Name,String} = 4796, N0.{OptimisticLockField,Int32} = 1200, N0.{GCRecord,Int32} = 1200  
18.09.20 16:22:13.913 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {a786412c-907d-4b36-beb6-004395d38e9d}  
18.09.20 16:22:13.927 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:13.931 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {cf656095-dcd6-46f1-b830-00af607693a9}  
18.09.20 16:22:13.949 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
.  
.  
.  
.  
18.09.20 16:22:20.036 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {0be207ec-9430-44e7-964c-fd684303d051}  
18.09.20 16:22:20.053 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:20.057 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {3b48cf0a-d610-4586-8dfb-fde802777653}  
18.09.20 16:22:20.078 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:20.122 Executing sql 'select top 1 count(*) from "dbo"."SlowOffer" N0 where N0."GCRecord" is null'  
18.09.20 16:22:20.126 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4  
18.09.20 16:22:20.127 Executing sql 'select top 1 count(*) from "dbo"."SlowOfferItem" N0 where N0."GCRecord" is null'  
18.09.20 16:22:20.170 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4

7. We have now several options and I will step through them from naive (slow) to crazy (fast). The last couple of techniques use some internals of XPO and are not supported by XPO, but I found them so useful so it would be a shame not not mention them.

Be warned! Some of them will probably break in the future, or won't work under all circumstances. I will mark them with a disclaimer.

But first let's talk about what's the N+1 problem is all about!

The N+1 problem

Object Relational Mappers (or ORM for short) are a pattern to abstract away the database access and project SQL queries to objects. In our case it will be XPO that translates our access to the database.

We use attributes to tell XPO about our database and its relationship between entities. That allows XPO to guess what SQL statements it should generate. This is a very powerful abstraction, because you don't have to think all the time about SQL and can focus on business logic. That's fine for the most part, but if the number of records grows (or tables and relationships get more complicated) that guess can go horribly wrong, or even worse, you give the wrong hints to the ORM and it it performs multiple superfluous queries to the database.

Expensive queries are also something that can occur with ORMs (like massive JOINs) but that's not the focus of this blog post.

I'll give you a litte example in very naive C#.

PLEASE NEVER DO SOMETHING LIKE THIS IN PRODUCTION. YOU HAVE BEEN WARNED. (Or I insist you NEED to book some consulting hours with me)

[DefaultClassOptions]  
[DefaultProperty(nameof(HourSum))]  
public class SlowOffer : BaseObject  
{  
    public SlowOffer(Session session) : base(session) { }
    private string _Name;  
    [Persistent("Name")]  
    public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }
    [NonPersistent]  
    public int HourSum  
    {  
        get  
        {  
            var sum = 0;  
            foreach (var item in OfferItems)  
            {  
                sum += item.Hours;  
            }  
            return sum;  
        }  
    }
    [Association, Aggregated]  
    public XPCollection<SlowOfferItem> OfferItems => GetCollection<SlowOfferItem>(nameof(OfferItems));  
}
public class SlowOfferItem : BaseObject  
{  
    public SlowOfferItem(Session session) : base(session) { }
    private int _Hours;  
    [Persistent("Hours")]  
    public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }
    private SlowOffer _SlowOffer;  
    [Persistent("SlowOffer"), Association]  
    public SlowOffer SlowOffer { get => _SlowOffer; set => SetPropertyValue(nameof(SlowOffer), ref _SlowOffer, value); }  
}

For all examples I use 300 18.09.20 16:22:13.906 Executing sql 'select N0."Oid",N0."Name",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOffer" N0 where N0."GCRecord" is null' > 18.09.20 16:22:13.908 Result: rowcount = 300, total = 11996, N0.{Oid,Guid} = 4800, N0.{Name,String} = 4796, N0.{OptimisticLockField,Int32} = 1200, N0.{GCRecord,Int32} = 1200 > 18.09.20 16:22:13.913 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {a786412c-907d-4b36-beb6-004395d38e9d} > 18.09.20 16:22:13.927 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000 > 18.09.20 16:22:13.931 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {cf656095-dcd6-46f1-b830-00af607693a9} > 18.09.20 16:22:13.949 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000 > . > . > . > . > 18.09.20 16:22:20.036 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {0be207ec-9430-44e7-964c-fd684303d051} > 18.09.20 16:22:20.053 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000 > 18.09.20 16:22:20.057 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {3b48cf0a-d610-4586-8dfb-fde802777653} > 18.09.20 16:22:20.078 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000 > 18.09.20 16:22:20.122 Executing sql 'select top 1 count() from "dbo"."SlowOffer" N0 where N0."GCRecord" is null' > 18.09.20 16:22:20.126 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4 > 18.09.20 16:22:20.127 Executing sql 'select top 1 count() from "dbo"."SlowOfferItem" N0 where N0."GCRecord" is null' > 18.09.20 16:22:20.170 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4 >
8 with 1000
18.09.20 16:22:13.906 Executing sql 'select N0."Oid",N0."Name",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOffer" N0 where N0."GCRecord" is null' > 18.09.20 16:22:13.908 Result: rowcount = 300, total = 11996, N0.{Oid,Guid} = 4800, N0.{Name,String} = 4796, N0.{OptimisticLockField,Int32} = 1200, N0.{GCRecord,Int32} = 1200 > 18.09.20 16:22:13.913 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {a786412c-907d-4b36-beb6-004395d38e9d} > 18.09.20 16:22:13.927 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000 > 18.09.20 16:22:13.931 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {cf656095-dcd6-46f1-b830-00af607693a9} > 18.09.20 16:22:13.949 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000 > . > . > . > . > 18.09.20 16:22:20.036 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {0be207ec-9430-44e7-964c-fd684303d051} > 18.09.20 16:22:20.053 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000 > 18.09.20 16:22:20.057 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {3b48cf0a-d610-4586-8dfb-fde802777653} > 18.09.20 16:22:20.078 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000 > 18.09.20 16:22:20.122 Executing sql 'select top 1 count() from "dbo"."SlowOffer" N0 where N0."GCRecord" is null' > 18.09.20 16:22:20.126 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4 > 18.09.20 16:22:20.127 Executing sql 'select top 1 count() from "dbo"."SlowOfferItem" N0 where N0."GCRecord" is null' > 18.09.20 16:22:20.170 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4 >
9 each. It's sorted by the
[DefaultClassOptions] > [DefaultProperty(nameof(HourSum))] > public class SlowOfferWithLinq : BaseObject > { > public SlowOfferWithLinq(Session session) : base(session) { } private string _Name; > [Persistent("Name")] > public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); } [NonPersistent] > // Sum up all the hours using Linq > public int HourSum => OfferItems.Sum(m => m.Hours); [Association, Aggregated] > public XPCollection<SlowOfferItemWithLinq> OfferItems => GetCollection<SlowOfferItemWithLinq>(nameof(OfferItems)); > } public class SlowOfferItemWithLinq : BaseObject > { > public SlowOfferItemWithLinq(Session session) : base(session) { } private int _Hours; > [Persistent("Hours")] > public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); } private SlowOfferWithLinq _SlowOfferWithLinq; > [Persistent("SlowOfferWithLinq"), Association] > public SlowOfferWithLinq SlowOfferWithLinq { get => _SlowOfferWithLinq; set => SetPropertyValue(nameof(SlowOfferWithLinq), ref _SlowOfferWithLinq, value); } > } > 0 property ascending. I use SqlServer LocalDb for these tests on a Intel(R) Core(TM) i7-4770K CPU @ 3.50GHz with 16 GB of memory and a two SSD disks Raid 0 setup. The build configuration is set on Debug with an attached debugger. XPO log verbosity = 4 XAF log verbosity = 3 This means those numbers will improve in Release configuration, so we set a pessimistic base line (and also aim for a better developer feedback cycle).

This will result in horrible performance:

18.09.20 16:22:13.906 Executing sql 'select N0."Oid",N0."Name",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOffer" N0 where N0."GCRecord" is null'  
18.09.20 16:22:13.908 Result: rowcount = 300, total = 11996, N0.{Oid,Guid} = 4800, N0.{Name,String} = 4796, N0.{OptimisticLockField,Int32} = 1200, N0.{GCRecord,Int32} = 1200  
18.09.20 16:22:13.913 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {a786412c-907d-4b36-beb6-004395d38e9d}  
18.09.20 16:22:13.927 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:13.931 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {cf656095-dcd6-46f1-b830-00af607693a9}  
18.09.20 16:22:13.949 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
.  
.  
.  
.  
18.09.20 16:22:20.036 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {0be207ec-9430-44e7-964c-fd684303d051}  
18.09.20 16:22:20.053 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:20.057 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {3b48cf0a-d610-4586-8dfb-fde802777653}  
18.09.20 16:22:20.078 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:20.122 Executing sql 'select top 1 count(*) from "dbo"."SlowOffer" N0 where N0."GCRecord" is null'  
18.09.20 16:22:20.126 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4  
18.09.20 16:22:20.127 Executing sql 'select top 1 count(*) from "dbo"."SlowOfferItem" N0 where N0."GCRecord" is null'  
18.09.20 16:22:20.170 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4

What happens is: for every row in the

18.09.20 16:22:13.906 Executing sql 'select N0."Oid",N0."Name",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOffer" N0 where N0."GCRecord" is null'  
18.09.20 16:22:13.908 Result: rowcount = 300, total = 11996, N0.{Oid,Guid} = 4800, N0.{Name,String} = 4796, N0.{OptimisticLockField,Int32} = 1200, N0.{GCRecord,Int32} = 1200  
18.09.20 16:22:13.913 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {a786412c-907d-4b36-beb6-004395d38e9d}  
18.09.20 16:22:13.927 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:13.931 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {cf656095-dcd6-46f1-b830-00af607693a9}  
18.09.20 16:22:13.949 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
.  
.  
.  
.  
18.09.20 16:22:20.036 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {0be207ec-9430-44e7-964c-fd684303d051}  
18.09.20 16:22:20.053 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:20.057 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {3b48cf0a-d610-4586-8dfb-fde802777653}  
18.09.20 16:22:20.078 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:20.122 Executing sql 'select top 1 count(*) from "dbo"."SlowOffer" N0 where N0."GCRecord" is null'  
18.09.20 16:22:20.126 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4  
18.09.20 16:22:20.127 Executing sql 'select top 1 count(*) from "dbo"."SlowOfferItem" N0 where N0."GCRecord" is null'  
18.09.20 16:22:20.170 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4

4 table a second select that selects all the related

18.09.20 16:22:13.906 Executing sql 'select N0."Oid",N0."Name",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOffer" N0 where N0."GCRecord" is null'  
18.09.20 16:22:13.908 Result: rowcount = 300, total = 11996, N0.{Oid,Guid} = 4800, N0.{Name,String} = 4796, N0.{OptimisticLockField,Int32} = 1200, N0.{GCRecord,Int32} = 1200  
18.09.20 16:22:13.913 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {a786412c-907d-4b36-beb6-004395d38e9d}  
18.09.20 16:22:13.927 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:13.931 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {cf656095-dcd6-46f1-b830-00af607693a9}  
18.09.20 16:22:13.949 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
.  
.  
.  
.  
18.09.20 16:22:20.036 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {0be207ec-9430-44e7-964c-fd684303d051}  
18.09.20 16:22:20.053 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:20.057 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {3b48cf0a-d610-4586-8dfb-fde802777653}  
18.09.20 16:22:20.078 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:20.122 Executing sql 'select top 1 count(*) from "dbo"."SlowOffer" N0 where N0."GCRecord" is null'  
18.09.20 16:22:20.126 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4  
18.09.20 16:22:20.127 Executing sql 'select top 1 count(*) from "dbo"."SlowOfferItem" N0 where N0."GCRecord" is null'  
18.09.20 16:22:20.170 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4

9 collection get selected one by one. So in total there are at least 300 single

[DefaultClassOptions]  
[DefaultProperty(nameof(HourSum))]  
public class SlowOfferWithLinq : BaseObject  
{  
    public SlowOfferWithLinq(Session session) : base(session) { }
    private string _Name;  
    [Persistent("Name")]  
    public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }
    [NonPersistent]  
    // Sum up all the hours using Linq  
    public int HourSum => OfferItems.Sum(m => m.Hours);
    [Association, Aggregated]  
    public XPCollection<SlowOfferItemWithLinq> OfferItems => GetCollection<SlowOfferItemWithLinq>(nameof(OfferItems));  
}
public class SlowOfferItemWithLinq : BaseObject  
{  
    public SlowOfferItemWithLinq(Session session) : base(session) { }
    private int _Hours;  
    [Persistent("Hours")]  
    public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }
    private SlowOfferWithLinq _SlowOfferWithLinq;  
    [Persistent("SlowOfferWithLinq"), Association]  
    public SlowOfferWithLinq SlowOfferWithLinq { get => _SlowOfferWithLinq; set => SetPropertyValue(nameof(SlowOfferWithLinq), ref _SlowOfferWithLinq, value); }  
}

3 statements dropped at your database.

Of course this happens because to sort the table on the client side, XPO needs to fetch all the records to order them.

To see some comparison:

Sort by

 [DefaultClassOptions]
[DefaultProperty(nameof(HourSum))]
public class SlowOfferWithLinq : BaseObject
{ 
public SlowOfferWithLinq(Session session) : base(session) { }  
private string _Name;  
[Persistent("Name")]  
public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }  
[NonPersistent]  
// Sum up all the hours using Linq  
public int HourSum => OfferItems.Sum(m => m.Hours);  
[Association, Aggregated]  
public XPCollection<SlowOfferItemWithLinq> OfferItems => GetCollection<SlowOfferItemWithLinq>(nameof(OfferItems));  
}
public class SlowOfferItemWithLinq : BaseObject
{ public SlowOfferItemWithLinq(Session session) : base(session) { }  
private int _Hours;  
[Persistent("Hours")]  
public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }  
private SlowOfferWithLinq _SlowOfferWithLinq;  
[Persistent("SlowOfferWithLinq"), Association]  
public SlowOfferWithLinq SlowOfferWithLinq { get => _SlowOfferWithLinq; set => SetPropertyValue(nameof(SlowOfferWithLinq), ref _SlowOfferWithLinq, value); }  
}

4: 0.814 seconds

Sort by

 [DefaultClassOptions]
[DefaultProperty(nameof(HourSum))]
public class SlowOfferWithLinq : BaseObject
{ 
public SlowOfferWithLinq(Session session) : base(session) { }  
private string _Name;  
[Persistent("Name")]  
public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }  
[NonPersistent]  
// Sum up all the hours using Linq  
public int HourSum => OfferItems.Sum(m => m.Hours);  
[Association, Aggregated]  
public XPCollection<SlowOfferItemWithLinq> OfferItems => GetCollection<SlowOfferItemWithLinq>(nameof(OfferItems));  
}
public class SlowOfferItemWithLinq : BaseObject
{ public SlowOfferItemWithLinq(Session session) : base(session) { }  
private int _Hours;  
[Persistent("Hours")]  
public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }  
private SlowOfferWithLinq _SlowOfferWithLinq;  
[Persistent("SlowOfferWithLinq"), Association]  
public SlowOfferWithLinq SlowOfferWithLinq { get => _SlowOfferWithLinq; set => SetPropertyValue(nameof(SlowOfferWithLinq), ref _SlowOfferWithLinq, value); }  
}

0: 6.622 seconds

The only reason why sorting by name is a lot faster is, that only the visible rows in the grid will be N+1 selected.

I am not doing any memory analysis here, but of course this approach will take a lot of memory as well, because XPO needs to fetch all the data into memory before it can do any sorting on it.

If you don't sort by

[DefaultClassOptions]  
[DefaultProperty(nameof(HourSum))]  
public class SlowOfferWithLinq : BaseObject  
{  
    public SlowOfferWithLinq(Session session) : base(session) { }
    private string _Name;  
    [Persistent("Name")]  
    public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }
    [NonPersistent]  
    // Sum up all the hours using Linq  
    public int HourSum => OfferItems.Sum(m => m.Hours);
    [Association, Aggregated]  
    public XPCollection<SlowOfferItemWithLinq> OfferItems => GetCollection<SlowOfferItemWithLinq>(nameof(OfferItems));  
}
public class SlowOfferItemWithLinq : BaseObject  
{  
    public SlowOfferItemWithLinq(Session session) : base(session) { }
    private int _Hours;  
    [Persistent("Hours")]  
    public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }
    private SlowOfferWithLinq _SlowOfferWithLinq;  
    [Persistent("SlowOfferWithLinq"), Association]  
    public SlowOfferWithLinq SlowOfferWithLinq { get => _SlowOfferWithLinq; set => SetPropertyValue(nameof(SlowOfferWithLinq), ref _SlowOfferWithLinq, value); }  
}

0 and scroll down the list, you will have a noticible lag when scrolling the list. This is caused by XPO lazy load collections by default.

Linq to the rescue?

You might be tempted to say: "Why not use the power of Linq?". So let's look at some code.

[DefaultClassOptions]  
[DefaultProperty(nameof(HourSum))]  
public class SlowOfferWithLinq : BaseObject  
{  
    public SlowOfferWithLinq(Session session) : base(session) { }
    private string _Name;  
    [Persistent("Name")]  
    public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }
    [NonPersistent]  
    // Sum up all the hours using Linq  
    public int HourSum => OfferItems.Sum(m => m.Hours);
    [Association, Aggregated]  
    public XPCollection<SlowOfferItemWithLinq> OfferItems => GetCollection<SlowOfferItemWithLinq>(nameof(OfferItems));  
}
public class SlowOfferItemWithLinq : BaseObject  
{  
    public SlowOfferItemWithLinq(Session session) : base(session) { }
    private int _Hours;  
    [Persistent("Hours")]  
    public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }
    private SlowOfferWithLinq _SlowOfferWithLinq;  
    [Persistent("SlowOfferWithLinq"), Association]  
    public SlowOfferWithLinq SlowOfferWithLinq { get => _SlowOfferWithLinq; set => SetPropertyValue(nameof(SlowOfferWithLinq), ref _SlowOfferWithLinq, value); }  
}

Let's look at the queries generated:

19.09.20 10:35:31.422 Executing sql 'select N0."Oid",N0."Name",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferWithLinq" N0 where N0."GCRecord" is null'  
19.09.20 10:35:31.425 Result: rowcount = 300, total = 11914, N0.{Oid,Guid} = 4800, N0.{Name,String} = 4714, N0.{OptimisticLockField,Int32} = 1200, N0.{GCRecord,Int32} = 1200  
19.09.20 10:35:31.426 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOfferWithLinq",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."SlowOfferWithLinq" = @p0))' with parameters {a917fb15-82e1-471e-95b3-031a070d492d}  
19.09.20 10:35:31.440 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOfferWithLinq,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
19.09.20 10:35:31.451 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOfferWithLinq",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."SlowOfferWithLinq" = @p0))' with parameters {b625d838-3747-4ad4-aaac-032e79a7d3a6}  
19.09.20 10:35:31.464 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOfferWithLinq,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000.  
.  
.  
.  
19.09.20 10:35:36.787 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOfferWithLinq",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."SlowOfferWithLinq" = @p0))' with parameters {e2774f41-7d31-4a5f-afa4-ffe8d3a21d47}  
19.09.20 10:35:36.803 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOfferWithLinq,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
19.09.20 10:35:36.851 Executing sql 'select top 1 count(*) from "dbo"."SlowOfferWithLinq" N0 where N0."GCRecord" is null'  
19.09.20 10:35:36.853 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4  
19.09.20 10:35:36.854 Executing sql 'select top 1 count(*) from "dbo"."SlowOfferItemWithLinq" N0 where N0."GCRecord" is null'  
19.09.20 10:35:36.877 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4

This results in the following performance

Sort by

 [DefaultClassOptions]
[DefaultProperty(nameof(HourSum))]
public class SlowOfferWithLinq : BaseObject
{ 
public SlowOfferWithLinq(Session session) : base(session) { }  
private string _Name;  
[Persistent("Name")]  
public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }  
[NonPersistent]  
// Sum up all the hours using Linq  
public int HourSum => OfferItems.Sum(m => m.Hours);  
[Association, Aggregated]  
public XPCollection<SlowOfferItemWithLinq> OfferItems => GetCollection<SlowOfferItemWithLinq>(nameof(OfferItems));  
}
public class SlowOfferItemWithLinq : BaseObject
{ public SlowOfferItemWithLinq(Session session) : base(session) { }  
private int _Hours;  
[Persistent("Hours")]  
public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }  
private SlowOfferWithLinq _SlowOfferWithLinq;  
[Persistent("SlowOfferWithLinq"), Association]  
public SlowOfferWithLinq SlowOfferWithLinq { get => _SlowOfferWithLinq; set => SetPropertyValue(nameof(SlowOfferWithLinq), ref _SlowOfferWithLinq, value); }  
}

4: 0.735 seconds

Sort by

 [DefaultClassOptions]
[DefaultProperty(nameof(HourSum))]
public class SlowOfferWithLinq : BaseObject
{ 
public SlowOfferWithLinq(Session session) : base(session) { }  
private string _Name;  
[Persistent("Name")]  
public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }  
[NonPersistent]  
// Sum up all the hours using Linq  
public int HourSum => OfferItems.Sum(m => m.Hours);  
[Association, Aggregated]  
public XPCollection<SlowOfferItemWithLinq> OfferItems => GetCollection<SlowOfferItemWithLinq>(nameof(OfferItems));  
}
public class SlowOfferItemWithLinq : BaseObject
{ public SlowOfferItemWithLinq(Session session) : base(session) { }  
private int _Hours;  
[Persistent("Hours")]  
public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }  
private SlowOfferWithLinq _SlowOfferWithLinq;  
[Persistent("SlowOfferWithLinq"), Association]  
public SlowOfferWithLinq SlowOfferWithLinq { get => _SlowOfferWithLinq; set => SetPropertyValue(nameof(SlowOfferWithLinq), ref _SlowOfferWithLinq, value); }  
}

0: 5.672 seconds

As you can see, it seams like it performs slightly faster, but on average it is exactly the same as the naive approach. The reason for this is hard to spot (and that is why lazy loading can be dangerous, if you don't know what you are doing):

[NonPersistent]  
// Sum up all the hours using Linq  
public int HourSum => OfferItems.Sum(m => m.Hours);

We first load the entire collection and sum up afterwards in memory. The reason for this is pretty simple by looking at the datatype of

18.09.20 16:22:13.906 Executing sql 'select N0."Oid",N0."Name",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOffer" N0 where N0."GCRecord" is null'  
18.09.20 16:22:13.908 Result: rowcount = 300, total = 11996, N0.{Oid,Guid} = 4800, N0.{Name,String} = 4796, N0.{OptimisticLockField,Int32} = 1200, N0.{GCRecord,Int32} = 1200  
18.09.20 16:22:13.913 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {a786412c-907d-4b36-beb6-004395d38e9d}  
18.09.20 16:22:13.927 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:13.931 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {cf656095-dcd6-46f1-b830-00af607693a9}  
18.09.20 16:22:13.949 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
.  
.  
.  
.  
18.09.20 16:22:20.036 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {0be207ec-9430-44e7-964c-fd684303d051}  
18.09.20 16:22:20.053 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:20.057 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {3b48cf0a-d610-4586-8dfb-fde802777653}  
18.09.20 16:22:20.078 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:20.122 Executing sql 'select top 1 count(*) from "dbo"."SlowOffer" N0 where N0."GCRecord" is null'  
18.09.20 16:22:20.126 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4  
18.09.20 16:22:20.127 Executing sql 'select top 1 count(*) from "dbo"."SlowOfferItem" N0 where N0."GCRecord" is null'  
18.09.20 16:22:20.170 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4

9. It's a

19.09.20 10:35:31.422 Executing sql 'select N0."Oid",N0."Name",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferWithLinq" N0 where N0."GCRecord" is null'  
19.09.20 10:35:31.425 Result: rowcount = 300, total = 11914, N0.{Oid,Guid} = 4800, N0.{Name,String} = 4714, N0.{OptimisticLockField,Int32} = 1200, N0.{GCRecord,Int32} = 1200  
19.09.20 10:35:31.426 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOfferWithLinq",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."SlowOfferWithLinq" = @p0))' with parameters {a917fb15-82e1-471e-95b3-031a070d492d}  
19.09.20 10:35:31.440 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOfferWithLinq,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
19.09.20 10:35:31.451 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOfferWithLinq",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."SlowOfferWithLinq" = @p0))' with parameters {b625d838-3747-4ad4-aaac-032e79a7d3a6}  
19.09.20 10:35:31.464 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOfferWithLinq,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000.  
.  
.  
.  
19.09.20 10:35:36.787 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOfferWithLinq",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."SlowOfferWithLinq" = @p0))' with parameters {e2774f41-7d31-4a5f-afa4-ffe8d3a21d47}  
19.09.20 10:35:36.803 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOfferWithLinq,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
19.09.20 10:35:36.851 Executing sql 'select top 1 count(*) from "dbo"."SlowOfferWithLinq" N0 where N0."GCRecord" is null'  
19.09.20 10:35:36.853 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4  
19.09.20 10:35:36.854 Executing sql 'select top 1 count(*) from "dbo"."SlowOfferItemWithLinq" N0 where N0."GCRecord" is null'  
19.09.20 10:35:36.877 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4

0. In order to let the database the work, we need to make sure we work with an

19.09.20 10:35:31.422 Executing sql 'select N0."Oid",N0."Name",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferWithLinq" N0 where N0."GCRecord" is null'  
19.09.20 10:35:31.425 Result: rowcount = 300, total = 11914, N0.{Oid,Guid} = 4800, N0.{Name,String} = 4714, N0.{OptimisticLockField,Int32} = 1200, N0.{GCRecord,Int32} = 1200  
19.09.20 10:35:31.426 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOfferWithLinq",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."SlowOfferWithLinq" = @p0))' with parameters {a917fb15-82e1-471e-95b3-031a070d492d}  
19.09.20 10:35:31.440 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOfferWithLinq,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
19.09.20 10:35:31.451 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOfferWithLinq",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."SlowOfferWithLinq" = @p0))' with parameters {b625d838-3747-4ad4-aaac-032e79a7d3a6}  
19.09.20 10:35:31.464 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOfferWithLinq,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000.  
.  
.  
.  
19.09.20 10:35:36.787 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOfferWithLinq",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."SlowOfferWithLinq" = @p0))' with parameters {e2774f41-7d31-4a5f-afa4-ffe8d3a21d47}  
19.09.20 10:35:36.803 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOfferWithLinq,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
19.09.20 10:35:36.851 Executing sql 'select top 1 count(*) from "dbo"."SlowOfferWithLinq" N0 where N0."GCRecord" is null'  
19.09.20 10:35:36.853 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4  
19.09.20 10:35:36.854 Executing sql 'select top 1 count(*) from "dbo"."SlowOfferItemWithLinq" N0 where N0."GCRecord" is null'  
19.09.20 10:35:36.877 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4

1.

Linq to the rescue with IQueryable

Now we know what we should aim for, let's look at a Linq version of the code that uses an

19.09.20 10:35:31.422 Executing sql 'select N0."Oid",N0."Name",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferWithLinq" N0 where N0."GCRecord" is null'  
19.09.20 10:35:31.425 Result: rowcount = 300, total = 11914, N0.{Oid,Guid} = 4800, N0.{Name,String} = 4714, N0.{OptimisticLockField,Int32} = 1200, N0.{GCRecord,Int32} = 1200  
19.09.20 10:35:31.426 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOfferWithLinq",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."SlowOfferWithLinq" = @p0))' with parameters {a917fb15-82e1-471e-95b3-031a070d492d}  
19.09.20 10:35:31.440 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOfferWithLinq,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
19.09.20 10:35:31.451 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOfferWithLinq",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."SlowOfferWithLinq" = @p0))' with parameters {b625d838-3747-4ad4-aaac-032e79a7d3a6}  
19.09.20 10:35:31.464 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOfferWithLinq,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000.  
.  
.  
.  
19.09.20 10:35:36.787 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOfferWithLinq",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."SlowOfferWithLinq" = @p0))' with parameters {e2774f41-7d31-4a5f-afa4-ffe8d3a21d47}  
19.09.20 10:35:36.803 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOfferWithLinq,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
19.09.20 10:35:36.851 Executing sql 'select top 1 count(*) from "dbo"."SlowOfferWithLinq" N0 where N0."GCRecord" is null'  
19.09.20 10:35:36.853 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4  
19.09.20 10:35:36.854 Executing sql 'select top 1 count(*) from "dbo"."SlowOfferItemWithLinq" N0 where N0."GCRecord" is null'  
19.09.20 10:35:36.877 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4

2:

[DefaultClassOptions]  
[DefaultProperty(nameof(HourSum))]  
public class FasterOfferWithLinq : BaseObject  
{  
    public FasterOfferWithLinq(Session session) : base(session) { }
    private string _Name;  
    [Persistent("Name")]  
    public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }
    [NonPersistent]  
    public int HourSum =>  
        //Query the data store  
        Session.Query<FasterOfferItemWithLinq>()  
        //Filter it down to the current record  
        .Where(i => i.FasterOfferWithLinq.Oid == Oid)  
        //Sum the total hours  
        .Sum(m => m.Hours);
    [Association, Aggregated]  
    public XPCollection<FasterOfferItemWithLinq> OfferItems => GetCollection<FasterOfferItemWithLinq>(nameof(OfferItems));  
}
public class FasterOfferItemWithLinq : BaseObject  
{  
    public FasterOfferItemWithLinq(Session session) : base(session) { }
    private int _Hours;  
    [Persistent("Hours")]  
    public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }
    private FasterOfferWithLinq _FasterOfferWithLinq;  
    [Persistent("FasterOfferWithLinq"), Association]  
    public FasterOfferWithLinq FasterOfferWithLinq { get => _FasterOfferWithLinq; set => SetPropertyValue(nameof(FasterOfferWithLinq), ref _FasterOfferWithLinq, value); }  
}

This will result in much leaner queries. Still does not solve the N+1 problem though:

19.09.20 10:58:05.069 Executing sql 'select N0."Oid",N0."Name",N0."OptimisticLockField",N0."GCRecord" from "dbo"."FasterOfferWithLinq" N0 where N0."GCRecord" is null'  
19.09.20 10:58:05.070 Result: rowcount = 300, total = 11992, N0.{Oid,Guid} = 4800, N0.{Name,String} = 4792, N0.{OptimisticLockField,Int32} = 1200, N0.{GCRecord,Int32} = 1200  
19.09.20 10:58:05.071 Executing sql 'select top 1 sum(N0."Hours") from "dbo"."FasterOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."FasterOfferWithLinq" = @p0))' with parameters {a3797700-1fcf-429f-9c34-005322afa892}  
19.09.20 10:58:05.075 Result: rowcount = 1, total = 4, SubQuery(Sum,N0.{Hours,Int32},) = 4  
19.09.20 10:58:05.076 Executing sql 'select top 1 sum(N0."Hours") from "dbo"."FasterOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."FasterOfferWithLinq" = @p0))' with parameters {3683bfc2-7314-452f-be51-00692f123965}  
.  
.  
.  
19.09.20 10:58:06.497 Executing sql 'select top 1 count(*) from "dbo"."FasterOfferWithLinq" N0 where N0."GCRecord" is null'  
19.09.20 10:58:06.497 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4  
19.09.20 10:58:06.498 Executing sql 'select top 1 count(*) from "dbo"."FasterOfferItemWithLinq" N0 where N0."GCRecord" is null'  
19.09.20 10:58:06.512 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4  
19.09.20 10:58:07.919 Executing sql 'select top 1 sum(N0."Hours") from "dbo"."FasterOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."FasterOfferWithLinq" = @p0))' with parameters {820161dc-8568-412c-bf84-c4f148e8122f}  
19.09.20 10:58:07.924 Result: rowcount = 1, total = 4, SubQuery(Sum,N0.{Hours,Int32},) = 4

This results in the following performance

Sort by

 [DefaultClassOptions]
[DefaultProperty(nameof(HourSum))]
public class SlowOfferWithLinq : BaseObject
{ 
public SlowOfferWithLinq(Session session) : base(session) { }  
private string _Name;  
[Persistent("Name")]  
public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }  
[NonPersistent]  
// Sum up all the hours using Linq  
public int HourSum => OfferItems.Sum(m => m.Hours);  
[Association, Aggregated]  
public XPCollection<SlowOfferItemWithLinq> OfferItems => GetCollection<SlowOfferItemWithLinq>(nameof(OfferItems));  
}
public class SlowOfferItemWithLinq : BaseObject
{ public SlowOfferItemWithLinq(Session session) : base(session) { }  
private int _Hours;  
[Persistent("Hours")]  
public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }  
private SlowOfferWithLinq _SlowOfferWithLinq;  
[Persistent("SlowOfferWithLinq"), Association]  
public SlowOfferWithLinq SlowOfferWithLinq { get => _SlowOfferWithLinq; set => SetPropertyValue(nameof(SlowOfferWithLinq), ref _SlowOfferWithLinq, value); }  
}

4: 0.156 seconds

Sort by

 [DefaultClassOptions]
[DefaultProperty(nameof(HourSum))]
public class SlowOfferWithLinq : BaseObject
{ 
public SlowOfferWithLinq(Session session) : base(session) { }  
private string _Name;  
[Persistent("Name")]  
public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }  
[NonPersistent]  
// Sum up all the hours using Linq  
public int HourSum => OfferItems.Sum(m => m.Hours);  
[Association, Aggregated]  
public XPCollection<SlowOfferItemWithLinq> OfferItems => GetCollection<SlowOfferItemWithLinq>(nameof(OfferItems));  
}
public class SlowOfferItemWithLinq : BaseObject
{ public SlowOfferItemWithLinq(Session session) : base(session) { }  
private int _Hours;  
[Persistent("Hours")]  
public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }  
private SlowOfferWithLinq _SlowOfferWithLinq;  
[Persistent("SlowOfferWithLinq"), Association]  
public SlowOfferWithLinq SlowOfferWithLinq { get => _SlowOfferWithLinq; set => SetPropertyValue(nameof(SlowOfferWithLinq), ref _SlowOfferWithLinq, value); }  
}

0: 1.554 seconds

As you can see, it performs a lot better. And for the most parts of your application, this will be totally fine (esp. if you aren't dealing with too many records, or lists that aren't used that frequently). The memory footprint of this method is also a lot better.

PersistentAlias can perform better?

XPO has a feature called

19.09.20 10:35:31.422 Executing sql 'select N0."Oid",N0."Name",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferWithLinq" N0 where N0."GCRecord" is null'  
19.09.20 10:35:31.425 Result: rowcount = 300, total = 11914, N0.{Oid,Guid} = 4800, N0.{Name,String} = 4714, N0.{OptimisticLockField,Int32} = 1200, N0.{GCRecord,Int32} = 1200  
19.09.20 10:35:31.426 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOfferWithLinq",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."SlowOfferWithLinq" = @p0))' with parameters {a917fb15-82e1-471e-95b3-031a070d492d}  
19.09.20 10:35:31.440 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOfferWithLinq,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
19.09.20 10:35:31.451 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOfferWithLinq",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."SlowOfferWithLinq" = @p0))' with parameters {b625d838-3747-4ad4-aaac-032e79a7d3a6}  
19.09.20 10:35:31.464 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOfferWithLinq,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000.  
.  
.  
.  
19.09.20 10:35:36.787 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOfferWithLinq",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."SlowOfferWithLinq" = @p0))' with parameters {e2774f41-7d31-4a5f-afa4-ffe8d3a21d47}  
19.09.20 10:35:36.803 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOfferWithLinq,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
19.09.20 10:35:36.851 Executing sql 'select top 1 count(*) from "dbo"."SlowOfferWithLinq" N0 where N0."GCRecord" is null'  
19.09.20 10:35:36.853 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4  
19.09.20 10:35:36.854 Executing sql 'select top 1 count(*) from "dbo"."SlowOfferItemWithLinq" N0 where N0."GCRecord" is null'  
19.09.20 10:35:36.877 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4

5. This allows you to specify an aggregate criteria that can be applied directly to the database:

[DefaultClassOptions]  
[DefaultProperty(nameof(HourSum))]  
public class SlowOfferWithPersistentAlias : BaseObject  
{  
    public SlowOfferWithPersistentAlias(Session session) : base(session) { }
    private string _Name;  
    [Persistent("Name")]  
    public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }
    //Calculate the sum on the server side  
    [PersistentAlias("OfferItems.Sum([Hours])")]  
    //Tell XPO to calculate the alias when accessed client side or from code  
    public int HourSum => (int)EvaluateAlias(nameof(HourSum));
    [Association, Aggregated]  
    public XPCollection<SlowOfferItemWithPersistentAlias> OfferItems => GetCollection<SlowOfferItemWithPersistentAlias>(nameof(OfferItems));  
}
public class SlowOfferItemWithPersistentAlias : BaseObject  
{  
    public SlowOfferItemWithPersistentAlias(Session session) : base(session) { }
    private int _Hours;  
    [Persistent("Hours")]  
    public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }
    private SlowOfferWithPersistentAlias _SlowOfferWithPersistentAlias;  
    [Persistent("SlowOfferWithPersistentAlias"), Association]  
    public SlowOfferWithPersistentAlias SlowOfferWithPersistentAlias { get => _SlowOfferWithPersistentAlias; set => SetPropertyValue(nameof(SlowOfferWithPersistentAlias), ref _SlowOfferWithPersistentAlias, value); }  
}

But wait this will perform the same as the second 2 attemts?

19.09.20 11:20:27.738 Executing sql 'select N0."Oid",N0."Name",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferWithPersistentAlias" N0 where N0."GCRecord" is null'  
19.09.20 11:20:27.739 Result: rowcount = 300, total = 12048, N0.{Oid,Guid} = 4800, N0.{Name,String} = 4848, N0.{OptimisticLockField,Int32} = 1200, N0.{GCRecord,Int32} = 1200  
19.09.20 11:20:27.740 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOfferWithPersistentAlias",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItemWithPersistentAlias" N0 where (N0."GCRecord" is null and (N0."SlowOfferWithPersistentAlias" = @p0))' with parameters {11328106-f965-495d-89d0-00896fa631a1}  
19.09.20 11:20:27.755 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOfferWithPersistentAlias,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
.  
.  
.  
19.09.20 11:20:33.695 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOfferWithPersistentAlias",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItemWithPersistentAlias" N0 where (N0."GCRecord" is null and (N0."SlowOfferWithPersistentAlias" = @p0))' with parameters {926fde40-af9c-4679-bdf9-fe0edcc49465}  
19.09.20 11:20:33.718 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOfferWithPersistentAlias,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
19.09.20 11:20:33.789 Executing sql 'select top 1 count(*) from "dbo"."SlowOfferWithPersistentAlias" N0 where N0."GCRecord" is null'  
19.09.20 11:20:33.790 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4  
19.09.20 11:20:33.790 Executing sql 'select top 1 count(*) from "dbo"."SlowOfferItemWithPersistentAlias" N0 where N0."GCRecord" is null'  
19.09.20 11:20:33.805 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4

This results in the following performance

Sort by

 [DefaultClassOptions]
[DefaultProperty(nameof(HourSum))]
public class SlowOfferWithLinq : BaseObject
{ 
public SlowOfferWithLinq(Session session) : base(session) { }  
private string _Name;  
[Persistent("Name")]  
public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }  
[NonPersistent]  
// Sum up all the hours using Linq  
public int HourSum => OfferItems.Sum(m => m.Hours);  
[Association, Aggregated]  
public XPCollection<SlowOfferItemWithLinq> OfferItems => GetCollection<SlowOfferItemWithLinq>(nameof(OfferItems));  
}
public class SlowOfferItemWithLinq : BaseObject
{ public SlowOfferItemWithLinq(Session session) : base(session) { }  
private int _Hours;  
[Persistent("Hours")]  
public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }  
private SlowOfferWithLinq _SlowOfferWithLinq;  
[Persistent("SlowOfferWithLinq"), Association]  
public SlowOfferWithLinq SlowOfferWithLinq { get => _SlowOfferWithLinq; set => SetPropertyValue(nameof(SlowOfferWithLinq), ref _SlowOfferWithLinq, value); }  
}

4: 0.671 seconds

Sort by

 [DefaultClassOptions]
[DefaultProperty(nameof(HourSum))]
public class SlowOfferWithLinq : BaseObject
{ 
public SlowOfferWithLinq(Session session) : base(session) { }  
private string _Name;  
[Persistent("Name")]  
public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }  
[NonPersistent]  
// Sum up all the hours using Linq  
public int HourSum => OfferItems.Sum(m => m.Hours);  
[Association, Aggregated]  
public XPCollection<SlowOfferItemWithLinq> OfferItems => GetCollection<SlowOfferItemWithLinq>(nameof(OfferItems));  
}
public class SlowOfferItemWithLinq : BaseObject
{ public SlowOfferItemWithLinq(Session session) : base(session) { }  
private int _Hours;  
[Persistent("Hours")]  
public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }  
private SlowOfferWithLinq _SlowOfferWithLinq;  
[Persistent("SlowOfferWithLinq"), Association]  
public SlowOfferWithLinq SlowOfferWithLinq { get => _SlowOfferWithLinq; set => SetPropertyValue(nameof(SlowOfferWithLinq), ref _SlowOfferWithLinq, value); }  
}

0: 6.501 seconds

But why is that? XAF will load data according to the

19.09.20 10:35:31.422 Executing sql 'select N0."Oid",N0."Name",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferWithLinq" N0 where N0."GCRecord" is null'  
19.09.20 10:35:31.425 Result: rowcount = 300, total = 11914, N0.{Oid,Guid} = 4800, N0.{Name,String} = 4714, N0.{OptimisticLockField,Int32} = 1200, N0.{GCRecord,Int32} = 1200  
19.09.20 10:35:31.426 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOfferWithLinq",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."SlowOfferWithLinq" = @p0))' with parameters {a917fb15-82e1-471e-95b3-031a070d492d}  
19.09.20 10:35:31.440 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOfferWithLinq,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
19.09.20 10:35:31.451 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOfferWithLinq",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."SlowOfferWithLinq" = @p0))' with parameters {b625d838-3747-4ad4-aaac-032e79a7d3a6}  
19.09.20 10:35:31.464 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOfferWithLinq,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000.  
.  
.  
.  
19.09.20 10:35:36.787 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOfferWithLinq",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."SlowOfferWithLinq" = @p0))' with parameters {e2774f41-7d31-4a5f-afa4-ffe8d3a21d47}  
19.09.20 10:35:36.803 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOfferWithLinq,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
19.09.20 10:35:36.851 Executing sql 'select top 1 count(*) from "dbo"."SlowOfferWithLinq" N0 where N0."GCRecord" is null'  
19.09.20 10:35:36.853 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4  
19.09.20 10:35:36.854 Executing sql 'select top 1 count(*) from "dbo"."SlowOfferItemWithLinq" N0 where N0."GCRecord" is null'  
19.09.20 10:35:36.877 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4

8 property of the

18.09.20 16:22:13.906 Executing sql 'select N0."Oid",N0."Name",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOffer" N0 where N0."GCRecord" is null'  
18.09.20 16:22:13.908 Result: rowcount = 300, total = 11996, N0.{Oid,Guid} = 4800, N0.{Name,String} = 4796, N0.{OptimisticLockField,Int32} = 1200, N0.{GCRecord,Int32} = 1200  
18.09.20 16:22:13.913 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {a786412c-907d-4b36-beb6-004395d38e9d}  
18.09.20 16:22:13.927 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:13.931 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {cf656095-dcd6-46f1-b830-00af607693a9}  
18.09.20 16:22:13.949 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
.  
.  
.  
.  
18.09.20 16:22:20.036 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {0be207ec-9430-44e7-964c-fd684303d051}  
18.09.20 16:22:20.053 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:20.057 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {3b48cf0a-d610-4586-8dfb-fde802777653}  
18.09.20 16:22:20.078 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:20.122 Executing sql 'select top 1 count(*) from "dbo"."SlowOffer" N0 where N0."GCRecord" is null'  
18.09.20 16:22:20.126 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4  
18.09.20 16:22:20.127 Executing sql 'select top 1 count(*) from "dbo"."SlowOfferItem" N0 where N0."GCRecord" is null'  
18.09.20 16:22:20.170 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4

7. Until now we always used the

[NonPersistent]  
// Sum up all the hours using Linq  
public int HourSum => OfferItems.Sum(m => m.Hours);

0 mode. Which is fine for the most part. It's the most convenient to use cause you almost never need to think about the underlying database. That also means, it will do the calculation client side, which will result in the same queries as we know from the naive approach.

PersistentAlias performs better with the correct DataAccessMode

We have several options provided by XAF to change the behavior how data will be loaded. I will list them in the order they were introduced in the framework. Every mode has its own strengths and weaknesses. I'll only look into the performance right now. Programming model varies between those and i will skip

[NonPersistent]  
// Sum up all the hours using Linq  
public int HourSum => OfferItems.Sum(m => m.Hours);

0 cause we looked at it before. The source code is exactly the same before. To change the

19.09.20 10:35:31.422 Executing sql 'select N0."Oid",N0."Name",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferWithLinq" N0 where N0."GCRecord" is null'  
19.09.20 10:35:31.425 Result: rowcount = 300, total = 11914, N0.{Oid,Guid} = 4800, N0.{Name,String} = 4714, N0.{OptimisticLockField,Int32} = 1200, N0.{GCRecord,Int32} = 1200  
19.09.20 10:35:31.426 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOfferWithLinq",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."SlowOfferWithLinq" = @p0))' with parameters {a917fb15-82e1-471e-95b3-031a070d492d}  
19.09.20 10:35:31.440 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOfferWithLinq,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
19.09.20 10:35:31.451 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOfferWithLinq",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."SlowOfferWithLinq" = @p0))' with parameters {b625d838-3747-4ad4-aaac-032e79a7d3a6}  
19.09.20 10:35:31.464 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOfferWithLinq,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000.  
.  
.  
.  
19.09.20 10:35:36.787 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOfferWithLinq",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."SlowOfferWithLinq" = @p0))' with parameters {e2774f41-7d31-4a5f-afa4-ffe8d3a21d47}  
19.09.20 10:35:36.803 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOfferWithLinq,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
19.09.20 10:35:36.851 Executing sql 'select top 1 count(*) from "dbo"."SlowOfferWithLinq" N0 where N0."GCRecord" is null'  
19.09.20 10:35:36.853 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4  
19.09.20 10:35:36.854 Executing sql 'select top 1 count(*) from "dbo"."SlowOfferItemWithLinq" N0 where N0."GCRecord" is null'  
19.09.20 10:35:36.877 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4

8 we change it's property in the

[NonPersistent]  
// Sum up all the hours using Linq  
public int HourSum => OfferItems.Sum(m => m.Hours);

3.

ServerMode

In

[NonPersistent]  
// Sum up all the hours using Linq  
public int HourSum => OfferItems.Sum(m => m.Hours);

4 only a fixed amount of records are fetched from the database, as well as including the sorting query and some support queries. Afterwards objects are fetched on demand.

[DefaultClassOptions]  
[DefaultProperty(nameof(HourSum))]  
public class SlowOffer : BaseObject  
{  
    public SlowOffer(Session session) : base(session) { }
    private string _Name;  
    [Persistent("Name")]  
    public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }
    [NonPersistent]  
    public int HourSum  
    {  
        get  
        {  
            var sum = 0;  
            foreach (var item in OfferItems)  
            {  
                sum += item.Hours;  
            }  
            return sum;  
        }  
    }
    [Association, Aggregated]  
    public XPCollection<SlowOfferItem> OfferItems => GetCollection<SlowOfferItem>(nameof(OfferItems));  
}
public class SlowOfferItem : BaseObject  
{  
    public SlowOfferItem(Session session) : base(session) { }
    private int _Hours;  
    [Persistent("Hours")]  
    public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }
    private SlowOffer _SlowOffer;  
    [Persistent("SlowOffer"), Association]  
    public SlowOffer SlowOffer { get => _SlowOffer; set => SetPropertyValue(nameof(SlowOffer), ref _SlowOffer, value); }  
}

0

This results in the following performance

Sort by

 [DefaultClassOptions]
[DefaultProperty(nameof(HourSum))]
public class SlowOfferWithLinq : BaseObject
{ 
public SlowOfferWithLinq(Session session) : base(session) { }  
private string _Name;  
[Persistent("Name")]  
public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }  
[NonPersistent]  
// Sum up all the hours using Linq  
public int HourSum => OfferItems.Sum(m => m.Hours);  
[Association, Aggregated]  
public XPCollection<SlowOfferItemWithLinq> OfferItems => GetCollection<SlowOfferItemWithLinq>(nameof(OfferItems));  
}
public class SlowOfferItemWithLinq : BaseObject
{ public SlowOfferItemWithLinq(Session session) : base(session) { }  
private int _Hours;  
[Persistent("Hours")]  
public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }  
private SlowOfferWithLinq _SlowOfferWithLinq;  
[Persistent("SlowOfferWithLinq"), Association]  
public SlowOfferWithLinq SlowOfferWithLinq { get => _SlowOfferWithLinq; set => SetPropertyValue(nameof(SlowOfferWithLinq), ref _SlowOfferWithLinq, value); }  
}

4: 0.692 seconds

Sort by

 [DefaultClassOptions]
[DefaultProperty(nameof(HourSum))]
public class SlowOfferWithLinq : BaseObject
{ 
public SlowOfferWithLinq(Session session) : base(session) { }  
private string _Name;  
[Persistent("Name")]  
public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }  
[NonPersistent]  
// Sum up all the hours using Linq  
public int HourSum => OfferItems.Sum(m => m.Hours);  
[Association, Aggregated]  
public XPCollection<SlowOfferItemWithLinq> OfferItems => GetCollection<SlowOfferItemWithLinq>(nameof(OfferItems));  
}
public class SlowOfferItemWithLinq : BaseObject
{ public SlowOfferItemWithLinq(Session session) : base(session) { }  
private int _Hours;  
[Persistent("Hours")]  
public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }  
private SlowOfferWithLinq _SlowOfferWithLinq;  
[Persistent("SlowOfferWithLinq"), Association]  
public SlowOfferWithLinq SlowOfferWithLinq { get => _SlowOfferWithLinq; set => SetPropertyValue(nameof(SlowOfferWithLinq), ref _SlowOfferWithLinq, value); }  
}

0: 0.741 seconds

DataView

In

[NonPersistent]  
// Sum up all the hours using Linq  
public int HourSum => OfferItems.Sum(m => m.Hours);

7 mode only visible columns are fetched from the database, as well as including the sorting query and some support queries. All records will be fetched in one query

[DefaultClassOptions]  
[DefaultProperty(nameof(HourSum))]  
public class SlowOffer : BaseObject  
{  
    public SlowOffer(Session session) : base(session) { }
    private string _Name;  
    [Persistent("Name")]  
    public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }
    [NonPersistent]  
    public int HourSum  
    {  
        get  
        {  
            var sum = 0;  
            foreach (var item in OfferItems)  
            {  
                sum += item.Hours;  
            }  
            return sum;  
        }  
    }
    [Association, Aggregated]  
    public XPCollection<SlowOfferItem> OfferItems => GetCollection<SlowOfferItem>(nameof(OfferItems));  
}
public class SlowOfferItem : BaseObject  
{  
    public SlowOfferItem(Session session) : base(session) { }
    private int _Hours;  
    [Persistent("Hours")]  
    public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }
    private SlowOffer _SlowOffer;  
    [Persistent("SlowOffer"), Association]  
    public SlowOffer SlowOffer { get => _SlowOffer; set => SetPropertyValue(nameof(SlowOffer), ref _SlowOffer, value); }  
}

1

This results in the following performance

Sort by

 [DefaultClassOptions]
[DefaultProperty(nameof(HourSum))]
public class SlowOfferWithLinq : BaseObject
{ 
public SlowOfferWithLinq(Session session) : base(session) { }  
private string _Name;  
[Persistent("Name")]  
public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }  
[NonPersistent]  
// Sum up all the hours using Linq  
public int HourSum => OfferItems.Sum(m => m.Hours);  
[Association, Aggregated]  
public XPCollection<SlowOfferItemWithLinq> OfferItems => GetCollection<SlowOfferItemWithLinq>(nameof(OfferItems));  
}
public class SlowOfferItemWithLinq : BaseObject
{ public SlowOfferItemWithLinq(Session session) : base(session) { }  
private int _Hours;  
[Persistent("Hours")]  
public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }  
private SlowOfferWithLinq _SlowOfferWithLinq;  
[Persistent("SlowOfferWithLinq"), Association]  
public SlowOfferWithLinq SlowOfferWithLinq { get => _SlowOfferWithLinq; set => SetPropertyValue(nameof(SlowOfferWithLinq), ref _SlowOfferWithLinq, value); }  
}

4: 0.071 seconds

Sort by

 [DefaultClassOptions]
[DefaultProperty(nameof(HourSum))]
public class SlowOfferWithLinq : BaseObject
{ 
public SlowOfferWithLinq(Session session) : base(session) { }  
private string _Name;  
[Persistent("Name")]  
public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }  
[NonPersistent]  
// Sum up all the hours using Linq  
public int HourSum => OfferItems.Sum(m => m.Hours);  
[Association, Aggregated]  
public XPCollection<SlowOfferItemWithLinq> OfferItems => GetCollection<SlowOfferItemWithLinq>(nameof(OfferItems));  
}
public class SlowOfferItemWithLinq : BaseObject
{ public SlowOfferItemWithLinq(Session session) : base(session) { }  
private int _Hours;  
[Persistent("Hours")]  
public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }  
private SlowOfferWithLinq _SlowOfferWithLinq;  
[Persistent("SlowOfferWithLinq"), Association]  
public SlowOfferWithLinq SlowOfferWithLinq { get => _SlowOfferWithLinq; set => SetPropertyValue(nameof(SlowOfferWithLinq), ref _SlowOfferWithLinq, value); }  
}

0: 0.078 seconds

InstantFeedback

In

[DefaultClassOptions]  
[DefaultProperty(nameof(HourSum))]  
public class FasterOfferWithLinq : BaseObject  
{  
    public FasterOfferWithLinq(Session session) : base(session) { }
    private string _Name;  
    [Persistent("Name")]  
    public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }
    [NonPersistent]  
    public int HourSum =>  
        //Query the data store  
        Session.Query<FasterOfferItemWithLinq>()  
        //Filter it down to the current record  
        .Where(i => i.FasterOfferWithLinq.Oid == Oid)  
        //Sum the total hours  
        .Sum(m => m.Hours);
    [Association, Aggregated]  
    public XPCollection<FasterOfferItemWithLinq> OfferItems => GetCollection<FasterOfferItemWithLinq>(nameof(OfferItems));  
}
public class FasterOfferItemWithLinq : BaseObject  
{  
    public FasterOfferItemWithLinq(Session session) : base(session) { }
    private int _Hours;  
    [Persistent("Hours")]  
    public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }
    private FasterOfferWithLinq _FasterOfferWithLinq;  
    [Persistent("FasterOfferWithLinq"), Association]  
    public FasterOfferWithLinq FasterOfferWithLinq { get => _FasterOfferWithLinq; set => SetPropertyValue(nameof(FasterOfferWithLinq), ref _FasterOfferWithLinq, value); }  
}

0 mode records are loaded in an async manor. It operates almost the same as the

[NonPersistent]  
// Sum up all the hours using Linq  
public int HourSum => OfferItems.Sum(m => m.Hours);

4 in terms of number of queries, but the user experience is much better, cause visible records will be fetched in the background.

[DefaultClassOptions]  
[DefaultProperty(nameof(HourSum))]  
public class SlowOffer : BaseObject  
{  
    public SlowOffer(Session session) : base(session) { }
    private string _Name;  
    [Persistent("Name")]  
    public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }
    [NonPersistent]  
    public int HourSum  
    {  
        get  
        {  
            var sum = 0;  
            foreach (var item in OfferItems)  
            {  
                sum += item.Hours;  
            }  
            return sum;  
        }  
    }
    [Association, Aggregated]  
    public XPCollection<SlowOfferItem> OfferItems => GetCollection<SlowOfferItem>(nameof(OfferItems));  
}
public class SlowOfferItem : BaseObject  
{  
    public SlowOfferItem(Session session) : base(session) { }
    private int _Hours;  
    [Persistent("Hours")]  
    public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }
    private SlowOffer _SlowOffer;  
    [Persistent("SlowOffer"), Association]  
    public SlowOffer SlowOffer { get => _SlowOffer; set => SetPropertyValue(nameof(SlowOffer), ref _SlowOffer, value); }  
}

2

This results in the following performance

First user interaction:

Sort by

 [DefaultClassOptions]
[DefaultProperty(nameof(HourSum))]
public class SlowOfferWithLinq : BaseObject
{ 
public SlowOfferWithLinq(Session session) : base(session) { }  
private string _Name;  
[Persistent("Name")]  
public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }  
[NonPersistent]  
// Sum up all the hours using Linq  
public int HourSum => OfferItems.Sum(m => m.Hours);  
[Association, Aggregated]  
public XPCollection<SlowOfferItemWithLinq> OfferItems => GetCollection<SlowOfferItemWithLinq>(nameof(OfferItems));  
}
public class SlowOfferItemWithLinq : BaseObject
{ public SlowOfferItemWithLinq(Session session) : base(session) { }  
private int _Hours;  
[Persistent("Hours")]  
public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }  
private SlowOfferWithLinq _SlowOfferWithLinq;  
[Persistent("SlowOfferWithLinq"), Association]  
public SlowOfferWithLinq SlowOfferWithLinq { get => _SlowOfferWithLinq; set => SetPropertyValue(nameof(SlowOfferWithLinq), ref _SlowOfferWithLinq, value); }  
}

4: 0.040 seconds

Sort by

 [DefaultClassOptions]
[DefaultProperty(nameof(HourSum))]
public class SlowOfferWithLinq : BaseObject
{ 
public SlowOfferWithLinq(Session session) : base(session) { }  
private string _Name;  
[Persistent("Name")]  
public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }  
[NonPersistent]  
// Sum up all the hours using Linq  
public int HourSum => OfferItems.Sum(m => m.Hours);  
[Association, Aggregated]  
public XPCollection<SlowOfferItemWithLinq> OfferItems => GetCollection<SlowOfferItemWithLinq>(nameof(OfferItems));  
}
public class SlowOfferItemWithLinq : BaseObject
{ public SlowOfferItemWithLinq(Session session) : base(session) { }  
private int _Hours;  
[Persistent("Hours")]  
public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }  
private SlowOfferWithLinq _SlowOfferWithLinq;  
[Persistent("SlowOfferWithLinq"), Association]  
public SlowOfferWithLinq SlowOfferWithLinq { get => _SlowOfferWithLinq; set => SetPropertyValue(nameof(SlowOfferWithLinq), ref _SlowOfferWithLinq, value); }  
}

0: 0.030 seconds

Visible data loaded:

Sort by

 [DefaultClassOptions]
[DefaultProperty(nameof(HourSum))]
public class SlowOfferWithLinq : BaseObject
{ 
public SlowOfferWithLinq(Session session) : base(session) { }  
private string _Name;  
[Persistent("Name")]  
public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }  
[NonPersistent]  
// Sum up all the hours using Linq  
public int HourSum => OfferItems.Sum(m => m.Hours);  
[Association, Aggregated]  
public XPCollection<SlowOfferItemWithLinq> OfferItems => GetCollection<SlowOfferItemWithLinq>(nameof(OfferItems));  
}
public class SlowOfferItemWithLinq : BaseObject
{ public SlowOfferItemWithLinq(Session session) : base(session) { }  
private int _Hours;  
[Persistent("Hours")]  
public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }  
private SlowOfferWithLinq _SlowOfferWithLinq;  
[Persistent("SlowOfferWithLinq"), Association]  
public SlowOfferWithLinq SlowOfferWithLinq { get => _SlowOfferWithLinq; set => SetPropertyValue(nameof(SlowOfferWithLinq), ref _SlowOfferWithLinq, value); }  
}

4: ~~0.800 seconds

Sort by

 [DefaultClassOptions]
[DefaultProperty(nameof(HourSum))]
public class SlowOfferWithLinq : BaseObject
{ 
public SlowOfferWithLinq(Session session) : base(session) { }  
private string _Name;  
[Persistent("Name")]  
public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }  
[NonPersistent]  
// Sum up all the hours using Linq  
public int HourSum => OfferItems.Sum(m => m.Hours);  
[Association, Aggregated]  
public XPCollection<SlowOfferItemWithLinq> OfferItems => GetCollection<SlowOfferItemWithLinq>(nameof(OfferItems));  
}
public class SlowOfferItemWithLinq : BaseObject
{ public SlowOfferItemWithLinq(Session session) : base(session) { }  
private int _Hours;  
[Persistent("Hours")]  
public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }  
private SlowOfferWithLinq _SlowOfferWithLinq;  
[Persistent("SlowOfferWithLinq"), Association]  
public SlowOfferWithLinq SlowOfferWithLinq { get => _SlowOfferWithLinq; set => SetPropertyValue(nameof(SlowOfferWithLinq), ref _SlowOfferWithLinq, value); }  
}

0: ~~0.800 seconds

InstantFeedbackView

In

[DefaultClassOptions]  
[DefaultProperty(nameof(HourSum))]  
public class FasterOfferWithLinq : BaseObject  
{  
    public FasterOfferWithLinq(Session session) : base(session) { }
    private string _Name;  
    [Persistent("Name")]  
    public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }
    [NonPersistent]  
    public int HourSum =>  
        //Query the data store  
        Session.Query<FasterOfferItemWithLinq>()  
        //Filter it down to the current record  
        .Where(i => i.FasterOfferWithLinq.Oid == Oid)  
        //Sum the total hours  
        .Sum(m => m.Hours);
    [Association, Aggregated]  
    public XPCollection<FasterOfferItemWithLinq> OfferItems => GetCollection<FasterOfferItemWithLinq>(nameof(OfferItems));  
}
public class FasterOfferItemWithLinq : BaseObject  
{  
    public FasterOfferItemWithLinq(Session session) : base(session) { }
    private int _Hours;  
    [Persistent("Hours")]  
    public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }
    private FasterOfferWithLinq _FasterOfferWithLinq;  
    [Persistent("FasterOfferWithLinq"), Association]  
    public FasterOfferWithLinq FasterOfferWithLinq { get => _FasterOfferWithLinq; set => SetPropertyValue(nameof(FasterOfferWithLinq), ref _FasterOfferWithLinq, value); }  
}

6 mode records are loaded in an async manor. It operates almost the same as the

[NonPersistent]  
// Sum up all the hours using Linq  
public int HourSum => OfferItems.Sum(m => m.Hours);

7 mode in terms of number of queries, but the user experience can be much better, cause all records will be fetched in the background.

[DefaultClassOptions]  
[DefaultProperty(nameof(HourSum))]  
public class SlowOffer : BaseObject  
{  
    public SlowOffer(Session session) : base(session) { }
    private string _Name;  
    [Persistent("Name")]  
    public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }
    [NonPersistent]  
    public int HourSum  
    {  
        get  
        {  
            var sum = 0;  
            foreach (var item in OfferItems)  
            {  
                sum += item.Hours;  
            }  
            return sum;  
        }  
    }
    [Association, Aggregated]  
    public XPCollection<SlowOfferItem> OfferItems => GetCollection<SlowOfferItem>(nameof(OfferItems));  
}
public class SlowOfferItem : BaseObject  
{  
    public SlowOfferItem(Session session) : base(session) { }
    private int _Hours;  
    [Persistent("Hours")]  
    public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }
    private SlowOffer _SlowOffer;  
    [Persistent("SlowOffer"), Association]  
    public SlowOffer SlowOffer { get => _SlowOffer; set => SetPropertyValue(nameof(SlowOffer), ref _SlowOffer, value); }  
}

3

This results in the following performance

First user interaction:

Sort by

 [DefaultClassOptions]
[DefaultProperty(nameof(HourSum))]
public class SlowOfferWithLinq : BaseObject
{ 
public SlowOfferWithLinq(Session session) : base(session) { }  
private string _Name;  
[Persistent("Name")]  
public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }  
[NonPersistent]  
// Sum up all the hours using Linq  
public int HourSum => OfferItems.Sum(m => m.Hours);  
[Association, Aggregated]  
public XPCollection<SlowOfferItemWithLinq> OfferItems => GetCollection<SlowOfferItemWithLinq>(nameof(OfferItems));  
}
public class SlowOfferItemWithLinq : BaseObject
{ public SlowOfferItemWithLinq(Session session) : base(session) { }  
private int _Hours;  
[Persistent("Hours")]  
public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }  
private SlowOfferWithLinq _SlowOfferWithLinq;  
[Persistent("SlowOfferWithLinq"), Association]  
public SlowOfferWithLinq SlowOfferWithLinq { get => _SlowOfferWithLinq; set => SetPropertyValue(nameof(SlowOfferWithLinq), ref _SlowOfferWithLinq, value); }  
}

4: 0.006 seconds

Sort by

 [DefaultClassOptions]
[DefaultProperty(nameof(HourSum))]
public class SlowOfferWithLinq : BaseObject
{ 
public SlowOfferWithLinq(Session session) : base(session) { }  
private string _Name;  
[Persistent("Name")]  
public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }  
[NonPersistent]  
// Sum up all the hours using Linq  
public int HourSum => OfferItems.Sum(m => m.Hours);  
[Association, Aggregated]  
public XPCollection<SlowOfferItemWithLinq> OfferItems => GetCollection<SlowOfferItemWithLinq>(nameof(OfferItems));  
}
public class SlowOfferItemWithLinq : BaseObject
{ public SlowOfferItemWithLinq(Session session) : base(session) { }  
private int _Hours;  
[Persistent("Hours")]  
public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }  
private SlowOfferWithLinq _SlowOfferWithLinq;  
[Persistent("SlowOfferWithLinq"), Association]  
public SlowOfferWithLinq SlowOfferWithLinq { get => _SlowOfferWithLinq; set => SetPropertyValue(nameof(SlowOfferWithLinq), ref _SlowOfferWithLinq, value); }  
}

0: 0.007 seconds

Visible data loaded:

Sort by

 [DefaultClassOptions]
[DefaultProperty(nameof(HourSum))]
public class SlowOfferWithLinq : BaseObject
{ 
public SlowOfferWithLinq(Session session) : base(session) { }  
private string _Name;  
[Persistent("Name")]  
public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }  
[NonPersistent]  
// Sum up all the hours using Linq  
public int HourSum => OfferItems.Sum(m => m.Hours);  
[Association, Aggregated]  
public XPCollection<SlowOfferItemWithLinq> OfferItems => GetCollection<SlowOfferItemWithLinq>(nameof(OfferItems));  
}
public class SlowOfferItemWithLinq : BaseObject
{ public SlowOfferItemWithLinq(Session session) : base(session) { }  
private int _Hours;  
[Persistent("Hours")]  
public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }  
private SlowOfferWithLinq _SlowOfferWithLinq;  
[Persistent("SlowOfferWithLinq"), Association]  
public SlowOfferWithLinq SlowOfferWithLinq { get => _SlowOfferWithLinq; set => SetPropertyValue(nameof(SlowOfferWithLinq), ref _SlowOfferWithLinq, value); }  
}

4: ~~0.0300 seconds

Sort by

 [DefaultClassOptions]
[DefaultProperty(nameof(HourSum))]
public class SlowOfferWithLinq : BaseObject
{ 
public SlowOfferWithLinq(Session session) : base(session) { }  
private string _Name;  
[Persistent("Name")]  
public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }  
[NonPersistent]  
// Sum up all the hours using Linq  
public int HourSum => OfferItems.Sum(m => m.Hours);  
[Association, Aggregated]  
public XPCollection<SlowOfferItemWithLinq> OfferItems => GetCollection<SlowOfferItemWithLinq>(nameof(OfferItems));  
}
public class SlowOfferItemWithLinq : BaseObject
{ public SlowOfferItemWithLinq(Session session) : base(session) { }  
private int _Hours;  
[Persistent("Hours")]  
public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }  
private SlowOfferWithLinq _SlowOfferWithLinq;  
[Persistent("SlowOfferWithLinq"), Association]  
public SlowOfferWithLinq SlowOfferWithLinq { get => _SlowOfferWithLinq; set => SetPropertyValue(nameof(SlowOfferWithLinq), ref _SlowOfferWithLinq, value); }  
}

0: ~~0.400 seconds

ServerView

In

19.09.20 10:58:05.069 Executing sql 'select N0."Oid",N0."Name",N0."OptimisticLockField",N0."GCRecord" from "dbo"."FasterOfferWithLinq" N0 where N0."GCRecord" is null'  
19.09.20 10:58:05.070 Result: rowcount = 300, total = 11992, N0.{Oid,Guid} = 4800, N0.{Name,String} = 4792, N0.{OptimisticLockField,Int32} = 1200, N0.{GCRecord,Int32} = 1200  
19.09.20 10:58:05.071 Executing sql 'select top 1 sum(N0."Hours") from "dbo"."FasterOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."FasterOfferWithLinq" = @p0))' with parameters {a3797700-1fcf-429f-9c34-005322afa892}  
19.09.20 10:58:05.075 Result: rowcount = 1, total = 4, SubQuery(Sum,N0.{Hours,Int32},) = 4  
19.09.20 10:58:05.076 Executing sql 'select top 1 sum(N0."Hours") from "dbo"."FasterOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."FasterOfferWithLinq" = @p0))' with parameters {3683bfc2-7314-452f-be51-00692f123965}  
.  
.  
.  
19.09.20 10:58:06.497 Executing sql 'select top 1 count(*) from "dbo"."FasterOfferWithLinq" N0 where N0."GCRecord" is null'  
19.09.20 10:58:06.497 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4  
19.09.20 10:58:06.498 Executing sql 'select top 1 count(*) from "dbo"."FasterOfferItemWithLinq" N0 where N0."GCRecord" is null'  
19.09.20 10:58:06.512 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4  
19.09.20 10:58:07.919 Executing sql 'select top 1 sum(N0."Hours") from "dbo"."FasterOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."FasterOfferWithLinq" = @p0))' with parameters {820161dc-8568-412c-bf84-c4f148e8122f}  
19.09.20 10:58:07.924 Result: rowcount = 1, total = 4, SubQuery(Sum,N0.{Hours,Int32},) = 4

2 nide only a fixed amount of records are fetched from the database, as well as including the sorting query and some support queries. Afterwards visible columns are fetched on demand similar to the

[NonPersistent]  
// Sum up all the hours using Linq  
public int HourSum => OfferItems.Sum(m => m.Hours);

7 but with pagination and visible columns support.

[DefaultClassOptions]  
[DefaultProperty(nameof(HourSum))]  
public class SlowOffer : BaseObject  
{  
    public SlowOffer(Session session) : base(session) { }
    private string _Name;  
    [Persistent("Name")]  
    public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }
    [NonPersistent]  
    public int HourSum  
    {  
        get  
        {  
            var sum = 0;  
            foreach (var item in OfferItems)  
            {  
                sum += item.Hours;  
            }  
            return sum;  
        }  
    }
    [Association, Aggregated]  
    public XPCollection<SlowOfferItem> OfferItems => GetCollection<SlowOfferItem>(nameof(OfferItems));  
}
public class SlowOfferItem : BaseObject  
{  
    public SlowOfferItem(Session session) : base(session) { }
    private int _Hours;  
    [Persistent("Hours")]  
    public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }
    private SlowOffer _SlowOffer;  
    [Persistent("SlowOffer"), Association]  
    public SlowOffer SlowOffer { get => _SlowOffer; set => SetPropertyValue(nameof(SlowOffer), ref _SlowOffer, value); }  
}

4

This results in the following performance

Sort by

 [DefaultClassOptions]
[DefaultProperty(nameof(HourSum))]
public class SlowOfferWithLinq : BaseObject
{ 
public SlowOfferWithLinq(Session session) : base(session) { }  
private string _Name;  
[Persistent("Name")]  
public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }  
[NonPersistent]  
// Sum up all the hours using Linq  
public int HourSum => OfferItems.Sum(m => m.Hours);  
[Association, Aggregated]  
public XPCollection<SlowOfferItemWithLinq> OfferItems => GetCollection<SlowOfferItemWithLinq>(nameof(OfferItems));  
}
public class SlowOfferItemWithLinq : BaseObject
{ public SlowOfferItemWithLinq(Session session) : base(session) { }  
private int _Hours;  
[Persistent("Hours")]  
public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }  
private SlowOfferWithLinq _SlowOfferWithLinq;  
[Persistent("SlowOfferWithLinq"), Association]  
public SlowOfferWithLinq SlowOfferWithLinq { get => _SlowOfferWithLinq; set => SetPropertyValue(nameof(SlowOfferWithLinq), ref _SlowOfferWithLinq, value); }  
}

4: 0.079 seconds

Sort by

 [DefaultClassOptions]
[DefaultProperty(nameof(HourSum))]
public class SlowOfferWithLinq : BaseObject
{ 
public SlowOfferWithLinq(Session session) : base(session) { }  
private string _Name;  
[Persistent("Name")]  
public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }  
[NonPersistent]  
// Sum up all the hours using Linq  
public int HourSum => OfferItems.Sum(m => m.Hours);  
[Association, Aggregated]  
public XPCollection<SlowOfferItemWithLinq> OfferItems => GetCollection<SlowOfferItemWithLinq>(nameof(OfferItems));  
}
public class SlowOfferItemWithLinq : BaseObject
{ public SlowOfferItemWithLinq(Session session) : base(session) { }  
private int _Hours;  
[Persistent("Hours")]  
public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }  
private SlowOfferWithLinq _SlowOfferWithLinq;  
[Persistent("SlowOfferWithLinq"), Association]  
public SlowOfferWithLinq SlowOfferWithLinq { get => _SlowOfferWithLinq; set => SetPropertyValue(nameof(SlowOfferWithLinq), ref _SlowOfferWithLinq, value); }  
}

0: 0.236 seconds

Important: When using


19.09.20 10:35:31.422 Executing sql 'select N0."Oid",N0."Name",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferWithLinq" N0 where N0."GCRecord" is null' >
19.09.20 10:35:31.425 Result: rowcount = 300, total = 11914, N0.{Oid,Guid} = 4800, N0.{Name,String} = 4714, N0.{OptimisticLockField,Int32} = 1200, N0.{GCRecord,Int32} = 1200 >
19.09.20 10:35:31.426 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOfferWithLinq",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."SlowOfferWithLinq" = @p0))' with parameters {a917fb15-82e1-471e-95b3-031a070d492d} >
19.09.20 10:35:31.440 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOfferWithLinq,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000 >
19.09.20 10:35:31.451 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOfferWithLinq",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."SlowOfferWithLinq" = @p0))' with parameters {b625d838-3747-4ad4-aaac-032e79a7d3a6} >
19.09.20 10:35:31.464 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOfferWithLinq,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000. >
. >
. >
. >
19.09.20 10:35:36.787 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOfferWithLinq",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."SlowOfferWithLinq" = @p0))' with parameters {e2774f41-7d31-4a5f-afa4-ffe8d3a21d47} >
19.09.20 10:35:36.803 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOfferWithLinq,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000 >
19.09.20 10:35:36.851 Executing sql 'select top 1 count() from "dbo"."SlowOfferWithLinq" N0 where N0."GCRecord" is null' >
19.09.20 10:35:36.853 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4 >
19.09.20 10:35:36.854 Executing sql 'select top 1 count() from "dbo"."SlowOfferItemWithLinq" N0 where N0."GCRecord" is null' > 19.09.20 10:35:36.877 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4 >

5 for aggregate calculations always consider using a


19.09.20 10:35:31.422 Executing sql 'select N0."Oid",N0."Name",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferWithLinq" N0 where N0."GCRecord" is null' >
19.09.20 10:35:31.425 Result: rowcount = 300, total = 11914, N0.{Oid,Guid} = 4800, N0.{Name,String} = 4714, N0.{OptimisticLockField,Int32} = 1200, N0.{GCRecord,Int32} = 1200 >
19.09.20 10:35:31.426 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOfferWithLinq",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."SlowOfferWithLinq" = @p0))' with parameters {a917fb15-82e1-471e-95b3-031a070d492d} >
19.09.20 10:35:31.440 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOfferWithLinq,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000 >
19.09.20 10:35:31.451 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOfferWithLinq",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."SlowOfferWithLinq" = @p0))' with parameters {b625d838-3747-4ad4-aaac-032e79a7d3a6} >
19.09.20 10:35:31.464 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOfferWithLinq,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000. >
. >
. >
. >
19.09.20 10:35:36.787 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOfferWithLinq",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."SlowOfferWithLinq" = @p0))' with parameters {e2774f41-7d31-4a5f-afa4-ffe8d3a21d47} >
19.09.20 10:35:36.803 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOfferWithLinq,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000 >
19.09.20 10:35:36.851 Executing sql 'select top 1 count() from "dbo"."SlowOfferWithLinq" N0 where N0."GCRecord" is null' >
19.09.20 10:35:36.853 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4 >
19.09.20 10:35:36.854 Executing sql 'select top 1 count() from "dbo"."SlowOfferItemWithLinq" N0 where N0."GCRecord" is null' > 19.09.20 10:35:36.877 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4 >

8 that can perform database side calculation. There is no silver bullet and you need to measure carefully using different configurations to find what is the best fit.

All comes with a cost. All data access modes have . If you need grouping and sorting capabilities by some fields (or even just display

19.09.20 10:58:05.069 Executing sql 'select N0."Oid",N0."Name",N0."OptimisticLockField",N0."GCRecord" from "dbo"."FasterOfferWithLinq" N0 where N0."GCRecord" is null'  
19.09.20 10:58:05.070 Result: rowcount = 300, total = 11992, N0.{Oid,Guid} = 4800, N0.{Name,String} = 4792, N0.{OptimisticLockField,Int32} = 1200, N0.{GCRecord,Int32} = 1200  
19.09.20 10:58:05.071 Executing sql 'select top 1 sum(N0."Hours") from "dbo"."FasterOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."FasterOfferWithLinq" = @p0))' with parameters {a3797700-1fcf-429f-9c34-005322afa892}  
19.09.20 10:58:05.075 Result: rowcount = 1, total = 4, SubQuery(Sum,N0.{Hours,Int32},) = 4  
19.09.20 10:58:05.076 Executing sql 'select top 1 sum(N0."Hours") from "dbo"."FasterOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."FasterOfferWithLinq" = @p0))' with parameters {3683bfc2-7314-452f-be51-00692f123965}  
.  
.  
.  
19.09.20 10:58:06.497 Executing sql 'select top 1 count(*) from "dbo"."FasterOfferWithLinq" N0 where N0."GCRecord" is null'  
19.09.20 10:58:06.497 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4  
19.09.20 10:58:06.498 Executing sql 'select top 1 count(*) from "dbo"."FasterOfferItemWithLinq" N0 where N0."GCRecord" is null'  
19.09.20 10:58:06.512 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4  
19.09.20 10:58:07.919 Executing sql 'select top 1 sum(N0."Hours") from "dbo"."FasterOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."FasterOfferWithLinq" = @p0))' with parameters {820161dc-8568-412c-bf84-c4f148e8122f}  
19.09.20 10:58:07.924 Result: rowcount = 1, total = 4, SubQuery(Sum,N0.{Hours,Int32},) = 4

8 members) you are out of luck. It is also may be limited to the complexity you can fit into a single criteria.

Techniques that overcome the N+1 problems

Now we learned about the N+1 problem and some useful ways to overcome some of the performance problems that XAF provides we will cover 2 supported and 1 unsupported techniques how to avoid the N+1 problem completely. One that is database agnostic and one is not.

CQRS pattern - XPO stored aggregates

CQRS stands for

19.09.20 10:58:05.069 Executing sql 'select N0."Oid",N0."Name",N0."OptimisticLockField",N0."GCRecord" from "dbo"."FasterOfferWithLinq" N0 where N0."GCRecord" is null'  
19.09.20 10:58:05.070 Result: rowcount = 300, total = 11992, N0.{Oid,Guid} = 4800, N0.{Name,String} = 4792, N0.{OptimisticLockField,Int32} = 1200, N0.{GCRecord,Int32} = 1200  
19.09.20 10:58:05.071 Executing sql 'select top 1 sum(N0."Hours") from "dbo"."FasterOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."FasterOfferWithLinq" = @p0))' with parameters {a3797700-1fcf-429f-9c34-005322afa892}  
19.09.20 10:58:05.075 Result: rowcount = 1, total = 4, SubQuery(Sum,N0.{Hours,Int32},) = 4  
19.09.20 10:58:05.076 Executing sql 'select top 1 sum(N0."Hours") from "dbo"."FasterOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."FasterOfferWithLinq" = @p0))' with parameters {3683bfc2-7314-452f-be51-00692f123965}  
.  
.  
.  
19.09.20 10:58:06.497 Executing sql 'select top 1 count(*) from "dbo"."FasterOfferWithLinq" N0 where N0."GCRecord" is null'  
19.09.20 10:58:06.497 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4  
19.09.20 10:58:06.498 Executing sql 'select top 1 count(*) from "dbo"."FasterOfferItemWithLinq" N0 where N0."GCRecord" is null'  
19.09.20 10:58:06.512 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4  
19.09.20 10:58:07.919 Executing sql 'select top 1 sum(N0."Hours") from "dbo"."FasterOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."FasterOfferWithLinq" = @p0))' with parameters {820161dc-8568-412c-bf84-c4f148e8122f}  
19.09.20 10:58:07.924 Result: rowcount = 1, total = 4, SubQuery(Sum,N0.{Hours,Int32},) = 4

9. It's a pattern to split the

[DefaultClassOptions]  
[DefaultProperty(nameof(HourSum))]  
public class SlowOfferWithPersistentAlias : BaseObject  
{  
    public SlowOfferWithPersistentAlias(Session session) : base(session) { }
    private string _Name;  
    [Persistent("Name")]  
    public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }
    //Calculate the sum on the server side  
    [PersistentAlias("OfferItems.Sum([Hours])")]  
    //Tell XPO to calculate the alias when accessed client side or from code  
    public int HourSum => (int)EvaluateAlias(nameof(HourSum));
    [Association, Aggregated]  
    public XPCollection<SlowOfferItemWithPersistentAlias> OfferItems => GetCollection<SlowOfferItemWithPersistentAlias>(nameof(OfferItems));  
}
public class SlowOfferItemWithPersistentAlias : BaseObject  
{  
    public SlowOfferItemWithPersistentAlias(Session session) : base(session) { }
    private int _Hours;  
    [Persistent("Hours")]  
    public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }
    private SlowOfferWithPersistentAlias _SlowOfferWithPersistentAlias;  
    [Persistent("SlowOfferWithPersistentAlias"), Association]  
    public SlowOfferWithPersistentAlias SlowOfferWithPersistentAlias { get => _SlowOfferWithPersistentAlias; set => SetPropertyValue(nameof(SlowOfferWithPersistentAlias), ref _SlowOfferWithPersistentAlias, value); }  
}

0 part (in our case the calculation of the total sums) from the

[DefaultClassOptions]  
[DefaultProperty(nameof(HourSum))]  
public class SlowOfferWithPersistentAlias : BaseObject  
{  
    public SlowOfferWithPersistentAlias(Session session) : base(session) { }
    private string _Name;  
    [Persistent("Name")]  
    public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }
    //Calculate the sum on the server side  
    [PersistentAlias("OfferItems.Sum([Hours])")]  
    //Tell XPO to calculate the alias when accessed client side or from code  
    public int HourSum => (int)EvaluateAlias(nameof(HourSum));
    [Association, Aggregated]  
    public XPCollection<SlowOfferItemWithPersistentAlias> OfferItems => GetCollection<SlowOfferItemWithPersistentAlias>(nameof(OfferItems));  
}
public class SlowOfferItemWithPersistentAlias : BaseObject  
{  
    public SlowOfferItemWithPersistentAlias(Session session) : base(session) { }
    private int _Hours;  
    [Persistent("Hours")]  
    public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }
    private SlowOfferWithPersistentAlias _SlowOfferWithPersistentAlias;  
    [Persistent("SlowOfferWithPersistentAlias"), Association]  
    public SlowOfferWithPersistentAlias SlowOfferWithPersistentAlias { get => _SlowOfferWithPersistentAlias; set => SetPropertyValue(nameof(SlowOfferWithPersistentAlias), ref _SlowOfferWithPersistentAlias, value); }  
}

1 part (e.g. handling with the

18.09.20 16:22:13.906 Executing sql 'select N0."Oid",N0."Name",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOffer" N0 where N0."GCRecord" is null'  
18.09.20 16:22:13.908 Result: rowcount = 300, total = 11996, N0.{Oid,Guid} = 4800, N0.{Name,String} = 4796, N0.{OptimisticLockField,Int32} = 1200, N0.{GCRecord,Int32} = 1200  
18.09.20 16:22:13.913 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {a786412c-907d-4b36-beb6-004395d38e9d}  
18.09.20 16:22:13.927 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:13.931 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {cf656095-dcd6-46f1-b830-00af607693a9}  
18.09.20 16:22:13.949 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
.  
.  
.  
.  
18.09.20 16:22:20.036 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {0be207ec-9430-44e7-964c-fd684303d051}  
18.09.20 16:22:20.053 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:20.057 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {3b48cf0a-d610-4586-8dfb-fde802777653}  
18.09.20 16:22:20.078 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:20.122 Executing sql 'select top 1 count(*) from "dbo"."SlowOffer" N0 where N0."GCRecord" is null'  
18.09.20 16:22:20.126 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4  
18.09.20 16:22:20.127 Executing sql 'select top 1 count(*) from "dbo"."SlowOfferItem" N0 where N0."GCRecord" is null'  
18.09.20 16:22:20.170 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4

4 objects)

When dealing with statistics or calculated data, instead of calculating on the fly, we can calculate the data upfront. We can use several techniques to do that and it highly depends on your business needs. It all depends on how stale data is allowed to be. This is not a performance discussion though. You need to talk to your business on the right strategy for that.

I'm not going into all details on every single technique on CQRS. If you are interested on more powerful techniques on this let me know in the comments below.

Imagine we store the sum of every order everytime an

18.09.20 16:22:13.906 Executing sql 'select N0."Oid",N0."Name",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOffer" N0 where N0."GCRecord" is null'  
18.09.20 16:22:13.908 Result: rowcount = 300, total = 11996, N0.{Oid,Guid} = 4800, N0.{Name,String} = 4796, N0.{OptimisticLockField,Int32} = 1200, N0.{GCRecord,Int32} = 1200  
18.09.20 16:22:13.913 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {a786412c-907d-4b36-beb6-004395d38e9d}  
18.09.20 16:22:13.927 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:13.931 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {cf656095-dcd6-46f1-b830-00af607693a9}  
18.09.20 16:22:13.949 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
.  
.  
.  
.  
18.09.20 16:22:20.036 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {0be207ec-9430-44e7-964c-fd684303d051}  
18.09.20 16:22:20.053 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:20.057 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {3b48cf0a-d610-4586-8dfb-fde802777653}  
18.09.20 16:22:20.078 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:20.122 Executing sql 'select top 1 count(*) from "dbo"."SlowOffer" N0 where N0."GCRecord" is null'  
18.09.20 16:22:20.126 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4  
18.09.20 16:22:20.127 Executing sql 'select top 1 count(*) from "dbo"."SlowOfferItem" N0 where N0."GCRecord" is null'  
18.09.20 16:22:20.170 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4

4 is saved. We will store that data in a separate table to keep our

[DefaultClassOptions]  
[DefaultProperty(nameof(HourSum))]  
public class SlowOfferWithPersistentAlias : BaseObject  
{  
    public SlowOfferWithPersistentAlias(Session session) : base(session) { }
    private string _Name;  
    [Persistent("Name")]  
    public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }
    //Calculate the sum on the server side  
    [PersistentAlias("OfferItems.Sum([Hours])")]  
    //Tell XPO to calculate the alias when accessed client side or from code  
    public int HourSum => (int)EvaluateAlias(nameof(HourSum));
    [Association, Aggregated]  
    public XPCollection<SlowOfferItemWithPersistentAlias> OfferItems => GetCollection<SlowOfferItemWithPersistentAlias>(nameof(OfferItems));  
}
public class SlowOfferItemWithPersistentAlias : BaseObject  
{  
    public SlowOfferItemWithPersistentAlias(Session session) : base(session) { }
    private int _Hours;  
    [Persistent("Hours")]  
    public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }
    private SlowOfferWithPersistentAlias _SlowOfferWithPersistentAlias;  
    [Persistent("SlowOfferWithPersistentAlias"), Association]  
    public SlowOfferWithPersistentAlias SlowOfferWithPersistentAlias { get => _SlowOfferWithPersistentAlias; set => SetPropertyValue(nameof(SlowOfferWithPersistentAlias), ref _SlowOfferWithPersistentAlias, value); }  
}

0 part from the

[DefaultClassOptions]  
[DefaultProperty(nameof(HourSum))]  
public class SlowOfferWithPersistentAlias : BaseObject  
{  
    public SlowOfferWithPersistentAlias(Session session) : base(session) { }
    private string _Name;  
    [Persistent("Name")]  
    public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }
    //Calculate the sum on the server side  
    [PersistentAlias("OfferItems.Sum([Hours])")]  
    //Tell XPO to calculate the alias when accessed client side or from code  
    public int HourSum => (int)EvaluateAlias(nameof(HourSum));
    [Association, Aggregated]  
    public XPCollection<SlowOfferItemWithPersistentAlias> OfferItems => GetCollection<SlowOfferItemWithPersistentAlias>(nameof(OfferItems));  
}
public class SlowOfferItemWithPersistentAlias : BaseObject  
{  
    public SlowOfferItemWithPersistentAlias(Session session) : base(session) { }
    private int _Hours;  
    [Persistent("Hours")]  
    public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }
    private SlowOfferWithPersistentAlias _SlowOfferWithPersistentAlias;  
    [Persistent("SlowOfferWithPersistentAlias"), Association]  
    public SlowOfferWithPersistentAlias SlowOfferWithPersistentAlias { get => _SlowOfferWithPersistentAlias; set => SetPropertyValue(nameof(SlowOfferWithPersistentAlias), ref _SlowOfferWithPersistentAlias, value); }  
}

1 part. With XPO it's quite easy to do that, cause we have a single point of truth for updating the

[DefaultClassOptions]  
[DefaultProperty(nameof(HourSum))]  
public class SlowOfferWithPersistentAlias : BaseObject  
{  
    public SlowOfferWithPersistentAlias(Session session) : base(session) { }
    private string _Name;  
    [Persistent("Name")]  
    public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }
    //Calculate the sum on the server side  
    [PersistentAlias("OfferItems.Sum([Hours])")]  
    //Tell XPO to calculate the alias when accessed client side or from code  
    public int HourSum => (int)EvaluateAlias(nameof(HourSum));
    [Association, Aggregated]  
    public XPCollection<SlowOfferItemWithPersistentAlias> OfferItems => GetCollection<SlowOfferItemWithPersistentAlias>(nameof(OfferItems));  
}
public class SlowOfferItemWithPersistentAlias : BaseObject  
{  
    public SlowOfferItemWithPersistentAlias(Session session) : base(session) { }
    private int _Hours;  
    [Persistent("Hours")]  
    public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }
    private SlowOfferWithPersistentAlias _SlowOfferWithPersistentAlias;  
    [Persistent("SlowOfferWithPersistentAlias"), Association]  
    public SlowOfferWithPersistentAlias SlowOfferWithPersistentAlias { get => _SlowOfferWithPersistentAlias; set => SetPropertyValue(nameof(SlowOfferWithPersistentAlias), ref _SlowOfferWithPersistentAlias, value); }  
}

0 table:

[DefaultClassOptions]  
[DefaultProperty(nameof(HourSum))]  
public class SlowOffer : BaseObject  
{  
    public SlowOffer(Session session) : base(session) { }
    private string _Name;  
    [Persistent("Name")]  
    public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }
    [NonPersistent]  
    public int HourSum  
    {  
        get  
        {  
            var sum = 0;  
            foreach (var item in OfferItems)  
            {  
                sum += item.Hours;  
            }  
            return sum;  
        }  
    }
    [Association, Aggregated]  
    public XPCollection<SlowOfferItem> OfferItems => GetCollection<SlowOfferItem>(nameof(OfferItems));  
}
public class SlowOfferItem : BaseObject  
{  
    public SlowOfferItem(Session session) : base(session) { }
    private int _Hours;  
    [Persistent("Hours")]  
    public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }
    private SlowOffer _SlowOffer;  
    [Persistent("SlowOffer"), Association]  
    public SlowOffer SlowOffer { get => _SlowOffer; set => SetPropertyValue(nameof(SlowOffer), ref _SlowOffer, value); }  
}

5

Important: this only covers this very basic example, and highly depends on your configuration (e.g. SecuritySystem, Deferred deletion etc.). You will need good testing on that to not get stale or out of sync. Database side foreignkeys can help to mark stale objects, as well as triggers to handle that on a database level. Also this simple technique only works if you are the only on that writes into the database. I will cover mode advanced scenarios in the future, just let me know in the comments below.

This will result in a slower write performance for the

18.09.20 16:22:13.906 Executing sql 'select N0."Oid",N0."Name",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOffer" N0 where N0."GCRecord" is null'  
18.09.20 16:22:13.908 Result: rowcount = 300, total = 11996, N0.{Oid,Guid} = 4800, N0.{Name,String} = 4796, N0.{OptimisticLockField,Int32} = 1200, N0.{GCRecord,Int32} = 1200  
18.09.20 16:22:13.913 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {a786412c-907d-4b36-beb6-004395d38e9d}  
18.09.20 16:22:13.927 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:13.931 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {cf656095-dcd6-46f1-b830-00af607693a9}  
18.09.20 16:22:13.949 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
.  
.  
.  
.  
18.09.20 16:22:20.036 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {0be207ec-9430-44e7-964c-fd684303d051}  
18.09.20 16:22:20.053 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:20.057 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {3b48cf0a-d610-4586-8dfb-fde802777653}  
18.09.20 16:22:20.078 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:20.122 Executing sql 'select top 1 count(*) from "dbo"."SlowOffer" N0 where N0."GCRecord" is null'  
18.09.20 16:22:20.126 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4  
18.09.20 16:22:20.127 Executing sql 'select top 1 count(*) from "dbo"."SlowOfferItem" N0 where N0."GCRecord" is null'  
18.09.20 16:22:20.170 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4

4 but a huge speed improvment on the

[DefaultClassOptions]  
[DefaultProperty(nameof(HourSum))]  
public class SlowOfferWithPersistentAlias : BaseObject  
{  
    public SlowOfferWithPersistentAlias(Session session) : base(session) { }
    private string _Name;  
    [Persistent("Name")]  
    public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }
    //Calculate the sum on the server side  
    [PersistentAlias("OfferItems.Sum([Hours])")]  
    //Tell XPO to calculate the alias when accessed client side or from code  
    public int HourSum => (int)EvaluateAlias(nameof(HourSum));
    [Association, Aggregated]  
    public XPCollection<SlowOfferItemWithPersistentAlias> OfferItems => GetCollection<SlowOfferItemWithPersistentAlias>(nameof(OfferItems));  
}
public class SlowOfferItemWithPersistentAlias : BaseObject  
{  
    public SlowOfferItemWithPersistentAlias(Session session) : base(session) { }
    private int _Hours;  
    [Persistent("Hours")]  
    public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }
    private SlowOfferWithPersistentAlias _SlowOfferWithPersistentAlias;  
    [Persistent("SlowOfferWithPersistentAlias"), Association]  
    public SlowOfferWithPersistentAlias SlowOfferWithPersistentAlias { get => _SlowOfferWithPersistentAlias; set => SetPropertyValue(nameof(SlowOfferWithPersistentAlias), ref _SlowOfferWithPersistentAlias, value); }  
}

0 table. I've set the

19.09.20 10:35:31.422 Executing sql 'select N0."Oid",N0."Name",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferWithLinq" N0 where N0."GCRecord" is null'  
19.09.20 10:35:31.425 Result: rowcount = 300, total = 11914, N0.{Oid,Guid} = 4800, N0.{Name,String} = 4714, N0.{OptimisticLockField,Int32} = 1200, N0.{GCRecord,Int32} = 1200  
19.09.20 10:35:31.426 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOfferWithLinq",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."SlowOfferWithLinq" = @p0))' with parameters {a917fb15-82e1-471e-95b3-031a070d492d}  
19.09.20 10:35:31.440 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOfferWithLinq,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
19.09.20 10:35:31.451 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOfferWithLinq",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."SlowOfferWithLinq" = @p0))' with parameters {b625d838-3747-4ad4-aaac-032e79a7d3a6}  
19.09.20 10:35:31.464 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOfferWithLinq,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000.  
.  
.  
.  
19.09.20 10:35:36.787 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOfferWithLinq",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."SlowOfferWithLinq" = @p0))' with parameters {e2774f41-7d31-4a5f-afa4-ffe8d3a21d47}  
19.09.20 10:35:36.803 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOfferWithLinq,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
19.09.20 10:35:36.851 Executing sql 'select top 1 count(*) from "dbo"."SlowOfferWithLinq" N0 where N0."GCRecord" is null'  
19.09.20 10:35:36.853 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4  
19.09.20 10:35:36.854 Executing sql 'select top 1 count(*) from "dbo"."SlowOfferItemWithLinq" N0 where N0."GCRecord" is null'  
19.09.20 10:35:36.877 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4

8 of the

[DefaultClassOptions]  
[DefaultProperty(nameof(HourSum))]  
public class SlowOfferWithPersistentAlias : BaseObject  
{  
    public SlowOfferWithPersistentAlias(Session session) : base(session) { }
    private string _Name;  
    [Persistent("Name")]  
    public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }
    //Calculate the sum on the server side  
    [PersistentAlias("OfferItems.Sum([Hours])")]  
    //Tell XPO to calculate the alias when accessed client side or from code  
    public int HourSum => (int)EvaluateAlias(nameof(HourSum));
    [Association, Aggregated]  
    public XPCollection<SlowOfferItemWithPersistentAlias> OfferItems => GetCollection<SlowOfferItemWithPersistentAlias>(nameof(OfferItems));  
}
public class SlowOfferItemWithPersistentAlias : BaseObject  
{  
    public SlowOfferItemWithPersistentAlias(Session session) : base(session) { }
    private int _Hours;  
    [Persistent("Hours")]  
    public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }
    private SlowOfferWithPersistentAlias _SlowOfferWithPersistentAlias;  
    [Persistent("SlowOfferWithPersistentAlias"), Association]  
    public SlowOfferWithPersistentAlias SlowOfferWithPersistentAlias { get => _SlowOfferWithPersistentAlias; set => SetPropertyValue(nameof(SlowOfferWithPersistentAlias), ref _SlowOfferWithPersistentAlias, value); }  
}

0 table to

[NonPersistent]  
// Sum up all the hours using Linq  
public int HourSum => OfferItems.Sum(m => m.Hours);

7 and left the

19.09.20 10:35:31.422 Executing sql 'select N0."Oid",N0."Name",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferWithLinq" N0 where N0."GCRecord" is null'  
19.09.20 10:35:31.425 Result: rowcount = 300, total = 11914, N0.{Oid,Guid} = 4800, N0.{Name,String} = 4714, N0.{OptimisticLockField,Int32} = 1200, N0.{GCRecord,Int32} = 1200  
19.09.20 10:35:31.426 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOfferWithLinq",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."SlowOfferWithLinq" = @p0))' with parameters {a917fb15-82e1-471e-95b3-031a070d492d}  
19.09.20 10:35:31.440 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOfferWithLinq,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
19.09.20 10:35:31.451 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOfferWithLinq",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."SlowOfferWithLinq" = @p0))' with parameters {b625d838-3747-4ad4-aaac-032e79a7d3a6}  
19.09.20 10:35:31.464 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOfferWithLinq,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000.  
.  
.  
.  
19.09.20 10:35:36.787 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOfferWithLinq",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."SlowOfferWithLinq" = @p0))' with parameters {e2774f41-7d31-4a5f-afa4-ffe8d3a21d47}  
19.09.20 10:35:36.803 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOfferWithLinq,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
19.09.20 10:35:36.851 Executing sql 'select top 1 count(*) from "dbo"."SlowOfferWithLinq" N0 where N0."GCRecord" is null'  
19.09.20 10:35:36.853 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4  
19.09.20 10:35:36.854 Executing sql 'select top 1 count(*) from "dbo"."SlowOfferItemWithLinq" N0 where N0."GCRecord" is null'  
19.09.20 10:35:36.877 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4

8 for the

19.09.20 11:20:27.738 Executing sql 'select N0."Oid",N0."Name",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferWithPersistentAlias" N0 where N0."GCRecord" is null'  
19.09.20 11:20:27.739 Result: rowcount = 300, total = 12048, N0.{Oid,Guid} = 4800, N0.{Name,String} = 4848, N0.{OptimisticLockField,Int32} = 1200, N0.{GCRecord,Int32} = 1200  
19.09.20 11:20:27.740 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOfferWithPersistentAlias",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItemWithPersistentAlias" N0 where (N0."GCRecord" is null and (N0."SlowOfferWithPersistentAlias" = @p0))' with parameters {11328106-f965-495d-89d0-00896fa631a1}  
19.09.20 11:20:27.755 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOfferWithPersistentAlias,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
.  
.  
.  
19.09.20 11:20:33.695 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOfferWithPersistentAlias",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItemWithPersistentAlias" N0 where (N0."GCRecord" is null and (N0."SlowOfferWithPersistentAlias" = @p0))' with parameters {926fde40-af9c-4679-bdf9-fe0edcc49465}  
19.09.20 11:20:33.718 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOfferWithPersistentAlias,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
19.09.20 11:20:33.789 Executing sql 'select top 1 count(*) from "dbo"."SlowOfferWithPersistentAlias" N0 where N0."GCRecord" is null'  
19.09.20 11:20:33.790 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4  
19.09.20 11:20:33.790 Executing sql 'select top 1 count(*) from "dbo"."SlowOfferItemWithPersistentAlias" N0 where N0."GCRecord" is null'  
19.09.20 11:20:33.805 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4

3 in

[NonPersistent]  
// Sum up all the hours using Linq  
public int HourSum => OfferItems.Sum(m => m.Hours);

0 mode:

18.09.20 16:22:13.906 Executing sql 'select N0."Oid",N0."Name",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOffer" N0 where N0."GCRecord" is null'  
18.09.20 16:22:13.908 Result: rowcount = 300, total = 11996, N0.{Oid,Guid} = 4800, N0.{Name,String} = 4796, N0.{OptimisticLockField,Int32} = 1200, N0.{GCRecord,Int32} = 1200  
18.09.20 16:22:13.913 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {a786412c-907d-4b36-beb6-004395d38e9d}  
18.09.20 16:22:13.927 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:13.931 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {cf656095-dcd6-46f1-b830-00af607693a9}  
18.09.20 16:22:13.949 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
.  
.  
.  
.  
18.09.20 16:22:20.036 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {0be207ec-9430-44e7-964c-fd684303d051}  
18.09.20 16:22:20.053 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:20.057 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {3b48cf0a-d610-4586-8dfb-fde802777653}  
18.09.20 16:22:20.078 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:20.122 Executing sql 'select top 1 count(*) from "dbo"."SlowOffer" N0 where N0."GCRecord" is null'  
18.09.20 16:22:20.126 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4  
18.09.20 16:22:20.127 Executing sql 'select top 1 count(*) from "dbo"."SlowOfferItem" N0 where N0."GCRecord" is null'  
18.09.20 16:22:20.170 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4

4:

[DefaultClassOptions]  
[DefaultProperty(nameof(HourSum))]  
public class SlowOffer : BaseObject  
{  
    public SlowOffer(Session session) : base(session) { }
    private string _Name;  
    [Persistent("Name")]  
    public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }
    [NonPersistent]  
    public int HourSum  
    {  
        get  
        {  
            var sum = 0;  
            foreach (var item in OfferItems)  
            {  
                sum += item.Hours;  
            }  
            return sum;  
        }  
    }
    [Association, Aggregated]  
    public XPCollection<SlowOfferItem> OfferItems => GetCollection<SlowOfferItem>(nameof(OfferItems));  
}
public class SlowOfferItem : BaseObject  
{  
    public SlowOfferItem(Session session) : base(session) { }
    private int _Hours;  
    [Persistent("Hours")]  
    public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }
    private SlowOffer _SlowOffer;  
    [Persistent("SlowOffer"), Association]  
    public SlowOffer SlowOffer { get => _SlowOffer; set => SetPropertyValue(nameof(SlowOffer), ref _SlowOffer, value); }  
}

6

This results in the following performance

Sort by

 [DefaultClassOptions]
[DefaultProperty(nameof(HourSum))]
public class SlowOfferWithLinq : BaseObject
{ 
public SlowOfferWithLinq(Session session) : base(session) { }  
private string _Name;  
[Persistent("Name")]  
public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }  
[NonPersistent]  
// Sum up all the hours using Linq  
public int HourSum => OfferItems.Sum(m => m.Hours);  
[Association, Aggregated]  
public XPCollection<SlowOfferItemWithLinq> OfferItems => GetCollection<SlowOfferItemWithLinq>(nameof(OfferItems));  
}
public class SlowOfferItemWithLinq : BaseObject
{ public SlowOfferItemWithLinq(Session session) : base(session) { }  
private int _Hours;  
[Persistent("Hours")]  
public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }  
private SlowOfferWithLinq _SlowOfferWithLinq;  
[Persistent("SlowOfferWithLinq"), Association]  
public SlowOfferWithLinq SlowOfferWithLinq { get => _SlowOfferWithLinq; set => SetPropertyValue(nameof(SlowOfferWithLinq), ref _SlowOfferWithLinq, value); }  
}

4: 0.014 seconds

Hint: we can still provide an


19.09.20 10:58:05.069 Executing sql 'select N0."Oid",N0."Name",N0."OptimisticLockField",N0."GCRecord" from "dbo"."FasterOfferWithLinq" N0 where N0."GCRecord" is null' >
19.09.20 10:58:05.070 Result: rowcount = 300, total = 11992, N0.{Oid,Guid} = 4800, N0.{Name,String} = 4792, N0.{OptimisticLockField,Int32} = 1200, N0.{GCRecord,Int32} = 1200 >
19.09.20 10:58:05.071 Executing sql 'select top 1 sum(N0."Hours") from "dbo"."FasterOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."FasterOfferWithLinq" = @p0))' with parameters {a3797700-1fcf-429f-9c34-005322afa892} >
19.09.20 10:58:05.075 Result: rowcount = 1, total = 4, SubQuery(Sum,N0.{Hours,Int32},) = 4 >
19.09.20 10:58:05.076 Executing sql 'select top 1 sum(N0."Hours") from "dbo"."FasterOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."FasterOfferWithLinq" = @p0))' with parameters {3683bfc2-7314-452f-be51-00692f123965} >
. >
. >
. >
19.09.20 10:58:06.497 Executing sql 'select top 1 count() from "dbo"."FasterOfferWithLinq" N0 where N0."GCRecord" is null' >
19.09.20 10:58:06.497 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4 >
19.09.20 10:58:06.498 Executing sql 'select top 1 count() from "dbo"."FasterOfferItemWithLinq" N0 where N0."GCRecord" is null' > 19.09.20 10:58:06.512 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4 >
19.09.20 10:58:07.919 Executing sql 'select top 1 sum(N0."Hours") from "dbo"."FasterOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."FasterOfferWithLinq" = @p0))' with parameters {820161dc-8568-412c-bf84-c4f148e8122f} >
19.09.20 10:58:07.924 Result: rowcount = 1, total = 4, SubQuery(Sum,N0.{Hours,Int32},) = 4 >

8

[DefaultClassOptions] >
[DefaultProperty(nameof(HourSum))] >
public class SlowOfferWithLinq : BaseObject >
{ >
public SlowOfferWithLinq(Session session) : base(session) { }
private string _Name; >
[Persistent("Name")] >
public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }
[NonPersistent] >
// Sum up all the hours using Linq >
public int HourSum => OfferItems.Sum(m => m.Hours);
[Association, Aggregated] >
public XPCollection<SlowOfferItemWithLinq> OfferItems => GetCollection<SlowOfferItemWithLinq>(nameof(OfferItems)); >
}
public class SlowOfferItemWithLinq : BaseObject >
{ >
public SlowOfferItemWithLinq(Session session) : base(session) { }
private int _Hours; >
[Persistent("Hours")] >
public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }
private SlowOfferWithLinq _SlowOfferWithLinq; >
[Persistent("SlowOfferWithLinq"), Association] >
public SlowOfferWithLinq SlowOfferWithLinq { get => _SlowOfferWithLinq; set => SetPropertyValue(nameof(SlowOfferWithLinq), ref _SlowOfferWithLinq, value); } >
} >

0 field in the


18.09.20 16:22:13.906 Executing sql 'select N0."Oid",N0."Name",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOffer" N0 where N0."GCRecord" is null' >
18.09.20 16:22:13.908 Result: rowcount = 300, total = 11996, N0.{Oid,Guid} = 4800, N0.{Name,String} = 4796, N0.{OptimisticLockField,Int32} = 1200, N0.{GCRecord,Int32} = 1200 >
18.09.20 16:22:13.913 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {a786412c-907d-4b36-beb6-004395d38e9d} >
18.09.20 16:22:13.927 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000 >
18.09.20 16:22:13.931 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {cf656095-dcd6-46f1-b830-00af607693a9} >
18.09.20 16:22:13.949 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000 >
. >
. >
. >
. >
18.09.20 16:22:20.036 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {0be207ec-9430-44e7-964c-fd684303d051} >
18.09.20 16:22:20.053 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000 >
18.09.20 16:22:20.057 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {3b48cf0a-d610-4586-8dfb-fde802777653} >
18.09.20 16:22:20.078 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000 >
18.09.20 16:22:20.122 Executing sql 'select top 1 count() from "dbo"."SlowOffer" N0 where N0."GCRecord" is null' >
18.09.20 16:22:20.126 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4 >
18.09.20 16:22:20.127 Executing sql 'select top 1 count() from "dbo"."SlowOfferItem" N0 where N0."GCRecord" is null' > 18.09.20 16:22:20.170 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4 >

4 class, but make sure users can't display, filter, group by this field in any


18.09.20 16:22:13.906 Executing sql 'select N0."Oid",N0."Name",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOffer" N0 where N0."GCRecord" is null' >
18.09.20 16:22:13.908 Result: rowcount = 300, total = 11996, N0.{Oid,Guid} = 4800, N0.{Name,String} = 4796, N0.{OptimisticLockField,Int32} = 1200, N0.{GCRecord,Int32} = 1200 >
18.09.20 16:22:13.913 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {a786412c-907d-4b36-beb6-004395d38e9d} >
18.09.20 16:22:13.927 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000 >
18.09.20 16:22:13.931 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {cf656095-dcd6-46f1-b830-00af607693a9} >
18.09.20 16:22:13.949 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000 >
. >
. >
. >
. >
18.09.20 16:22:20.036 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {0be207ec-9430-44e7-964c-fd684303d051} >
18.09.20 16:22:20.053 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000 >
18.09.20 16:22:20.057 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {3b48cf0a-d610-4586-8dfb-fde802777653} >
18.09.20 16:22:20.078 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000 >
18.09.20 16:22:20.122 Executing sql 'select top 1 count() from "dbo"."SlowOffer" N0 where N0."GCRecord" is null' >
18.09.20 16:22:20.126 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4 >
18.09.20 16:22:20.127 Executing sql 'select top 1 count() from "dbo"."SlowOfferItem" N0 where N0."GCRecord" is null' > 18.09.20 16:22:20.170 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4 >

7. In

[DefaultClassOptions] >
[DefaultProperty(nameof(HourSum))] >
public class SlowOffer : BaseObject >
{ >
public SlowOffer(Session session) : base(session) { }
private string _Name; >
[Persistent("Name")] >
public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }
[NonPersistent] >
public int HourSum >
{ >
get >
{ >
var sum = 0; >
foreach (var item in OfferItems) >
{ >
sum += item.Hours; >
} >
return sum; >
} >
}
[Association, Aggregated] >
public XPCollection<SlowOfferItem> OfferItems => GetCollection<SlowOfferItem>(nameof(OfferItems)); >
}
public class SlowOfferItem : BaseObject >
{ >
public SlowOfferItem(Session session) : base(session) { }
private int _Hours; >
[Persistent("Hours")] >
public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }
private SlowOffer _SlowOffer; >
[Persistent("SlowOffer"), Association] >
public SlowOffer SlowOffer { get => _SlowOffer; set => SetPropertyValue(nameof(SlowOffer), ref _SlowOffer, value); } >
}

01 this should not be a huge performance penalty.

[DefaultClassOptions]  
[DefaultProperty(nameof(HourSum))]  
public class SlowOfferWithPersistentAlias : BaseObject  
{  
    public SlowOfferWithPersistentAlias(Session session) : base(session) { }
    private string _Name;  
    [Persistent("Name")]  
    public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }
    //Calculate the sum on the server side  
    [PersistentAlias("OfferItems.Sum([Hours])")]  
    //Tell XPO to calculate the alias when accessed client side or from code  
    public int HourSum => (int)EvaluateAlias(nameof(HourSum));
    [Association, Aggregated]  
    public XPCollection<SlowOfferItemWithPersistentAlias> OfferItems => GetCollection<SlowOfferItemWithPersistentAlias>(nameof(OfferItems));  
}
public class SlowOfferItemWithPersistentAlias : BaseObject  
{  
    public SlowOfferItemWithPersistentAlias(Session session) : base(session) { }
    private int _Hours;  
    [Persistent("Hours")]  
    public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }
    private SlowOfferWithPersistentAlias _SlowOfferWithPersistentAlias;  
    [Persistent("SlowOfferWithPersistentAlias"), Association]  
    public SlowOfferWithPersistentAlias SlowOfferWithPersistentAlias { get => _SlowOfferWithPersistentAlias; set => SetPropertyValue(nameof(SlowOfferWithPersistentAlias), ref _SlowOfferWithPersistentAlias, value); }  
}

0:

[DefaultClassOptions]  
[DefaultProperty(nameof(HourSum))]  
public class SlowOffer : BaseObject  
{  
    public SlowOffer(Session session) : base(session) { }
    private string _Name;  
    [Persistent("Name")]  
    public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }
    [NonPersistent]  
    public int HourSum  
    {  
        get  
        {  
            var sum = 0;  
            foreach (var item in OfferItems)  
            {  
                sum += item.Hours;  
            }  
            return sum;  
        }  
    }
    [Association, Aggregated]  
    public XPCollection<SlowOfferItem> OfferItems => GetCollection<SlowOfferItem>(nameof(OfferItems));  
}
public class SlowOfferItem : BaseObject  
{  
    public SlowOfferItem(Session session) : base(session) { }
    private int _Hours;  
    [Persistent("Hours")]  
    public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }
    private SlowOffer _SlowOffer;  
    [Persistent("SlowOffer"), Association]  
    public SlowOffer SlowOffer { get => _SlowOffer; set => SetPropertyValue(nameof(SlowOffer), ref _SlowOffer, value); }  
}

7

This results in the following performance

Sort by

 [DefaultClassOptions]
[DefaultProperty(nameof(HourSum))]
public class SlowOfferWithLinq : BaseObject
{ 
public SlowOfferWithLinq(Session session) : base(session) { }  
private string _Name;  
[Persistent("Name")]  
public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }  
[NonPersistent]  
// Sum up all the hours using Linq  
public int HourSum => OfferItems.Sum(m => m.Hours);  
[Association, Aggregated]  
public XPCollection<SlowOfferItemWithLinq> OfferItems => GetCollection<SlowOfferItemWithLinq>(nameof(OfferItems));  
}
public class SlowOfferItemWithLinq : BaseObject
{ public SlowOfferItemWithLinq(Session session) : base(session) { }  
private int _Hours;  
[Persistent("Hours")]  
public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }  
private SlowOfferWithLinq _SlowOfferWithLinq;  
[Persistent("SlowOfferWithLinq"), Association]  
public SlowOfferWithLinq SlowOfferWithLinq { get => _SlowOfferWithLinq; set => SetPropertyValue(nameof(SlowOfferWithLinq), ref _SlowOfferWithLinq, value); }  
}

4: 0.005 seconds

Sort by

 [DefaultClassOptions]
[DefaultProperty(nameof(HourSum))]
public class SlowOfferWithLinq : BaseObject
{ 
public SlowOfferWithLinq(Session session) : base(session) { }  
private string _Name;  
[Persistent("Name")]  
public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }  
[NonPersistent]  
// Sum up all the hours using Linq  
public int HourSum => OfferItems.Sum(m => m.Hours);  
[Association, Aggregated]  
public XPCollection<SlowOfferItemWithLinq> OfferItems => GetCollection<SlowOfferItemWithLinq>(nameof(OfferItems));  
}
public class SlowOfferItemWithLinq : BaseObject
{ public SlowOfferItemWithLinq(Session session) : base(session) { }  
private int _Hours;  
[Persistent("Hours")]  
public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }  
private SlowOfferWithLinq _SlowOfferWithLinq;  
[Persistent("SlowOfferWithLinq"), Association]  
public SlowOfferWithLinq SlowOfferWithLinq { get => _SlowOfferWithLinq; set => SetPropertyValue(nameof(SlowOfferWithLinq), ref _SlowOfferWithLinq, value); }  
}

0: 0.004 seconds

Important: This will of course result in data duplication and use more disk space. Keep that in mind when designing your datacenter. It also will slow down bulk inserts, because a lot of more records need to be created.

Use the database at your advantage - Views

Until now every technique we used does not care about the database underneath. If you don't plan to support more than one or two databases (and be honest, when did you switch your database the last time?) use the power of your database. Databases are good at this. That is their job. But we need to drop down to SQL. Don't be afraid, it has a lot of benefits on the long run.

This time there is a little bit more code but it's not that difficult:

[DefaultClassOptions]  
[DefaultProperty(nameof(HourSum))]  
public class SlowOffer : BaseObject  
{  
    public SlowOffer(Session session) : base(session) { }
    private string _Name;  
    [Persistent("Name")]  
    public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }
    [NonPersistent]  
    public int HourSum  
    {  
        get  
        {  
            var sum = 0;  
            foreach (var item in OfferItems)  
            {  
                sum += item.Hours;  
            }  
            return sum;  
        }  
    }
    [Association, Aggregated]  
    public XPCollection<SlowOfferItem> OfferItems => GetCollection<SlowOfferItem>(nameof(OfferItems));  
}
public class SlowOfferItem : BaseObject  
{  
    public SlowOfferItem(Session session) : base(session) { }
    private int _Hours;  
    [Persistent("Hours")]  
    public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }
    private SlowOffer _SlowOffer;  
    [Persistent("SlowOffer"), Association]  
    public SlowOffer SlowOffer { get => _SlowOffer; set => SetPropertyValue(nameof(SlowOffer), ref _SlowOffer, value); }  
}

8

18.09.20 16:22:13.906 Executing sql 'select N0."Oid",N0."Name",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOffer" N0 where N0."GCRecord" is null'  
18.09.20 16:22:13.908 Result: rowcount = 300, total = 11996, N0.{Oid,Guid} = 4800, N0.{Name,String} = 4796, N0.{OptimisticLockField,Int32} = 1200, N0.{GCRecord,Int32} = 1200  
18.09.20 16:22:13.913 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {a786412c-907d-4b36-beb6-004395d38e9d}  
18.09.20 16:22:13.927 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:13.931 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {cf656095-dcd6-46f1-b830-00af607693a9}  
18.09.20 16:22:13.949 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
.  
.  
.  
.  
18.09.20 16:22:20.036 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {0be207ec-9430-44e7-964c-fd684303d051}  
18.09.20 16:22:20.053 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:20.057 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {3b48cf0a-d610-4586-8dfb-fde802777653}  
18.09.20 16:22:20.078 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:20.122 Executing sql 'select top 1 count(*) from "dbo"."SlowOffer" N0 where N0."GCRecord" is null'  
18.09.20 16:22:20.126 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4  
18.09.20 16:22:20.127 Executing sql 'select top 1 count(*) from "dbo"."SlowOfferItem" N0 where N0."GCRecord" is null'  
18.09.20 16:22:20.170 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4

4:

[DefaultClassOptions]  
[DefaultProperty(nameof(HourSum))]  
public class SlowOffer : BaseObject  
{  
    public SlowOffer(Session session) : base(session) { }
    private string _Name;  
    [Persistent("Name")]  
    public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }
    [NonPersistent]  
    public int HourSum  
    {  
        get  
        {  
            var sum = 0;  
            foreach (var item in OfferItems)  
            {  
                sum += item.Hours;  
            }  
            return sum;  
        }  
    }
    [Association, Aggregated]  
    public XPCollection<SlowOfferItem> OfferItems => GetCollection<SlowOfferItem>(nameof(OfferItems));  
}
public class SlowOfferItem : BaseObject  
{  
    public SlowOfferItem(Session session) : base(session) { }
    private int _Hours;  
    [Persistent("Hours")]  
    public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }
    private SlowOffer _SlowOffer;  
    [Persistent("SlowOffer"), Association]  
    public SlowOffer SlowOffer { get => _SlowOffer; set => SetPropertyValue(nameof(SlowOffer), ref _SlowOffer, value); }  
}

9

This results in the following performance

Sort by

 [DefaultClassOptions]
[DefaultProperty(nameof(HourSum))]
public class SlowOfferWithLinq : BaseObject
{ 
public SlowOfferWithLinq(Session session) : base(session) { }  
private string _Name;  
[Persistent("Name")]  
public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }  
[NonPersistent]  
// Sum up all the hours using Linq  
public int HourSum => OfferItems.Sum(m => m.Hours);  
[Association, Aggregated]  
public XPCollection<SlowOfferItemWithLinq> OfferItems => GetCollection<SlowOfferItemWithLinq>(nameof(OfferItems));  
}
public class SlowOfferItemWithLinq : BaseObject
{ public SlowOfferItemWithLinq(Session session) : base(session) { }  
private int _Hours;  
[Persistent("Hours")]  
public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }  
private SlowOfferWithLinq _SlowOfferWithLinq;  
[Persistent("SlowOfferWithLinq"), Association]  
public SlowOfferWithLinq SlowOfferWithLinq { get => _SlowOfferWithLinq; set => SetPropertyValue(nameof(SlowOfferWithLinq), ref _SlowOfferWithLinq, value); }  
}

4: 0.009 seconds

Hint: we can still provide a


19.09.20 10:58:05.069 Executing sql 'select N0."Oid",N0."Name",N0."OptimisticLockField",N0."GCRecord" from "dbo"."FasterOfferWithLinq" N0 where N0."GCRecord" is null' >
19.09.20 10:58:05.070 Result: rowcount = 300, total = 11992, N0.{Oid,Guid} = 4800, N0.{Name,String} = 4792, N0.{OptimisticLockField,Int32} = 1200, N0.{GCRecord,Int32} = 1200 >
19.09.20 10:58:05.071 Executing sql 'select top 1 sum(N0."Hours") from "dbo"."FasterOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."FasterOfferWithLinq" = @p0))' with parameters {a3797700-1fcf-429f-9c34-005322afa892} >
19.09.20 10:58:05.075 Result: rowcount = 1, total = 4, SubQuery(Sum,N0.{Hours,Int32},) = 4 >
19.09.20 10:58:05.076 Executing sql 'select top 1 sum(N0."Hours") from "dbo"."FasterOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."FasterOfferWithLinq" = @p0))' with parameters {3683bfc2-7314-452f-be51-00692f123965} >
. >
. >
. >
19.09.20 10:58:06.497 Executing sql 'select top 1 count() from "dbo"."FasterOfferWithLinq" N0 where N0."GCRecord" is null' >
19.09.20 10:58:06.497 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4 >
19.09.20 10:58:06.498 Executing sql 'select top 1 count() from "dbo"."FasterOfferItemWithLinq" N0 where N0."GCRecord" is null' > 19.09.20 10:58:06.512 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4 >
19.09.20 10:58:07.919 Executing sql 'select top 1 sum(N0."Hours") from "dbo"."FasterOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."FasterOfferWithLinq" = @p0))' with parameters {820161dc-8568-412c-bf84-c4f148e8122f} >
19.09.20 10:58:07.924 Result: rowcount = 1, total = 4, SubQuery(Sum,N0.{Hours,Int32},) = 4 >

8

[DefaultClassOptions] >
[DefaultProperty(nameof(HourSum))] >
public class SlowOfferWithLinq : BaseObject >
{ >
public SlowOfferWithLinq(Session session) : base(session) { }
private string _Name; >
[Persistent("Name")] >
public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }
[NonPersistent] >
// Sum up all the hours using Linq >
public int HourSum => OfferItems.Sum(m => m.Hours);
[Association, Aggregated] >
public XPCollection<SlowOfferItemWithLinq> OfferItems => GetCollection<SlowOfferItemWithLinq>(nameof(OfferItems)); >
}
public class SlowOfferItemWithLinq : BaseObject >
{ >
public SlowOfferItemWithLinq(Session session) : base(session) { }
private int _Hours; >
[Persistent("Hours")] >
public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }
private SlowOfferWithLinq _SlowOfferWithLinq; >
[Persistent("SlowOfferWithLinq"), Association] >
public SlowOfferWithLinq SlowOfferWithLinq { get => _SlowOfferWithLinq; set => SetPropertyValue(nameof(SlowOfferWithLinq), ref _SlowOfferWithLinq, value); } >
} >

0 field in the


18.09.20 16:22:13.906 Executing sql 'select N0."Oid",N0."Name",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOffer" N0 where N0."GCRecord" is null' >
18.09.20 16:22:13.908 Result: rowcount = 300, total = 11996, N0.{Oid,Guid} = 4800, N0.{Name,String} = 4796, N0.{OptimisticLockField,Int32} = 1200, N0.{GCRecord,Int32} = 1200 >
18.09.20 16:22:13.913 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {a786412c-907d-4b36-beb6-004395d38e9d} >
18.09.20 16:22:13.927 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000 >
18.09.20 16:22:13.931 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {cf656095-dcd6-46f1-b830-00af607693a9} >
18.09.20 16:22:13.949 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000 >
. >
. >
. >
. >
18.09.20 16:22:20.036 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {0be207ec-9430-44e7-964c-fd684303d051} >
18.09.20 16:22:20.053 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000 >
18.09.20 16:22:20.057 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {3b48cf0a-d610-4586-8dfb-fde802777653} >
18.09.20 16:22:20.078 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000 >
18.09.20 16:22:20.122 Executing sql 'select top 1 count() from "dbo"."SlowOffer" N0 where N0."GCRecord" is null' >
18.09.20 16:22:20.126 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4 >
18.09.20 16:22:20.127 Executing sql 'select top 1 count() from "dbo"."SlowOfferItem" N0 where N0."GCRecord" is null' > 18.09.20 16:22:20.170 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4 >

4 class, but make sure users can't display, filter, group by this field in any


18.09.20 16:22:13.906 Executing sql 'select N0."Oid",N0."Name",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOffer" N0 where N0."GCRecord" is null' >
18.09.20 16:22:13.908 Result: rowcount = 300, total = 11996, N0.{Oid,Guid} = 4800, N0.{Name,String} = 4796, N0.{OptimisticLockField,Int32} = 1200, N0.{GCRecord,Int32} = 1200 >
18.09.20 16:22:13.913 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {a786412c-907d-4b36-beb6-004395d38e9d} >
18.09.20 16:22:13.927 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000 >
18.09.20 16:22:13.931 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {cf656095-dcd6-46f1-b830-00af607693a9} >
18.09.20 16:22:13.949 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000 >
. >
. >
. >
. >
18.09.20 16:22:20.036 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {0be207ec-9430-44e7-964c-fd684303d051} >
18.09.20 16:22:20.053 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000 >
18.09.20 16:22:20.057 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {3b48cf0a-d610-4586-8dfb-fde802777653} >
18.09.20 16:22:20.078 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000 >
18.09.20 16:22:20.122 Executing sql 'select top 1 count() from "dbo"."SlowOffer" N0 where N0."GCRecord" is null' >
18.09.20 16:22:20.126 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4 >
18.09.20 16:22:20.127 Executing sql 'select top 1 count() from "dbo"."SlowOfferItem" N0 where N0."GCRecord" is null' > 18.09.20 16:22:20.170 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4 >

7. In

[DefaultClassOptions] >
[DefaultProperty(nameof(HourSum))] >
public class SlowOffer : BaseObject >
{ >
public SlowOffer(Session session) : base(session) { }
private string _Name; >
[Persistent("Name")] >
public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }
[NonPersistent] >
public int HourSum >
{ >
get >
{ >
var sum = 0; >
foreach (var item in OfferItems) >
{ >
sum += item.Hours; >
} >
return sum; >
} >
}
[Association, Aggregated] >
public XPCollection<SlowOfferItem> OfferItems => GetCollection<SlowOfferItem>(nameof(OfferItems)); >
}
public class SlowOfferItem : BaseObject >
{ >
public SlowOfferItem(Session session) : base(session) { }
private int _Hours; >
[Persistent("Hours")] >
public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }
private SlowOffer _SlowOffer; >
[Persistent("SlowOffer"), Association] >
public SlowOffer SlowOffer { get => _SlowOffer; set => SetPropertyValue(nameof(SlowOffer), ref _SlowOffer, value); } >
}

01 this should not be a huge performance penalty.

[DefaultClassOptions]  
[DefaultProperty(nameof(HourSum))]  
public class SlowOfferWithPersistentAlias : BaseObject  
{  
    public SlowOfferWithPersistentAlias(Session session) : base(session) { }
    private string _Name;  
    [Persistent("Name")]  
    public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }
    //Calculate the sum on the server side  
    [PersistentAlias("OfferItems.Sum([Hours])")]  
    //Tell XPO to calculate the alias when accessed client side or from code  
    public int HourSum => (int)EvaluateAlias(nameof(HourSum));
    [Association, Aggregated]  
    public XPCollection<SlowOfferItemWithPersistentAlias> OfferItems => GetCollection<SlowOfferItemWithPersistentAlias>(nameof(OfferItems));  
}
public class SlowOfferItemWithPersistentAlias : BaseObject  
{  
    public SlowOfferItemWithPersistentAlias(Session session) : base(session) { }
    private int _Hours;  
    [Persistent("Hours")]  
    public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }
    private SlowOfferWithPersistentAlias _SlowOfferWithPersistentAlias;  
    [Persistent("SlowOfferWithPersistentAlias"), Association]  
    public SlowOfferWithPersistentAlias SlowOfferWithPersistentAlias { get => _SlowOfferWithPersistentAlias; set => SetPropertyValue(nameof(SlowOfferWithPersistentAlias), ref _SlowOfferWithPersistentAlias, value); }  
}

0:

18.09.20 16:22:13.906 Executing sql 'select N0."Oid",N0."Name",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOffer" N0 where N0."GCRecord" is null'  
18.09.20 16:22:13.908 Result: rowcount = 300, total = 11996, N0.{Oid,Guid} = 4800, N0.{Name,String} = 4796, N0.{OptimisticLockField,Int32} = 1200, N0.{GCRecord,Int32} = 1200  
18.09.20 16:22:13.913 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {a786412c-907d-4b36-beb6-004395d38e9d}  
18.09.20 16:22:13.927 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:13.931 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {cf656095-dcd6-46f1-b830-00af607693a9}  
18.09.20 16:22:13.949 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
.  
.  
.  
.  
18.09.20 16:22:20.036 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {0be207ec-9430-44e7-964c-fd684303d051}  
18.09.20 16:22:20.053 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:20.057 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {3b48cf0a-d610-4586-8dfb-fde802777653}  
18.09.20 16:22:20.078 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:20.122 Executing sql 'select top 1 count(*) from "dbo"."SlowOffer" N0 where N0."GCRecord" is null'  
18.09.20 16:22:20.126 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4  
18.09.20 16:22:20.127 Executing sql 'select top 1 count(*) from "dbo"."SlowOfferItem" N0 where N0."GCRecord" is null'  
18.09.20 16:22:20.170 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4

0

This results in the following performance

Sort by

 [DefaultClassOptions]
[DefaultProperty(nameof(HourSum))]
public class SlowOfferWithLinq : BaseObject
{ 
public SlowOfferWithLinq(Session session) : base(session) { }  
private string _Name;  
[Persistent("Name")]  
public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }  
[NonPersistent]  
// Sum up all the hours using Linq  
public int HourSum => OfferItems.Sum(m => m.Hours);  
[Association, Aggregated]  
public XPCollection<SlowOfferItemWithLinq> OfferItems => GetCollection<SlowOfferItemWithLinq>(nameof(OfferItems));  
}
public class SlowOfferItemWithLinq : BaseObject
{ public SlowOfferItemWithLinq(Session session) : base(session) { }  
private int _Hours;  
[Persistent("Hours")]  
public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }  
private SlowOfferWithLinq _SlowOfferWithLinq;  
[Persistent("SlowOfferWithLinq"), Association]  
public SlowOfferWithLinq SlowOfferWithLinq { get => _SlowOfferWithLinq; set => SetPropertyValue(nameof(SlowOfferWithLinq), ref _SlowOfferWithLinq, value); }  
}

4: 0.079 seconds

Sort by

 [DefaultClassOptions]
[DefaultProperty(nameof(HourSum))]
public class SlowOfferWithLinq : BaseObject
{ 
public SlowOfferWithLinq(Session session) : base(session) { }  
private string _Name;  
[Persistent("Name")]  
public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }  
[NonPersistent]  
// Sum up all the hours using Linq  
public int HourSum => OfferItems.Sum(m => m.Hours);  
[Association, Aggregated]  
public XPCollection<SlowOfferItemWithLinq> OfferItems => GetCollection<SlowOfferItemWithLinq>(nameof(OfferItems));  
}
public class SlowOfferItemWithLinq : BaseObject
{ public SlowOfferItemWithLinq(Session session) : base(session) { }  
private int _Hours;  
[Persistent("Hours")]  
public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }  
private SlowOfferWithLinq _SlowOfferWithLinq;  
[Persistent("SlowOfferWithLinq"), Association]  
public SlowOfferWithLinq SlowOfferWithLinq { get => _SlowOfferWithLinq; set => SetPropertyValue(nameof(SlowOfferWithLinq), ref _SlowOfferWithLinq, value); }  
}

0: 0.070 seconds

That's pretty impressiv! Finally let's have a look at the totally unsupported pitched version that saved me several times.

Totally unsupported - There will be dragons - N+N query version

WARNING: THIS WILL AND CAN BREAK IN THE FUTURE! FROM THIS POINT ON YOU ARE ON YOUR OWN.

What if we can not avoid N+1 queries, but at least come down to N+N queries? That means we let XAF use a normal

[NonPersistent]  
// Sum up all the hours using Linq  
public int HourSum => OfferItems.Sum(m => m.Hours);

0 mode ListView and do 1 additional query for all records in that particular

18.09.20 16:22:13.906 Executing sql 'select N0."Oid",N0."Name",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOffer" N0 where N0."GCRecord" is null'  
18.09.20 16:22:13.908 Result: rowcount = 300, total = 11996, N0.{Oid,Guid} = 4800, N0.{Name,String} = 4796, N0.{OptimisticLockField,Int32} = 1200, N0.{GCRecord,Int32} = 1200  
18.09.20 16:22:13.913 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {a786412c-907d-4b36-beb6-004395d38e9d}  
18.09.20 16:22:13.927 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:13.931 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {cf656095-dcd6-46f1-b830-00af607693a9}  
18.09.20 16:22:13.949 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
.  
.  
.  
.  
18.09.20 16:22:20.036 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {0be207ec-9430-44e7-964c-fd684303d051}  
18.09.20 16:22:20.053 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:20.057 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {3b48cf0a-d610-4586-8dfb-fde802777653}  
18.09.20 16:22:20.078 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:20.122 Executing sql 'select top 1 count(*) from "dbo"."SlowOffer" N0 where N0."GCRecord" is null'  
18.09.20 16:22:20.126 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4  
18.09.20 16:22:20.127 Executing sql 'select top 1 count(*) from "dbo"."SlowOfferItem" N0 where N0."GCRecord" is null'  
18.09.20 16:22:20.170 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4

7?

There is one in XAF unsupported features called

[DefaultClassOptions]  
[DefaultProperty(nameof(HourSum))]  
public class SlowOffer : BaseObject  
{  
    public SlowOffer(Session session) : base(session) { }
    private string _Name;  
    [Persistent("Name")]  
    public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }
    [NonPersistent]  
    public int HourSum  
    {  
        get  
        {  
            var sum = 0;  
            foreach (var item in OfferItems)  
            {  
                sum += item.Hours;  
            }  
            return sum;  
        }  
    }
    [Association, Aggregated]  
    public XPCollection<SlowOfferItem> OfferItems => GetCollection<SlowOfferItem>(nameof(OfferItems));  
}
public class SlowOfferItem : BaseObject  
{  
    public SlowOfferItem(Session session) : base(session) { }
    private int _Hours;  
    [Persistent("Hours")]  
    public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }
    private SlowOffer _SlowOffer;  
    [Persistent("SlowOffer"), Association]  
    public SlowOffer SlowOffer { get => _SlowOffer; set => SetPropertyValue(nameof(SlowOffer), ref _SlowOffer, value); }  
}

17 but it also has some limitations. We need a way to do 1 query when the first N+1 query would occur, afterwards we cache it and just lookup data from this cache. We can't use static fields, cause we have no idea when to purge the cache. But there is one undocumented feature of XPO called

[DefaultClassOptions]  
[DefaultProperty(nameof(HourSum))]  
public class SlowOffer : BaseObject  
{  
    public SlowOffer(Session session) : base(session) { }
    private string _Name;  
    [Persistent("Name")]  
    public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }
    [NonPersistent]  
    public int HourSum  
    {  
        get  
        {  
            var sum = 0;  
            foreach (var item in OfferItems)  
            {  
                sum += item.Hours;  
            }  
            return sum;  
        }  
    }
    [Association, Aggregated]  
    public XPCollection<SlowOfferItem> OfferItems => GetCollection<SlowOfferItem>(nameof(OfferItems));  
}
public class SlowOfferItem : BaseObject  
{  
    public SlowOfferItem(Session session) : base(session) { }
    private int _Hours;  
    [Persistent("Hours")]  
    public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }
    private SlowOffer _SlowOffer;  
    [Persistent("SlowOffer"), Association]  
    public SlowOffer SlowOffer { get => _SlowOffer; set => SetPropertyValue(nameof(SlowOffer), ref _SlowOffer, value); }  
}

18 we can leverage.

Update: As Dennis kindly mentioned in the comments, [DefaultClassOptions] > [DefaultProperty(nameof(HourSum))] > public class SlowOffer : BaseObject > { > public SlowOffer(Session session) : base(session) { } private string _Name; > [Persistent("Name")] > public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); } [NonPersistent] > public int HourSum > { > get > { > var sum = 0; > foreach (var item in OfferItems) > { > sum += item.Hours; > } > return sum; > } > } [Association, Aggregated] > public XPCollection<SlowOfferItem> OfferItems => GetCollection<SlowOfferItem>(nameof(OfferItems)); > } public class SlowOfferItem : BaseObject > { > public SlowOfferItem(Session session) : base(session) { } private int _Hours; > [Persistent("Hours")] > public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); } private SlowOffer _SlowOffer; > [Persistent("SlowOffer"), Association] > public SlowOffer SlowOffer { get => _SlowOffer; set => SetPropertyValue(nameof(SlowOffer), ref _SlowOffer, value); } > } 17 is supported but won't work in every scenario.

18.09.20 16:22:13.906 Executing sql 'select N0."Oid",N0."Name",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOffer" N0 where N0."GCRecord" is null'  
18.09.20 16:22:13.908 Result: rowcount = 300, total = 11996, N0.{Oid,Guid} = 4800, N0.{Name,String} = 4796, N0.{OptimisticLockField,Int32} = 1200, N0.{GCRecord,Int32} = 1200  
18.09.20 16:22:13.913 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {a786412c-907d-4b36-beb6-004395d38e9d}  
18.09.20 16:22:13.927 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:13.931 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {cf656095-dcd6-46f1-b830-00af607693a9}  
18.09.20 16:22:13.949 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
.  
.  
.  
.  
18.09.20 16:22:20.036 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {0be207ec-9430-44e7-964c-fd684303d051}  
18.09.20 16:22:20.053 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:20.057 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {3b48cf0a-d610-4586-8dfb-fde802777653}  
18.09.20 16:22:20.078 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:20.122 Executing sql 'select top 1 count(*) from "dbo"."SlowOffer" N0 where N0."GCRecord" is null'  
18.09.20 16:22:20.126 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4  
18.09.20 16:22:20.127 Executing sql 'select top 1 count(*) from "dbo"."SlowOfferItem" N0 where N0."GCRecord" is null'  
18.09.20 16:22:20.170 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4

1

Let's look at the queries generated in the Client mode:

18.09.20 16:22:13.906 Executing sql 'select N0."Oid",N0."Name",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOffer" N0 where N0."GCRecord" is null'  
18.09.20 16:22:13.908 Result: rowcount = 300, total = 11996, N0.{Oid,Guid} = 4800, N0.{Name,String} = 4796, N0.{OptimisticLockField,Int32} = 1200, N0.{GCRecord,Int32} = 1200  
18.09.20 16:22:13.913 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {a786412c-907d-4b36-beb6-004395d38e9d}  
18.09.20 16:22:13.927 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:13.931 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {cf656095-dcd6-46f1-b830-00af607693a9}  
18.09.20 16:22:13.949 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
.  
.  
.  
.  
18.09.20 16:22:20.036 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {0be207ec-9430-44e7-964c-fd684303d051}  
18.09.20 16:22:20.053 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:20.057 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {3b48cf0a-d610-4586-8dfb-fde802777653}  
18.09.20 16:22:20.078 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:20.122 Executing sql 'select top 1 count(*) from "dbo"."SlowOffer" N0 where N0."GCRecord" is null'  
18.09.20 16:22:20.126 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4  
18.09.20 16:22:20.127 Executing sql 'select top 1 count(*) from "dbo"."SlowOfferItem" N0 where N0."GCRecord" is null'  
18.09.20 16:22:20.170 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4

2

This results in the following performance

Sort by

 [DefaultClassOptions]
[DefaultProperty(nameof(HourSum))]
public class SlowOfferWithLinq : BaseObject
{ 
public SlowOfferWithLinq(Session session) : base(session) { }  
private string _Name;  
[Persistent("Name")]  
public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }  
[NonPersistent]  
// Sum up all the hours using Linq  
public int HourSum => OfferItems.Sum(m => m.Hours);  
[Association, Aggregated]  
public XPCollection<SlowOfferItemWithLinq> OfferItems => GetCollection<SlowOfferItemWithLinq>(nameof(OfferItems));  
}
public class SlowOfferItemWithLinq : BaseObject
{ public SlowOfferItemWithLinq(Session session) : base(session) { }  
private int _Hours;  
[Persistent("Hours")]  
public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }  
private SlowOfferWithLinq _SlowOfferWithLinq;  
[Persistent("SlowOfferWithLinq"), Association]  
public SlowOfferWithLinq SlowOfferWithLinq { get => _SlowOfferWithLinq; set => SetPropertyValue(nameof(SlowOfferWithLinq), ref _SlowOfferWithLinq, value); }  
}

4: 0.082 seconds

Sort by

 [DefaultClassOptions]
[DefaultProperty(nameof(HourSum))]
public class SlowOfferWithLinq : BaseObject
{ 
public SlowOfferWithLinq(Session session) : base(session) { }  
private string _Name;  
[Persistent("Name")]  
public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }  
[NonPersistent]  
// Sum up all the hours using Linq  
public int HourSum => OfferItems.Sum(m => m.Hours);  
[Association, Aggregated]  
public XPCollection<SlowOfferItemWithLinq> OfferItems => GetCollection<SlowOfferItemWithLinq>(nameof(OfferItems));  
}
public class SlowOfferItemWithLinq : BaseObject
{ public SlowOfferItemWithLinq(Session session) : base(session) { }  
private int _Hours;  
[Persistent("Hours")]  
public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }  
private SlowOfferWithLinq _SlowOfferWithLinq;  
[Persistent("SlowOfferWithLinq"), Association]  
public SlowOfferWithLinq SlowOfferWithLinq { get => _SlowOfferWithLinq; set => SetPropertyValue(nameof(SlowOfferWithLinq), ref _SlowOfferWithLinq, value); }  
}

0: 0.076 seconds

Note: Because XAF recreates a

[DefaultClassOptions] >
[DefaultProperty(nameof(HourSum))] >
public class SlowOffer : BaseObject >
{ >
public SlowOffer(Session session) : base(session) { }
private string _Name; >
[Persistent("Name")] >
public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }
[NonPersistent] >
public int HourSum >
{ >
get >
{ >
var sum = 0; >
foreach (var item in OfferItems) >
{ >
sum += item.Hours; >
} >
return sum; >
} >
}
[Association, Aggregated] >
public XPCollection<SlowOfferItem> OfferItems => GetCollection<SlowOfferItem>(nameof(OfferItems)); >
}
public class SlowOfferItem : BaseObject >
{ >
public SlowOfferItem(Session session) : base(session) { }
private int _Hours; >
[Persistent("Hours")] >
public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }
private SlowOffer _SlowOffer; >
[Persistent("SlowOffer"), Association] >
public SlowOffer SlowOffer { get => _SlowOffer; set => SetPropertyValue(nameof(SlowOffer), ref _SlowOffer, value); } >
}

22 object every time it's refreshed, will fetch the second query only once in the lifetime of the session. You can control the cache whichever way you like. Beware that you will pay the cost of calculating the aggregates of ALL objects in the

[DefaultClassOptions] >
[DefaultProperty(nameof(HourSum))] >
public class SlowOffer : BaseObject >
{ >
public SlowOffer(Session session) : base(session) { }
private string _Name; >
[Persistent("Name")] >
public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }
[NonPersistent] >
public int HourSum >
{ >
get >
{ >
var sum = 0; >
foreach (var item in OfferItems) >
{ >
sum += item.Hours; >
} >
return sum; >
} >
}
[Association, Aggregated] >
public XPCollection<SlowOfferItem> OfferItems => GetCollection<SlowOfferItem>(nameof(OfferItems)); >
}
public class SlowOfferItem : BaseObject >
{ >
public SlowOfferItem(Session session) : base(session) { }
private int _Hours; >
[Persistent("Hours")] >
public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }
private SlowOffer _SlowOffer; >
[Persistent("SlowOffer"), Association] >
public SlowOffer SlowOffer { get => _SlowOffer; set => SetPropertyValue(nameof(SlowOffer), ref _SlowOffer, value); } >
}

23 context as well (because there is no natural way to figure out if you are currently displayed in a

[DefaultClassOptions] >
[DefaultProperty(nameof(HourSum))] >
public class SlowOffer : BaseObject >
{ >
public SlowOffer(Session session) : base(session) { }
private string _Name; >
[Persistent("Name")] >
public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }
[NonPersistent] >
public int HourSum >
{ >
get >
{ >
var sum = 0; >
foreach (var item in OfferItems) >
{ >
sum += item.Hours; >
} >
return sum; >
} >
}
[Association, Aggregated] >
public XPCollection<SlowOfferItem> OfferItems => GetCollection<SlowOfferItem>(nameof(OfferItems)); >
}
public class SlowOfferItem : BaseObject >
{ >
public SlowOfferItem(Session session) : base(session) { }
private int _Hours; >
[Persistent("Hours")] >
public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }
private SlowOffer _SlowOffer; >
[Persistent("SlowOffer"), Association] >
public SlowOffer SlowOffer { get => _SlowOffer; set => SetPropertyValue(nameof(SlowOffer), ref _SlowOffer, value); } >
}

23). There are several strategies you can further improve using this technique, but for now I think this is pretty impressive.

This is really awesome performance gain for very little effort. Of course you can combine those techniques. Use

[DefaultClassOptions]  
[DefaultProperty(nameof(HourSum))]  
public class SlowOffer : BaseObject  
{  
    public SlowOffer(Session session) : base(session) { }
    private string _Name;  
    [Persistent("Name")]  
    public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }
    [NonPersistent]  
    public int HourSum  
    {  
        get  
        {  
            var sum = 0;  
            foreach (var item in OfferItems)  
            {  
                sum += item.Hours;  
            }  
            return sum;  
        }  
    }
    [Association, Aggregated]  
    public XPCollection<SlowOfferItem> OfferItems => GetCollection<SlowOfferItem>(nameof(OfferItems));  
}
public class SlowOfferItem : BaseObject  
{  
    public SlowOfferItem(Session session) : base(session) { }
    private int _Hours;  
    [Persistent("Hours")]  
    public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }
    private SlowOffer _SlowOffer;  
    [Persistent("SlowOffer"), Association]  
    public SlowOffer SlowOffer { get => _SlowOffer; set => SetPropertyValue(nameof(SlowOffer), ref _SlowOffer, value); }  
}

25 combined with

[DefaultClassOptions]  
[DefaultProperty(nameof(HourSum))]  
public class SlowOffer : BaseObject  
{  
    public SlowOffer(Session session) : base(session) { }
    private string _Name;  
    [Persistent("Name")]  
    public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }
    [NonPersistent]  
    public int HourSum  
    {  
        get  
        {  
            var sum = 0;  
            foreach (var item in OfferItems)  
            {  
                sum += item.Hours;  
            }  
            return sum;  
        }  
    }
    [Association, Aggregated]  
    public XPCollection<SlowOfferItem> OfferItems => GetCollection<SlowOfferItem>(nameof(OfferItems));  
}
public class SlowOfferItem : BaseObject  
{  
    public SlowOfferItem(Session session) : base(session) { }
    private int _Hours;  
    [Persistent("Hours")]  
    public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }
    private SlowOffer _SlowOffer;  
    [Persistent("SlowOffer"), Association]  
    public SlowOffer SlowOffer { get => _SlowOffer; set => SetPropertyValue(nameof(SlowOffer), ref _SlowOffer, value); }  
}

26 and so on. The main goal of this post is how to identify performance bottlenecks in your application and how to overcome them when dealing with aggregates especially in

[DefaultClassOptions]  
[DefaultProperty(nameof(HourSum))]  
public class SlowOffer : BaseObject  
{  
    public SlowOffer(Session session) : base(session) { }
    private string _Name;  
    [Persistent("Name")]  
    public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }
    [NonPersistent]  
    public int HourSum  
    {  
        get  
        {  
            var sum = 0;  
            foreach (var item in OfferItems)  
            {  
                sum += item.Hours;  
            }  
            return sum;  
        }  
    }
    [Association, Aggregated]  
    public XPCollection<SlowOfferItem> OfferItems => GetCollection<SlowOfferItem>(nameof(OfferItems));  
}
public class SlowOfferItem : BaseObject  
{  
    public SlowOfferItem(Session session) : base(session) { }
    private int _Hours;  
    [Persistent("Hours")]  
    public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }
    private SlowOffer _SlowOffer;  
    [Persistent("SlowOffer"), Association]  
    public SlowOffer SlowOffer { get => _SlowOffer; set => SetPropertyValue(nameof(SlowOffer), ref _SlowOffer, value); }  
}

27.

Summary - Lessions learned

Performance is analysis is hard work with so much available options. To pinpoint some learnings:

Measure, measure, measure
Pick the right tool (and always keep memory and database load in sight)
Never combine 19.09.20 10:35:31.422 Executing sql 'select N0."Oid",N0."Name",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferWithLinq" N0 where N0."GCRecord" is null' 19.09.20 10:35:31.425 Result: rowcount = 300, total = 11914, N0.{Oid,Guid} = 4800, N0.{Name,String} = 4714, N0.{OptimisticLockField,Int32} = 1200, N0.{GCRecord,Int32} = 1200 19.09.20 10:35:31.426 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOfferWithLinq",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."SlowOfferWithLinq" = @p0))' with parameters {a917fb15-82e1-471e-95b3-031a070d492d} 19.09.20 10:35:31.440 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOfferWithLinq,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000 19.09.20 10:35:31.451 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOfferWithLinq",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."SlowOfferWithLinq" = @p0))' with parameters {b625d838-3747-4ad4-aaac-032e79a7d3a6} 19.09.20 10:35:31.464 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOfferWithLinq,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000. . . . 19.09.20 10:35:36.787 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOfferWithLinq",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."SlowOfferWithLinq" = @p0))' with parameters {e2774f41-7d31-4a5f-afa4-ffe8d3a21d47} 19.09.20 10:35:36.803 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOfferWithLinq,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000 19.09.20 10:35:36.851 Executing sql 'select top 1 count() from "dbo"."SlowOfferWithLinq" N0 where N0."GCRecord" is null' 19.09.20 10:35:36.853 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4 19.09.20 10:35:36.854 Executing sql 'select top 1 count() from "dbo"."SlowOfferItemWithLinq" N0 where N0."GCRecord" is null' 19.09.20 10:35:36.877 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4 5 with client mode aggregates if possible
Use your database as a tool
If everything performance wise breaks down: use and measure the last 3 options

Use N+N query wisely. It doesn't have the same benefits like all the other

 [DefaultClassOptions]
[DefaultProperty(nameof(HourSum))]
public class SlowOffer : BaseObject
{ 
public SlowOffer(Session session) : base(session) { }  
private string _Name;  
[Persistent("Name")]  
public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }  
[NonPersistent]  
public int HourSum  
{  
    get  
    {  
        var sum = 0;  
        foreach (var item in OfferItems)  
        {  
            sum += item.Hours;  
        }  
        return sum;  
    }  
}  
[Association, Aggregated]  
public XPCollection<SlowOfferItem> OfferItems => GetCollection<SlowOfferItem>(nameof(OfferItems));  
}
public class SlowOfferItem : BaseObject
{ public SlowOfferItem(Session session) : base(session) { }  
private int _Hours;  
[Persistent("Hours")]  
public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }  
private SlowOffer _SlowOffer;  
[Persistent("SlowOffer"), Association]  
public SlowOffer SlowOffer { get => _SlowOffer; set => SetPropertyValue(nameof(SlowOffer), ref _SlowOffer, value); }  
}

29 but it behaves linear, and can help calculate complicated business rules only once and avoid the N+1 problem.

Everything is a tradeoff (implementation time, memory, cpu time, stale data)
Use [NonPersistent] // Sum up all the hours using Linq public int HourSum => OfferItems.Sum(m => m.Hours);
7 or
[DefaultClassOptions] [DefaultProperty(nameof(HourSum))] public class FasterOfferWithLinq : BaseObject { public FasterOfferWithLinq(Session session) : base(session) { } private string _Name; [Persistent("Name")] public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); } [NonPersistent] public int HourSum => //Query the data store Session.Query<FasterOfferItemWithLinq>() //Filter it down to the current record .Where(i => i.FasterOfferWithLinq.Oid == Oid) //Sum the total hours .Sum(m => m.Hours); [Association, Aggregated] public XPCollection<FasterOfferItemWithLinq> OfferItems => GetCollection<FasterOfferItemWithLinq>(nameof(OfferItems)); } public class FasterOfferItemWithLinq : BaseObject { public FasterOfferItemWithLinq(Session session) : base(session) { } private int _Hours; [Persistent("Hours")] public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); } private FasterOfferWithLinq _FasterOfferWithLinq; [Persistent("FasterOfferWithLinq"), Association] public FasterOfferWithLinq FasterOfferWithLinq { get => _FasterOfferWithLinq; set => SetPropertyValue(nameof(FasterOfferWithLinq), ref _FasterOfferWithLinq, value); } }
6 in combination with
19.09.20 10:35:31.422 Executing sql 'select N0."Oid",N0."Name",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferWithLinq" N0 where N0."GCRecord" is null' 19.09.20 10:35:31.425 Result: rowcount = 300, total = 11914, N0.{Oid,Guid} = 4800, N0.{Name,String} = 4714, N0.{OptimisticLockField,Int32} = 1200, N0.{GCRecord,Int32} = 1200 19.09.20 10:35:31.426 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOfferWithLinq",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."SlowOfferWithLinq" = @p0))' with parameters {a917fb15-82e1-471e-95b3-031a070d492d} 19.09.20 10:35:31.440 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOfferWithLinq,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000 19.09.20 10:35:31.451 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOfferWithLinq",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."SlowOfferWithLinq" = @p0))' with parameters {b625d838-3747-4ad4-aaac-032e79a7d3a6} 19.09.20 10:35:31.464 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOfferWithLinq,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000. . . . 19.09.20 10:35:36.787 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOfferWithLinq",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."SlowOfferWithLinq" = @p0))' with parameters {e2774f41-7d31-4a5f-afa4-ffe8d3a21d47} 19.09.20 10:35:36.803 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOfferWithLinq,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000 19.09.20 10:35:36.851 Executing sql 'select top 1 count() from "dbo"."SlowOfferWithLinq" N0 where N0."GCRecord" is null' 19.09.20 10:35:36.853 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4 19.09.20 10:35:36.854 Executing sql 'select top 1 count() from "dbo"."SlowOfferItemWithLinq" N0 where N0."GCRecord" is null' 19.09.20 10:35:36.877 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4 5 wherever you need to do aggregation with larger amounts of data
[NonPersistent] // Sum up all the hours using Linq public int HourSum => OfferItems.Sum(m => m.Hours);
4,
19.09.20 10:58:05.069 Executing sql 'select N0."Oid",N0."Name",N0."OptimisticLockField",N0."GCRecord" from "dbo"."FasterOfferWithLinq" N0 where N0."GCRecord" is null' 19.09.20 10:58:05.070 Result: rowcount = 300, total = 11992, N0.{Oid,Guid} = 4800, N0.{Name,String} = 4792, N0.{OptimisticLockField,Int32} = 1200, N0.{GCRecord,Int32} = 1200 19.09.20 10:58:05.071 Executing sql 'select top 1 sum(N0."Hours") from "dbo"."FasterOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."FasterOfferWithLinq" = @p0))' with parameters {a3797700-1fcf-429f-9c34-005322afa892} 19.09.20 10:58:05.075 Result: rowcount = 1, total = 4, SubQuery(Sum,N0.{Hours,Int32},) = 4 19.09.20 10:58:05.076 Executing sql 'select top 1 sum(N0."Hours") from "dbo"."FasterOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."FasterOfferWithLinq" = @p0))' with parameters {3683bfc2-7314-452f-be51-00692f123965} . . . 19.09.20 10:58:06.497 Executing sql 'select top 1 count() from "dbo"."FasterOfferWithLinq" N0 where N0."GCRecord" is null' 19.09.20 10:58:06.497 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4 19.09.20 10:58:06.498 Executing sql 'select top 1 count() from "dbo"."FasterOfferItemWithLinq" N0 where N0."GCRecord" is null' 19.09.20 10:58:06.512 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4 19.09.20 10:58:07.919 Executing sql 'select top 1 sum(N0."Hours") from "dbo"."FasterOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."FasterOfferWithLinq" = @p0))' with parameters {820161dc-8568-412c-bf84-c4f148e8122f} 19.09.20 10:58:07.924 Result: rowcount = 1, total = 4, SubQuery(Sum,N0.{Hours,Int32},) = 4
2 and
[DefaultClassOptions] [DefaultProperty(nameof(HourSum))] public class FasterOfferWithLinq : BaseObject { public FasterOfferWithLinq(Session session) : base(session) { } private string _Name; [Persistent("Name")] public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); } [NonPersistent] public int HourSum => //Query the data store Session.Query<FasterOfferItemWithLinq>() //Filter it down to the current record .Where(i => i.FasterOfferWithLinq.Oid == Oid) //Sum the total hours .Sum(m => m.Hours); [Association, Aggregated] public XPCollection<FasterOfferItemWithLinq> OfferItems => GetCollection<FasterOfferItemWithLinq>(nameof(OfferItems)); } public class FasterOfferItemWithLinq : BaseObject { public FasterOfferItemWithLinq(Session session) : base(session) { } private int _Hours; [Persistent("Hours")] public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); } private FasterOfferWithLinq _FasterOfferWithLinq; [Persistent("FasterOfferWithLinq"), Association] public FasterOfferWithLinq FasterOfferWithLinq { get => _FasterOfferWithLinq; set => SetPropertyValue(nameof(FasterOfferWithLinq), ref _FasterOfferWithLinq, value); } }
0 will drive your
[DefaultClassOptions] [DefaultProperty(nameof(HourSum))] public class SlowOffer : BaseObject { public SlowOffer(Session session) : base(session) { } private string _Name; [Persistent("Name")] public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); } [NonPersistent] public int HourSum { get { var sum = 0; foreach (var item in OfferItems) { sum += item.Hours; } return sum; } } [Association, Aggregated] public XPCollection<SlowOfferItem> OfferItems => GetCollection<SlowOfferItem>(nameof(OfferItems)); } public class SlowOfferItem : BaseObject { public SlowOfferItem(Session session) : base(session) { } private int _Hours; [Persistent("Hours")] public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); } private SlowOffer _SlowOffer; [Persistent("SlowOffer"), Association] public SlowOffer SlowOffer { get => _SlowOffer; set => SetPropertyValue(nameof(SlowOffer), ref _SlowOffer, value); } } 36 crazy, if used incorrectly
Aim for UX first and stay with [NonPersistent] // Sum up all the hours using Linq public int HourSum => OfferItems.Sum(m => m.Hours); 0 mode as much as you can

You really need good reasons for

 [DefaultClassOptions]
[DefaultProperty(nameof(HourSum))]
public class SlowOffer : BaseObject
{ 
public SlowOffer(Session session) : base(session) { }  
private string _Name;  
[Persistent("Name")]  
public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }  
[NonPersistent]  
public int HourSum  
{  
    get  
    {  
        var sum = 0;  
        foreach (var item in OfferItems)  
        {  
            sum += item.Hours;  
        }  
        return sum;  
    }  
}  
[Association, Aggregated]  
public XPCollection<SlowOfferItem> OfferItems => GetCollection<SlowOfferItem>(nameof(OfferItems));  
}
public class SlowOfferItem : BaseObject
{ public SlowOfferItem(Session session) : base(session) { }  
private int _Hours;  
[Persistent("Hours")]  
public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }  
private SlowOffer _SlowOffer;  
[Persistent("SlowOffer"), Association]  
public SlowOffer SlowOffer { get => _SlowOffer; set => SetPropertyValue(nameof(SlowOffer), ref _SlowOffer, value); }  
}

38. It adds loads of performance, but increases complexity and maintenance a lot

Database

 [DefaultClassOptions]
[DefaultProperty(nameof(HourSum))]
public class SlowOffer : BaseObject
{ 
public SlowOffer(Session session) : base(session) { }  
private string _Name;  
[Persistent("Name")]  
public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }  
[NonPersistent]  
public int HourSum  
{  
    get  
    {  
        var sum = 0;  
        foreach (var item in OfferItems)  
        {  
            sum += item.Hours;  
        }  
        return sum;  
    }  
}  
[Association, Aggregated]  
public XPCollection<SlowOfferItem> OfferItems => GetCollection<SlowOfferItem>(nameof(OfferItems));  
}
public class SlowOfferItem : BaseObject
{ public SlowOfferItem(Session session) : base(session) { }  
private int _Hours;  
[Persistent("Hours")]  
public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }  
private SlowOffer _SlowOffer;  
[Persistent("SlowOffer"), Association]  
public SlowOffer SlowOffer { get => _SlowOffer; set => SetPropertyValue(nameof(SlowOffer), ref _SlowOffer, value); }  
}

39 are cheaper from maintenance perspective than

 [DefaultClassOptions]
[DefaultProperty(nameof(HourSum))]
public class SlowOffer : BaseObject
{ 
public SlowOffer(Session session) : base(session) { }  
private string _Name;  
[Persistent("Name")]  
public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }  
[NonPersistent]  
public int HourSum  
{  
    get  
    {  
        var sum = 0;  
        foreach (var item in OfferItems)  
        {  
            sum += item.Hours;  
        }  
        return sum;  
    }  
}  
[Association, Aggregated]  
public XPCollection<SlowOfferItem> OfferItems => GetCollection<SlowOfferItem>(nameof(OfferItems));  
}
public class SlowOfferItem : BaseObject
{ public SlowOfferItem(Session session) : base(session) { }  
private int _Hours;  
[Persistent("Hours")]  
public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }  
private SlowOffer _SlowOffer;  
[Persistent("SlowOffer"), Association]  
public SlowOffer SlowOffer { get => _SlowOffer; set => SetPropertyValue(nameof(SlowOffer), ref _SlowOffer, value); }  
}

38

I didn't even dig into execution plans or something special database wise. That is totally out of scope of this post. One thing I always recomend is: stick with the

[NonPersistent]  
// Sum up all the hours using Linq  
public int HourSum => OfferItems.Sum(m => m.Hours);

0

19.09.20 10:35:31.422 Executing sql 'select N0."Oid",N0."Name",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferWithLinq" N0 where N0."GCRecord" is null'  
19.09.20 10:35:31.425 Result: rowcount = 300, total = 11914, N0.{Oid,Guid} = 4800, N0.{Name,String} = 4714, N0.{OptimisticLockField,Int32} = 1200, N0.{GCRecord,Int32} = 1200  
19.09.20 10:35:31.426 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOfferWithLinq",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."SlowOfferWithLinq" = @p0))' with parameters {a917fb15-82e1-471e-95b3-031a070d492d}  
19.09.20 10:35:31.440 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOfferWithLinq,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
19.09.20 10:35:31.451 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOfferWithLinq",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."SlowOfferWithLinq" = @p0))' with parameters {b625d838-3747-4ad4-aaac-032e79a7d3a6}  
19.09.20 10:35:31.464 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOfferWithLinq,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000.  
.  
.  
.  
19.09.20 10:35:36.787 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOfferWithLinq",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItemWithLinq" N0 where (N0."GCRecord" is null and (N0."SlowOfferWithLinq" = @p0))' with parameters {e2774f41-7d31-4a5f-afa4-ffe8d3a21d47}  
19.09.20 10:35:36.803 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOfferWithLinq,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
19.09.20 10:35:36.851 Executing sql 'select top 1 count(*) from "dbo"."SlowOfferWithLinq" N0 where N0."GCRecord" is null'  
19.09.20 10:35:36.853 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4  
19.09.20 10:35:36.854 Executing sql 'select top 1 count(*) from "dbo"."SlowOfferItemWithLinq" N0 where N0."GCRecord" is null'  
19.09.20 10:35:36.877 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4

8 as long as you can, esp. for smaller record sets. It will perform really well, if you keep an eye on chatty requests (N+1).

We had no

[DefaultClassOptions]  
[DefaultProperty(nameof(HourSum))]  
public class SlowOffer : BaseObject  
{  
    public SlowOffer(Session session) : base(session) { }
    private string _Name;  
    [Persistent("Name")]  
    public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }
    [NonPersistent]  
    public int HourSum  
    {  
        get  
        {  
            var sum = 0;  
            foreach (var item in OfferItems)  
            {  
                sum += item.Hours;  
            }  
            return sum;  
        }  
    }
    [Association, Aggregated]  
    public XPCollection<SlowOfferItem> OfferItems => GetCollection<SlowOfferItem>(nameof(OfferItems));  
}
public class SlowOfferItem : BaseObject  
{  
    public SlowOfferItem(Session session) : base(session) { }
    private int _Hours;  
    [Persistent("Hours")]  
    public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }
    private SlowOffer _SlowOffer;  
    [Persistent("SlowOffer"), Association]  
    public SlowOffer SlowOffer { get => _SlowOffer; set => SetPropertyValue(nameof(SlowOffer), ref _SlowOffer, value); }  
}

43 whatsoever (except the default ones XPO creates for us). Databases are pretty damn fast.

Bonus round

Last test done with 3000

18.09.20 16:22:13.906 Executing sql 'select N0."Oid",N0."Name",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOffer" N0 where N0."GCRecord" is null'  
18.09.20 16:22:13.908 Result: rowcount = 300, total = 11996, N0.{Oid,Guid} = 4800, N0.{Name,String} = 4796, N0.{OptimisticLockField,Int32} = 1200, N0.{GCRecord,Int32} = 1200  
18.09.20 16:22:13.913 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {a786412c-907d-4b36-beb6-004395d38e9d}  
18.09.20 16:22:13.927 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:13.931 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {cf656095-dcd6-46f1-b830-00af607693a9}  
18.09.20 16:22:13.949 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
.  
.  
.  
.  
18.09.20 16:22:20.036 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {0be207ec-9430-44e7-964c-fd684303d051}  
18.09.20 16:22:20.053 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:20.057 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {3b48cf0a-d610-4586-8dfb-fde802777653}  
18.09.20 16:22:20.078 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:20.122 Executing sql 'select top 1 count(*) from "dbo"."SlowOffer" N0 where N0."GCRecord" is null'  
18.09.20 16:22:20.126 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4  
18.09.20 16:22:20.127 Executing sql 'select top 1 count(*) from "dbo"."SlowOfferItem" N0 where N0."GCRecord" is null'  
18.09.20 16:22:20.170 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4

8 and 3.000.000

18.09.20 16:22:13.906 Executing sql 'select N0."Oid",N0."Name",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOffer" N0 where N0."GCRecord" is null'  
18.09.20 16:22:13.908 Result: rowcount = 300, total = 11996, N0.{Oid,Guid} = 4800, N0.{Name,String} = 4796, N0.{OptimisticLockField,Int32} = 1200, N0.{GCRecord,Int32} = 1200  
18.09.20 16:22:13.913 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {a786412c-907d-4b36-beb6-004395d38e9d}  
18.09.20 16:22:13.927 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:13.931 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {cf656095-dcd6-46f1-b830-00af607693a9}  
18.09.20 16:22:13.949 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
.  
.  
.  
.  
18.09.20 16:22:20.036 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {0be207ec-9430-44e7-964c-fd684303d051}  
18.09.20 16:22:20.053 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:20.057 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {3b48cf0a-d610-4586-8dfb-fde802777653}  
18.09.20 16:22:20.078 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:20.122 Executing sql 'select top 1 count(*) from "dbo"."SlowOffer" N0 where N0."GCRecord" is null'  
18.09.20 16:22:20.126 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4  
18.09.20 16:22:20.127 Executing sql 'select top 1 count(*) from "dbo"."SlowOfferItem" N0 where N0."GCRecord" is null'  
18.09.20 16:22:20.170 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4

9 with the N+N query approach:

18.09.20 16:22:13.906 Executing sql 'select N0."Oid",N0."Name",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOffer" N0 where N0."GCRecord" is null'  
18.09.20 16:22:13.908 Result: rowcount = 300, total = 11996, N0.{Oid,Guid} = 4800, N0.{Name,String} = 4796, N0.{OptimisticLockField,Int32} = 1200, N0.{GCRecord,Int32} = 1200  
18.09.20 16:22:13.913 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {a786412c-907d-4b36-beb6-004395d38e9d}  
18.09.20 16:22:13.927 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:13.931 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {cf656095-dcd6-46f1-b830-00af607693a9}  
18.09.20 16:22:13.949 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
.  
.  
.  
.  
18.09.20 16:22:20.036 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {0be207ec-9430-44e7-964c-fd684303d051}  
18.09.20 16:22:20.053 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:20.057 Executing sql 'select N0."Oid",N0."Hours",N0."SlowOffer",N0."OptimisticLockField",N0."GCRecord" from "dbo"."SlowOfferItem" N0 where (N0."GCRecord" is null and (N0."SlowOffer" = @p0))' with parameters {3b48cf0a-d610-4586-8dfb-fde802777653}  
18.09.20 16:22:20.078 Result: rowcount = 1000, total = 44000, N0.{Oid,Guid} = 16000, N0.{Hours,Int32} = 4000, N0.{SlowOffer,Guid} = 16000, N0.{OptimisticLockField,Int32} = 4000, N0.{GCRecord,Int32} = 4000  
18.09.20 16:22:20.122 Executing sql 'select top 1 count(*) from "dbo"."SlowOffer" N0 where N0."GCRecord" is null'  
18.09.20 16:22:20.126 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4  
18.09.20 16:22:20.127 Executing sql 'select top 1 count(*) from "dbo"."SlowOfferItem" N0 where N0."GCRecord" is null'  
18.09.20 16:22:20.170 Result: rowcount = 1, total = 4, SubQuery(Count,,) = 4

2

This results in the following performance

Sort by

 [DefaultClassOptions]
[DefaultProperty(nameof(HourSum))]
public class SlowOfferWithLinq : BaseObject
{ 
public SlowOfferWithLinq(Session session) : base(session) { }  
private string _Name;  
[Persistent("Name")]  
public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }  
[NonPersistent]  
// Sum up all the hours using Linq  
public int HourSum => OfferItems.Sum(m => m.Hours);  
[Association, Aggregated]  
public XPCollection<SlowOfferItemWithLinq> OfferItems => GetCollection<SlowOfferItemWithLinq>(nameof(OfferItems));  
}
public class SlowOfferItemWithLinq : BaseObject
{ public SlowOfferItemWithLinq(Session session) : base(session) { }  
private int _Hours;  
[Persistent("Hours")]  
public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }  
private SlowOfferWithLinq _SlowOfferWithLinq;  
[Persistent("SlowOfferWithLinq"), Association]  
public SlowOfferWithLinq SlowOfferWithLinq { get => _SlowOfferWithLinq; set => SetPropertyValue(nameof(SlowOfferWithLinq), ref _SlowOfferWithLinq, value); }  
}

4: 0.920 seconds

Sort by

 [DefaultClassOptions]
[DefaultProperty(nameof(HourSum))]
public class SlowOfferWithLinq : BaseObject
{ 
public SlowOfferWithLinq(Session session) : base(session) { }  
private string _Name;  
[Persistent("Name")]  
public string Name { get => _Name; set => SetPropertyValue(nameof(Name), ref _Name, value); }  
[NonPersistent]  
// Sum up all the hours using Linq  
public int HourSum => OfferItems.Sum(m => m.Hours);  
[Association, Aggregated]  
public XPCollection<SlowOfferItemWithLinq> OfferItems => GetCollection<SlowOfferItemWithLinq>(nameof(OfferItems));  
}
public class SlowOfferItemWithLinq : BaseObject
{ public SlowOfferItemWithLinq(Session session) : base(session) { }  
private int _Hours;  
[Persistent("Hours")]  
public int Hours { get => _Hours; set => SetPropertyValue(nameof(Hours), ref _Hours, value); }  
private SlowOfferWithLinq _SlowOfferWithLinq;  
[Persistent("SlowOfferWithLinq"), Association]  
public SlowOfferWithLinq SlowOfferWithLinq { get => _SlowOfferWithLinq; set => SetPropertyValue(nameof(SlowOfferWithLinq), ref _SlowOfferWithLinq, value); }  
}

0: 0.876 seconds

Recap

There is no one size fits it all. Performance will always be hard. But I hope you learned some techniques to measure and improve your applications performance.

If you find interesting what I'm doing, consider becoming a patreon or contact me for training, development or consultancy.

mẹo hay Top List Top List