Hi

I'm using a RelationEntityBucket with two different object aliases for the same table. So I'm doing SetObjectAlias on each of the fields.

Originally I was doing a Fetch and was setting filter.SelectListAlias to set the alias for the table from which the fields were being selected. This worked fine. But now I've gone one stage further to UpdateEntitiesDirectly, but now the aliasing fails because RelationPredicateBucket.SelectListAlias obviously isn't used as a table alias when doing an Update.

This is the incorrect query that I get from my code:

UPDATE [UnivarQ].[dbo].[SpecialPrice] SET [ExpiryDate]=@ExpiryDate,[IsInForce]=@IsInForce WHERE ( ( ( [sp1].[IsInForce] = @IsInForce1 AND NOT (  EXISTS (SELECT [UnivarQ].[dbo].[CustomerRecordCard].[Id] FROM [UnivarQ].[dbo].[CustomerRecordCard]  WHERE ( ( ( [UnivarQ].[dbo].[CustomerRecordCard].[Quantity] <> @Quantity2 AND [UnivarQ].[dbo].[CustomerRecordCard].[ProductId] = [sp1].[ProductId]) AND [UnivarQ].[dbo].[CustomerRecordCard].[CustomerId] = [sp1].[CustomerId]) AND [UnivarQ].[dbo].[CustomerRecordCard].[PeriodStartDate] > @PeriodStartDate3)) OR NOT  EXISTS (SELECT [UnivarQ].[dbo].[CustomerRecordCard].[Id] FROM [UnivarQ].[dbo].[CustomerRecordCard]  WHERE ( ( [UnivarQ].[dbo].[CustomerRecordCard].[ProductId] = [sp1].[ProductId] AND [UnivarQ].[dbo].[CustomerRecordCard].[CustomerId] = [sp1].[CustomerId]) AND [UnivarQ].[dbo].[CustomerRecordCard].[PeriodStartDate] > @PeriodStartDate4))) AND  EXISTS (SELECT [sp2].[Id] FROM [UnivarQ].[dbo].[SpecialPrice] [sp2]  WHERE ( ( ( [sp1].[CustomerId] = [sp2].[CustomerId] AND [sp1].[ProductId] = [sp2].[ProductId]) AND [sp1].[VolumePackNo] = [sp2].[VolumePackNo]) AND [sp2].[EffectiveDate] < @EffectiveDate5)))))
    Parameter: @ExpiryDate : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 04/07/2008 12:40:06.
    Parameter: @IsInForce : Boolean. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: False.
    Parameter: @IsInForce1 : Boolean. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: True.
    Parameter: @Quantity2 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 0.
    Parameter: @PeriodStartDate3 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 04/03/2008 00:00:00.
    Parameter: @PeriodStartDate4 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 04/03/2008 00:00:00.
    Parameter: @EffectiveDate5 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 04/03/2008 00:00:00.

You can see that the table being updated is missing its alias, whereas all the other usages are correctly aliased.

Here's my query generation code:

        public int RemoveUnusedSpecialPrices()
        {
            IRelationPredicateBucket filter = new RelationPredicateBucket();
            const string spAlias = "sp1";
            filter.SelectListAlias = spAlias;
            filter.PredicateExpression.Add(SpecialPriceFields.IsInForce.SetObjectAlias(spAlias) == true);

            DateTime checkStartDate = CreateRecordCardCheckStartDate();
            Predicate noRecordCardsExistForPeriodPredicate = CreateNoRecordCardsExistForPeriodPredicate(checkStartDate, spAlias);
            Predicate recordCardsWithPurchasesPredicate = CreateRecordCardsWithPurchasesExistForPeriodPredicate(checkStartDate, spAlias);

            filter.PredicateExpression.Add(!(recordCardsWithPurchasesPredicate | noRecordCardsExistForPeriodPredicate));
            const string spExistsAlias = "sp2";
            filter.PredicateExpression.Add(CreateEquivalentSpecialPriceHasExistedForPeriodPredicate(spAlias, spExistsAlias, checkStartDate));
            return ExpiryHelper.ExpireSpecialPricesMatchingCriteria(filter);
        }

        private static FieldCompareSetPredicate CreateEquivalentSpecialPriceHasExistedForPeriodPredicate(string spAlias, string spExistsAlias, DateTime checkStartDate)
        {
            Predicate existsPred = SpecialPriceFields.CustomerId.SetObjectAlias(spAlias) ==
                                   SpecialPriceFields.CustomerId.SetObjectAlias(spExistsAlias) &
                                   SpecialPriceFields.ProductId.SetObjectAlias(spAlias) ==
                                   SpecialPriceFields.ProductId.SetObjectAlias(spExistsAlias) &
                                   SpecialPriceFields.VolumePackNo.SetObjectAlias(spAlias) ==
                                   SpecialPriceFields.VolumePackNo.SetObjectAlias(spExistsAlias) &
                                   SpecialPriceFields.EffectiveDate.SetObjectAlias(spExistsAlias) < checkStartDate;

            return new FieldCompareSetPredicate(null, null, SpecialPriceFields.Id.SetObjectAlias(spExistsAlias), null,
                                                SetOperator.Exist, existsPred, spExistsAlias);
        }

        private static Predicate CreateNoRecordCardsExistForPeriodPredicate(DateTime recordCardCheckStartDate, string specialPriceAlias)
        {
            Predicate recordCardsExistExp = CustomerRecordCardFields.ProductId ==
                                            SpecialPriceFields.ProductId.SetObjectAlias(specialPriceAlias) &
                                            CustomerRecordCardFields.CustomerId ==
                                            SpecialPriceFields.CustomerId.SetObjectAlias(specialPriceAlias) &
                                            CustomerRecordCardFields.PeriodStartDate > recordCardCheckStartDate;

            return new FieldCompareSetPredicate(null, null, CustomerRecordCardFields.Id, null,
                                                SetOperator.Exist,
                                                recordCardsExistExp, true);
        }

        private static Predicate CreateRecordCardsWithPurchasesExistForPeriodPredicate(DateTime recordCardCheckStartDate, string spAlias)
        {
            Predicate recordCardsExistWithPurchasesExp = CustomerRecordCardFields.Quantity != 0 &
                                                         CustomerRecordCardFields.ProductId == SpecialPriceFields.ProductId.SetObjectAlias(spAlias) &
                                                         CustomerRecordCardFields.CustomerId == SpecialPriceFields.CustomerId.SetObjectAlias(spAlias) &
                                                         CustomerRecordCardFields.PeriodStartDate > recordCardCheckStartDate;

            return new FieldCompareSetPredicate(null, null, CustomerRecordCardFields.Id, null,
                                                                                           SetOperator.Exist,
                                                                                           recordCardsExistWithPurchasesExp);
        }

The update entity creation looks like this:

        public static SpecialPriceEntity CreateExpireSpecialPriceEntity(DateTime expiryDate, string spTableAlias)
        {
            var specialPriceUpdateEnt = new SpecialPriceEntity();
            if (!string.IsNullOrEmpty(spTableAlias))
            {
                specialPriceUpdateEnt.Fields[(int)SpecialPriceFieldIndex.IsInForce].SetObjectAlias(spTableAlias);
                specialPriceUpdateEnt.Fields[(int)SpecialPriceFieldIndex.ExpiryDate].SetObjectAlias(spTableAlias);
            }
            if (expiryDate.Date <= DateTime.Today)
            {
                specialPriceUpdateEnt.IsInForce = false;
                specialPriceUpdateEnt.ExpiryDate = DateTime.Now;
            }
            else
                specialPriceUpdateEnt.ExpiryDate = expiryDate;
            return specialPriceUpdateEnt;
        }

The bucket and the entity are then passed to Adapter.UpdateEntitiesDirectly with nothing else being done to them.

Hi saggett,

I read your posts but I don't get what is the problem. AFAIK, SelectListAlias is used for fetches (I used it for DerivedTables). Also what query would you expect?

UPDATE table_name alias SET ...

That is an incorrect syntax, isn't?

If you use the same entity twice, alias one of them (the one you're filtering on, not the one you're updating)

Daelmo, you're right, UPDATE table_name alias SET is incorrect syntax now I think about it.

So as a result I need a slightly different aliasing strategy when doing an update than when doing a fetch. Aliasing the same table twice works in select (you can fetch on an aliased table), but not in update (you can't update on an aliased table). So what I ought to have done was only aliased one of the tables to start with, like Otis said.

Thanks!