LINQ: "Enumeration yielded no results"

Question

I am having a terrible time trying to get a LINQ statement working.

I have tried using both SQL syntax and lambda following this post:

C# Joins/Where with Linq and Lambda

This is what my working SQL looks like:

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')

This is my first attempt and it yields no results:

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 
            };

Here is my second attempt using Lamdba which also yields no results:

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);

I just cannot see what is going wrong...

I don't know is this has any relevance but I am using Code First Entity Framework - he is my model for OccupationGroups & Occupations:

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; }
}

Answer 1

Instead of directly answering your question I will rather come with a suggestion of strategy. One strategy then is to add an extension method that will reveal the SQL your Entity Framework query or IQueryable will run. This can be done in such a manner that you create a unit test and do a Test Driven Development approach or TDD.

You know the SQL you want to get the expected result. It is better then to work with your EF query until you get a SQL that will deliver the result you are after. You can debug an integration test and then work your way towards the end result - the SQL you are after - written in Entity Framework Linq to Entities code.

First off, we can create the following extension method:

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; 
        }     

}

Then we need some integration test, sample from my own system:

  [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);
            }
        }

    }

While you can of course use Linqpad to find the EF query and SQL you are after for, the benefit of this strategy is that you can use it inside Visual Studio for the more complex real world scenarios, you also get a better debugging experience than switching between VS and Linqpad.

If you debug such an integration test you can then watch the SQL being generated. Note that you also can do Console.WriteLine or Debug.WriteLine to watch the output if you want to run the Integration Test and not debug it.

Answer 2

In your SQL you are joining on the following

ws_lookup_OccupationGroup.Code = ws_lookup_Occupation.ws_lookup_OccupationGroup_Code

But in the Linq you join on

occupationGroup.Code equals occupations.Code

Depending on exactly what your entity looks like I would assume you actually need this

occupationGroup.Code = occupations.ws_lookup_OccupationGroup_Code

Based on your entity it looks like you can do the following with navigation properties instead of joins

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;

To get all the occupation groups that have at least one occupation with the desired code. Or if you just want a combination of group and occupation then you could do

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
            };