Hi there,

I've got...

const string aliasRightOperand = "ta";

var joinExpression = new PredicateExpression(VwStatsWeekenderChaletsTotalAvailableFields.Berth.SetObjectAlias(aliasRightOperand) == new EntityField2("Berth", initalSumsTable.Alias, typeof(int)));

var dynamicRelation = new DynamicRelation(initalSumsTable, JoinHint.Right, EntityType.VwStatsWeekenderChaletsTotalAvailableEntity, aliasRightOperand, joinExpression);

In the SQL, "EntityType.VwStatsWeekenderChaletsTotalAvailableEntity" is being referred to as, 'LPA_t2'.

If I have the following field...

fields.DefineField(new EntityField2("TotalAvailableChalets", dynamicRelation.AliasRightOperand, typeof(int)), 7);

In the SELECT list its being referred to as, '[ta].[TotalAvailableChalets]'.

So why is it that the alias being set when creating the join is ignored but the alias set on the field (even though its looked up from the join!) is being used in the query?

Cheers, Ian.

Hi Ian, Please post this:

• Full relevant code snippet
• Generated SQL
• LLBLGen version and runtime library version

Here it is...

        public DataTable GetChaletStats(int eventId, PaymentMethodEnum[] selectedPaymentMethods,
                                        TransactionStatusEnum[] selectedStatus)
        {
            var adapter = new DataAccessAdapter();

            DerivedTableDefinition initalSumsTable = GetInitalTotalsDynamicRelation(eventId, selectedPaymentMethods,
                                                                             selectedStatus);

            var bucket = new RelationPredicateBucket();

            const string aliasRightOperand = "ta";

            var joinExpression = new PredicateExpression(VwStatsWeekenderChaletsTotalAvailableFields.Berth.SetObjectAlias(aliasRightOperand) == new EntityField2("Berth", initalSumsTable.Alias, typeof(int)));

            var dynamicRelation = new DynamicRelation(initalSumsTable, JoinHint.Right, EntityType.VwStatsWeekenderChaletsTotalAvailableEntity, aliasRightOperand, joinExpression);
            bucket.Relations.Add(dynamicRelation);


            var dataTable = new DataTable();

            var fields = new ResultsetFields(8);

            fields.DefineField(new EntityField2("Berth", initalSumsTable.Alias, typeof(int)), 0);

            fields.DefineField(new EntityField2("Takings", initalSumsTable.Alias, typeof(int)), 1);
            fields.DefineField(new EntityField2("RequiredBalanceTakings", initalSumsTable.Alias, typeof(int)), 2);
            fields.DefineField(new EntityField2("BalanceTakings", initalSumsTable.Alias, typeof(int)), 3);

            fields.DefineField(new EntityField2("FullPaymentsQuantity", initalSumsTable.Alias, typeof(int)), 4);
            fields.DefineField(new EntityField2("DepositPaymentsQuantity", initalSumsTable.Alias, typeof(int)), 5);
            fields.DefineField(new EntityField2("BalancePaymentsQuantity", initalSumsTable.Alias, typeof(int)), 6);

           fields.DefineField(new EntityField2("TotalAvailableChalets", dynamicRelation.AliasRightOperand, typeof(int)), 7);
        
            adapter.FetchTypedList(fields, dataTable, bucket, 0, null, true, null);

            return dataTable;
        }


private DerivedTableDefinition GetInitalTotalsDynamicRelation(int eventId, PaymentMethodEnum[] selectedPaymentMethods,
                                                               TransactionStatusEnum[] selectedStatus)
        {
            var fields = new ResultsetFields(7);
            
            fields.DefineField(VwChaletOrderDetail2Fields.Berth, 0, "Berth");

            fields.DefineField(VwChaletOrderDetail2Fields.Takings, 1, "Takings", AggregateFunction.Sum);
            fields.DefineField(VwChaletOrderDetail2Fields.RequiredBalanceTakings, 2, "RequiredBalanceTakings", AggregateFunction.Sum);
            fields.DefineField(VwChaletOrderDetail2Fields.BalanceTakings, 3, "BalanceTakings", AggregateFunction.Sum);

            fields.DefineField(VwChaletOrderDetail2Fields.FullPaymentsQuantity, 4, "FullPaymentsQuantity", AggregateFunction.Sum);
            fields.DefineField(VwChaletOrderDetail2Fields.DepositPaymentsQuantity, 5, "DepositPaymentsQuantity", AggregateFunction.Sum);
            fields.DefineField(VwChaletOrderDetail2Fields.BalancePaymentsQuantity, 6, "BalancePaymentsQuantity", AggregateFunction.Sum);
            
            IPredicateExpression predicateExpression = new PredicateExpression
                                                           {
                                                               VwChaletOrderDetail2Fields.EventId == eventId,
                                                               VwChaletOrderDetail2Fields.PaymentMethodId ==
                                                               selectedPaymentMethods,
                                                               VwChaletOrderDetail2Fields.Status ==
                                                               selectedStatus
                                                           };

            IGroupByCollection groupByClause = new GroupByCollection { VwChaletOrderDetail2Fields.Berth };

            var dtDefinition = new DerivedTableDefinition(fields, "t0", predicateExpression, groupByClause);

            return dtDefinition;
        }

exec sp_executesql N'SELECT [LPA_t1].[Berth], [LPA_t1].[Takings], [LPA_t1].[RequiredBalanceTakings], [LPA_t1].[BalanceTakings], [LPA_t1].[FullPaymentsQuantity], [LPA_t1].[DepositPaymentsQuantity], [LPA_t1].[BalancePaymentsQuantity], [ta].[TotalAvailableChalets] FROM ( (SELECT [bangfaceweekender].[dbo].[vw_chalet_order_detail2].[Berth], SUM([bangfaceweekender].[dbo].[vw_chalet_order_detail2].[Takings]) AS [Takings], SUM([bangfaceweekender].[dbo].[vw_chalet_order_detail2].[RequiredBalanceTakings]) AS [RequiredBalanceTakings], SUM([bangfaceweekender].[dbo].[vw_chalet_order_detail2].[BalanceTakings]) AS [BalanceTakings], SUM([bangfaceweekender].[dbo].[vw_chalet_order_detail2].[FullPaymentsQuantity]) AS [FullPaymentsQuantity], SUM([bangfaceweekender].[dbo].[vw_chalet_order_detail2].[DepositPaymentsQuantity]) AS [DepositPaymentsQuantity], SUM([bangfaceweekender].[dbo].[vw_chalet_order_detail2].[BalancePaymentsQuantity]) AS [BalancePaymentsQuantity] FROM [bangfaceweekender].[dbo].[vw_chalet_order_detail2]  WHERE ( [bangfaceweekender].[dbo].[vw_chalet_order_detail2].[EventId] = @EventId1 AND [bangfaceweekender].[dbo].[vw_chalet_order_detail2].[PaymentMethodId] IN (@PaymentMethodId2, @PaymentMethodId3, @PaymentMethodId4) AND [bangfaceweekender].[dbo].[vw_chalet_order_detail2].[Status] IN (@Status5)) GROUP BY [bangfaceweekender].[dbo].[vw_chalet_order_detail2].[Berth]) [LPA_t1]  RIGHT JOIN [bangfaceweekender].[dbo].[vw_stats_weekender_chalets_total_available] [LPA_t2]  ON  ( [LPA_t2].[Berth] = [LPA_t1].[Berth]))',N'@EventId1 int,@PaymentMethodId2 int,@PaymentMethodId3 int,@PaymentMethodId4 int,@Status5 int',@EventId1=9,@PaymentMethodId2=1,@PaymentMethodId3=2,@PaymentMethodId4=3,@Status5=1

The multi-part identifier "ta.TotalAvailableChalets" could not be bound.

And this is with V2.6 October 9th 2009 and 2.6.10.0421.

I need you to try 2 things:

1- Use the follwoing:

var dynamicRelation = new DynamicRelation(initalSumsTable, JoinHint.Right, EntityType.VwStatsWeekenderChaletsTotalAvailableEntity, aliasRightOperand, joinExpression);
            bucket.Relations.Add(dynamicRelation, aliasRightOperand);

2- Use The latest build of the runtime library.

Hi Walaa,

I can't do one because there's no suitable overload. I can't try two because I haven't yet had the opportunity to fix the security error I was getting on my shared server when using LLBLGen V3.

( http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=18828 )

Ian.

bucket.Relations.Add(dynamicRelation, aliasRightOperand);

The following overloads are available: SD.LLBLGen.Pro.ORMSupportClasses Namespace > IRelationCollection Interface : Add Method.

Add(IRelation) Add(IEntityRelation) Add(IEntityRelation,JoinHint) Add(IEntityRelation,String) Add(IEntityRelation,String,JoinHint) Add(IEntityRelation,String,String,JoinHint) Add(IDynamicRelation)

Please check the reference manual.

But you were suggesting that I try

Add(IDynamicRelation, string)

which doesn't exist.

You are right, sorry I missed that.

I can't try two because I haven't yet had the opportunity to fix the security error I was getting on my shared server when using LLBLGen V3.

Can you test the latest build on a development machine?

That's a lot of projects that need their references updated. I think I'm just gonna use a stored procedure for now.

OK I switched it over to V3 and got some new looking SQL but the same problem is there...

exec sp_executesql N'SELECT [LPA_t1].[Berth], [LPA_t1].[Takings], [LPA_t1].[RequiredBalanceTakings], [LPA_t1].[BalanceTakings], [LPA_t1].[FullPaymentsQuantity], [LPA_t1].[DepositPaymentsQuantity], [LPA_t1].[BalancePaymentsQuantity], [ta].[TotalAvailableChalets] FROM ( (SELECT [bangfaceweekender].[dbo].[vw_chalet_order_detail2].[Berth], SUM([bangfaceweekender].[dbo].[vw_chalet_order_detail2].[Takings]) AS [Takings], SUM([bangfaceweekender].[dbo].[vw_chalet_order_detail2].[RequiredBalanceTakings]) AS [RequiredBalanceTakings], SUM([bangfaceweekender].[dbo].[vw_chalet_order_detail2].[BalanceTakings]) AS [BalanceTakings], SUM([bangfaceweekender].[dbo].[vw_chalet_order_detail2].[FullPaymentsQuantity]) AS [FullPaymentsQuantity], SUM([bangfaceweekender].[dbo].[vw_chalet_order_detail2].[DepositPaymentsQuantity]) AS [DepositPaymentsQuantity], SUM([bangfaceweekender].[dbo].[vw_chalet_order_detail2].[BalancePaymentsQuantity]) AS [BalancePaymentsQuantity] FROM [bangfaceweekender].[dbo].[vw_chalet_order_detail2]   WHERE ( [bangfaceweekender].[dbo].[vw_chalet_order_detail2].[EventId] = @p1 AND [bangfaceweekender].[dbo].[vw_chalet_order_detail2].[PaymentMethodId] IN (@p2, @p3, @p4) AND [bangfaceweekender].[dbo].[vw_chalet_order_detail2].[Status] IN (@p5)) GROUP BY [bangfaceweekender].[dbo].[vw_chalet_order_detail2].[Berth]) [LPA_t1]  RIGHT JOIN [bangfaceweekender].[dbo].[vw_stats_weekender_chalets_total_available] [LPA_t2]  ON  ( [LPA_t2].[Berth] = [LPA_t1].[Berth]))',N'@p1 int,@p2 int,@p3 int,@p4 int,@p5 int',@p1=9,@p2=1,@p3=2,@p4=3,@p5=1

The multi-part identifier "ta.TotalAvailableChalets" could not be bound.

V3 October 1st, 2010, 3.0.10.1001.

A quick fix at the moment is to do the following...

fields.DefineField(new EntityField2("TotalAvailableChalets", "LPA_t2", typeof(int)), 7);

It's the 'AliasRightOperand' property. This property will return the alias of the derived table if the right side is a derived table, otherwise the set value.

If you do fields.DefineField(new EntityField2("TotalAvailableChalets", aliasRightOperand, typeof(int)), 7);

instead of fields.DefineField(new EntityField2("TotalAvailableChalets", dynamicRelation.AliasRightOperand, typeof(int)), 7);

it should work properly. It's a little odd perhaps, but it's mainly used by our query system: you don't read aliases from relations really: you set them in the first place with a value, so normally one would use that value instead of the value read from the relationship.

The big problem with aliases in our framework is that from the get go we didn't force developers to alias the elements to query. In cases where this is required, and no aliases were specified because the types were different (although they for example share the same supertype), aliases have to be 'created', which are called artificial aliases internally. These properties produce them if required.

It might have been better if we would have used an internal API for this, but the problem is that the code reading the aliases for query production isn't always in the same assembly, making it harder to use internals.

If you do fields.DefineField(new EntityField2("TotalAvailableChalets", aliasRightOperand, typeof(int)), 7);

instead of fields.DefineField(new EntityField2("TotalAvailableChalets", dynamicRelation.AliasRightOperand, typeof(int)), 7);

it should work properly.

It doesn't work though! It gives the same error. Whatever I use as the alias in the above code is _exactly _what is used in SELECT list of the generated SQL.

The problem seems to be further up when setting the alias upon creating the DynamicRelation and the join expression. The alias is being ignored and is being replaced by an automatically generated one - "LPA_t2".

OK, but what's the code which reproduces it exactly, as your method isn't small and uses methods not posted. Instead of wading through a lot of code, I'd like to know what exactly triggers it, could you trim down the code and check what code reproduces the problem? Thanks

Firstly, which way round do alias' work?

If I specify an alias, does the runtime translate it to an internal automatically generated alias or is the alias that I specify supposed to appear in the query as is?

If its the former then the alias set on the field in my code is not being translated. If its the latter then the alias set on the join is being ignored.

... and uses methods not posted

I don't think it does. Its completely LLBLGen code.

Ian wrote:

Firstly, which way round do alias' work?

If I specify an alias, does the runtime translate it to an internal automatically generated alias or is the alias that I specify supposed to appear in the query as is?

It always produces an own alias, LPA_.... This is converted from the alias given. THis is necessary because if you alias a subtype, two or more tables /views have to be aliased with the same alias, which goes wrong, hence we always generate our own aliases.

If its the former then the alias set on the field in my code is not being translated. If its the latter then the alias set on the join is being ignored.

Well, for one thing, your code is not really ok, as I described above, but as you say it doesn't solve it, something else is also not right. What it is, is not yet clear, so we have to reproduce it locally, for which we need a piece of code which is small and reproduces the problem on our own databases.

... and uses methods not posted

I don't think it does. Its completely LLBLGen code.

My bad, I thought the GetInitalTotalsDynamicRelation method wasn't given. Still it's a lot of code and I'd like to know what the smallest amount of code still produces the error, so we don't have to convert all the code to a repro with our own databases.