I have an ASP.NET Blazor app that uses a Telerik grid to display transaction data. As the grid needs to show a lot of related information (source of the transaction, including details about the payment terminal it was made on, who that was rented from and so on), there are quite a lot of .Include() clauses on the query.
For example, if the grid's page size is set to 30, then the following query will pull 30 rows from the database...
Data = ctx.Transactions
.OrderByDescending(a => a.Date)
.AsQueryable();
However, if I add some related data (not all shown)...
Data = ctx.Transactions
.Include(t => t.Card)
.Include(t => t.Device)
.ThenInclude(d => d.DistributorRentals)
.Include(t => t.Device)
.ThenInclude(d => d.CauseRentals)
.ThenInclude(cr => cr.CharityAccount)
.ThenInclude(ca => ca.Charity)
.OrderByDescending(a => a.Date)
.AsQueryable();
...then the number of rows pulled from the database (as seen in SQL Server Profiler) jumps up significantly.
I need the query to return an IQueryable<T> as the grid requires this so it can handle the paging and filtering. Simplified grid markup is as follows (most columns for related data removed for clarity)...
<TelerikGrid Data="Data"
ScrollMode="@GridScrollMode.Virtual"
Height="800px"
RowHeight="40"
PageSize="30">
<GridColumns>
<GridColumn Field="Date" Title="Date" Width="210px" />
<GridColumn Field="Amount" Width="180px" />
</GridColumns>
</TelerikGrid>
I I didn't need to use an IQueryable<T>, I would cast the data to an anonymous type before enumerating, which would cut down the number of rows. However, that would require me to write an enormous amount of code to handle the filtering and sorting that the grid does for me. Casting the data into a concrete type (which would be required to use IQueryable<T>) can't be done in the database (as it won't know about my C# type), so I'd have to enumerate the query before casting, which then removes all the advantages of using an IQueryable<T>.
Anyone any idea how I can solve this? I need the included data, but don't want so many rows puled back from the database.
That is the Cartesian Product which will happen whenever you Join tables in a query. Not only does this increase the depth of the results, (# of rows) it also increases the breadth of the results. (# of columns) This leads to huge blocks of data.
One option available in EF Core is to use the Split Query option, however there are some significant limitations to this option that you will possibly run into when using pagination. This options splits the queries up so that it will execute a query per entity/table, but this can lead to problems around ensuring that sorted results and associations are linked up correctly. It solves the depth issue, but still results in a lot of data coming back as you are fetching every Included entity in its entirety.
The best way to mitigate this issue when it comes to things like search results is to use projection with a view model. What fields from what entities do you actually need? There may be situations where you might want a count, or check for the existence of a row, or just return one or the top few associated rows. All of this can be trimmed down into a projection of a view model or graph of view models which is much smaller than the sum of the tables used to populate it. Use Select or Automapper's ProjectTo to populate a view model with just the details needed for the results.
Then when you want to open/work on a particular Transaction you can fetch that Tx with all of the details included, pulling the single Tx graph from the database by ID.