Multiple LINQ Join conditions with differing comparison operators

[Gary]

I am trying to build a LINQ query that will accommodate a dynamic list of WHERE conditions, but also provide multiple non-equity join conditions between two tables. I am dealing with existing ORACLE tables which have no foreign keys defined  so I am unable to define navigation properties between the tables. 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 definitions. 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 ....

 

 

 

[Gary]

Has anybody read this yet? I am absolutely dying here ...