Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > Bugs & Issues> Aliasing when using UpdateEntitiesDirectly
 

Pages: 1
Bugs & Issues
Aliasing when using UpdateEntitiesDirectly
Page:1/1 

  Print all messages in this thread  
Poster Message
saggett
User



Location:
Manchester, UK
Joined on:
12-Nov-2007 15:44:46
Posted:
50 posts
# Posted on: 04-Jul-2008 13:58:29.  
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:

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.

  Top
saggett
User



Location:
Manchester, UK
Joined on:
12-Nov-2007 15:44:46
Posted:
50 posts
# Posted on: 04-Jul-2008 14:04:00.  
Here's my query generation code:

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:
Code:

        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.


  Top
daelmo
Support Team



Location:
Guatemala City
Joined on:
28-Nov-2005 23:35:24
Posted:
8099 posts
# Posted on: 05-Jul-2008 05:16:11.  
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?
Code:
UPDATE table_name alias SET ...

That is an incorrect syntax, isn't?
David Elizondo
LLBLGen'ing (articles and code snippets) | linkedin | twitter
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37805 posts
# Posted on: 05-Jul-2008 10:39:46.  
If you use the same entity twice, alias one of them Regular Smiley (the one you're filtering on, not the one you're updating)

Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
saggett
User



Location:
Manchester, UK
Joined on:
12-Nov-2007 15:44:46
Posted:
50 posts
# Posted on: 07-Jul-2008 10:47:59.  
Daelmo, you're right, UPDATE table_name alias SET is incorrect syntax now I think about it. Embarrassed

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!
  Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.