LLBLGenPro Ver 2.0.0.0 Final (November 6th, 2006) SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll 2.0.0.61107 Using Adapater with subclasses for .NET 2.0 Using SQL Server 2005

I was experimenting with using dynamic lists. So I started out with a simple example where I just select some fields from a table. But it generates SQL that doesn't work.

Here is my code:

    DataAccessAdapter adapter = new DataAccessAdapter();
    System.Data.DataTable list = new System.Data.DataTable();

    // SELECT DocumentNumber, Volume, Page
    ResultsetFields fields = new ResultsetFields(3);
    fields.DefineField(DocumentFieldIndex.DocumentNumber, 0, "DocumentNumber", "Document" );
    fields.DefineField(DocumentFieldIndex.Volume, 1, "Volume", "Document" );
    fields.DefineField(DocumentFieldIndex.Page, 2, "Page", "Document" );

    // WHERE Volume = '  1100'
    IRelationPredicateBucket bucket = new RelationPredicateBucket();
    bucket.PredicateExpression.Add(DocumentFields.Volume == "  1100");

    // ORDER BY DocumentNumber
    ISortExpression sorter = new SortExpression();
    sorter.Add(SortClauseFactory.Create(DocumentFieldIndex.DocumentNumber, SortOperator.Ascending));

    int maxItems = 0;
    bool allowDuplicates = false;

    adapter.FetchTypedList(fields, list, bucket, maxItems, sorter, allowDuplicates);

Generated Sql query:

    Query: SELECT [Document].[DocumentNumber], [Document].[Volume], [Document].[Page] FROM [GCSLRMS_Grant].[dbo].[Document] AS [Document]  WHERE ( ( [GCSLRMS_Grant].[dbo].[Document].[Volume] = @Volume1)) ORDER BY [GCSLRMS_Grant].[dbo].[Document].[DocumentNumber] ASC
    Parameter: @Volume1 : AnsiString. Length: 6. Precision: 0. Scale: 0. Direction: Input. Value: "  1100".

I tried running the query in SQL Server Management Studio, and it says: Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "GCSLRMS_Grant.dbo.Document.Volume" could not be bound.

The query would work if it didn't use an alias in the FROM clause or if the field in the WHERE clause used the table alias just like the fields in the SELECT list. How should I resolve this issue?

Thanks.

You specify an entity alias here:

    fields.DefineField(DocumentFieldIndex.DocumentNumber, 0, "DocumentNumber", "Document" );
    fields.DefineField(DocumentFieldIndex.Volume, 1, "Volume", "Document" );
    fields.DefineField(DocumentFieldIndex.Page, 2, "Page", "Document" );

namely 'Document'. Please change this into:

    fields.DefineField(DocumentFieldIndex.DocumentNumber, 0, "DocumentNumber");
    fields.DefineField(DocumentFieldIndex.Volume, 1, "Volume");
    fields.DefineField(DocumentFieldIndex.Page, 2, "Page");