c# – Linq选择在内存中执行的不同计数

概述我正在努力了解LINQ如何转换为SQL. 我有以下查询,我正在尝试使用LINQ生成. SELECT [OrganizationId] ,[DepartmentId] ,[LocationName] ,[Group1] ,[Group2] ,[Group3] ,[BooklistId] ,[BooklistName] ,COUNT(DISTINCT [OrdererI 我正在努力了解liNQ如何转换为sql.

我有以下查询,我正在尝试使用liNQ生成.

SELECT [OrganizationID],[DepartmentID],[Locationname],[Group1],[Group2],[Group3],[BookListID],[BookListname],COUNT(disTINCT [OrdererID]),MAX([ExpectedDate])FROM [Orders]WHERE ([DepartmentID] IS NOT NulL AND ([DepartmentID] = '<GuID>')) AND ([Schoolyear] = '2018')GROUP BY [OrganizationID],[BookListname]ORDER BY [BookListname]

使用索引,此查询的执行时间低于200毫秒

我的liNQ查询如下：

await _context            .Orders            .Where(i => i.DepartmentID != null && i.DepartmentID.Equals(Parameters.DepartmentID))            .Where(i => i.SchoolYear.Equals(Parameters.SchoolYear))            // Group the data.            .GroupBy(orders => new            {                orders.BookListID,orders.BookListname,orders.OrganizationID,orders.DepartmentID,orders.Locationname,orders.Groep1,orders.Groep2,orders.Groep3            })            .OrderBy(i => i.Key.BookListname)            .Select(i => new BookListviewmodel            {                Count = i.Select(orders => orders.OrdererID).distinct().Count(s => s != null),ID = i.Key.OrganizationID,name = i.Key.BookListname,Locationname = i.Key.Locationname,Number = i.Key.BookListID,Group1 = i.Key.Group1,Group2 = i.Key.Group2,Group3 = i.Key.Group3,DepartmentID = i.Key.DepartmentID,ExpectedDate = i.Max(orders => orders.ExpectedDate)            })            .ToListAsync();

但是我一直看到：

Microsoft.EntityFrameworkCore.query:Warning: The liNQ Expression ‘GroupBy(new <>f__AnonymousType1`8(BookListID = [i].BookListID,BookListname = [i].BookListname,OrganizationID = [i].OrganizationID,DepartmentID = [i].DepartmentID,Locationname = [i].Locationname,Group1 = [i].Group1,Group2 = [i].Group2,Group3 = [i].Group3),[i])’ Could not be translated and will be evaluated locally.

Microsoft.EntityFrameworkCore.query:Warning: The liNQ Expression ‘distinct()’ Could not be translated and will be evaluated locally.

Microsoft.EntityFrameworkCore.query:Warning: The liNQ Expression ‘where ([s] != null)’ Could not be translated and will be evaluated locally.

Microsoft.EntityFrameworkCore.query:Warning: The liNQ Expression ‘Count()’ Could not be translated and will be evaluated locally.

任何人都可以告诉我为什么liNQ查询在内存中执行？我需要在liNQ查询中更改以获得我想要的结果？

解决方法 查询正在内存中执行,因为您使用该语句实例化了BookListviewmodel对象的集合

.Select(i => new BookListviewmodel            {...})

如果您只是删除类BookListviewmodel,linq将执行在db端执行查询(这是一个好主意,因为优化器更有效),像这样……

.Select(i => new            {                Count = i.Select(orders => orders.OrdererID).distinct().Count(s => s != null),i.Key.OrganizationID,i.Key.BookListname,i.Key.Locationname,i.Key.BookListID,i.Key.Group1,i.Key.Group2,i.Key.Group3,i.Key.DepartmentID,ExpectedDate = i.Max(orders => orders.ExpectedDate)            })

然后你可以在最后实例化你的集合,所以整个事情看起来像这样……

await _context            .Orders            .Where(i => i.DepartmentID != null && i.DepartmentID.Equals(Parameters.DepartmentID))            .Where(i => i.SchoolYear.Equals(Parameters.SchoolYear))            // Group the data.            .GroupBy(orders => new            {                orders.BookListID,orders.Group1,orders.Group2,orders.Group3            })            .OrderBy(i => i.Key.BookListname).Select(i => new            {                Count = i.Select(orders => orders.OrdererID).distinct().Count(s => s != null),ExpectedDate = i.Max(orders => orders.ExpectedDate)            })            .Select(i => new BookListviewmodel            {                Count = i.Count,ID = i.ID,name = i.name,Locationname = i.Locationname,Number = i.Number,Group1 = i.Group1,Group2 = i.Group2,Group3 = i.Group3,DepartmentID = i.DepartmentID,ExpectedDate = i.ExpectedDate            })            .ToListAsync();

总结

以上是内存溢出为你收集整理的c# – Linq选择在内存中执行的不同计数全部内容，希望文章能够帮你解决c# – Linq选择在内存中执行的不同计数所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。