Hi,

Am using LLBLGEN 2.6 and using the LINQ to sQL features of it. Here is the linq code I'm executing:

using (IDataAccessAdapter da = _dataAccessAdapterFactory.Create())
            {
                (new DataSource2<T>(da, new MyElementCreator(), null, null)).OrderByDescending(o => o.CreatedDateTimeUTC).Take(20).Where(c => c.CreatedDateTimeUTC < new DateTime(2012, 12, 5, 6, 23, 0)).ToList();
            
            }

where _**da **_is an instance derived from:

SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase

The SQL generated is:

SELECT [LPLA_1].[SubscriptionGroupID], [LPLA_1].[ShortCode], [LPLA_1].[UserID], [LPLA_1].[AuditFieldID], [LPLA_1].[ApprovalDateTimeUTC], [LPLA_1].[ApprovedByUserID], [LPLA_1].[SubscriptionID], [LPLA_1].[AuditGroupDescription], [LPLA_1].[AuditGroupMappingID], [LPLA_1].[AuditNoteGroupID], [LPLA_1].[CreatedDateTimeUTC], [LPLA_1].[IsDisapproval], [LPLA_1].[ReferenceDataNew], [LPLA_1].[ReferenceDataNewTranslated], [LPLA_1].[ReferenceDataOld], [LPLA_1].[ReferenceDataOldTranslated], [LPLA_1].[ReferenceEventCode], [LPLA_1].[ReferenceField], [LPLA_1].[ReferenceTable], [LPLA_1].[ReferenceNameDataXML], [LPLA_1].[RevenueAfter], [LPLA_1].[RevenueBefore], [LPLA_1].[RevenueExtAfter], [LPLA_1].[RevenueExtBefore], [LPLA_1].[RevenueNumberOfDays], [LPLA_1].[RevenueSumAfter], [LPLA_1].[RevenueSumBefore], [LPLA_1].[ActionDescription], [LPLA_1].[SubscriptionName], [LPLA_1].[SubscriptionEmail], [LPLA_1].[ActionByUser], [LPLA_1].[ActionByUserName], [LPLA_1].[ActionApprovedByUser], [LPLA_1].[ActionApprovedByUserName] FROM [Reports].[AuditSummary] [LPLA_1]  WHERE ( ( ( ( [LPLA_1].[CreatedDateTimeUTC] < @CreatedDateTimeUTC1)))) ORDER BY [LPLA_1].[CreatedDateTimeUTC] DESC
    Parameter: @CreatedDateTimeUTC1 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 5/12/2012 6:23:00 a.m..

Seeing as though I'm using the Take LINQ method in my linq query, how come there's no TOP statement in the select clause of the generated SQL. Is there an option I have to enable in the DataSource2 object used?

Any help greatly appreciated

Note: I'm using .NET 4.0, SQL Server 2008

Which runtimelibrary version (i.e. build no.) are you using?

Do you get back all results, or a the limited set? Sometimes Top doesn't get generated because it needs Distinct, and the query might not be eligible for a distinct. That's when the Framework turns into limiting the results it reads at the client side.

2.6.0.

Just tried Distinct() method an it didn't work? What conditions do I need to make that query eligible? Surely top should be applied regardless if I tell it to apply top

I've just realised that one of the fields I use is an XML field. Would that prevent top from being applied even when I use Distinct :

if(!allowDuplicates && !distinctViolatingTypesFound)
            {
                bool emitDistinct = true;
                if(sortClausesSpecified)
                {
                    // check if the sortclause fields are in the selectlist.
                    emitDistinct = CheckIfSortClausesAreInSelectList(fieldNamesInSelectList, sortClauses);
                }
                if(emitDistinct)
                {
                    queryText.Append(" DISTINCT");
                    toReturn = true;
                }
            }

There's distinctViolatingTypesFound condition in the source and one of the violating types appears to be an XML type. How do get !allowDuplicates to be satisfied with a linq query? My other option is to use a group by clause. How would I use the group by clause?

Tried the group by LINQ function to get it through and it works. However two queries are generated. The first:

Generated Sql query: 
    Query: SELECT [LPA_L1].[CreatedDateTimeUTC] AS [key], @LO11 AS [LPFA_2], [LPA_L1].[CreatedDateTimeUTC] FROM (SELECT TOP(@top1) [LPLA_1].[CreatedDateTimeUTC] FROM [Reports].[AuditSummary] [LPLA_1]  WHERE ( ( ( ( ( ( [LPLA_1].[ApprovedByUserID] IS NULL))) AND ( [LPLA_1].[CreatedDateTimeUTC] < @CreatedDateTimeUTC3)))) GROUP BY [LPLA_1].[CreatedDateTimeUTC] ORDER BY [LPLA_1].[CreatedDateTimeUTC] DESC) [LPA_L1]
    Parameter: @LO11 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.
    Parameter: @top1 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 20.
    Parameter: @CreatedDateTimeUTC3 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 5/12/2012 6:23:00 a.m..

The second:

Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [LPLA_4].[SubscriptionGroupID], [LPLA_4].[ShortCode], [LPLA_4].[UserID], [LPLA_4].[AuditFieldID], [LPLA_4].[ApprovalDateTimeUTC], [LPLA_4].[ApprovedByUserID], [LPLA_4].[SubscriptionID], [LPLA_4].[AuditGroupDescription], [LPLA_4].[AuditGroupMappingID], [LPLA_4].[AuditNoteGroupID], [LPLA_4].[CreatedDateTimeUTC], [LPLA_4].[IsDisapproval], [LPLA_4].[ReferenceDataNew], [LPLA_4].[ReferenceDataNewTranslated], [LPLA_4].[ReferenceDataOld], [LPLA_4].[ReferenceDataOldTranslated], [LPLA_4].[ReferenceEventCode], [LPLA_4].[ReferenceField], [LPLA_4].[ReferenceTable], [LPLA_4].[ReferenceNameDataXML], [LPLA_4].[RevenueAfter], [LPLA_4].[RevenueBefore], [LPLA_4].[RevenueExtAfter], [LPLA_4].[RevenueExtBefore], [LPLA_4].[RevenueNumberOfDays], [LPLA_4].[RevenueSumAfter], [LPLA_4].[RevenueSumBefore], [LPLA_4].[ActionDescription], [LPLA_4].[SubscriptionName], [LPLA_4].[SubscriptionEmail], [LPLA_4].[ActionByUser], [LPLA_4].[ActionByUserName], [LPLA_4].[ActionApprovedByUser], [LPLA_4].[ActionApprovedByUserName] FROM [Reports].[AuditSummary] [LPLA_4]  WHERE ( ( ( ( ( ( ( ( [LPLA_4].[ApprovedByUserID] IS NULL))) AND ( [LPLA_4].[CreatedDateTimeUTC] < @CreatedDateTimeUTC1))) AND ( [LPLA_4].[CreatedDateTimeUTC] IN (@CreatedDateTimeUTC2, @CreatedDateTimeUTC3, @CreatedDateTimeUTC4, @CreatedDateTimeUTC5, @CreatedDateTimeUTC6, @CreatedDateTimeUTC7, @CreatedDateTimeUTC8, @CreatedDateTimeUTC9, @CreatedDateTimeUTC10, @CreatedDateTimeUTC11, @CreatedDateTimeUTC12, @CreatedDateTimeUTC13, @CreatedDateTimeUTC14, @CreatedDateTimeUTC15, @CreatedDateTimeUTC16, @CreatedDateTimeUTC17, @CreatedDateTimeUTC18, @CreatedDateTimeUTC19, @CreatedDateTimeUTC20, @CreatedDateTimeUTC21))))) ORDER BY [LPLA_4].[CreatedDateTimeUTC] DESC
    Parameter: @CreatedDateTimeUTC1 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 5/12/2012 6:23:00 a.m..
    Parameter: @CreatedDateTimeUTC2 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 5/12/2012 6:19:01 a.m..
    Parameter: @CreatedDateTimeUTC3 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 5/12/2012 6:18:33 a.m..
    Parameter: @CreatedDateTimeUTC4 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 5/12/2012 6:18:10 a.m..
    Parameter: @CreatedDateTimeUTC5 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 5/12/2012 6:17:55 a.m..
    Parameter: @CreatedDateTimeUTC6 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 5/12/2012 6:17:37 a.m..
    Parameter: @CreatedDateTimeUTC7 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 5/12/2012 6:05:51 a.m..
    Parameter: @CreatedDateTimeUTC8 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 5/12/2012 6:02:47 a.m..
    Parameter: @CreatedDateTimeUTC9 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 5/12/2012 5:57:28 a.m..
    Parameter: @CreatedDateTimeUTC10 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 5/12/2012 5:56:41 a.m..
    Parameter: @CreatedDateTimeUTC11 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 5/12/2012 5:52:52 a.m..
    Parameter: @CreatedDateTimeUTC12 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 5/12/2012 5:49:17 a.m..
    Parameter: @CreatedDateTimeUTC13 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 5/12/2012 5:43:51 a.m..
    Parameter: @CreatedDateTimeUTC14 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 5/12/2012 5:36:54 a.m..
    Parameter: @CreatedDateTimeUTC15 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 5/12/2012 5:20:39 a.m..
    Parameter: @CreatedDateTimeUTC16 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 5/12/2012 5:16:32 a.m..
    Parameter: @CreatedDateTimeUTC17 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 5/12/2012 1:56:35 a.m..
    Parameter: @CreatedDateTimeUTC18 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 5/12/2012 1:48:04 a.m..
    Parameter: @CreatedDateTimeUTC19 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 5/12/2012 1:45:53 a.m..
    Parameter: @CreatedDateTimeUTC20 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 5/12/2012 1:34:47 a.m..
    Parameter: @CreatedDateTimeUTC21 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 5/12/2012 1:30:42 a.m..

Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.

Is there a better way to do it so that only one query is produced? I'm trying to implement a keyset based paging by searching on the Datetime clustered index: CreatedDateTimeUTC so that I don't have to pull back all records in a massive table to get the page I want

How do I force the framework to group elements by DateTime down to the millisecond since this is the level of precision I use in the database?

Indeed the XML field would prevent a Distinct.

How would you group a dateTime field down to the millisecond in pure SQL?

It appears to group down to the millisecond. What I was seeing was several records in db that all had the same datetime down to the millisecond all grouped together which is right. Problem solved. Thank you for your help

Good you figured it out

Actually, I'm not good again. I am grouping on DateTime field only, and it appears that the list of parameters is too short.

Firstly, here is the LINQ query run:

var theResult = temp.OrderByDescending(o => o.CreatedDateTimeUTC).Where(c => c.CreatedDateTimeUTC < new DateTime(2008, 08, 21, 23, 00, 0)).Take(20).GroupBy(g => g.CreatedDateTimeUTC).Select(g => g.ToList()).ToList();

And here is the first query run by LLBLGEN to presumably get the group by key values for the grouping:

Generated Sql query: 
    Query: SELECT [LPA_L1].[CreatedDateTimeUTC] AS [key], @LO11 AS [LPFA_2], [LPA_L1].[CreatedDateTimeUTC] FROM (SELECT TOP(@top1) [LPLA_1].[CreatedDateTimeUTC] FROM [Reports].[AuditSummary] [LPLA_1]  WHERE ( ( ( ( ( ( [LPLA_1].[ApprovedByUserID] IS NULL))) AND ( [LPLA_1].[CreatedDateTimeUTC] < @CreatedDateTimeUTC3)))) GROUP BY [LPLA_1].[CreatedDateTimeUTC] ORDER BY [LPLA_1].[CreatedDateTimeUTC] DESC) [LPA_L1]
    Parameter: @LO11 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.
    Parameter: @top1 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 20.
    Parameter: @CreatedDateTimeUTC3 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 21/08/2008 11:00:00 p.m..

The results of this query are as follows:

• key LPFA_20 CreatedDateTimeUTC 2008-08-21 22:48:13.607 1 2008-08-21 22:48:13.607 2008-08-21 04:40:39.153 1 2008-08-21 04:40:39.153 2008-08-21 04:14:49.747 1 2008-08-21 04:14:49.747 2008-08-21 04:12:57.717 1 2008-08-21 04:12:57.717 2008-08-21 04:10:26.263 1 2008-08-21 04:10:26.263 2008-08-21 04:10:26.077 1 2008-08-21 04:10:26.077 2008-08-21 04:10:26.060 1 2008-08-21 04:10:26.060 2008-08-21 04:10:26.043 1 2008-08-21 04:10:26.043 2008-08-21 04:10:25.937 1 2008-08-21 04:10:25.937 2008-08-21 04:10:25.590 1 2008-08-21 04:10:25.590 2008-08-21 04:10:25.577 1 2008-08-21 04:10:25.577 2008-08-21 04:10:25.560 1 2008-08-21 04:10:25.560 2008-08-21 04:10:25.293 1 2008-08-21 04:10:25.293 2008-08-21 04:10:25.170 1 2008-08-21 04:10:25.170 2008-08-21 04:10:25.030 1 2008-08-21 04:10:25.030

I check the theResult variable in the LINQ query above and it returns all these keys. However, the associated list for each key is empty for some keys.

The query then run to get the values for each of the group keys above is:

Generated Sql query: 
    Query: SELECT [LPLA_4].[SubscriptionGroupID], [LPLA_4].[ShortCode], [LPLA_4].[UserID], [LPLA_4].[AuditFieldID], [LPLA_4].[ApprovalDateTimeUTC], [LPLA_4].[ApprovedByUserID], [LPLA_4].[SubscriptionID], [LPLA_4].[AuditGroupDescription], [LPLA_4].[AuditGroupMappingID], [LPLA_4].[AuditNoteGroupID], [LPLA_4].[CreatedDateTimeUTC], [LPLA_4].[IsDisapproval], [LPLA_4].[ReferenceDataNew], [LPLA_4].[ReferenceDataNewTranslated], [LPLA_4].[ReferenceDataOld], [LPLA_4].[ReferenceDataOldTranslated], [LPLA_4].[ReferenceEventCode], [LPLA_4].[ReferenceField], [LPLA_4].[ReferenceTable], [LPLA_4].[ReferenceNameDataXML], [LPLA_4].[RevenueAfter], [LPLA_4].[RevenueBefore], [LPLA_4].[RevenueExtAfter], [LPLA_4].[RevenueExtBefore], [LPLA_4].[RevenueNumberOfDays], [LPLA_4].[RevenueSumAfter], [LPLA_4].[RevenueSumBefore], [LPLA_4].[ActionDescription], [LPLA_4].[SubscriptionName], [LPLA_4].[SubscriptionEmail], [LPLA_4].[ActionByUser], [LPLA_4].[ActionByUserName], [LPLA_4].[ActionApprovedByUser], [LPLA_4].[ActionApprovedByUserName] FROM [Reports].[AuditSummary] [LPLA_4]  WHERE ( ( ( ( ( ( ( ( [LPLA_4].[ApprovedByUserID] IS NULL))) AND ( [LPLA_4].[CreatedDateTimeUTC] < @CreatedDateTimeUTC1))) AND ( [LPLA_4].[CreatedDateTimeUTC] IN (@CreatedDateTimeUTC2, @CreatedDateTimeUTC3, @CreatedDateTimeUTC4, @CreatedDateTimeUTC5, @CreatedDateTimeUTC6, @CreatedDateTimeUTC7, @CreatedDateTimeUTC8, @CreatedDateTimeUTC9, @CreatedDateTimeUTC10))))) ORDER BY [LPLA_4].[CreatedDateTimeUTC] DESC
    Parameter: @CreatedDateTimeUTC1 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 21/08/2008 11:00:00 p.m..
    Parameter: @CreatedDateTimeUTC2 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 21/08/2008 10:48:13 p.m..
    Parameter: @CreatedDateTimeUTC3 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 21/08/2008 5:21:15 a.m..
    Parameter: @CreatedDateTimeUTC4 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 21/08/2008 5:21:07 a.m..
    Parameter: @CreatedDateTimeUTC5 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 21/08/2008 5:20:29 a.m..
    Parameter: @CreatedDateTimeUTC6 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 21/08/2008 4:40:39 a.m..
    Parameter: @CreatedDateTimeUTC7 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 21/08/2008 4:14:49 a.m..
    Parameter: @CreatedDateTimeUTC8 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 21/08/2008 4:12:57 a.m..
    Parameter: @CreatedDateTimeUTC9 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 21/08/2008 4:10:26 a.m..
    Parameter: @CreatedDateTimeUTC10 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 21/08/2008 4:10:25 a.m..

As you can see from the quoted parameters in the second query, there aren't enough parameters. There should be parameters for example for all the 2008-08-21 04:10:25 DateTime's down to the millisecond as outlined in the results of the first query. In the second query there's only one parameter value listed for 2008-08-21 04:10:25 ( can't tell from the log which of the 2008-08-21 04:10:25 it is; from the values in theResult, it appears 2008-08-21 04:10:25 key with millisecond value 030 is the only one populated. All other 2008-08-21 04:10:25 keys are empty. This makes me think that the underlying Parameters collection in the source code is adding parameters only down to the second and not the millisecond. The Parameters collection is a list so adding DateTime values down to the millisecond to it should work. Basically the parameters list output in the log should match the number values output in the results of the first query shouldn't it? Otherwise there's no way the results of the second query will return values for all keys in output of the first query.

Please help. Is there a way I can configure the precision or something?

There is a second problem. When the underlying query contains the like operator in the where clause, no values are returned for any of the DateTime keys. The problem is that I am telling LINQ to look across fields where the LIke operator is applied to search for any instances that match in those fields; therefore, the expressions involving the LIKE operator should be separated by OR not AND. LLBLGEN is putting AND in even though I told LINQ to use an OR.

Here is part the LINQ where condition which is attached to the rest of the where clause in my LINQ function:

Expression<Func<AuditSummary, bool>> search = h =>
                    (h.ActionDescription != null && h.ActionDescription.ToLower().Contains(searchKeywords)) ||
                    (h.SubscriptionName != null && h.SubscriptionName.ToLower().Contains(searchKeywords)) ||
                    (h.SubscriptionEmail != null && h.SubscriptionEmail.ToLower().Contains(searchKeywords)) ||
                    (h.ActionByUserName != null && h.ActionByUserName.ToLower().Contains(searchKeywords)) ||
                    (h.ActionByUser != null && h.ActionByUser.ToLower().Contains(searchKeywords)) ||
                    (h.ActionApprovedByUser != null && h.ActionApprovedByUser.ToLower().Contains(searchKeywords)) ||
                    (h.ActionApprovedByUserName != null && h.ActionApprovedByUserName.ToLower().Contains(searchKeywords)) ||
                    (h.ReferenceTable != null && h.ReferenceTable.ToLower().Contains(searchKeywords)) ||
                    (h.ReferenceField != null && h.ReferenceField.ToLower().Contains(searchKeywords));

and here is the generated query when I'm filtering on fields with the LiKe operator:

The first query to get the grouping keys:

Generated Sql query: 
    Query: SELECT [LPA_L1].[CreatedDateTimeUTC] AS [key], @LO11 AS [LPFA_20], [LPA_L1].[CreatedDateTimeUTC] FROM (SELECT TOP(@top1) [LPLA_1].[CreatedDateTimeUTC] FROM [Reports].[AuditSummary] [LPLA_1]  WHERE ( ( ( ( ( ( ( [LPLA_1].[ApprovedByUserID] IS NULL)) AND ( ( ( ( ( ( ( ( ( ( [LPLA_1].[ActionDescription] IS NOT NULL) AND ( LOWER([LPLA_1].[ActionDescription]) LIKE @LPFA_13)) OR ( ( [LPLA_1].[SubscriptionName] IS NOT NULL) AND ( LOWER([LPLA_1].[SubscriptionName]) LIKE @LPFA_24))) OR ( ( [LPLA_1].[SubscriptionEmail] IS NOT NULL) AND ( LOWER([LPLA_1].[SubscriptionEmail]) LIKE @LPFA_35))) OR ( ( [LPLA_1].[ActionByUserName] IS NOT NULL) AND ( LOWER([LPLA_1].[ActionByUserName]) LIKE @LPFA_46))) OR ( ( [LPLA_1].[ActionByUser] IS NOT NULL) AND ( LOWER([LPLA_1].[ActionByUser]) LIKE @LPFA_57))) OR ( ( [LPLA_1].[ActionApprovedByUser] IS NOT NULL) AND ( LOWER([LPLA_1].[ActionApprovedByUser]) LIKE @LPFA_68))) OR ( ( [LPLA_1].[ActionApprovedByUserName] IS NOT NULL) AND ( LOWER([LPLA_1].[ActionApprovedByUserName]) LIKE @LPFA_79))) OR ( ( [LPLA_1].[ReferenceTable] IS NOT NULL) AND ( LOWER([LPLA_1].[ReferenceTable]) LIKE @LPFA_810))) OR ( ( [LPLA_1].[ReferenceField] IS NOT NULL) AND ( LOWER([LPLA_1].[ReferenceField]) LIKE @LPFA_911))))) AND ( [LPLA_1].[CreatedDateTimeUTC] < @CreatedDateTimeUTC12)))) GROUP BY [LPLA_1].[CreatedDateTimeUTC] ORDER BY [LPLA_1].[CreatedDateTimeUTC] DESC) [LPA_L1]
    Parameter: @LO11 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.
    Parameter: @top1 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 20.
    Parameter: @LPFA_13 : String. Length: 14. Precision: 0. Scale: 0. Direction: Input. Value: "%billingevent%".
    Parameter: @LPFA_24 : String. Length: 14. Precision: 0. Scale: 0. Direction: Input. Value: "%billingevent%".
    Parameter: @LPFA_35 : String. Length: 14. Precision: 0. Scale: 0. Direction: Input. Value: "%billingevent%".
    Parameter: @LPFA_46 : String. Length: 14. Precision: 0. Scale: 0. Direction: Input. Value: "%billingevent%".
    Parameter: @LPFA_57 : String. Length: 14. Precision: 0. Scale: 0. Direction: Input. Value: "%billingevent%".
    Parameter: @LPFA_68 : String. Length: 14. Precision: 0. Scale: 0. Direction: Input. Value: "%billingevent%".
    Parameter: @LPFA_79 : String. Length: 14. Precision: 0. Scale: 0. Direction: Input. Value: "%billingevent%".
    Parameter: @LPFA_810 : String. Length: 14. Precision: 0. Scale: 0. Direction: Input. Value: "%billingevent%".
    Parameter: @LPFA_911 : String. Length: 14. Precision: 0. Scale: 0. Direction: Input. Value: "%billingevent%".
    Parameter: @CreatedDateTimeUTC12 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 5/12/2012 6:23:00 a.m..

This returns all the keys as expected and I can see this in the theResult variable of the LINQ query mentioned in my last post.

The second query to get the group values is:

SELECT [LPLA_4].[SubscriptionGroupID], [LPLA_4].[ShortCode], [LPLA_4].[UserID], [LPLA_4].[AuditFieldID], [LPLA_4].[ApprovalDateTimeUTC], [LPLA_4].[ApprovedByUserID], [LPLA_4].[SubscriptionID], [LPLA_4].[AuditGroupDescription], [LPLA_4].[AuditGroupMappingID], [LPLA_4].[AuditNoteGroupID], [LPLA_4].[CreatedDateTimeUTC], [LPLA_4].[IsDisapproval], [LPLA_4].[ReferenceDataNew], [LPLA_4].[ReferenceDataNewTranslated], [LPLA_4].[ReferenceDataOld], [LPLA_4].[ReferenceDataOldTranslated], [LPLA_4].[ReferenceEventCode], [LPLA_4].[ReferenceField], [LPLA_4].[ReferenceTable], [LPLA_4].[ReferenceNameDataXML], [LPLA_4].[RevenueAfter], [LPLA_4].[RevenueBefore], [LPLA_4].[RevenueExtAfter], [LPLA_4].[RevenueExtBefore], [LPLA_4].[RevenueNumberOfDays], [LPLA_4].[RevenueSumAfter], [LPLA_4].[RevenueSumBefore], [LPLA_4].[ActionDescription], [LPLA_4].[SubscriptionName], [LPLA_4].[SubscriptionEmail], [LPLA_4].[ActionByUser], [LPLA_4].[ActionByUserName], [LPLA_4].[ActionApprovedByUser], [LPLA_4].[ActionApprovedByUserName] FROM [Reports].[AuditSummary] [LPLA_4]  WHERE ( ( ( ( ( ( ( ( ( [LPLA_4].[ApprovedByUserID] IS NULL)) AND ( ( ( ( ( ( ( ( ( ( [LPLA_4].[ActionDescription] IS NOT NULL) AND ( LOWER([LPLA_4].[ActionDescription]) LIKE @LPFA_101)) AND ( ( [LPLA_4].[SubscriptionName] IS NOT NULL) AND ( LOWER([LPLA_4].[SubscriptionName]) LIKE @LPFA_112))) AND ( ( [LPLA_4].[SubscriptionEmail] IS NOT NULL) AND ( LOWER([LPLA_4].[SubscriptionEmail]) LIKE @LPFA_123))) AND ( ( [LPLA_4].[ActionByUserName] IS NOT NULL) AND ( LOWER([LPLA_4].[ActionByUserName]) LIKE @LPFA_134))) AND ( ( [LPLA_4].[ActionByUser] IS NOT NULL) AND ( LOWER([LPLA_4].[ActionByUser]) LIKE @LPFA_145))) AND ( ( [LPLA_4].[ActionApprovedByUser] IS NOT NULL) AND ( LOWER([LPLA_4].[ActionApprovedByUser]) LIKE @LPFA_156))) AND ( ( [LPLA_4].[ActionApprovedByUserName] IS NOT NULL) AND ( LOWER([LPLA_4].[ActionApprovedByUserName]) LIKE @LPFA_167))) AND ( ( [LPLA_4].[ReferenceTable] IS NOT NULL) AND ( LOWER([LPLA_4].[ReferenceTable]) LIKE @LPFA_178))) AND ( ( [LPLA_4].[ReferenceField] IS NOT NULL) AND ( LOWER([LPLA_4].[ReferenceField]) LIKE @LPFA_189))))) AND ( [LPLA_4].[CreatedDateTimeUTC] < @CreatedDateTimeUTC10))) AND ( [LPLA_4].[CreatedDateTimeUTC] IN (@CreatedDateTimeUTC11, @CreatedDateTimeUTC12, @CreatedDateTimeUTC13, @CreatedDateTimeUTC14, @CreatedDateTimeUTC15, @CreatedDateTimeUTC16, @CreatedDateTimeUTC17, @CreatedDateTimeUTC18, @CreatedDateTimeUTC19, @CreatedDateTimeUTC20, @CreatedDateTimeUTC21, @CreatedDateTimeUTC22, @CreatedDateTimeUTC23, @CreatedDateTimeUTC24, @CreatedDateTimeUTC25, @CreatedDateTimeUTC26, @CreatedDateTimeUTC27, @CreatedDateTimeUTC28, @CreatedDateTimeUTC29, @CreatedDateTimeUTC30))))) ORDER BY [LPLA_4].[CreatedDateTimeUTC] DESC
    Parameter: @LPFA_101 : String. Length: 14. Precision: 0. Scale: 0. Direction: Input. Value: "%billingevent%".
    Parameter: @LPFA_112 : String. Length: 14. Precision: 0. Scale: 0. Direction: Input. Value: "%billingevent%".
    Parameter: @LPFA_123 : String. Length: 14. Precision: 0. Scale: 0. Direction: Input. Value: "%billingevent%".
    Parameter: @LPFA_134 : String. Length: 14. Precision: 0. Scale: 0. Direction: Input. Value: "%billingevent%".
    Parameter: @LPFA_145 : String. Length: 14. Precision: 0. Scale: 0. Direction: Input. Value: "%billingevent%".
    Parameter: @LPFA_156 : String. Length: 14. Precision: 0. Scale: 0. Direction: Input. Value: "%billingevent%".
    Parameter: @LPFA_167 : String. Length: 14. Precision: 0. Scale: 0. Direction: Input. Value: "%billingevent%".
    Parameter: @LPFA_178 : String. Length: 14. Precision: 0. Scale: 0. Direction: Input. Value: "%billingevent%".
    Parameter: @LPFA_189 : String. Length: 14. Precision: 0. Scale: 0. Direction: Input. Value: "%billingevent%".
    Parameter: @CreatedDateTimeUTC10 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 5/12/2012 6:23:00 a.m..
    Parameter: @CreatedDateTimeUTC11 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 5/12/2012 6:19:01 a.m..
    Parameter: @CreatedDateTimeUTC12 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 5/12/2012 6:18:33 a.m..
    Parameter: @CreatedDateTimeUTC13 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 5/12/2012 6:18:10 a.m..
    Parameter: @CreatedDateTimeUTC14 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 5/12/2012 6:17:55 a.m..
    Parameter: @CreatedDateTimeUTC15 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 5/12/2012 6:17:37 a.m..
    Parameter: @CreatedDateTimeUTC16 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 5/12/2012 6:05:51 a.m..
    Parameter: @CreatedDateTimeUTC17 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 5/12/2012 6:02:47 a.m..
    Parameter: @CreatedDateTimeUTC18 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 5/12/2012 5:57:28 a.m..
    Parameter: @CreatedDateTimeUTC19 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 5/12/2012 5:56:41 a.m..
    Parameter: @CreatedDateTimeUTC20 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 5/12/2012 5:52:52 a.m..
    Parameter: @CreatedDateTimeUTC21 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 5/12/2012 5:49:17 a.m..
    Parameter: @CreatedDateTimeUTC22 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 5/12/2012 5:43:51 a.m..
    Parameter: @CreatedDateTimeUTC23 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 5/12/2012 5:36:54 a.m..
    Parameter: @CreatedDateTimeUTC24 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 5/12/2012 5:20:39 a.m..
    Parameter: @CreatedDateTimeUTC25 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 5/12/2012 5:16:32 a.m..
    Parameter: @CreatedDateTimeUTC26 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 5/12/2012 1:56:35 a.m..
    Parameter: @CreatedDateTimeUTC27 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 5/12/2012 1:48:04 a.m..
    Parameter: @CreatedDateTimeUTC28 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 5/12/2012 1:45:53 a.m..
    Parameter: @CreatedDateTimeUTC29 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 5/12/2012 1:34:47 a.m..
    Parameter: @CreatedDateTimeUTC30 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 5/12/2012 1:30:42 a.m..

This second query should return values if each parameter value passed in is precise down to the millisecond as each of the keys returned by the first query are accurate to the millisecond. However, as the LIKE operator conditions in the where clause are separated by AND and not OR obviously no values are returned. The LIKE operator conditions should be separated by OR as I told it to in the LINQ query!

When I check the theResult variable, all keys are empty

Please help?

Before we dig deeper into this, and since you are using v.2.6, it's crucial to answer this question.

Which runtime library version (i.e. build no.) are you using?

Please check the forum guidelines to know, how to get that number.

From the forum guidelines at this URL: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=9076

When it's a problem occuring at runtime, post the Runtime library version. The runtime library version is obtainable by rightclicking the SD.LLBLGen.Pro.ORMSupportClasses.NETxy.dll in windows explorer and then by selecting properties and the version tab. The version is then enlisted at the top as the fileversion. It has the typical format as 2.0.0.YYMMDD, or starting in 2007, the format 2.0.YY.MMDD

Following this I get 2.6.9.116 as the file version in the ORMSupportClasses dll.

Also, is there a way to debug through the source in my application. I've tried compiling the source and I get the attached error when I run my application. How do I get round it? Sorry for all the questions. I've tried a bindingRedirect in my web.config but it doesn't solve it. I must need the same publickeytoken of the original assembly, but that would require getting access to the assembly key file that was used to sign it. Does the designer have to use my compiled version of the assembly in order for the generated model project to recognize a reference to my version of the compiled ORMSupportClasses assembly? I get this error when I change the reference in the generated model project:

Error   246 The type 'SD.LLBLGen.Pro.ORMSupportClasses.Context' is defined in an assembly that is not referenced. You must add a reference to assembly 'SD.LLBLGen.Pro.ORMSupportClasses.NET20, Version=2.6.0.0, Culture=neutral, PublicKeyToken=ca73b74ba4e3ff27'. C:\Code\SubXero\project\Xero.Billing.Model\DatabaseGeneric\Linq\LinqMetaData.cs 362 10  Xero.Billing.Model

Am I right in thinking that the only way I can debug through the source is if I compile my own version of the assembly?

With regard to debugging the source, I've discovered that there is a folder in the LLBLGen designer installtion directory called C:\Program Files (x86)\Solutions Design\LLBLGen Pro v2.6\RuntimeLibraries\DotNET20\ORMSupportClassesDebugBuild which gives me access to the debug symbols that was generated during compilation of the source in the C:\Program Files (x86)\Solutions Design\LLBLGen Pro v2.6\Sourcecode directory. Setting the debugger going in VS for this source code in this directory for my web app works for the ORMSupportClasses dll but do you have the dll with associated symbols for the SD.LLBLGen.Pro.DQE.SqlServer.NET20.dll that was generated using the code in the source code directory so I can debug it? I could compile it myself but would need to associatethe same publickeytoken to the compiled assembly to make it work (otherwise I'll get the error message in the attachment of the previous post) - i don't know how to do it basically?

I notice there is this is the AssemblyInfo file: [assembly: AssemblyKeyFile("C:\Myprojects\mystrongkey.key")]. My understanding is that I'd need that key file in order to associate the same publickeytoken to my compiled assembly, correct me if I'm wrong

Also, how does Visual studio enforce a specific runtime ORMSupportClasses assembly with a specific publickeytoken to be compiled with the generated LLBLGen model project?

To be able to work with the Sourcecode:

1. Add the following projects from the [LLBLGen installation folder]\SourceCode location to your solution: ORMSupportClasses, LinqSupportClasses and SQLServerDQE.

2. Comment those lines on AssemblyInfo.cs that try to reference the strongKey, that is not necessary for debugging. Do it for all the projects mentioned.

3. Fix the references on your generated code projects and your GUI so they reference the included projects. You also have to fix the reference on LinqSupportClasses project to reference the project ORMSupportClasses, because I think it is referencing the assembly directly. I think that is the error you are getting in your last post.

About your millisecond problem (the one with the grouping, not the LIKE), I don't understand this:

var theResult = temp.OrderByDescending(o => o.CreatedDateTimeUTC).Where(c => c.CreatedDateTimeUTC < new DateTime(2008, 08, 21, 23, 00, 0)).Take(20).GroupBy(g => g.CreatedDateTimeUTC).Select(g => g.ToList()).ToList();

Why are you using .Select(g => g.ToList()).ToList() (.ToList twice). What is the real sql you are trying to get?

I can't come with a reproducible case with this using Northwind, I can't reproduce it. Even if I use PrefetchPaths to force the use of the datetime parameters (with millisecond precision), it works. Can you code a reproducible code using Nothwind that shows the underlying problem (the millisecond precision)?

Will get back to you about then LINQ problem later. About debugging the source code though. I have followed your instructions and added those three projects from the source code directory to my solution and then added project references to them in the generated code projects and when I compile I get many errors but the one that is most relevant is this:

Error   431 The type 'SD.LLBLGen.Pro.ORMSupportClasses.Context' is defined in an assembly that is not referenced. You must add a reference to assembly 'SD.LLBLGen.Pro.ORMSupportClasses.NET20, Version=2.6.0.0, Culture=neutral, PublicKeyToken=ca73b74ba4e3ff27'. C:\Code\SubXero\project\Xero.Billing.Model\DatabaseGeneric\Linq\LinqMetaData.cs 341 10  Xero.Billing.Model

It's almost like Visual studio knows which assembly with a certain publickeytoken was used to produce the generated code? How would it know that? Is it some kind of security feature?

Cheers, for your help

About this:

Why are you using .Select(g => g.ToList()).ToList()

am using ToList() in .Select to get the actual values in each grouping. Would that be causing the problem? Should i just use

.Select(g => g).ToList()

This is the SQL I am trying to get:

SELECT [LPLA_4].[SubscriptionGroupID], [LPLA_4].[ShortCode], [LPLA_4].[UserID], [LPLA_4].[AuditFieldID], [LPLA_4].[ApprovalDateTimeUTC], [LPLA_4].[ApprovedByUserID], [LPLA_4].[SubscriptionID], [LPLA_4].[AuditGroupDescription], [LPLA_4].[AuditGroupMappingID], [LPLA_4].[AuditNoteGroupID], [LPLA_4].[CreatedDateTimeUTC], [LPLA_4].[IsDisapproval], [LPLA_4].[ReferenceDataNew], [LPLA_4].[ReferenceDataNewTranslated], [LPLA_4].[ReferenceDataOld], [LPLA_4].[ReferenceDataOldTranslated], [LPLA_4].[ReferenceEventCode], [LPLA_4].[ReferenceField], [LPLA_4].[ReferenceTable], [LPLA_4].[ReferenceNameDataXML], [LPLA_4].[RevenueAfter], [LPLA_4].[RevenueBefore], [LPLA_4].[RevenueExtAfter], [LPLA_4].[RevenueExtBefore], [LPLA_4].[RevenueNumberOfDays], [LPLA_4].[RevenueSumAfter], [LPLA_4].[RevenueSumBefore], [LPLA_4].[ActionDescription], [LPLA_4].[SubscriptionName], [LPLA_4].[SubscriptionEmail], [LPLA_4].[ActionByUser], [LPLA_4].[ActionByUserName], [LPLA_4].[ActionApprovedByUser], [LPLA_4].[ActionApprovedByUserName] FROM [Reports].[AuditSummary] [LPLA_4] WHERE ( ( ( ( ( ( ( ( [LPLA_4].[ApprovedByUserID] IS NULL))) AND ( [LPLA_4].[CreatedDateTimeUTC] < '2008-08-21 23:00:00'))) AND ( [LPLA_4].[CreatedDateTimeUTC] IN ('2008-08-21 22:48:13.607', '2008-08-21 04:40:39.153', '2008-08-21 04:14:49.747', '2008-08-21 04:12:57.717', '2008-08-21 04:10:26.263', '2008-08-21 04:10:26.077', '2008-08-21 04:10:26.060', '2008-08-21 04:10:26.043', '2008-08-21 04:10:25.937', '2008-08-21 04:10:25.590', '2008-08-21 04:10:25.577', '2008-08-21 04:10:25.560', '2008-08-21 04:10:25.293', '2008-08-21 04:10:25.170', '2008-08-21 04:10:25.030'))))) ORDER BY [LPLA_4].[CreatedDateTimeUTC] DESC

As you can see there are more datetime values in the IN clause then there are parameters output in the generated SQL. For instance, there is only one parameter for 2008-08-21 04:10:25, but there are many more values for 2008-08-21 04:10:25 produced down to the millisecond produced by the first generated SQL statement; where that first generated query was used to get the group key values.

I can't say for certain without testing it, but with the Northwind database it could be that it appears to be working because there aren't multiple values down to the second like in my example. Will try an see if I can replicate with Northwind. Is this the correct download to get the Northwind db: http://northwinddatabase.codeplex.com/?

Have downloaded the Northwind db from the codeplex location mentioned in my previous post and I can't see any date fields that are accurate down to the millisecond. They are all accurate down to the day only (i.e. ShippedDate in the dbo.Orders table and HireDate in the dbo.Employees table).

I have attached a screenshot of the tables/views I am seeing in SQL server. Have I got the wrong one?

You have the correct one. In order to do your test you should set them in code (or in a DB script), like this:

// set all orders from customer "ALFKI"
var adapter = new DataAccessAdapter();
var metaData = new LinqMetaData(adapter);
var orderQuery = from o in metaData.Order where o.CustomerId == "ALFKI" select o;
var orders = ((ILLBLGenProQuery)orderQuery).Execute<EntityCollection<OrderEntity>>();
Assert.AreEqual(6, orders.Count);

// set dates down to millisecond
var dateToSet = new DateTime(2013, 1, 15, 6, 30, 25, 0);
orders[0].OrderDate = new DateTime(2013, 1, 15, 6, 30, 25, 0);
orders[1].OrderDate = new DateTime(2013, 1, 15, 6, 30, 25, 100);
orders[2].OrderDate = new DateTime(2013, 1, 15, 6, 30, 25, 500);
orders[3].OrderDate = new DateTime(2013, 1, 15, 6, 30, 26, 0);
orders[4].OrderDate = new DateTime(2013, 1, 15, 6, 30, 26, 100);
orders[5].OrderDate = new DateTime(2013, 1, 15, 6, 30, 26, 500);

// save them
adapter.SaveEntityCollection(orders, true, false);

// do the problematic query... ?

If you can come with a linq query using this, it will be very helpful for us to reproduce the issue.

About debugging the source code though. I have followed your instructions and added those three projects from the source code directory to my solution and then added project references to them in the generated code projects and when I compile I get many errors but the one that is most relevant is this:

Code:
Error   431 The type 'SD.LLBLGen.Pro.ORMSupportClasses.Context' is defined in an assembly that is not referenced. You must add a reference to assembly 'SD.LLBLGen.Pro.ORMSupportClasses.NET20, Version=2.6.0.0, Culture=neutral, PublicKeyToken=ca73b74ba4e3ff27'. C:\Code\SubXero\project\Xero.Billing.Model\DatabaseGeneric\Linq\LinqMetaData.cs 341 10  Xero.Billing.Model

It's almost like Visual studio knows which assembly with a certain publickeytoken was used to produce the generated code? How would it know that? Is it some kind of security feature?

Cheers, for your help

Did you fix the ORMSupportClasses reference on the LinqSupportClasses project? Build the projects one by one to see where is the problem, you don't need to know Public tokens, just reference the correct projects everywhere the assemblies are referenced already.

Ok cool. I changed the references on the LINQSupportClasses project and it work. Thank you

Just thought I'd post in this in case other people are having trouble getting the TOP clause with SQL server to be generated with a LINQ query in LLBLGen 2.6....

A bit of background: I was trying to implement an efficient paging solution using keyset driven paging by accessing a particular page based on filtering over a clustered index field; in my case a datetime field. To find the page after you order the query ascending and get datetime field values that are greater than some passed in datetime value (which functions much like the page number index in ordinary paging). If you want the page before, you order the query descending and get all datetime field values that are less than some passed in datetime value. If you want more information on keyset paging by surfing a clustered index see here: [h]http://stackoverflow.com/questions/2214276/t-sql-how-to-perform-optimised-paging[/h]. Part of the solution for my case will follow.

Now to get the TOP clause to output in the underlying generated query, there are several tricks. The first is to avoid a certain set of columns data types (see the source code with installation for a complete list of excluded types - search for CreateSelectDQ method in the ORMSupportClasses project). In my case I was using an XML type which prevents the TOP clause from being produced. The second trick is to make sure you are using the Distinct method in your LINQ query. Following these two rules will make sure the TOP clause is produced.

Because I was using XML columns I decided to run two queries. The first query I run gets a set of unique Ids (the clustered index DateTime field: CreatedDateTimeUTC and the primary key: AuditField) only in the SELECT clause and therefore I can use the Take method then to get the TOP clause output. This efficiently gets a specific page with just the page size window number of records returned. Here is the LINQ query to get the page after (to get the page before, it's just the reverse, as mentioned above):

var theResult = (LLBLGenProQuery<AuditSummary>)GetAudit(filter).OrderByDescending(o => o.CreatedDateTimeUTC).Where(c => c.CreatedDateTimeUTC < new DateTime(2008, 08, 21, 23, 00, 0)).Take(20).Select(s => new { s.AuditFieldID, s.CreatedDateTimeUTC }).Distinct().ToList();

The page window size is 20 hence Take(20) and new DateTime(2008, 08, 21, 23, 00, 0) would in actuality be the the last record datetime value in the previous page. (LLBLGenProQuery<AuditSummary>)GetAudit(filter) just returns an IQuerable which is generated by a repository that accesses a SQL server view which returns the records I need with the XML column included. Notice I use the select method to pull back just CreatedDateTimeUTC (the clustered index page filtering key) and AuditField (the primary key which has a non clustered index on it). AuditField is used the second query to get the rows I need with the full set of columns (including the XML column) from the underlying view.

To get the records I need with all the columns returned (including the XML column) I ran this query:

var completeResults = AuditService.GetAuditSummary<AuditSummary>().Where(c => theResult.Select(s => s.AuditFieldID).Contains(c.AuditFieldID)).ToList();

It's as simple as that. The reason I'm not bothered about running two separate queries is that that the only other solution to get the TOP clause output in the generated query when using XML columns was to use grouping on some field which generates two separate queries to return the results anyway. Unfortunately this didn't work in all cases for me when I was filtering with the LIKE operator (generated by the LINQ Contains method - see previous posts). The where clause was mangled and an AND instead of Or was generated (see previous posts).

One final point about keyset based paging is that you lose the ability to navigate straight to a specific page number since you need foreknowledge of the page key and having that knowledge would mandate that you generate all pages first to know what the page key will be for each page. This is a big disadvantage; you can only do next and previous without bringing back all the data. However, when you think about it, it doesn't make sense to give the user the ability to navigate to specific page numbers in huge datasets anyway since there are so many pages - the user would never get through them all anyway and who knows whether a specific page number will contain the records relevant to you anyway; there is just no way to tell if a page is relevant to you based on just it's number. Typically what the user needs is good searching capabilities to filter large datasets down to smaller more relevant one. This makes needing to navigate to specific page number redundant in my view since the good filtering functionality effectively brings the most relevant records closer to the first page in a filtered list. Typically it's assumed that search functionality should return records ordered by relevance relative to your search criteria. If we think about google searches, most people always navigate pages sequentially anyway from the first to last where the first page is assumed to be the most relevant. This would make having previous and next paging functionality only more than sufficient when combined with good searching/filtering functionality if it is assumed to be sorted by relevance.

Thanks for the detailed feedback That will help other users.