LINQ:“枚举未产生结果”

本文关键字：结果 枚举 LINQ | 更新日期: 2023-09-27 18:15:57

我有一个可怕的时间试图让LINQ语句工作

我已经尝试使用SQL语法和lambda以下这篇文章:

c# join/Where with Linq and Lambda

这是我的工作SQL看起来像:

SELECT ws_lookup_OccupationGroup.Code
FROM ws_lookup_OccupationGroup
INNER JOIN ws_lookup_Occupation ON
ws_lookup_OccupationGroup.Code = ws_lookup_Occupation.ws_lookup_OccupationGroup_Code
WHERE (ws_lookup_Occupation.Code = N'413')

这是我的第一次尝试，没有结果:

var query = from occupationGroup in db.ws_lookup_OccupationGroups 
            join occupations in db.ws_lookup_Occupations on occupationGroup.Code equals occupations.Code 
            where occupations.Code == model.Client.Client_Details_Enhanced.Occupation.Code 
            select new 
            { 
                OccupationGroup = occupationGroup, 
                Occupations = occupations 
            };

这是我第二次尝试使用Lamdba，也没有结果:

var queryLambda = db.ws_lookup_OccupationGroups
                    .Join(db.ws_lookup_Occupations, 
                          occupation => occupation.Code, 
                          occupationGroup => occupationGroup.Code,
                          (occupation, occupationGroup) => new 
                          { 
                              OCCUPATION = occupation, 
                              OCCUPATIONGROUP = occupationGroup 
                          })
                    .Where(all => all.OCCUPATION.Code == model.Client.Client_Details_Enhanced.Occupation.Code);

我就是不知道出了什么问题…

我不知道这是否有任何相关性，但我正在使用代码第一实体框架-他是我的职业团体模型&职业:

public class ws_lookup_OccupationGroup {
    [Key]
    [MaxLength(250)]
    public string Code { get; set; }
    [MaxLength(250)]
    public string Name { get; set; }
    public int SortOrder { get; set; }
    public List<ws_lookup_Occupation> Occupations { get; set; }
}
public class ws_lookup_Occupation {
    [Key]
    [MaxLength(10)]
    public string Code { get; set; }
    [MaxLength(250)]
    public string Name { get; set; }
    [MaxLength(250)]
    public string BarbadosMotorFactor { get; set; }
    [MaxLength(250)]
    public string TrinidadMotorFactor { get; set; }
    [MaxLength(250)]
    public string OtherRegionsMotorFactor { get; set; }
}

LINQ:“枚举未产生结果”

我不直接回答你的问题，而是提出一个策略建议。一种策略是添加一个扩展方法，该方法将显示实体框架查询或IQueryable将运行的SQL。这可以通过创建单元测试并执行测试驱动开发方法(TDD)的方式来完成。

你知道你想要得到预期结果的SQL。最好是与您的EF查询工作，直到您得到一个SQL，将提供您所追求的结果。你可以调试一个集成测试，然后朝着最终结果——你想要的SQL——用实体框架Linq到实体代码编写。

首先，我们可以创建以下扩展方法: 
public static class IQueryableExtensions
    {
        /// <summary>
        /// Shows the sql the IQueryable query will be generated into and executed on the DbServer
        /// </summary>
        /// <param name="query">The IQueryable to analyze</param>
        /// <param name="decodeParameters">Set to true if this method should try decoding the parameters</param>
        /// <remarks>This is the generated SQL query in use for Entity Framework</remarks>
        public static string ShowSql(this IQueryable query, bool decodeParameters = false)
        {
            var objectQuery = (ObjectQuery)query; 
            string result = ((ObjectQuery)query).ToTraceString();
            if (!decodeParameters)
                return result; 
            foreach (var p in objectQuery.Parameters)
            {
                string valueString = p.Value != null ? p.Value.ToString() : string.Empty;
                if (p.ParameterType == typeof(string) || p.ParameterType == typeof(DateTime))
                    valueString = "'" + valueString + "'";
                result = result.Replace("@" +p.Name, p.Value != null ? valueString : string.Empty); 
            }
            return result; 
        }     
}

然后我们需要一些集成测试，从我自己的系统中取样:

  [TestFixture]
    public class IqueryableExtensionsTest
    {
        [Test]
        public void QueryableReturnsSqlAndDoesNotThrow()
        {
            using (var dbContext = ObjectContextManager.ScopedOpPlanDataContext)
            {
                var operations = from operation in dbContext.Operations
                    where operation.Status == (int) OperationStatusDataContract.Postponed
                    && operation.OperatingDate >= new DateTime(2015, 2, 12)
                    select operation;
                string sql = operations.ShowSql();
                Assert.IsNotNull(sql);
            }
        }
    }

虽然你当然可以使用Linqpad来查找EF查询和SQL，但这种策略的好处是，你可以在Visual Studio中使用它来处理更复杂的现实世界场景，你也可以获得比在VS和Linqpad之间切换更好的调试体验。

如果您调试这样的集成测试，那么您可以看到生成的SQL。注意，您还可以执行Console。WriteLine或Debug。WriteLine来观察输出，如果您想运行集成测试而不是调试它。

在您的SQL中，您将加入以下

ws_lookup_OccupationGroup.Code = ws_lookup_Occupation.ws_lookup_OccupationGroup_Code

但是在Linq中你加入了

occupationGroup.Code equals occupations.Code

根据你的实体看起来像什么，我假设你确实需要这个

occupationGroup.Code = occupations.ws_lookup_OccupationGroup_Code

根据您的实体，看起来您可以使用导航属性而不是连接来执行以下操作

var query = from occupationGroup in db.ws_lookup_OccupationGroups 
            where occupationGroup.Occupations.Any(
                o => o.Code == model.Client.Client_Details_Enhanced.Occupation.Code) 
            select occupationGroup;

获取至少有一个职业具有所需代码的所有职业组。或者如果你只是想要组合group和occupation那么你可以输入

var query = from occupationGroup in db.ws_lookup_OccupationGroups 
            from occupation in occupationGroup.Occupations
            where occupation.Code == model.Client.Client_Details_Enhanced.Occupation.Code
            select new 
            {
                occupationGroup,
                occupation
            };
相关文章：