I used the RAD tools to generate an entity, then migrated over 3 million records into the generated table. Now the app hangs attempting to retrieve a 10 record page of results. I added a logger to the DBContext and found that it is waiting for a long running query.
SELECT [e].*
FROM [MyEntityTable] AS [e]
LEFT JOIN (
SELECT [e0].*
FROM [MyOtherEntityTable] AS [e0]
WHERE ([e0].[IsDeleted] = 0) OR ([e0].[IsDeleted] <> 'True')
) AS [t] ON [e].[DocumentId] = [t].[Id]
WHERE ([e].[IsDeleted] = 0) OR ([e].[IsDeleted] <> 'True')
The raw SQL query looks weird since I am only going after a page of data (ie. not the entire table). Other captured queries are including pagination in the query, like this one for AbpUserAccounts which includes ORDER BY and OFFSET clauses.
SELECT [e].[UserId] AS [Id], [e].[TenantId], CASE
WHEN [t].[Id] IS NULL
THEN N'.' ELSE [t].[TenancyName]
END AS [TenancyName], [e].[UserName], [e].[LastLoginTime]
FROM [AbpUserAccounts] AS [e]
LEFT JOIN (
SELECT [e0].*
FROM [AbpTenants] AS [e0]
WHERE ([e0].[IsDeleted] = 0) OR ([e0].[IsDeleted] <> @__ef_filter__IsSoftDeleteFilterEnabled_1)
) AS [t] ON [e].[TenantId] = [t].[Id]
WHERE (([e].[IsDeleted] = 0) OR ([e].[IsDeleted] <> @__ef_filter__IsSoftDeleteFilterEnabled_0)) AND ((((([e].[TenantId] <> @__currentUserIdentifier_TenantId_0) OR [e].[TenantId] IS NULL) OR ([e].[UserId] <> @__currentUserIdentifier_UserId_1)) AND [e].[UserLinkId] IS NOT NULL) AND [e].[UserLinkId] IS NULL)
ORDER BY [e].[UserName]
OFFSET @__p_3 ROWS FETCH NEXT @__p_4 ROWS ONLY
I noticed these warnings which could be the culprit.
Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression 'orderby __ObjectMapper_0.Map([e]).Status desc' could not be translated and will be evaluated locally.
Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression 'Skip(__p_1)' could not be translated and will be evaluated locally.
Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression 'Take(__p_2)' could not be translated and will be evaluated locally.
It looks like the query is running without pagination against the database, and then applied once the data is loaded into application memory. This agrees with my observation of the app's memory usage while waiting for this query to return.
I found this generated code for my entity which I believe should take care of pagination, but instead seems to generate the warnings above.
var myEntities = await query
.OrderBy(input.Sorting ?? "myEntity.id asc")
.PageBy(input)
.ToListAsync();
Any idea why these LINQ expressions could not be translated?
4 Answer(s)
-
0
Try:
- .OrderBy(input.Sorting ?? "myEntity.id asc") + .OrderBy(input.Sorting ?? "Id asc") -
0
Thanks for the tip, but it didn't work. Here is the error I get in the logs after making the suggested change.
No property or field 'Id' exists in type 'GetDocumentForView' (at index 0)I was able to solve this with the following workaround. It looks like LINQ doesn't like the ObjectMapper.Map usage. Here is the original GetAll method for Documents.
public async Task<PagedResultDto<GetDocumentForView>> GetAll(GetAllDocumentsInput input) { var filteredDocuments = _documentRepository.GetAll() .WhereIf(!string.IsNullOrWhiteSpace(input.Filter), e => false) .WhereIf(input.MinStatusFilter != null, e => e.Status >= input.MinStatusFilter) .WhereIf(input.MaxStatusFilter != null, e => e.Status <= input.MaxStatusFilter); var query = from o in filteredDocuments select new GetDocumentForView() { Document = ObjectMapper.Map<DocumentDto>(o) }; var totalCount = await query.CountAsync(); var documents = await query .OrderBy(input.Sorting ?? "document.id asc") .PageBy(input) .ToListAsync(); return new PagedResultDto<GetDocumentForView>( totalCount, documents ); }Once I replaced the ObjectMapper code with a DTO construction specific to the Document entity, the LINQ to SQL translation went through. So instead of doing this...
var query = from o in filteredDocuments select new GetDocumentForView() { Document = ObjectMapper.Map<DocumentDto>(o) };...I did this...
var query = from o in filteredDocuments select new GetDocumentForView() { Document = new DocumentDto() { Id = o.Id, Status = o.Status } };This seems to work just fine, but it does require more code.
Is there a better way to do this that I am not seeing?
-
0
Use AutoMappers ProjectTo extension?
-
0
@csegin,
For such kind of large data, we suggest you to write the query by yourself.
