Hi,

I'm using the final build of LLBLGen Pro 2.6 against an MS SQL Server 2008 database, working in Visual Studio 2008, C# 3.0.

I'm trying to create a DynamicList from a number of tables in my database joined together. One of the tables I am joining to twice, from different start tables. From that table, (FeeEarner) in both cases I want to concatenate 2 fields together - FirstName and LastName to give me FullName. (Hope that makes sense)

Here's my code:

            ResultsetFields fields = new ResultsetFields(11);
            fields.DefineField(TaskFields.TaskId, 0);
            fields.DefineField(TaskFields.Title, 1);
            fields.DefineField(TaskFields.Description, 2);
            fields.DefineField(TaskFields.PriorityId, 3);
            fields.DefineField(new EntityField("FeeEarner", (FeeEarnerFields.FirstName + " " + FeeEarnerFields.LastName)), 4);
            fields.DefineField(TeamFields.Title, 5, "Team");
            fields.DefineField(WorkSourceFields.Name, 6, "Worksource");
            fields.DefineField(TaskFields.MatterId, 7, "LegisId");
            fields.DefineField(MatterFields.AccountsReference, 8);
            fields.DefineField(TaskFields.StartDate, 9, "Due");
            fields.DefineField(new EntityField("FeeEarner", (FeeEarnerFields.FirstName + " " + FeeEarnerFields.LastName)), 10, "MatterFeeEarnerFullName", "MatterFeeEarnerEntity");

            RelationCollection relationsToUse = new RelationCollection();
            relationsToUse.Add(TaskEntity.Relations.MatterEntityUsingMatterId, JoinHint.Left);
            relationsToUse.Add(MatterEntity.Relations.WorkSourceEntityUsingWorkSourceId, JoinHint.Left);
            relationsToUse.Add(TaskEntity.Relations.TeamEntityUsingTeamId, JoinHint.Left);
            relationsToUse.Add(TaskEntity.Relations.FeeEarnerEntityUsingFeeEarnerId, JoinHint.Left);
            relationsToUse.Add(MatterEntity.Relations.FeeEarnerEntityUsingFeeEarnerId, "MatterFeeEarnerEntity", JoinHint.Left);

The problem is that my second concatenation expression doesn't use the table alias that I need it to. Here's the SQL:

SELECT TOP 500
        [Legis].[dbo].[Task].[TaskId],
        [Legis].[dbo].[Task].[Title],
        [Legis].[dbo].[Task].[Description],
        [Legis].[dbo].[Task].[PriorityId],
        ( [Legis].[dbo].[FeeEarner].[FirstName] + @LO1a0d4c71 )
        + [Legis].[dbo].[FeeEarner].[LastName] AS [FeeEarner],
        [Legis].[dbo].[Team].[Title] AS [Team],
        [Legis].[dbo].[WorkSource].[Name] AS [Worksource],
        [Legis].[dbo].[Task].[MatterId] AS [LegisId],
        [Legis].[dbo].[Matter].[AccountsReference],
        [Legis].[dbo].[Task].[StartDate] AS [Due],
        ( [Legis].[dbo].[FeeEarner].[FirstName] + @LO2c213e42 )
        + [Legis].[dbo].[FeeEarner].[LastName] AS [MatterFeeEarnerFullName]
FROM    ( ( ( ( ( [Legis].[dbo].[Matter]
                  RIGHT JOIN [Legis].[dbo].[Task] ON [Legis].[dbo].[Matter].[MatterId] = [Legis].[dbo].[Task].[MatterId]
                )
                LEFT JOIN [Legis].[dbo].[WorkSource] ON [Legis].[dbo].[WorkSource].[WorkSourceId] = [Legis].[dbo].[Matter].[WorkSourceId]
              )
              LEFT JOIN [Legis].[dbo].[Team] ON [Legis].[dbo].[Team].[TeamId] = [Legis].[dbo].[Task].[TeamId]
            )
            LEFT JOIN [Legis].[dbo].[FeeEarner] ON [Legis].[dbo].[FeeEarner].[FeeEarnerId] = [Legis].[dbo].[Task].[FeeEarnerId]
          )
          LEFT JOIN [Legis].[dbo].[FeeEarner] [LPA_M1] ON [LPA_M1].[FeeEarnerId] = [Legis].[dbo].[Matter].[FeeEarnerId]
        )

I need the second concatenation to be:

( [LPA_M1].[FirstName] + ' ' ) + [LPA_M1].[LastName] AS [MatterFeeEarnerFullName]

I guess I need to work on the second expression, but I'm not sure how - could you point me in the right direction please?

Please try the following:

fields.DefineField(new EntityField("FeeEarner", (FeeEarnerFields.FirstName.SelObjectAlias("MatterFeeEarnerEntity") + " " + FeeEarnerFields.LastName.SelObjectAlias("MatterFeeEarnerEntity"))), 10, "MatterFeeEarnerFullName", "MatterFeeEarnerEntity");

Brilliant! That's exactly what I wanted!

I hadn't realised that SetObjectAlias was a way to refer to an aliased table - I'd assumed it was to alias the field.

Thanks - you guys rule!