Hi, I have two custom entites, Product and ProductType, linked together in many-to-one relationship. Product has a lookup field to ProductType.

I'm trying to write a query to fetch Type1 products with a price over 100, and Type2 products with a price lower than 100.

Here's how I would do it in SQL :

select *
  from Product P
 inner join ProductType T on T.Id = P.TypeId
 where (T.Code = 'Type1' and P.Price >= 100)
    or (T.Code = 'Type2' and P.Price < 100)

I can't figure out a way to build a QueryExpression to do exactly that. I know I could do it with two queries, but I'd like to minimize roundtrips to the server.

Is there a way to perform that query in only one operation ?

Thanks!