Multiple LINQ Join conditions with differing comparison operators

Question

I am trying to build a LINQ query that will accomodate a dynamic list of predicates, but also provide multiple non-equity join conditions between two tables. The ORM I am using is Telerik Open Access/Data Access going against an Oracle database.

Here is the PL-SQL Query I am trying to build in Linq.

SELECT DISTINCT
      "asset".asset_number
    , "hdr".revision
    , "hdr".syscfg_booth_num
    , "hdr".message_id
    , "hdr".msg_num
    , "hdr".msg_create_date
    , "hdr".msg_xmit_date
    , "hdr".skytel_date
FROM xfe_rep.wi_transmits "hdr"
INNER JOIN xfe_rep.pin2pagerid "asset" ON
      "hdr".pin = "asset".wireless_pager_pin 
    AND
        "hdr".msg_create_date >= "asset".effective_start_date 
    AND
        "hdr".msg_create_date <= "asset".effective_end_date
WHERE
    "hdr".field_one = ??
AND
    "hdr".field_two = ??;

The WHERE clause will be added dynamically from individual predicates provided in a list. My first pass looked something like this ...

IQueryable<WITransmits> query = from wiTransmits in ctx.WITransmits
                        join assetMap in ctx.AssetNumberMaps
                            on wiTransmits.PIN equals assetMap.PIN 
                        where
                            assetMap.EffectiveStartDate <= wiTransmits.MessageCreateDate &&
                            assetMap.EffectiveEndDate >= wiTransmits.MessageCreateDate
                        select wiTransmits;

foreach (Expression<Func<WITransmits, Boolean>> filter in messagePredicate) 
{ 
    query = query.Where(filter); 
}

The problem with the above code is that it raises the exception "ORA-01013: user requested cancel of current operation". I believe that this is due to the number of records being returned. I have learned from other tests that when the WHERE clause is added via both the expression and method syntax to the same query it generates multiple SQL Select statements and executes each separately against the database performing the actual join in memory instead. With millions of records this is latent and impractical, I believe giving rise to the exception.

Of course when I removed the WHERE clause from the Expression syntax only one SQL statement was created, and executes as expected.

IQueryable<WITransmits> query = from wiTransmits in ctx.WITransmits
                                join assetMap in ctx.AssetNumberMaps
                                    on wiTransmits.PIN equals assetMap.PIN 
                                select wiTransmits;

foreach (Expression<Func<WITransmits, Boolean>> filter in messagePredicate) 
{ 
    query = query.Where(filter); 
}

But, of course I lose the JOIN filters on the dates. The other alternative I had considered was to add multiple conditions to the JOIN. This, however, only allows me to compare for EQUALITY between two object definitons. I need to compare each property with a different comparison operator ( ==, >=, <= ) though ... so again this does not work.

IQueryable<WITransmits> query = from wiTransmits in ctx.WITransmits
                                join assetMap in ctx.AssetNumberMaps on
                                    new { wiTransmits.PIN, wiTransmits.MessageCreateDate, wiTransmits.MessageCreateDate } equals 
                                    new { assetMap.PIN, assetMap.EffectiveStartDate, assetMap.EffectiveEndDate }
                                select wiTransmits;

foreach (Expression<Func<WITransmits, Boolean>> filter in messagePredicate) 
{ 
    query = query.Where(filter); 
}

The final thought I had was to simply add the conditions as a couple of additional WHERE filters. The problem here is that the WHERE clause is between two table values and not a static value provided by the consumer. The query does not return a Queryable typed for both tables involved in the comparison so this will not work either.

IQueryable<WITransmits> query = from wiTransmits in ctx.WITransmits
                                join assetMap in ctx.AssetNumberMaps
                                    on wiTransmits.PIN equals assetMap.PIN
                                select wiTransmits;

var joinPredicate = new List<Expression<Func<WITransmits, AssetNumberMaps, Boolean>>>();
    joinPredicate.Add((wiTransmits, assetMap) => assetMap.EffectiveStartDate <= wiTransmits.MessageCreateDate);
    joinPredicate.Add((wiTransmits, assetMap) => assetMap.EffectiveEndDate >= wiTransmits.MessageCreateDate);

foreach (Expression<Func<WITransmits, AssetNumberMaps, Boolean>> filter in joinPredicate)
{
    query = query.Where(filter); // DOES NOT WORK 
}

foreach (Expression<Func<WITransmits, Boolean>> filter in messagePredicate) 
{ 
    query = query.Where(filter); 
}

Anybody have any ideas on how to do this? I am fresh out of direction ....