CODEWITHSUNDEEP
c#entity-frameworklinq
user6063812
user6063812

Reputation: 201

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)
            .ToList()
            .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).

Upvotes: 0

Views: 76

Answers (2)

user6063812
user6063812

Reputation: 201

I managed to do it within EF navigation properties. I was on the right lines in my commented code but had forgotten to ensure that the two field being compared were of the same type (after changing one along the way for testing purposes).

Here's the navigation property linq code:

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

        return Ok(fixtureDtos);
    }

Upvotes: 0

Krishna
Krishna

Reputation: 1985

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

Upvotes: 1

Related Questions