Entity Framework Core的坑，Select后再对导航属性进行查询或Select前进行Skip/Take

Posted on 2019-05-21 17:29 火冰·瓶阅读(2082) 评论(0) 编辑收藏举报

把asp.net core的项目发布到ubuntu上了，运行的时候出现了如下警告：

warn: Microsoft.EntityFrameworkCore.Query[20500]
The LINQ expression 'where [g.ResBaseInfo].ResType.Equals(Hotel)' could not be translated and will be evaluated locally.
warn: Microsoft.EntityFrameworkCore.Query[20500]
The LINQ expression 'Skip(__p_3)' could not be translated and will be evaluated locally.
warn: Microsoft.EntityFrameworkCore.Query[20500]
The LINQ expression 'Take(__p_4)' could not be translated and will be evaluated locally.
warn: Microsoft.EntityFrameworkCore.Query[10106]
The Include operation for navigation '[g].ResBaseInfo' is unnecessary and was ignored because the navigation is not reachable in the final query results. See https://go.microsoft.com/fwlink/?linkid=850303 for more information.
warn: Microsoft.EntityFrameworkCore.Query[20500]
The LINQ expression 'where [g.ResBaseInfo].ResType.Equals(Hotel)' could not be translated and will be evaluated locally.
warn: Microsoft.EntityFrameworkCore.Query[20500]
The LINQ expression 'Count()' could not be translated and will be evaluated locally.

本来打算不理会的，但是仔细看了下，大概的意思是会把所有数据拉到本地再进行查询处理，如果数据量大了，服务器肯定遭不住，还是分析代码找到了原因。

错误在于先在一个公共方法里select出了基础数据，然后在Controller中再次根据条件进行where，如果where的是导航属性中的属性，就会报上面的警告。修改了代码，把对导航属性的where语句放到select前，问题解决。

public IQueryable<ResBaseInfoView> GetResBaseInfoViewQueryable(Lang lang, ResType resType = 0)
{
    var jquery = _context.ResBaseInfoMultipleLanguage
        //.Include(g => g.ResBaseInfo)
        //.ThenInclude(g => g.AreaCode)
        .Where(g => !g.ResBaseInfo.IsDelete && g.Language.Equals(lang));
 
    if (resType != 0)
    {
        jquery = jquery.Where(g => g.ResBaseInfo.ResType == resType);
    }
 
    var result = jquery
        .Select(rbml => new ResBaseInfoView
        {
            ID = rbml.ResBaseInfoID,
            AddAccountID = rbml.ResBaseInfo.AddAccountID,
            AreaCodeID = rbml.ResBaseInfo.AreaCodeID,
            IsDelete = rbml.ResBaseInfo.IsDelete,
            Language = rbml.Language,
            MLID = rbml.MLID,
            UpdateTime = rbml.ResBaseInfo.UpdateTime,
            ImageName = rbml.ResBaseInfo.ImageName,
            Name = rbml.Name,
            ResType = rbml.ResBaseInfo.ResType,
            ResTypeName = rbml.ResBaseInfo.ResType.GetDescription(lang),
            AreaName = rbml.ResBaseInfo.AreaCode.GetML_Name(lang),
            Address = rbml.Address,
            AddTime = rbml.ResBaseInfo.AddTime,
            Lng = rbml.ResBaseInfo.Lng,
            Lat = rbml.ResBaseInfo.Lat,
            ZoomLevel = rbml.ResBaseInfo.ZoomLevel,
            IsShow = rbml.ResBaseInfo.IsShow,
            MobilePhone = rbml.MobilePhone,
            Telephone = rbml.Telephone,
            Sequence = rbml.ResBaseInfo.Sequence
        });
 
    return result;
}