Hi there, I am using Linq to LLBLGen because I prefer how close it remains to SQL syntax (which I consider user friendly). I am attempting to query from an MSSQL database, but now that I have rewritten a query suddenly my use case fails. It appears that eager loading does not work if you don't directly use the IQueryable<T> output.

This is the query I had before:

        IQueryable<ProcessStepEntity> query =
            from ps in linqMetaData.ProcessStep
                .With(e => e.Process
                        .With(f => f.ProcessType
                            .With(g => g.ProcessExtraFields)),
                    e => e.TaskEmployeeProcessExtraField,
                    e => e.Task)
            where ps.Guid == processStepGuid
            select ps;

        return query.FirstOrDefault();

I need to retrieve a ProcessStep record with some related Entities (nested). The .With() syntax has worked for me so far and populates the resulting Entity with nexted properties properly.

Now I wanted to optimize and retrieve some more related data, so I added some left joins on the original (ProcessStep) Entity. I haven't touched the .With() syntax.

        IQueryable<ProcessStepContext> query =
            // current Process Step with related Entities
            from cps in linqMetaData.ProcessStep
                .With(e => e.Process
                        .With(f => f.ProcessType
                            .With(g => g.ProcessExtraFields)),
                    e => e.TaskEmployeeProcessExtraField,
                    e => e.Task)
            // fetch the yes next Process Step by type
            join yps in linqMetaData.ProcessStep
                on new { cps.ProcessGuid, TypeGuid = cps.YesTypeStepGuid.GetValueOrDefault() }
                equals new { yps.ProcessGuid, TypeGuid = yps.ProcessTypeStepGuid }
                into ypsJoin
            from yps in ypsJoin.DefaultIfEmpty()
            // fetch the no next Process Step by type
            join nps in linqMetaData.ProcessStep
                on new { cps.ProcessGuid, TypeGuid = cps.NoTypeStepGuid.GetValueOrDefault() }
                equals new { nps.ProcessGuid, TypeGuid = nps.ProcessTypeStepGuid }
                into npsJoin
            from nps in ypsJoin.DefaultIfEmpty()
            where cps.Guid == processStepGuid
            select new ProcessStepContext(cps, yps, nps);

        return query.First();

The resulting Entity (current Process Step) which should be the same as it was before, now no longer respects the eager loading and all its related Entities (e.g. Process) are null. This breaks the existing code (the difference between First() and FirstOrDefault() in the return statement is not relevant because this always was a fetch 1 query with exactly 1 result).

The output object is a simple POCO with nullable properties for the left join Entities. My expectation was that currentProcessStep would be populated as before, but this is not the case.

public class ProcessStepContext(
    ProcessStepEntity currentProcessStep,
    ProcessStepEntity? yesProcessStepEntity,
    ProcessStepEntity? noProcessStepEntity)
{
    public ProcessStepEntity CurrentProcessStep { get; } = currentProcessStep;
    public ProcessStepEntity? YesProcessStepEntity { get; } = yesProcessStepEntity;
    public ProcessStepEntity? NoProcessStepEntity { get; } = noProcessStepEntity;
}

What am I missing in my usage of the .With() syntax? I would prefer not to use prefetch paths or other LLBL features and stick with the Linq syntax, so I would like to find out how to query this using only the Linq syntax. I have tried using an anon object as output

 select new (cps, yps, nps);

and then casting it (has worked for me before), but this also does not properly populate the related Entities. What am I missing?

Added:

I have outcommented the left joins, it still returns a cps with all related Entities being null:

```cs

    IQueryable<ProcessStepContext> query =
        // current Process Step with related Entities
        from cps in linqMetaData.ProcessStep
            .With(e => e.Process
                    .With(f => f.ProcessType
                        .With(g => g.ProcessExtraFields)),
                e => e.TaskEmployeeProcessExtraField,
                e => e.Task)
        //// fetch the yes next Process Step by type
        //join yps in linqMetaData.ProcessStep
        //    on new { cps.ProcessGuid, TypeGuid = cps.YesTypeStepGuid.GetValueOrDefault() }
        //    equals new { yps.ProcessGuid, TypeGuid = yps.ProcessTypeStepGuid }
        //    into ypsJoin
        //from yps in ypsJoin.DefaultIfEmpty()
        //// fetch the no next Process Step by type
        //join nps in linqMetaData.ProcessStep
        //    on new { cps.ProcessGuid, TypeGuid = cps.NoTypeStepGuid.GetValueOrDefault() }
        //    equals new { nps.ProcessGuid, TypeGuid = nps.ProcessTypeStepGuid }
        //    into npsJoin
        //from nps in ypsJoin.DefaultIfEmpty()
        where cps.Guid == processStepGuid
        select new ProcessStepContext(cps, null, null);

    return query.First();

```

Edit: I have fixed the issue for now by splitting the queries, but I would still like to know how to solve this properly:

  // the eager loading syntax does not work if the IQueryable is not used (outputted) directly, so this query
  // needs to be split in order to retrieve all the results.
  IQueryable<ProcessStepEntity> currentProcessStepQuery =
      // current Process Step with related Entities
      from cps in linqMetaData.ProcessStep
          .With(e => e.Process
                  .With(f => f.ProcessType
                      .With(g => g.ProcessExtraFields)),
              e => e.TaskEmployeeProcessExtraField,
              e => e.Task)
      where cps.Guid == processStepGuid
      select cps;

  ProcessStepEntity result = currentProcessStepQuery.First();

  IQueryable<ProcessStepContext> otherProcessStepsQuery =
      from cps in linqMetaData.ProcessStep
      // fetch the yes next Process Step by type
      join yps in linqMetaData.ProcessStep
          on new { cps.ProcessGuid, TypeGuid = cps.YesTypeStepGuid.GetValueOrDefault() }
          equals new { yps.ProcessGuid, TypeGuid = yps.ProcessTypeStepGuid }
          into ypsJoin
      from yps in ypsJoin.DefaultIfEmpty()
      // fetch the no next Process Step by type
      join nps in linqMetaData.ProcessStep
          on new { cps.ProcessGuid, TypeGuid = cps.NoTypeStepGuid.GetValueOrDefault() }
          equals new { nps.ProcessGuid, TypeGuid = nps.ProcessTypeStepGuid }
          into npsJoin
      from nps in ypsJoin.DefaultIfEmpty()
      where cps.Guid == processStepGuid
      select new ProcessStepContext(result, yps, nps);

  return otherProcessStepsQuery.First();

Eager loading specifications always have to be out the outside of the query. So as you specify them in a nested query, the specification is lost. It's not propagated to the outside as it's not said the entities you're fetching in the nested query are the ones the outer query will retrieve.

The eager loading specification is really a specification for "<main query> + these extra entities related to elements returned in <main query>". Eager loading also only works on entity queries, and you use a projection. While it looks all C#, and should work, under the hood a lot of trickery is happening to make it work at all, e.g. fetching entities and store them in a projection for instance. Eager loading isn't any different: it works based on the entities returned by the main query, using that as the parent for the child entities to retrieve.

So in your case, it's not going to work unless you specify With on the outside of the query.