EF Code First oddity with Distinct()

Question

There is something I don't fully understand with the code bellow. I'm trying to find the workers that have a duplicate module in their modules collection.

Here's the entities (simplified for the sake of brevity):

public class Worker
{
    public int Id { get; private set; }
    public ICollection<TakenTrainingModule> TakenTrainingModules { get; private set; }

    public Worker()
    {
        TakenTrainingModules = new HashSet<TakenTrainingModule>();
    }
}

public class TakenTrainingModule
{
    public int Id { get; set; }
    public int TrainingModuleId { get; set; }
}

And here's the query:

var query = from worker in _context.Workers.Include(worker => worker.TakenTrainingModules)
            let distinctModules = worker.TakenTrainingModules.Select(module => module.TrainingModuleId).Distinct()
            where worker.TakenTrainingModules.Count != distinctModules.Count()
            select worker;

With the query bellow, the returned workers have their TakenTrainingModules collection empty.

But, with the next query (without using the keywork let), the collection is fully and correctly loaded:

var query = from worker in _context.Workers.Include(worker => worker.TakenTrainingModules)
            where worker.TakenTrainingModules.Count != worker.TakenTrainingModules.Select(module => module.TrainingModuleId).Distinct().Count()
            select worker;

What am I missing? Is the let keyword first executing the distinct query and fools the object state manager that the module are loaded but they aren't and the selector doesn't load them next?

Any explanations welcome! :-)

Please see the following answer: http://stackoverflow.com/a/4475504/143919 — Dismissile, Apr 07 '14 at 12:21

score 0 · Answer 1 · edited May 23 '17 at 12:20

I've updated the query as this:

var query = from worker in _context.Workers
            let distinctModules = worker.TakenTrainingModules.Select(module => module.TrainingModuleId).Distinct()
            where worker.TakenTrainingModules.Count != distinctModules.Count()
            select worker;

return query.Include(worker => worker.TakenTrainingModules).ToArray();

And everything is fine. Thanks to Dismissile for pointing me out a nice answer.

EF Code First oddity with Distinct()

1 Answers1