This is a simplification of query I’m trying to get working:

from r in Risk
from rl in r.RiskLikelihoodSeverities.DefaultIfEmpty()
select new 
{
r.WRNumber,
r.WRPeriod,
rl.InitialLikelihoodID,
r.RiskReview.Title,                                 
} 

I’m after all Risks and their RiskReview if they have one. RiskReview is a descendent of WorkRequest. The query produces this sql:

DECLARE @p1 VarChar(4); SET @p1='RR'
DECLARE @p2 VarChar(4); SET @p2='Abst'
DECLARE @p3 VarChar(4); SET @p3='ORR'
DECLARE @p4 VarChar(4); SET @p4='FRR'
DECLARE @p5 VarChar(4); SET @p5='HRR'
DECLARE @p6 VarChar(4); SET @p6='SRR'
DECLARE @p7 VarChar(4); SET @p7='RR'
DECLARE @p8 VarChar(4); SET @p8='Abst'
DECLARE @p9 VarChar(4); SET @p9='ORR'
DECLARE @p10 VarChar(4); SET @p10='FRR'
DECLARE @p11 VarChar(4); SET @p11='HRR'
DECLARE @p12 VarChar(4); SET @p12='SRR'
SELECT [LPA_L1].[WR_Number] AS [WRNumber],
  [LPA_L1].[WR_Period] AS [WRPeriod],
  [LPA_L2].[Initial_Likelihood_ID] AS [InitialLikelihoodID],
  [LPA_L3].[Title]
FROM
 (( [AQD].[rm_Risk] [LPA_L1] 
LEFT JOIN
 [AQD].[rm_Risk_Likelihood_Severity] [LPA_L2]  ON  [LPA_L1].[Risk_No]=[LPA_L2].[Risk_No])
LEFT JOIN
 [AQD].[qaoc_Work_Request] [LPA_L3]  ON  [LPA_L3].[WR_Number]=[LPA_L1].[WR_Number] AND [LPA_L3].[WR_Period]=[LPA_L1].[WR_Period] AND [LPA_L3].[WR_Type]=[LPA_L1].[WR_Type] AND ( ( [LPA_L3].[WR_Type] IN (@p1,
  @p2,
  @p3,
  @p4,
  @p5,
  @p6))))
WHERE
 ( ( ( [LPA_L3].[WR_Type] IN (@p7,
  @p8,
  @p9,
  @p10,
  @p11,
  @p12))))

Which includes a pesky where clause which excludes risks with a null WR_Type (i.e. no risk reviews). If I remove the where clause from the SQL and run it I get the results I want.

Question is: How do I get rid of the where clause? And it seems totally redundant given that it is in the join as well.

Is this the WR_Type the Discriminator field of the Inheritance Hierarchy?

Walaa wrote:

Is this the WR_Type the Discriminator field of the Inheritance Hierarchy?

Yes

It seems that this filter is added automatically, due to inheritance requirements, and it doesn't look at left/right joins.

i.o.w: We don't think this is doable at the moment, as there's no logic which corrects the type filter based on whatever comes out of the join: it doesn't interpret the join, it simply applies the filter to the query.

We also don't know if this is doable at all, as it then needs to interpret the join list, which is very complicated.

After some thinking, I do think there's a solution which doesn't require interpreting the JOIN clause: adding NULL to the IN clause.

The problem might be that IN doesn't support NULL, not sure, however as an IN clause is an OR query, and discriminator fields are required to be not null, this doesn't change existing behavior, it only makes queries like the one you're using possible.

If you add 'NULL' to the IN clause in sql, does your query return the required rows as well?

Otis wrote:

After some thinking, I do think there's a solution which doesn't require interpreting the JOIN clause: adding NULL to the IN clause.

The problem might be that IN doesn't support NULL, not sure, however as an IN clause is an OR query, and discriminator fields are required to be not null, this doesn't change existing behavior, it only makes queries like the one you're using possible.

If you add 'NULL' to the IN clause in sql, does your query return the required rows as well?

No change, WR_Type is a string field btw. Making the where clause like this worked:

WHERE
 ( ( ( [LPA_L3].[WR_Type] IN (@p7,
    @p8,
    @p9,
    @p10,
    @p11,
    @p12)) or [LPA_L1].[WR_Type]='' or [LPA_L1].[WR_Type] is null))

(on clause) ( ( [LPA_L3].[WR_Type] IN (@p1, @p2, @p3, @p4, @p5, @p6, NULL))))

(where clause) WHERE ( ( ( [LPA_L3].[WR_Type] IN (@p7, @p8, @p9, @p10, @p11, @p12, NULL))

should work too. IN is an OR predicate. I don't understand the '' part btw, the where only fails because due to the left join the WR_Type column doesn't have a value everywhere, so the rows you want to return aren't in the resultset if NULL is not in the IN clause. The NULL can't occur for LPA_L1, as it's never NULL (discriminator fields can't be null). The NULL value you want to have in the where clause is for LPA_L3, as that part is joined using a LEFT join, which could result in NULL for WR_Type, however as NULL isn't in the filter now, it fails to return these rows.

Otis wrote:

should work too. IN is an OR predicate. I don't understand the '' part btw, the where only fails because due to the left join the WR_Type column doesn't have a value everywhere, so the rows you want to return aren't in the resultset if NULL is not in the IN clause. The NULL can't occur for LPA_L1, as it's never NULL (discriminator fields can't be null). The NULL value you want to have in the where clause is for LPA_L3, as that part is joined using a LEFT join, which could result in NULL for WR_Type, however as NULL isn't in the filter now, it fails to return these rows.

Still no change. LPA_L1(Risk) doesn't have a discriminator - the column is just part for the FK. Unfortunatly this example is complicated by the 3 column key - one of which is the work request descriminator. Altering the where to this also gives the correct results:

WHERE
( ( ( [LPA_L3].[WR_Type] IN (@p7,
        @p8,
        @p9,
        @p10,
        @p11,
        @p12)) or [LPA_L1].[WR_Number] is null))

I've basically added an 'or risk fk to wr is null'

This also works - which is close to what you suggested:

WHERE
( ( ( [LPA_L3].[WR_Type] IN (@p7,
        @p8,
        @p9,
        @p10,
        @p11,
        @p12)) or [LPA_L3].[WR_Type] is null))

TomDog wrote:

Otis wrote:

should work too. IN is an OR predicate. I don't understand the '' part btw, the where only fails because due to the left join the WR_Type column doesn't have a value everywhere, so the rows you want to return aren't in the resultset if NULL is not in the IN clause. The NULL can't occur for LPA_L1, as it's never NULL (discriminator fields can't be null). The NULL value you want to have in the where clause is for LPA_L3, as that part is joined using a LEFT join, which could result in NULL for WR_Type, however as NULL isn't in the filter now, it fails to return these rows.

Still no change. LPA_L1(Risk) doesn't have a discriminator - the column is just part for the FK. Unfortunatly this example is complicated by the 3 column key - one of which is the work request descriminator. Altering the where to this also gives the correct results:

WHERE
( ( ( [LPA_L3].[WR_Type] IN (@p7,
        @p8,
        @p9,
        @p10,
        @p11,
        @p12)) or [LPA_L1].[WR_Number] is null))

I've basically added an 'or risk fk to wr is null'

This is addable through linq I think: you can add the predicate manually as a where, although it might be the type filter will be added as an 'and'....

This also works - which is close to what you suggested:

WHERE
( ( ( [LPA_L3].[WR_Type] IN (@p7,
        @p8,
        @p9,
        @p10,
        @p11,
        @p12)) or [LPA_L3].[WR_Type] is null))

Yes that's more in line of the type filter, as that's added automatically. Though it's odd that NULL doesn't work inside the IN clause. Likely the RDBMS engine does field = null, which always fails... Will look into adding this to the engine, although it's a once in a lifetime edge case I guess...

Could you try the attached ormsupportclasses, to see whether that gives you the query you need?

Otis wrote:

This is addable through linq I think: you can add the predicate manually as a where, although it might be the type filter will be added as an 'and'....

Yes 'and'

Otis wrote:

Yes that's more in line of the type filter, as that's added automatically. Though it's odd that NULL doesn't work inside the IN clause. Likely the RDBMS engine does field = null, which always fails... Will look into adding this to the engine, although it's a once in a lifetime edge case I guess...

I'll leave it with you, though I wouldn't have thought Left Joining on to a entity in an Inheritance Hierarchy would be quite that rare. Is this similar? http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=13480

TomDog wrote:

Otis wrote:

This is addable through linq I think: you can add the predicate manually as a where, although it might be the type filter will be added as an 'and'....

Yes 'and'

Otis wrote:

Yes that's more in line of the type filter, as that's added automatically. Though it's odd that NULL doesn't work inside the IN clause. Likely the RDBMS engine does field = null, which always fails... Will look into adding this to the engine, although it's a once in a lifetime edge case I guess...

I'll leave it with you, though I wouldn't have thought Left Joining on to a entity in an Inheritance Hierarchy would be quite that rare. Is this similar? http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=13480

The rarity is in the discriminator field being an fk field as well and becoming NULL due to the left join.

Did the build work btw?

Otis wrote:

The rarity is in the discriminator field being an fk field as well and becoming NULL due to the left join.

Did the build work btw?

Yes - just tried it. Can now finish this off on monday - thanks again - off to bed...