Hi all,

Please have a look at the attached...

I have a TPH inheritance : ProductType1 and ProductType2 are subtypes of Product. The discrimator field is RefType (0 for Product, 1 for ProductType1, 2 for ProductType2). These three entities are the only ones in the LLBLGen (V3.0 Final november 15th, 2010) model.

The expected result for the second any() query on the ProductType1 entity should be false. But it returns true!

Product.Any(p => p.Id != 10 && p.Label == "ProductName"); Executed Sql Query: Query: SELECT TOP(@p4) CASE WHEN EXISTS (SELECT [LPLA_1].[RefType] FROM [FABI_BIOPERA].[dbo].[Product] [LPLA_1] WHERE ( ( ( [LPLA_1].[Id] <> @p1) AND ( [LPLA_1].[Label] = @p2)))) THEN 1 ELSE 0 END AS [LPFA_1] FROM [FABI_BIOPERA].[dbo].[Product] [LPLA_1]
Parameter: @p1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 10. Parameter: @p2 : String. Length: 50. Precision: 0. Scale: 0. Direction: Input. Value: "ProductName". Parameter: @p4 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.

ProductType1.Any(p => p.Id != 10 && p.Label == "ProductName"); Executed Sql Query: Query: SELECT TOP(@p4) CASE WHEN EXISTS (SELECT [LPLA_1].[RefType] FROM [FABI_BIOPERA].[dbo].[Product] [LPLA_1] WHERE ( ( ( [LPLA_1].[Id] <> @p1) AND ( [LPLA_1].[Label] = @p2)))) THEN 1 ELSE 0 END AS [LPFA_1] FROM [FABI_BIOPERA].[dbo].[Product] [LPLA_1] WHERE ( ( ( [LPLA_1].[RefType] = @p5 OR [LPLA_1].[RefType] IS NULL))) Parameter: @p1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 10. Parameter: @p2 : String. Length: 50. Precision: 0. Scale: 0. Direction: Input. Value: "ProductName". Parameter: @p4 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1. Parameter: @p5 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 1.

You are using an old release, which might had some issues that were solved afterwards. So stand on the same ground, would you please try the latest release of v.3.0 or better v.3.1 (free upgrade).

Same problem with V3.1 Final September 30th, 2011. Same unexpected result. Same generated query.

The where clause concerned by the RefType is misplaced. Below is the generated SQL query : Query: SELECT TOP(@p4) CASE WHEN EXISTS (SELECT [LPLA_1].[RefType] FROM [FABI_BIOPERA].[dbo].[Product] [LPLA_1] WHERE ( ( ( [LPLA_1].[Id] <> @p1) AND ( [LPLA_1].[Label] = @p2)))) THEN 1 ELSE 0 END AS [LPFA_1] FROM [FABI_BIOPERA].[dbo].[Product] [LPLA_1] WHERE ( ( ( [LPLA_1].[RefType] = @p5 OR [LPLA_1].[RefType] IS NULL))) Parameter: @p1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 10. Parameter: @p2 : String. Length: 50. Precision: 0. Scale: 0. Direction: Input. Value: "ProductName". Parameter: @p4 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1. Parameter: @p5 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 1.

The right SQL query should be : Query: SELECT TOP(@p5) CASE WHEN EXISTS (SELECT [LPLA_1].[RefType] FROM [FABI_BIOPERA].[dbo].[Product] [LPLA_1] WHERE ( ( ( ( [LPLA_1].[Id] <> @p1) AND ( [LPLA_1].[Label] = @p2)) AND ( [LPLA_1].[RefType] = @p3)))) THEN 1 ELSE 0 END AS [LPFA_1] FROM [FABI_BIOPERA].[dbo].[Product] [LPLA_1] WHERE ( ( ( [LPLA_1].[RefType] = @p6 OR [LPLA_1].[RefType] IS NULL))) Parameter: @p1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 10. Parameter: @p2 : String. Length: 50. Precision: 0. Scale: 0. Direction: Input. Value: "ProductName". Parameter: @p3 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 1. Parameter: @p5 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1. Parameter: @p6 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 1.

Reproduced.

We'll look into it.

Hmm...

It's a little difficult to add the typefilter in the filter lambda. Well, it's not hard to add it, but it's hard to determine when NOT to. The issue is that the lambda in the any clause can be anything, for example it can be using an IQueryable created earlier in the code to produce a result, i.o.w. not related to the IQueryable it's defined on. Adding the type filter in that case will crash the query.

To work around this issue, could you try to add an extra filter? ->

Product.Any(p => p.Id != 10 && p.Label == "ProductName" && (p is ProductType1));

To work around the issue, I have written :

ProductType1.Any(p => p.Id != 10 && p.Label == "ProductName" && p.RefType == 1)

That works fine so once known the issue is not blocking at all.

Thanks for the feedback, and I'm glad there's a workaround .

We have planned to update the Any handler a bit with an expression visitor which can figure out whether the filter has to be applied to the EXISTS query. Hopefully this will make it into v3.5 otherwise the version after that.