在我的Azure托管的ASP.NET核心站点中,我有一个用户表,我实现了如下搜索：
var inner = from user in db.Users
select new
{
Name = user.Name,Verified = user.Verified,PhotoURL = user.PhotoURL,UserID = user.Id,Subdomain = user.Subdomain,Deleted=user.Deleted,AppearInSearch = user.AppearInSearch
};
return await inner.Where(u=>u.Name.Contains(name)&& !u.Deleted && u.AppearInSearch)
.OrderByDescending(u => u.Verified)
.Skip(page * recordsInPage)
.Take(recordsInPage)
.Select(u => new UserSearchResult()
{
Name = u.Name,Verified = u.Verified,PhotoURL = u.PhotoURL,UserID = u.UserID,Subdomain = u.Subdomain
}).ToListAsync();
SELECT [t].[Name],[t].[Verified],[t].[PhotoURL],[t].[Id],[t].[Subdomain],[t].[Deleted],[t].[AppearInSearch]
FROM (
SELECT [user0].[Name],[user0].[Verified],[user0].[PhotoURL],[user0].[Id],[user0].[Subdomain],[user0].[Deleted],[user0].[AppearInSearch]
FROM [AspNetUsers] AS [user0]
WHERE (((CHARINDEX('khaled',[user0].[Name]) > 0) OR ('khaled' = N''))
AND ([user0].[Deleted] = 0))
AND ([user0].[AppearInSearch] = 1)
ORDER BY [user0].[Verified] DESC
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY ) AS [t]
如果搜索项在数据库中可用,则结果将在不到一秒的时间内获得.
但是,如果没有找到该查询运行很长时间(我已经看到它一次达到48秒).
谢谢
更新：此问题在此处继续：Empty Login Name When Showing sys.processes
解决方法
您已经可以像这样简化查询;)：
int start=page * recordsInPage;
var inner = (from user in db.Users
where user.Name.Contains(name) && !user.Deleted && user.AppearInSearch
orderby user.Verified descending
select new
{
Name = user.Name,AppearInSearch = user.AppearInSearch
}
).Skip(start).Take(recordsInPage);
return await inner.ToListAsync();
