user6063812 user6063812 - 10 months ago 40
C# Question

Linq query WHERE one field is >= another field in parent table?

How can I translate this:

SELECT ExchangeTypes.*, Fixtures.*
FROM Fixtures
INNER JOIN ExchangeTypes ON Fixtures.ExchangeTypeId = ExchangeTypes.Id
WHERE ExchangeTypes.LayOdds <= Fixtures.BackOdds

into Linq - here's my basic query:

var fixtureDtos = _context.Fixtures //.Where(c => c.BackOdds >= c.ExchangeType.LayOdds)
.Include(c => c.ExchangeType)
.Select(Mapper.Map<Fixture, FixtureDto>);

return Ok(fixtureDtos);

Notice there is already a navigation property set up between Fixture and Exchange type (Many to 1).

Answer Source
var fixtureDtos = (from m in _context.Fixtures
                  join n in _context.ExchangeTypes
                  on m.ExchangeTypeId = n.Id
                  where n.BackOdds > m.BackOdds
                  select new {/*add columns needed from m and n here*/}).ToList();