时间戳

首页 > 解决方案 > 实体框架中的 Linq group by

c# - 实体框架中的 Linq group by

问题描述

我目前正在尝试将一些代码从使用 Linq 的系统移植到 SQL 到 Entity Framework Core 2.0。下面的代码在 Linq to SQL 中工作，但在 EF 中不起作用。

我究竟做错了什么？

int lnScore1 = 0;
int lnScore2 = 0;
int lnScore3 = 0;

try
{
    var loqs = from r in _context.tblemployee_incidents
               join c in _context.tblemployees on r.diEmployeeID equals c.diID
           join c3 in _context.tbl_config_event_categories on r.dnCategory3 equals c3.tiID into j7
           from c3 in j7.DefaultIfEmpty()
           where c.dbDeleted == false
           where r.diAppID == 1 && r.dbDeleted == false
           group c3 by new { c.diID } into g
           select new
           {
               dnEmployee = g.Key.diID,
               dnWeight = g.Sum(ity => ity.tnMobileWeight)
           };

    lnScore1 = loqs.Count(x => x.dnEmployee > 0 && (x.dnWeight > 0 && x.dnWeight < 30));
    lnScore2 = loqs.Count(x => x.dnEmployee > 0 && (x.dnWeight >= 30 && x.dnWeight < 40));
    lnScore3 = loqs.Count(x => x.dnEmployee > 0 && (x.dnWeight >= 40));
}
catch (Exception ex) { }

谢谢！

@DavidG对不起，我打算输入错误。

是的，当第一个 loqs.Count 被调用时，我得到了一个例外：

{System.NullReferenceException: Object reference not set to an instance of an object.
at lambda_method(Closure , tbl_config_event_categories )
at System.Linq.Enumerable.SelectIListIterator`2.MoveNext()
at System.Linq.Enumerable.Sum(IEnumerable`1 source)
at lambda_method(Closure , IGrouping`2 )
at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.GetCount(Boolean onlyIfCheap)
at System.Linq.Enumerable.Count[TSource](IEnumerable`1 source)
at lambda_method(Closure , QueryContext )
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass17_1`1.<CompileQueryCore>b__0(QueryContext qc)
at System.Linq.Queryable.Count[TSource](IQueryable`1 source, Expression`1 predicate)
at T9.Web.Controllers.HomeController.Index()

标签： c#entity-frameworklinq

解决方案

我终于弄明白了——不知道为什么在 LinqPad 和 Linq to SQL 中没问题，但在 EF .Net Core 中失败了。

如果我添加该行：

                       where c3.tnMobileWeight != null

在第二个 where 语句之后-一切正常。

IE：

int lnScore1 = 0;
int lnScore2 = 0;
int lnScore3 = 0;
try
{
    var loqs = from r in _context.tblemployee_incidents
           join c in _context.tblemployees on r.diEmployeeID equals c.diID
       join c3 in _context.tbl_config_event_categories on r.dnCategory3 equals c3.tiID into j7
       from c3 in j7.DefaultIfEmpty()
       where c.dbDeleted == false
       where r.diAppID == 1 && r.dbDeleted == false
       where c3.tnMobileWeight != null
       group c3 by new { c.diID } into g
       select new
       {
           dnEmployee = g.Key.diID,
           dnWeight = g.Sum(ity => ity.tnMobileWeight)
       };

    lnScore1 = loqs.Count(x => x.dnEmployee > 0 && (x.dnWeight > 0 && x.dnWeight < 30));
    lnScore2 = loqs.Count(x => x.dnEmployee > 0 && (x.dnWeight >= 30 && x.dnWeight < 40));
    lnScore3 = loqs.Count(x => x.dnEmployee > 0 && (x.dnWeight >= 40));
}
catch (Exception ex) { }

推荐阅读