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