I have a LLBLGenProDataSource2 control with LivePersistence false and paging true.

<llblgenpro:LLBLGenProDataSource2 ID="LLBLGenProDataSource2_1" runat="server" AdapterTypeName="Test.AdapterDataLayer.DatabaseSpecific.DataAccessAdapter, Test.AdapterDataLayerDBSpecific, Version=1.0.3079.26715, Culture=neutral, PublicKeyToken=0dc7c1a061008ccc" AllowDuplicates="False" CacheLocation="ASPNetCache" DataContainerType="EntityCollection" EnablePaging="True" LivePersistence="False" OnPerformGetDbCount="LLBLGenProDataSource2_1_PerformGetDbCount"
OnPerformSelect="LLBLGenProDataSource2_1_PerformSelect" EntityFactoryTypeName="Test.AdapterDataLayer.FactoryClasses.WebFormEntityFactory, Test.AdapterDataLayer, Version=1.0.3079.26716, Culture=neutral, PublicKeyToken=0dc7c1a061008ccc">
</llblgenpro:LLBLGenProDataSource2>

In my code, I'm trying (desperately, for the last 2 days) to retrieve a grouped subset of table records for viewing only. I figured out that I need to use a TypedList and I'm close, but when I run the code, I get an "Object reference not set to an instance of an object" on the "e.ContainedTypedList" object:

protected void LLBLGenProDataSource2_1_PerformSelect(object sender, PerformSelectEventArgs2 e)
{
    using (DataAccessAdapter adapter = new DataAccessAdapter(Test.Common.Settings.ConnectionString))
    {
        WebFormTypedList list = new WebFormTypedList();
        IRelationPredicateBucket bucket = list.GetRelationInfo();           
        adapter.FetchTypedList(list.GetFieldsInfo(), (DataTable)e.ContainedTypedList, bucket, 0, null, true, e.GroupBy, e.PageNumber, e.PageSize);
    }
}

Earlier in the code, on the search button click, I'm setting the groupby and related fields

WebFormTypedList list = new WebFormTypedList();
            IGroupByCollection groupByClause = new GroupByCollection();
            IEntityFields2 fields = list.GetFieldsInfo();
            groupByClause.Add(fields["Email"]);
            groupByClause.Add(fields["FormName"]);
            groupByClause.Add(fields["CurrURL"]);
            groupByClause.HavingClause = new PredicateExpression();
            groupByClause.HavingClause.Add(new FieldBetweenPredicate(WebFormFields.FormDate, null, StartDate, EndDate));
            groupByClause.HavingClause.Add(WebFormFields.FormName == ddlWebForms.SelectedValue);

            SortExpression sorter = new SortExpression(new SortClause(new WebFormEntity().Fields[this.SortExpression], null, this.SortDirection == "ASC" ? SortOperator.Ascending : SortOperator.Descending));
            LLBLGenProDataSource2_1.GroupByToUse = groupByClause;

I seem to need to use this 'DataTable' overload, because the 'typedListToFill' overload doesn't give me the option to specify the e.GroupBy parameter.

Can you possibly point me in the right direction?

<llblgenpro:LLBLGenProDataSource2 ID="LLBLGenProDataSource2_1" runat="server" AdapterTypeName="Test.AdapterDataLayer.DatabaseSpecific.DataAccessAdapter, Test.AdapterDataLayerDBSpecific, Version=1.0.3079.26715, Culture=neutral, PublicKeyToken=0dc7c1a061008ccc" AllowDuplicates="False" CacheLocation="ASPNetCache" DataContainerType="EntityCollection" EnablePaging="True" LivePersistence="False" OnPerformGetDbCount="LLBLGenProDataSource2_1_PerformGetDbCount" OnPerformSelect="LLBLGenProDataSource2_1_PerformSelect" [b]EntityFactoryTypeName="Test.AdapterDataLayer.FactoryClasses.WebFormEntityFactory[/b], Test.AdapterDataLayer, Version=1.0.3079.26716, Culture=neutral, PublicKeyToken=0dc7c1a061008ccc"> </llblgenpro:LLBLGenProDataSource2>

Your LLBLGenProDataSource was setup to use an entityCollection not a TypedList.

Thanks.

You were right. I had tried EntityCollection, TypedView and finally TypedList and somehow the page got out of sync with the codebehind.

Now I have finally managed to get the thing working, however I notice (using Sql Server Profiler) that when paging the GridView, the entire set of records is retrieved from the database when you move to each page.

I was expecting a 'SELECT DISTINCT TOP <pageSize>.....'

Why would this be?

I have EnablePaging set to true and AllowDuplicates set to false on the LLBLGenProDataSource2 control.

Some conditions may stop paging from being executed in the database side. Please check the following thread for more info: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=13411

Well, I don't have any relationships set up, I have made AllowDuplicates=true and there are no 'text' or 'ntext' fields in the table, yet the datasource control still retrieves all records for every page. The only reason I'm handling PerformSelect and PerformDBCount is because I'm grouping the results and I can't see any other way to do this.

At the moment this is not so much of an issue, because there are only 800 records in the table, but later it will become an issue.

Here's the code for the event procedures on the datasource:

protected void LLBLGenProDataSource2_1_PerformSelect(object sender, PerformSelectEventArgs2 e)
        {
            if (Page.IsPostBack)
            {
                using (DataAccessAdapter adapter = new DataAccessAdapter(Settings.ConnectionString))
                {
                    adapter.FetchTypedList(this.List.GetFieldsInfo(), (DataTable)e.ContainedTypedList, e.Filter, 0, e.Sorter, false, e.GroupBy, e.PageNumber, e.PageSize);
                }
            }
        }
        protected void LLBLGenProDataSource2_1_PerformGetDbCount(object sender, PerformGetDbCountEventArgs2 e)
        {
            int count = 0;
            if (Page.IsPostBack)
            {
                using (DataAccessAdapter adapter = new DataAccessAdapter(Settings.ConnectionString))
                {
                    count = (int)adapter.GetDbCount(this.List.GetFieldsInfo(), e.Filter, e.GroupBy, false);
                }
            }
            e.DbCount = count;
            lblTotalRecs.Text = string.Format(RECORDSFOUNDTEXT, count);
        }

The 'List' property of the page returns the TypedList object which is created on the Search button click event, and stored in the session.

The GroupBy, Sort, and Filter properties of the datasource control are also set on click of the search button.

I didn't realise this would take me so long. I thought I'd have solved the problem in a couple of hours and it's taken 3 days. There just doesn't seem to be much information on setting up Filters and GroupBy and Sorting, (let alone a 'HAVING' clause), in conjunction with the LLBLGenProDataSource control. I've been right through the documentation, examples and so forth, and hundreds of posts on the forum, and still had to figure most of it out myself - by trial and error.

Hope you can throw some light on it.

I was expecting a 'SELECT DISTINCT TOP <pageSize>.....'

Was it the first page? (PageNumber == 1)? Would you please post the generated SQL Query? Also please attach a simple repro solution? better if it was based on Northwind.

There just doesn't seem to be much information on setting up Filters and GroupBy and Sorting, (let alone a 'HAVING' clause), in conjunction with the LLBLGenProDataSource control.

There is nothing much about it anyway, specifying a Filter, Sorter, Group By collection (with or without having), can be done using the corresponding LLBLGenProDataSource public properties.

And if LivePersistence = flase, i.e. you handle the PerformSelect and PerformGetDBCount yourself then pass the e._propertyName_ to the corresponding fetch method parameter.

Or you might create the filter(or sorter ...etc) inside the method handler and use it instead of the passed in e._propertyName_.

In the debugger, please break on the PerformSelect method and check whether there are paging properties send to the datasourcecontrol, i.e. if e.PageNumber and PageSize are > 0. Are these > 0 ?

Also, the first page is never a paging query, just a query using TOP. All subsequential queries will either use a temp table, or if you set the compatibility level to 2, a CTE.

Also, if the query results in duplicates, or if the engine thinks it might be resulting in duplicate rows, it can't page on the server and pages on the client. This isn't as bad as it looks, it simply traverses through the datareader till there are enough rows read.

It would be great if you could post the query which is generated and executed when you click on page 2 (so not the first page). Obtain the query by enabling DQE tracing (see Troubleshooting and debugging).

Otis, yes, e.PageNumber and e.PageSize are correct in PerformSelect. They are set to 2 and 25 respectively for Page 2.

I'm using SQL Server 2000. I haven't set the compatibility level manually and don't know what a CTE is (?).

I enabled DQE Tracing as per the manual, and here's the output for the 3rd page:

Method Enter: CreateRowCountDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT DISTINCT MAX([TC].[dbo].[tbl_WebForm].[Company]) AS [Company], MAX([TC].[dbo].[tbl_WebForm].[FormDate]) AS [FormDate], MAX([TC].[dbo].[tbl_WebForm].[Firstname]) AS [Firstname], MAX([TC].[dbo].[tbl_WebForm].[Lastname]) AS [Lastname], [TC].[dbo].[tbl_WebForm].[Email], MAX([TC].[dbo].[tbl_WebForm].[Telephone]) AS [Telephone], MAX([TC].[dbo].[tbl_WebForm].[State]) AS [State], [TC].[dbo].[tbl_WebForm].[FormName], MAX([TC].[dbo].[tbl_WebForm].[InfoNightType]) AS [InfoNightType], MAX([TC].[dbo].[tbl_WebForm].[InfoNight]) AS [InfoNight], MAX([TC].[dbo].[tbl_WebForm].[RefBy]) AS [RefBy], MAX([TC].[dbo].[tbl_WebForm].[Advert]) AS [Advert], MAX([TC].[dbo].[tbl_WebForm].[RefURL]) AS [RefUrl], [TC].[dbo].[tbl_WebForm].[CurrURL] AS [CurrUrl] FROM [TC].[dbo].[tbl_WebForm]  WHERE ( ( ( ( [TC].[dbo].[tbl_WebForm].[FormDate] BETWEEN @FormDate1 AND @FormDate2 AND [TC].[dbo].[tbl_WebForm].[FormName] = @FormName3)))) GROUP BY [TC].[dbo].[tbl_WebForm].[Email], [TC].[dbo].[tbl_WebForm].[FormName], [TC].[dbo].[tbl_WebForm].[CurrURL]
    Parameter: @FormDate1 : DateTime. Length: 0. Precision: 23. Scale: 3. Direction: Input. Value: 12/03/2008 12:00:00 AM.
    Parameter: @FormDate2 : DateTime. Length: 0. Precision: 23. Scale: 3. Direction: Input. Value: 12/06/2008 12:00:00 AM.
    Parameter: @FormName3 : String. Length: 100. Precision: 0. Scale: 0. Direction: Input. Value: "http://www.invcircle.com.au/Free/thankYou_IC_Evenings.aspx".

Method Exit: CreateSelectDQ
Generated Sql query: 
    Query: SELECT COUNT(*) AS NumberOfRows FROM (SELECT DISTINCT MAX([TC].[dbo].[tbl_WebForm].[Company]) AS [Company], MAX([TC].[dbo].[tbl_WebForm].[FormDate]) AS [FormDate], MAX([TC].[dbo].[tbl_WebForm].[Firstname]) AS [Firstname], MAX([TC].[dbo].[tbl_WebForm].[Lastname]) AS [Lastname], [TC].[dbo].[tbl_WebForm].[Email], MAX([TC].[dbo].[tbl_WebForm].[Telephone]) AS [Telephone], MAX([TC].[dbo].[tbl_WebForm].[State]) AS [State], [TC].[dbo].[tbl_WebForm].[FormName], MAX([TC].[dbo].[tbl_WebForm].[InfoNightType]) AS [InfoNightType], MAX([TC].[dbo].[tbl_WebForm].[InfoNight]) AS [InfoNight], MAX([TC].[dbo].[tbl_WebForm].[RefBy]) AS [RefBy], MAX([TC].[dbo].[tbl_WebForm].[Advert]) AS [Advert], MAX([TC].[dbo].[tbl_WebForm].[RefURL]) AS [RefUrl], [TC].[dbo].[tbl_WebForm].[CurrURL] AS [CurrUrl] FROM [TC].[dbo].[tbl_WebForm]  WHERE ( ( ( ( [TC].[dbo].[tbl_WebForm].[FormDate] BETWEEN @FormDate1 AND @FormDate2 AND [TC].[dbo].[tbl_WebForm].[FormName] = @FormName3)))) GROUP BY [TC].[dbo].[tbl_WebForm].[Email], [TC].[dbo].[tbl_WebForm].[FormName], [TC].[dbo].[tbl_WebForm].[CurrURL]) TmpResult
    Parameter: @FormDate1 : DateTime. Length: 0. Precision: 23. Scale: 3. Direction: Input. Value: 12/03/2008 12:00:00 AM.
    Parameter: @FormDate2 : DateTime. Length: 0. Precision: 23. Scale: 3. Direction: Input. Value: 12/06/2008 12:00:00 AM.
    Parameter: @FormName3 : String. Length: 100. Precision: 0. Scale: 0. Direction: Input. Value: "http://www.invcircle.com.au/Free/thankYou_IC_Evenings.aspx".

Method Exit: CreateRowCountDQ
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT MAX([TC].[dbo].[tbl_WebForm].[Company]) AS [Company], MAX([TC].[dbo].[tbl_WebForm].[FormDate]) AS [FormDate], MAX([TC].[dbo].[tbl_WebForm].[Firstname]) AS [Firstname], MAX([TC].[dbo].[tbl_WebForm].[Lastname]) AS [Lastname], [TC].[dbo].[tbl_WebForm].[Email], MAX([TC].[dbo].[tbl_WebForm].[Telephone]) AS [Telephone], MAX([TC].[dbo].[tbl_WebForm].[State]) AS [State], [TC].[dbo].[tbl_WebForm].[FormName], MAX([TC].[dbo].[tbl_WebForm].[InfoNightType]) AS [InfoNightType], MAX([TC].[dbo].[tbl_WebForm].[InfoNight]) AS [InfoNight], MAX([TC].[dbo].[tbl_WebForm].[RefBy]) AS [RefBy], MAX([TC].[dbo].[tbl_WebForm].[Advert]) AS [Advert], MAX([TC].[dbo].[tbl_WebForm].[RefURL]) AS [RefUrl], [TC].[dbo].[tbl_WebForm].[CurrURL] AS [CurrUrl] FROM [TC].[dbo].[tbl_WebForm]  WHERE ( ( ( ( [TC].[dbo].[tbl_WebForm].[FormDate] BETWEEN @FormDate1 AND @FormDate2 AND [TC].[dbo].[tbl_WebForm].[FormName] = @FormName3)))) GROUP BY [TC].[dbo].[tbl_WebForm].[Email], [TC].[dbo].[tbl_WebForm].[FormName], [TC].[dbo].[tbl_WebForm].[CurrURL] ORDER BY [TC].[dbo].[tbl_WebForm].[FormDate] ASC
    Parameter: @FormDate1 : DateTime. Length: 0. Precision: 23. Scale: 3. Direction: Input. Value: 12/03/2008 12:00:00 AM.
    Parameter: @FormDate2 : DateTime. Length: 0. Precision: 23. Scale: 3. Direction: Input. Value: 12/06/2008 12:00:00 AM.
    Parameter: @FormName3 : String. Length: 100. Precision: 0. Scale: 0. Direction: Input. Value: "http://www.invcircle.com.au/Free/thankYou_IC_Evenings.aspx".

Method Exit: CreateSelectDQ
Generated Sql query: 
    Query: SELECT MAX([TC].[dbo].[tbl_WebForm].[Company]) AS [Company], MAX([TC].[dbo].[tbl_WebForm].[FormDate]) AS [FormDate], MAX([TC].[dbo].[tbl_WebForm].[Firstname]) AS [Firstname], MAX([TC].[dbo].[tbl_WebForm].[Lastname]) AS [Lastname], [TC].[dbo].[tbl_WebForm].[Email], MAX([TC].[dbo].[tbl_WebForm].[Telephone]) AS [Telephone], MAX([TC].[dbo].[tbl_WebForm].[State]) AS [State], [TC].[dbo].[tbl_WebForm].[FormName], MAX([TC].[dbo].[tbl_WebForm].[InfoNightType]) AS [InfoNightType], MAX([TC].[dbo].[tbl_WebForm].[InfoNight]) AS [InfoNight], MAX([TC].[dbo].[tbl_WebForm].[RefBy]) AS [RefBy], MAX([TC].[dbo].[tbl_WebForm].[Advert]) AS [Advert], MAX([TC].[dbo].[tbl_WebForm].[RefURL]) AS [RefUrl], [TC].[dbo].[tbl_WebForm].[CurrURL] AS [CurrUrl] FROM [TC].[dbo].[tbl_WebForm]  WHERE ( ( ( ( [TC].[dbo].[tbl_WebForm].[FormDate] BETWEEN @FormDate1 AND @FormDate2 AND [TC].[dbo].[tbl_WebForm].[FormName] = @FormName3)))) GROUP BY [TC].[dbo].[tbl_WebForm].[Email], [TC].[dbo].[tbl_WebForm].[FormName], [TC].[dbo].[tbl_WebForm].[CurrURL] ORDER BY [TC].[dbo].[tbl_WebForm].[FormDate] ASC
    Parameter: @FormDate1 : DateTime. Length: 0. Precision: 23. Scale: 3. Direction: Input. Value: 12/03/2008 12:00:00 AM.
    Parameter: @FormDate2 : DateTime. Length: 0. Precision: 23. Scale: 3. Direction: Input. Value: 12/06/2008 12:00:00 AM.
    Parameter: @FormName3 : String. Length: 100. Precision: 0. Scale: 0. Direction: Input. Value: "http://www.invcircle.com.au/Free/thankYou_IC_Evenings.aspx".

Method Exit: CreatePagingSelectDQ

It looks like paging is definitely being done on the client side, for whatever reason.

Walaa, I will try to set up something with Northwind for a repro.

You're using on all fields the MAX aggregate, is that intentional? You don't need to cast a typedlist to a datatable, a typedlist derives from a datatable so it will compile fine.

The problem is your sorter in combination of the MAX aggregate.

Take this example query: select distinct max(employeeId) as maxempid, customerid from orders group by customerid order by employeeid

This gives the following error: Server: Msg 145, Level 15, State 1, Line 1 ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

The DQE checks for this situation and if it encounters a sortclause which isn't in the select list, it switches off distinct. The query can't predict if duplicates aren't occuring so it switches off paging on the server.

group by in general doesn't give duplicates, but it might be: grouping on a constant for example.

I'm not entirely sure if the max aggregate is intentional or by accident, as to me it doesn't do much, simply because it will result in the same data in every row (as there's just 1 max )

Thanks for your response Otis.

Ok. Removing the sorter works, in that the trace now shows 'SELECT DISTINCT TOP 25 MAX'. Unfortunately I need the recordset sorted, so I s'pose I'll have to live with doing it the old way.

Removing the DataTable cast, however, causes errors:

adapter.FetchTypedList(this.List.GetFieldsInfo(), e.ContainedTypedList, e.Filter, 0, e.Sorter, false, e.GroupBy, e.PageNumber, e.PageSize);

..and this is what I get...

The best overloaded method match for 'SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchTypedList(SD.LLBLGen.Pro.ORMSupportClasses.IEntityFields2, System.Data.DataTable, SD.LLBLGen.Pro.ORMSupportClasses.IRelationPredicateBucket, int, SD.LLBLGen.Pro.ORMSupportClasses.ISortExpression, bool, SD.LLBLGen.Pro.ORMSupportClasses.IGroupByCollection, int, int)' has some invalid arguments

Argument '2': cannot convert from 'SD.LLBLGen.Pro.ORMSupportClasses.ITypedListLgp2' to 'System.Data.DataTable'

So I guess I'll leave that cast in there as well <sigh>.

You're using on all fields the MAX aggregate, is that intentional?

I'm not entirely sure if the max aggregate is intentional or by accident, as to me it doesn't do much, simply because it will result in the same data in every row (as there's just 1 max )

The **MAX **aggregate on the other fields is definitely intentional. It's there because I know that only the fields in the GroupBy will differ. The other field values (like firstname, lastname, state) will be the same for the grouped records. Therefore it doesn't matter whether I use MIN or MAX, I'll get the correct values.

You see, occasionally there is method behind our madness

Meteor wrote:

Thanks for your response Otis.

Ok. Removing the sorter works, in that the trace now shows 'SELECT DISTINCT TOP 25 MAX'. Unfortunately I need the recordset sorted, so I s'pose I'll have to live with doing it the old way.

What does 'doing it the old way' mean? In any way: the query won't pull all rows into memory. If you fetch page 2, and pages are 25 rows each, it will read till it has seen 50 unique rows and then quit. This is done over a datareader, so it won't read all rows into memory, just the ones it needs.

So it's not as dramatic as it looks. Just because there's no limit in the query doesn't mean a limit isn't possible.

Removing the DataTable cast, however, causes errors:

adapter.FetchTypedList(this.List.GetFieldsInfo(), e.ContainedTypedList, e.Filter, 0, e.Sorter, false, e.GroupBy, e.PageNumber, e.PageSize);

..and this is what I get...

The best overloaded method match for 'SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchTypedList(SD.LLBLGen.Pro.ORMSupportClasses.IEntityFields2, System.Data.DataTable, SD.LLBLGen.Pro.ORMSupportClasses.IRelationPredicateBucket, int, SD.LLBLGen.Pro.ORMSupportClasses.ISortExpression, bool, SD.LLBLGen.Pro.ORMSupportClasses.IGroupByCollection, int, int)' has some invalid arguments

Argument '2': cannot convert from 'SD.LLBLGen.Pro.ORMSupportClasses.ITypedListLgp2' to 'System.Data.DataTable'

So I guess I'll leave that cast in there as well <sigh>.

Ah, I see what the problem is now. e.ContainedTypedList is an interface type, so you can either use the overloads which accept the interface type, or indeed have to cast. I must say that the 'sigh' remark suggests this is all a big pain and life is miserable. I hope it's not THAT bad

You're using on all fields the MAX aggregate, is that intentional?

I'm not entirely sure if the max aggregate is intentional or by accident, as to me it doesn't do much, simply because it will result in the same data in every row (as there's just 1 max )

The **MAX **aggregate on the other fields is definitely intentional. It's there because I know that only the fields in the GroupBy will differ. The other field values (like firstname, lastname, state) will be the same for the grouped records. Therefore it doesn't matter whether I use MIN or MAX, I'll get the correct values.

You see, occasionally there is method behind our madness

Though it could lead to a lot less performance with the query in the database, so that's also what should be kept in mind.