Sometimes it's better to have multiple CTEs with smaller footprint, then have the cross product of all the datasets in one huge CTE.
These changes allow the user to call FromLocalList on the DbContext multiple times, with different model classes (using different table attributes).
The changes were tested on SqlServer.
Just an example:
Let's say you want to find the entry logs of selected employees for given dates and location codes.
You have a set of 500 employee IDs, 30 dates and 5 office location codes. The result dataset would contain 75000 entries in one CTE. Obviously that's too much to have in one query.
With the changes the query would have 3 CTEs with element count of 500, 30 and 5. The result is a much smaller query.
Sometimes it's better to have multiple CTEs with smaller footprint, then have the cross product of all the datasets in one huge CTE. These changes allow the user to call FromLocalList on the DbContext multiple times, with different model classes (using different table attributes). The changes were tested on SqlServer.
Just an example: Let's say you want to find the entry logs of selected employees for given dates and location codes. You have a set of 500 employee IDs, 30 dates and 5 office location codes. The result dataset would contain 75000 entries in one CTE. Obviously that's too much to have in one query. With the changes the query would have 3 CTEs with element count of 500, 30 and 5. The result is a much smaller query.