Please advise, the above error occurs depending on the order of my define fields.

For example this will produce the error:

fields = new ResultsetFields(2);

            fields.DefineField(
                new EntityField2("BytesGB", RawLogsFields.ScBytes / 1024 , AggregateFunction.Avg),
                0
            );

            fields.DefineField(RawLogsFields.CIp, 1, "TotalUniqueIPs", AggregateFunction.CountDistinct);

But this will not produce an error:

fields = new ResultsetFields(2);

fields.DefineField(RawLogsFields.CIp, 0, "TotalUniqueIPs", AggregateFunction.CountDistinct);

fields.DefineField(
                new EntityField2("BytesGB", RawLogsFields.ScBytes / 1024 , AggregateFunction.Avg),
                1
            );

Why does changing the order affect this? What should I fix?

Thanks!

What is the exact exception message raised and also provide the call stack.

Could you also send the generated SQL statement

Again it is simply the issue that I cannot do a .definefield(new EntityField2()) as the first field. As long as this is added after a standard definefield(entityfields.fieldname... etc) everything is ok.

Not sure how to get you the SQL statement that LLBLGEN is generating, I am using SQL Express 2005. Let me know how and I'lll get it for you.

Exception:

System.Data.SqlClient.SqlException:

Call stack:

[SqlException (0x80131904): The multi-part identifier "SHClientLogs.dbo.raw_logs.sc_bytes" could not be bound.
The multi-part identifier "SHClientLogs.dbo.raw_logs.sc_bytes" could not be bound.
The multi-part identifier "SHClientLogs.dbo.raw_logs.c_ip" could not be bound.
The multi-part identifier "SHClientLogs.dbo.raw_logs.c_playerid" could not be bound.
The multi-part identifier "SHClientLogs.dbo.raw_logs.c_ip" could not be bound.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +95
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +82
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +346
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +3244
   System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +52
   System.Data.SqlClient.SqlDataReader.get_MetaData() +130
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +371
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1121
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +334
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +45
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +162
   System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +35
   System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +32
   System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +183
   System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior) +339
   System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) +198
   SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteMultiRowDataTableRetrievalQuery(IRetrievalQuery queryToExecute, DbDataAdapter dataAdapterToUse, DataTable tableToFill, IFieldPersistenceInfo[] fieldsPersistenceInfo) +666
   SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchTypedList(IEntityFields2 fieldCollectionToFetch, DataTable dataTableToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize) +239
   SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchTypedList(IEntityFields2 fieldCollectionToFetch, DataTable dataTableToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, Boolean allowDuplicates) +59
   SH.OPS.ClientCP.DL.ReportsDL.GetAggregateTotalsWms(String serviceName, DateTime startDate, DateTime endDate) in C:\Inetpub\wwwroot\test\SHOPS_ClassLib\test\DL\ReportsDL.cs:42
   Reports_Reports.Page_Load(Object sender, EventArgs e) in c:\Inetpub\wwwroot\test\www\Reports\Reports.aspx.cs:13
   System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +31
   System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +68
   System.Web.UI.Control.OnLoad(EventArgs e) +88
   System.Web.UI.Control.LoadRecursive() +74
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3034

Not sure how to get you the SQL statement that LLBLGEN is generating, I am using SQL Express 2005. Let me know how and I'lll get it for you.

Please read LLBLGenPro Help - Generated Code - Troubleshooting and debugging.

Please provide the two sql dqe generated for each case. I'll look into it to try repro your scenario.

Ok, this is what my test reflects:

This code is "normal", define two fields, one of those is AVG and the oder is for grouping.

DataAccessAdapter adapter = new DataAccessAdapter();
ResultsetFields fields = new ResultsetFields(2);

// define fields
fields.DefineField(OrdersFields.CustomerId, 0, "CustomerId");
fields.DefineField(new EntityField2("CustomFreightAvg", OrdersFields.Freight + 1, AggregateFunction.Avg), 1);
                
// define gruop clause
IGroupByCollection groupByClause = new GroupByCollection();
groupByClause.Add(fields[0]);           

// retrieve
DataTable dynamicList = new DataTable();
adapter.FetchTypedList(fields, dynamicList, null, 0, null, true, groupByClause);

The above code produce this SQL (everything ok):

SELECT [Northwind].[dbo].[Orders].[CustomerID] AS [CustomerId], AVG([Northwind].[dbo].[Orders].[Freight] + @LO1fa0abc1) AS [CustomFreightAvg] FROM [Northwind].[dbo].[Orders]  GROUP BY [Northwind].[dbo].[Orders].[CustomerID]
    Parameter: @LO1fa0abc1 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.

On the other hand, if I switch the order of fields, and of course change the groupBy field, trows an exception:

DataAccessAdapter adapter = new DataAccessAdapter();
ResultsetFields fields = new ResultsetFields(2);

// define fields            
fields.DefineField(new EntityField2("CustomFreightAvg", OrdersFields.Freight + 1, AggregateFunction.Avg), 0);
fields.DefineField(OrdersFields.CustomerId, 1, "CustomerId");
                
// define gruop clause
IGroupByCollection groupByClause = new GroupByCollection();
groupByClause.Add(fields[1]);           

// retrieve
DataTable dynamicList = new DataTable();
adapter.FetchTypedList(fields, dynamicList, null, 0, null, true, groupByClause);

The above code produce the following SQL (Weird, seems to be missing the FROM clause)

SELECT AVG([Northwind].[dbo].[Orders].[Freight] + @LO1fa0abc1) AS [CustomFreightAvg], [Northwind].[dbo].[Orders].[CustomerID] AS [CustomerId] GROUP BY [Northwind].[dbo].[Orders].[CustomerID]
    Parameter: @LO1fa0abc1 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.

**Exception: **System.Data.SqlClient.SqlException

Error Message:

The column prefix 'Northwind.dbo.Orders' does not match with a table name or alias name used in the query.
The column prefix 'Northwind.dbo.Orders' does not match with a table name or alias name used in the query.
The column prefix 'Northwind.dbo.Orders' does not match with a table name or alias name used in the query.

Error Trace:

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteMultiRowDataTableRetrievalQuery(IRetrievalQuery queryToExecute, DbDataAdapter dataAdapterToUse, DataTable tableToFill, IFieldPersistenceInfo[] fieldsPersistenceInfo)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchTypedList(IEntityFields2 fieldCollectionToFetch, DataTable dataTableToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchTypedList(IEntityFields2 fieldCollectionToFetch, DataTable dataTableToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, Boolean allowDuplicates, IGroupByCollection groupByClause)
   at NorthwindTest.Program.RetrieveDYLChangingOrder() in D:\samples\LLBLGen test\Northwind Adpater\code\NorthwindTest\Program.cs:line 485
   at NorthwindTest.Program.Main(String[] args) in D:\samples\LLBLGen test\Northwind Adpater\code\NorthwindTest\Program.cs:line 514
   at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
   at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
   at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
   at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.ThreadHelper.ThreadStart()

We'll looking into this soon. So please use the workaround that works for the moment.

This is a known issue, please use the workaround with the field instance. As there's no persistence info available, the query engine can't produce a FROM clause.