Multiple LINQ Join conditions with differing comparison operators

Thread is closed for posting
2 posts, 0 answers
  1. Gary
    Gary avatar
    2 posts
    Member since:
    May 2015

    Posted 21 Nov 2016 Link to this post

    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 ....

     

     

     

  2. Gary
    Gary avatar
    28 posts
    Member since:
    Jun 2015

    Posted 29 Nov 2016 in reply to Gary Link to this post

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