I am having issue in writing an optimized lambda-based query for Entity Framework entities. I hope the below code snippet and embedded comments are succinct enough for the context.
Entity Framework database context class, entity classes and repository class below
public sealed class ProjectDbContext : DbContext
{
// removed other code for brevity
public DbSet<City> Cities { get; set; }
}
public sealed class City
{
public Guid Id { get; private set;}
private readonly List<Student> _students = new();
public IReadOnlyList<Student> Students => _students.AsReadOnly();
}
public sealed class Student
{
public Guid Id { get; private set;}
// removed other code for brevity
}
public interface ICityQueryRepository
{
Task<City> GetByCityIdAndStudentId(Guid cityId, Guid studentId);
}
public sealed class CityQueryRepository : ICityQueryRepository
{
// simplified for brevity
private readonly DbSet<City> _dbSet;
protected IQueryable<City> Query;
public CityQueryRepository(ProjectDbContext dbContext)
{
_dbSet = context.Set<City>();
Query = _dbSet;
}
public async Task<City> GetByCityIdAndStudentId(Guid cityId, Guid studentId)
{
var result = await Query
.AsNoTracking()
.Where(x => x.Id == cityId && x.Students.Any(sc => sc.Id == studentId))
.Include(x => x.Students)
.SingleOrDefaultAsync();
// I see that 'result' at this point is a valid City object
// but 'result.Students' collection is of size 0. It should be non-zero because
// there are some rows there.
return result;
}
}
The relevant Mediatr handler below
public sealed class TestCommand : IRequest
{
public Guid CityId { get; init; }
public Guid StudentId { get; init; }
}
public sealed class TestCommandHandler : IRequestHandler<TestCommand>
{
private readonly ICityQueryRepository _queryRepository;
public TestCommandHandler(ICityQueryRepository queryRepository)
{
_queryRepository = queryRepository;
}
public async Task Handle(TestCommand request, CancellationToken cancellationToken)
{
var city = await _queryRepository.GetByCityIdAndStudentId(request.CityId, request.StudentId);
// See previous inline comment e.g. 'city.Students' is empty collection
}
}
The actual database schema looks like the following (simplified for brevity)
Cities table
Id: Guid
Name: varchar{100}
Students table
Id: Guid
Name: nvarchar(100)
CityId: Guid (nullable)
QUESTION:
What change should I do in my approach to produce a SQL query like the following (which works perfectly fine)
SELECT
*
FROM
Cities city
LEFT JOIN Students st ON st.CityId = city.Id
WHERE
st.Id = '123123-f4da-4f15-bffa-000cb1123123'
AND city.Id = '48748767-760d-45a6-b2c3-18279b2e46d'