bucket.PredicateExpression.Add(new FieldCompareSetPredicate(null, null, AccountReminderFields.AccountReminderId, null, SetOperator.Exist, filter, true));

Surely the above code should result in SQL sub-query of the form...

NOT  EXISTS (SELECT [bangfaceweekender].[dbo].[tbl_account_reminder].[AccountReminderId]
FROM ( [bangfaceweekender].[dbo].[tbl_account_reminder] ...

Instead its generating this...

NOT  EXISTS (SELECT [bangfaceweekender].[dbo].[tbl_account_reminder].[AccountReminderId]
FROM ( [bangfaceweekender].[dbo].[vw_eticket] ...

'VwEticket' doesn't appear anywhere in my query. (Although it is a sub class of a type used in the query.)

Any ideas?

Please post a complete code snippet and the corresponding generated SQL.

What is the build number of the LLBLGen Pro runtime library used?

Hello,

I've got...

        public EntityCollection<VwChaletAccountEntity> GetCompletedAccountsAndGroupLeader()
        {
            var collection = new EntityCollection<VwChaletAccountEntity>();

            var fieldsList = new ExcludeIncludeFieldsList(false)
                                 {
                                     VwChaletAccountFields.AccountId,
                                     VwChaletAccountFields.EticketRef,
                                     VwChaletAccountFields.Berth
                                 };

            var adapter = new DataAccessAdapter();

            var bucket = new RelationPredicateBucket();
            bucket.Relations.Add(VwChaletAccountEntity.Relations.AccountAssignmentEntityUsingAccountId);

            var filter = new PredicateExpression();
            filter.Add(AccountReminderFields.AccountReminderTypeId == AccountNotificationTypeEnum.AccountInfoComplete);
            filter.Add(AccountReminderFields.AccountId == VwChaletAccountFields.AccountId);
                
            bucket.PredicateExpression.Add(new FieldCompareSetPredicate(null, null, AccountReminderFields.AccountReminderId, null, SetOperator.Exist, filter, true));

            bucket.PredicateExpression.Add(VwChaletAccountFields.Berth == VwChaletAccountFields.GuestCount);

            var path2 = new PrefetchPath2(EntityType.VwChaletAccountEntity);
            path2.Add(VwChaletAccountEntity.PrefetchPathGuest, 1,
                      new PredicateExpression(GuestFields.Ordinal == GuestEntity.GroupLeaderOrdinal));

            adapter.FetchEntityCollection(collection, bucket, 0, null, path2, fieldsList);

            return collection;
        }

...and its coming up with...

exec sp_executesql N'SELECT DISTINCT [bangfaceweekender ].[dbo].[vw_eticket].[AccountID] AS [F14_0], [bangfaceweekender ].[dbo].[vw_eticket].[EventID] AS [F14_1], [bangfaceweekender ].[dbo].[vw_eticket].[ETicketRef] AS [F14_2], [bangfaceweekender ].[dbo].[vw_chalet_account].[AccountID] AS [F13_6], [bangfaceweekender ].[dbo].[vw_chalet_account].[Berth] AS [F13_10] FROM (( [bangfaceweekender ].[dbo].[vw_eticket]  INNER JOIN [bangfaceweekender ].[dbo].[vw_chalet_account]  ON  [bangfaceweekender ].[dbo].[vw_eticket].[AccountID]=[bangfaceweekender ].[dbo].[vw_chalet_account].[AccountID]) INNER JOIN [bangfaceweekender ].[dbo].[tbl_account_assignment]  ON  [bangfaceweekender ].[dbo].[vw_chalet_account].[AccountID]=[bangfaceweekender ].[dbo].[tbl_account_assignment].[AccountID]) WHERE ( ( NOT  EXISTS (SELECT [bangfaceweekender ].[dbo].[tbl_account_reminder].[AccountReminderID] AS [AccountReminderId] FROM ( [bangfaceweekender ].[dbo].[vw_eticket]  LEFT JOIN [bangfaceweekender ].[dbo].[vw_chalet_account]  ON  [bangfaceweekender ].[dbo].[vw_eticket].[AccountID]=[bangfaceweekender ].[dbo].[vw_chalet_account].[AccountID]) WHERE ( [bangfaceweekender ].[dbo].[tbl_account_reminder].[AccountReminderTypeID] = @AccountReminderTypeId1 AND [bangfaceweekender ].[dbo].[tbl_account_reminder].[AccountID] = [bangfaceweekender ].[dbo].[vw_chalet_account].[AccountID])) AND [bangfaceweekender ].[dbo].[vw_chalet_account].[Berth] = [bangfaceweekender ].[dbo].[vw_chalet_account].[GuestCount]) AND ( [bangfaceweekender ].[dbo].[vw_chalet_account].[AccountID] IS NOT NULL))',N'@AccountReminderTypeId1 int',@AccountReminderTypeId1=2

The query is returning accounts that have all their guests entered and which haven't already been sent a confirmation email. This used to work.

SD.LLBLGen.Pro.DQE.SqlServer.NET20 - 2.6.8.1006 SD.LLBLGen.Pro.ORMSupportClasses.NET20 - 2.6.8.1013

I think I might be able to use .net 3 now which might make this problem go away but it would be easier if this just worked.

Cheers!

When you say "used to work" have you changed or upgraded anything to stop it working ?

Matt

The database went through some changes so yes that might have broken it. But isn't it a bit odd that there's a table in the query that's not mentioned in the code? This is the only time I've ever used a FieldCompareSetPredicate.. shouldn't the table in the sub-query be the table that the 'set field' comes from? i.e. tbl_account_reminder which is the one missing from the generated code!

SD.LLBLGen.Pro.ORMSupportClasses.NET20 - 2.6.8.1013

Please try the following:

1- Download and install the latest release of LLBLGen Pro. 2- Open the project in the designer and refresh the catalog 3- Re-generate the code. 4- Make sure your application is using the newly installed runtime libraries and the newly generated code (database specific & database generic).

The same result I'm afrad.

I think I will just re-write it using a join.

The problem is that you're using inheritance, and it has to join the subtype tables as you're referring to these tables through the fields in the filter specified in the fieldcompareset predicate.

That this didn't occur previously was because it was actually a bug. (and I also find it hard to believe it did work as the subtype field's table you refer to has to be present in the FROM clause, which wouldn't be the case with your proposed FROM clause: that one would make the WHERE clause crash as it referred to a field in a table which isn't in the from clause otherwise. )

So if the query doesn't work now (as it doesn't return the right values according to you), please help us understand the inheritance hierarchy and what exactly you want to do and what's pulled from which table.

            var collection1 = new EntityCollection<OrderEntity>();

            var bucket2 = new RelationPredicateBucket();
            bucket2.PredicateExpression.Add(new FieldCompareSetPredicate(null, null, AccountReminderFields.AccountReminderId, null, SetOperator.Exist, null, true));

            var adapter2 = new DataAccessAdapter();
            adapter2.FetchEntityCollection(collection1, bucket2);

This simple code with entities not from an inheritance hierarchy results in the following sub-squery...

WHERE ( ( NOT  EXISTS (SELECT [bangfaceweekender ].[dbo].[tbl_account_reminder].[AccountReminderID] AS [AccountReminderId] FROM [bangfaceweekender ].[dbo].[tbl_account_reminder] ))

I presume that the sub-query is using 'tbl_account_reminder' in its from clause because an AccountReminderEntity field is specified in the FieldCompareSetPredicate predicate constructor call.

But I'm creating the FieldCompareSetPredicate in a similar way in the original query and yet there 'tbl_account_reminder' is missing from the from clause. Surely it should still be there?

The problem is that you're using inheritance, and it has to join the subtype tables as you're referring to these tables through the fields in the filter specified in the fieldcompareset predicate.

I'm not quite following. I don't see why the sub-query should need to refer to these tables in the from clause at all.

The sub-query should just look like this when the filter is added to the FieldCompareSetPredicate predicate constructor call...

WHERE ( ( NOT  EXISTS (SELECT [bangfaceweekender ].[dbo].[tbl_account_reminder].[AccountReminderID] AS [AccountReminderId]
FROM [bangfaceweekender ].[dbo].[tbl_account_reminder]

WHERE [bangfaceweekender ].[dbo].[tbl_account_reminder].[AccountReminderTypeID] = @AccountReminderTypeId1

 AND [bangfaceweekender ].[dbo].[tbl_account_reminder].[AccountID] = [bangfaceweekender ].[dbo].[vw_chalet_account].[AccountID]))

...and '[bangfaceweekender ].[dbo].[vw_chalet_account]' refers to the sub-query's parent query.

I'm trying to write a correlated sub-query here right?!

So what you are saying is that AccountReminderEntity is not in an inheritance hierarchy, right? Could you please attach a screen shot of the database diagram containing entities involved in the entire query. And please explain any inheritance built with LLBLGen Pro Designer.

Could you first try just testing a sub-query where the main query is selecting from a derived entity? Things are fine when using a base table in the main query. Using a derived table seems to result in the sub-query's from clause getting polluted.

Hi Ian. I'm joining to the discussion...

Ian wrote:

Otis wrote:

The problem is that you're using inheritance, and it has to join the subtype tables as you're referring to these tables through the fields in the filter specified in the fieldcompareset predicate.

I'm not quite following. I don't see why the sub-query should need to refer to these tables in the from clause at all.

The thing is that in you subquery you are filtering a value based on the value of a derived table (the outer query). That VwChaletAccountEntity is indeed a VwTicketEntity (?) due to inheritance. So the join is ok, it should be there.

I think the query is ok for what you are passing to it. Now, lets examine what you want:

accounts that have all their guests entered and which haven't already been sent a confirmation email.

I guess that the _FieldCompareSetPredicate _(the subquery) performs the "which haven't already been sent a confirmation email" part, ritght?

Now, the dilemma is this part:

filter.Add(AccountReminderFields.AccountId == VwChaletAccountFields.AccountId);

That (as expected, at least for the framework point of view) generates this:

LEFT JOIN [bangfaceweekender ].[dbo].[vw_chalet_account] 
     ON [bangfaceweekender].[dbo].[vw_eticket].[AccountID]
     =[bangfaceweekender].[dbo].[vw_chalet_account].[AccountID]
...
WHERE ... 
     AND [bangfaceweekender].[dbo].[tbl_account_reminder].[AccountID] 
     = [bangfaceweekender ].[dbo].[vw_chalet_account].[AccountID]

Now, what is the real problem with that join? Every VwChaletAccount is (throgh inheritance) a VwETicket right?

Is there something I don't see here? more complex inheritance scenario maybe? What are the values expected and what values are you already getting?

Hi daelmo,

The sub-query should just have..

WHERE ( ( NOT EXISTS (SELECT [bangfaceweekender ].[dbo].[tbl_account_reminder].[AccountReminderID] AS [AccountReminderId]
FROM [bangfaceweekender ].[dbo].[tbl_account_reminder]

WHERE [bangfaceweekender ].[dbo].[tbl_account_reminder].[AccountReminderTypeID] = @AccountReminderTypeId1

AND [bangfaceweekender ].[dbo].[tbl_account_reminder].[AccountID] = [bangfaceweekender ].[dbo].[vw_chalet_account].[AccountID]))

Of course the join should be in the outer query but it has no place in the sub-query. The sub-query is just trying to select a row from tbl_account_reminder.

I don't think this is any more complicated than you think. Yes, VwChaletAccount derives from VwEticket and ultimately I'm just selecting chalet accounts. Its just that for each account I want to make sure its not being referenced in the account reminder table.

I think there's a bug here and its that part of the outer query's from clause has found its way into the sub-query's from clause.

I fixed this with a left join now so this isn't really an issue anymore!

So, intead of

filter.Add(AccountReminderFields.AccountId == VwChaletAccountFields.AccountId);

you added a left-join relation between AccountReminder and VwChaletAccount?

Yes if there's no reminder row to join to then no notification has been sent.

The join is there because a subtype's field is mentioned in the predicate like I explained earlier.

If you mentioned a field using a SUBTYPEs fields class, while the field is inherited from a supertype, use the SUPERTYPEs fields class instead to avoid joins with subtype(s).

So e.g.: if you have Employee <- Manager and there's a field Employee.Name, and you use ManagerFields.Name, it will cause joins, if you use EmployeeFields.Name, it won't. This might sound silly, but actually it's the only way to get things correctly joined/fetched.

The join is there because a subtype's field is mentioned in the predicate like I explained earlier.

I'm just not following or I'm not explaining the situtation properly. Lets say I have the following query...

SELECT A.Field1 FROM A WHERE EXISTS (SELECT B.Field1 FROM B WHERE B.Field2 = A.Field1)

So I'm trying to write this in LLBLGen code and out pops...

SELECT A.Field1 FROM A WHERE EXISTS (SELECT B.Field1 FROM A WHERE B.Field2 = A.Field1)

Surely that's a bug? The issue isn't that there's a join going on, its that the subquery is selecting from the wrong table!

Would it be possible for you to produce us a repro case - just the smallest possible code project against either Northwind (or your db is you can script the schema for us)

This will allow us to investigate in more depth.

Thanks

Matt