I'm trying to add a calculated field to this table and am getting an error because I need to alias my field expression. I just don't know how to go about doing that.

      Dim fields As New ResultsetFields(8)

      Dim myExpr As Expression
      myExpr = New Expression(EntityFieldFactory.Create(MaPlansEquipmentRateFieldIndex.Volume), _
        ExOp.Mul, EntityFieldFactory.Create(MaPlansEquipmentRateFieldIndex.Volume))


      fields.DefineField(MaPlanFieldIndex.PlanId, 0, "PlanId", "Plan")
      fields.DefineField(MaPlanFieldIndex.CycleId, 1, "CycleId", "Plan")
      fields.DefineField(MaPlansEquipmentRateFieldIndex.MeterTypeId, 2, "MeterTypeId", "Rates")
      fields.DefineField(MaMeterTypeFieldIndex.MeterType, 3, "MeterType", "MeterTypes")
      fields.DefineField(MaPlansEquipmentRateFieldIndex.FlatFee, 4, "FlatFee", "Rates", AggregateFunction.Sum)
      fields.DefineField(MaPlansEquipmentRateFieldIndex.Volume, 5, "Volume", "Rates", AggregateFunction.Sum)
      fields.DefineField(MaPlansEquipmentRateFieldIndex.Base, 6, "Base", "Rates", AggregateFunction.Sum)
      fields.DefineField(MaPlansEquipmentRateFieldIndex.Base, 7, "Test", "Rates", AggregateFunction.Sum)
      fields(7).ExpressionToApply = myExpr


      Dim relations As New RelationCollection()
      relations.Add(MaPlanEntity.Relations.MaPlansEquipmentEntityUsingPlanId, "Plan", "Machine", JoinHint.None)
      relations.Add(MaPlansEquipmentEntity.Relations.MaPlansEquipmentRateEntityUsingMaPlansEquipmentId, "Machine", "Rates", JoinHint.None)
      relations.Add(MaPlansEquipmentRateEntity.Relations.MaMeterTypeEntityUsingMeterTypeId, "Rates", "MeterTypes", JoinHint.None)

      Dim pe As IPredicateExpression = New PredicateExpression
      pe.Add(PredicateFactory.CompareValue(MaPlanFieldIndex.PlanId, ComparisonOperator.Equal, planId, "Plan"))

      Dim groupByClause As New GroupByCollection()
      groupByClause.Add(fields(0))
      groupByClause.Add(fields(1))
      groupByClause.Add(fields(2))
      groupByClause.Add(fields(3))

If I look at the SQL generated by this code I just need to include the alias for my calulated field and all will be good.

Is this possible?

Thanks in advance, Warren

What's the RuntimeLibrary version you are using? (SD.LLBLGen.Pro.ORMSupportClasses.NET11.dll -> RightClick -> Properties -> Version -> File Version)

Also it would be helpful if you post the generated SQL.

Thanks.

You don't need entity aliasing in your query, as you don't use tables twice. Remove all aliasses (except the field aliases in definefield) and it should work.

To set aliases on fields in an expression:

myExpr = New Expression(MaPlansEquipmentRateFields.Volume.SetObjectAlias("Plan"), _
        ExOp.Mul, MaPlansEquipmentRateFields.Volume.SetObjectAlias("Plan"))

SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll I'm using version 1.0.20051.60317

Self Serving/ 2 class

The reason I used Object Alias in the first place is because when I added my aggregate fields to my query it became a required parameter. I tried using "nothing" but that threw an error.

I tried Frans suggestion just now but SetObjectAlias is not a member of Volume.

This is the SQL currently being generated.

SELECT DISTINCT [LPA_P2].[PlanId], [LPA_P2].[CycleId], [LPA_R1].[MeterTypeId],
[LPA_M4].[MeterType], SUM([LPA_R1].[FlatFee]) AS [FlatFee], 
SUM([LPA_R1].[Volume]) AS [Volume], SUM([LPA_R1].[Base]) AS [Base], 
SUM([Admin].[dbo].[Ma_Plans_Equipment_Rates].[Volume] * 
         [Admin].[dbo].[Ma_Plans_Equipment_Rates].[Volume]) AS [Test] 

FROM ((( [Admin].[dbo].[Ma_Plans] [LPA_P2] INNER JOIN [Admin].[dbo].[Ma_Plans_Equipment] [LPA_M3]  
     ON  [LPA_P2].[PlanId]=[LPA_M3].[PlanId]) INNER JOIN [Admin].[dbo].[Ma_Plans_Equipment_Rates] [LPA_R1]  
     ON  [LPA_M3].[MaPlansEquipmentId]=[LPA_R1].[MaPlansEquipmentId]) INNER JOIN [Admin].[dbo].[Ma_MeterTypes] [LPA_M4]  
     ON  [LPA_M4].[MeterTypeId]=[LPA_R1].[MeterTypeId]) 
WHERE ( ( [LPA_P2].[PlanId] = @PlanId1)) 
GROUP BY [LPA_P2].[PlanId],[LPA_P2].[CycleId],[LPA_R1].[MeterTypeId],[LPA_M4].[MeterType]

It's the Helperclass which creates the fields, not the fieldindex. Did you by any chance specify the field index instead of entitynameFields.FieldName?

That exactly what I did .

Is it possible to define a field and set its aggregate function without setting the object alias?

I think I can do it if I set the aggregate separately but would like to do it in one line.

Thanks for the prompt responses; love the product.

Is it possible to define a field and set its aggregate function without setting the object alias?

I think I can do it if I set the aggregate separately but would like to do it in one line.

Do you mean you want to define the object alias later? If you want to define the alias later then you could do something like this after you have setup the expression.

CType(myExpr.LeftOperand, IEntityField).SetObjectAlias("Plan")

CustomerFields.CompanyName.SetAggregateFunction(...)

See the reference manual

Do you mean you want to define the object alias later?

No, I don't want to use object alias at all.

I guess I'm looking for a overload of fields.Definefield

So this

fields.DefineField(MaPlansEquipmentRateFieldIndex.Volume, 5, "Volume", "Rates", AggregateFunction.Sum)

becomes

fields.DefineField(MaPlansEquipmentRateFieldIndex.Volume, 5, "Volume", AggregateFunction.Sum)

I tried Nothing in place of "Rates" as well but got an error.

My SQL doesn't need table aliases but when I was creating the fields collection and got to the aggregates I didn't see a way around not using them.

I tried Nothing in place of "Rates" as well but got an error.

So you tried the following and failed?

fields.DefineField(MaPlansEquipmentRateFieldIndex.Volume, 5, "Volume", "", AggregateFunction.Sum)

note the "" parameter?

If this fails what was the error/exception?

That works, thanks.

I had it like this:

fields.DefineField(MaPlansEquipmentRateFieldIndex.Volume, 5, "Volume", nothing, AggregateFunction.Sum)