Relation to #6643 RAD Issues. SQL (to entities) Selecting entire table (instead of only the page) #7251

The original question can be viewed at issue #6643. My apologies for missing that communication, but here my reply... Here is the question/statement once again below for issues #6643:

**The view table page have a problem after generated with the RAD Tool. Each record being queried are inserted into a GetXXXXXXXForViewDto object before it is sorted / paged. Problem with this is that it needs to get ALL the records in the table into these objects before it can sort / page the data. Sorting is then also done incorrectly on the GetXXXXXXXForViewDto object's inner object instead of directly on the entity.

**I have to change this everytime it re-generates to only put the record into GetXXXXXXXForViewDto object AFTER the sorting and paging happened. I am doing my own sorting / paging manually (SQL LINQ query using OrderBy / OrderByDescending with Skip / Take) so that it only queries that page of data from the DB.

Initially performance is not a problem here but as the table grows the problem grows with it. I actually found this issue by changing the AppService to point to a totally different DB table that is very big. Off course the ability to point to a differtent Database table in the RAD tool is a different problem...****

A question was then directed to me from ASPNET support:

Can you show example code before and after your change?

Example of change to the GetAll function after using the RAD tool - to query only the page of data, and not the entire table:

	public async Task<PagedResultDto<GetSageApiLogForViewDto>> GetAll(GetAllSageApiLogsInput input)
     {
			
	    var filteredSageApiLogs = _sageApiLogRepository.GetAll()
						.WhereIf(!string.IsNullOrWhiteSpace(input.Filter), e => false  || e.Message.Contains(input.Filter) || e.MessageTemplate.Contains(input.Filter) || e.Level.Contains(input.Filter) || e.Exception.Contains(input.Filter) || e.Properties.Contains(input.Filter) || e.RequestJson.Contains(input.Filter) || e.ResponseJson.Contains(input.Filter) || e.Origin.Contains(input.Filter))
						.WhereIf(!string.IsNullOrWhiteSpace(input.MessageFilter),  e => e.Message.ToLower().Contains(input.MessageFilter.ToLower().Trim()))
						.WhereIf(!string.IsNullOrWhiteSpace(input.MessageTemplateFilter),  e => e.MessageTemplate.ToLower().Contains(input.MessageTemplateFilter.ToLower().Trim()))
						.WhereIf(!string.IsNullOrWhiteSpace(input.LevelFilter),  e => e.Level.ToLower().Contains(input.LevelFilter.ToLower().Trim()))
						.WhereIf(input.MinTimeStampFilter != null, e => e.TimeStamp >= input.MinTimeStampFilter)
						.WhereIf(input.MaxTimeStampFilter != null, e => e.TimeStamp <= input.MaxTimeStampFilter)
						.WhereIf(!string.IsNullOrWhiteSpace(input.ExceptionFilter),  e => e.Exception.ToLower().Contains(input.ExceptionFilter.ToLower().Trim()))
						.WhereIf(!string.IsNullOrWhiteSpace(input.PropertiesFilter),  e => e.Properties.ToLower().Contains(input.PropertiesFilter.ToLower().Trim()))
						.WhereIf(!string.IsNullOrWhiteSpace(input.RequestJsonFilter),  e => e.RequestJson.ToLower().Contains(input.RequestJsonFilter.ToLower().Trim()))
						.WhereIf(!string.IsNullOrWhiteSpace(input.ResponseJsonFilter),  e => e.ResponseJson.ToLower().Contains(input.ResponseJsonFilter.ToLower().Trim()))
						.WhereIf(input.MinResponseCodeFilter != null, e => e.ResponseCode >= input.MinResponseCodeFilter)
						.WhereIf(input.MaxResponseCodeFilter != null, e => e.ResponseCode <= input.MaxResponseCodeFilter)
						.WhereIf(!string.IsNullOrWhiteSpace(input.OriginFilter),  e => e.Origin.ToLower().Contains(input.OriginFilter.ToLower().Trim()));


            //var query = (from o in filteredSageApiLogs
            //                      select new GetSageApiLogForViewDto() {
            //				SageApiLog = ObjectMapper.Map<SageApiLogDto>(o)
            //			});

             var totalCount = await filteredSageApiLogs.CountAsync();

             if (string.IsNullOrWhiteSpace(input.Sorting))
                 input.Sorting = "TimeStamp desc";
             bool asc;
             string columnSort = GetColumnName(input.Sorting, out asc);
             columnSort += " " + (asc
                               ? "asc"
                               : "desc");

             List<SageApiLog> sageApiLogs = await filteredSageApiLogs
                 .OrderBy(columnSort)
                 .Skip(input.SkipCount)
                 .Take(input.MaxResultCount)
                 .ToListAsync();

            return new PagedResultDto<GetSageApiLogForViewDto>(
                totalCount,
                sageApiLogs.Select(x => new GetSageApiLogForViewDto
                {
                    SageApiLog = ObjectMapper.Map<SageApiLogDto>(x)
                }).ToList().AsReadOnly()
            );
         }

        public static string GetColumnName(string sortExpression, out bool asc)
        {
            string[] sortParts = sortExpression.Split(' ');
            if (sortParts.Length == 0)
                throw new NotImplementedException("Sort expression cannot be null");
            string columnName = sortParts[0];
            string[] columnNameParts = columnName.Split('.');
            columnName = columnNameParts[columnNameParts.Length - 1];

            if (sortParts.Length == 1)
                asc = true;
            else
                asc = !sortParts[1].ToUpper().Equals("DESC");

            return columnName;
        }

After using the RAD tool, the above code is what I had to implement to ensure that the query will only select the current page data records, and not the entire table everytime GetAll is ran. I had to run the query without the view model (GetSageApiLogForViewDto) and then only map the data entity to the dto view model after the data came from the DB (for only the relevant page). You will notice the original code commented out in the example above - that piece of code is the problem because the generated code is trying to sort (and skip/take) the dto instead of the entity object (which does a proper sort, skip, take directly on the DB using SQL when done correctly).

Please update your RAD tool to fix this problem. The GetAll method does surely sound like it should query the entire table, but in actual fact only one page of data is displayed, so only one page of data must be queried from the DB as well for numerous reasons, like better performance, less bandwidth/network load etc. You may need to provide a better solution than what I did with the GetColumnName method, I needed to provide the proper sort expression that entities to SQL uses to sort the data directly on the DB using SQL.