Here is the code.
public DataTable GetFormListForUser(int userID, string formState, int pageIndex, int pageSize, out int recordCount)
{
SortExpression sort = new SortExpression();
sort.Add(new SortClause(FormStateTrackingFields.RecordCreationDate, SortOperator.Descending));
DataTable dt = new DataTable();
TypedListDAO dao = new TypedListDAO();
recordCount = 0;
recordCount = GetFormListCountForUser(userID, formState);
dao.GetMultiAsDataTable(UserFormListFields, dt, 0, sort, GetUserFormListFilter(userID, formState), UserFormListRelations, false, null, null, pageIndex, pageSize);
return dt;
}
private ResultsetFields UserFormListFields
{
get
{
ResultsetFields fields = new ResultsetFields(7);
fields.DefineField(WorkflowInstanceFields.WorkflowInstanceInternalId, 0);
fields.DefineField(FormStateTrackingFields.FormStateTrackingId, 1);
fields.DefineField(FormFields.FormName, 2);
fields.DefineField(FormStateTrackingFieldIndex.TargetState, 3, "Status");
fields.DefineField(FormStateTrackingFields.WorkflowInstanceId, 4);
fields.DefineField(FormStateTrackingFieldIndex.RecordCreationDate, 5, "LastAction");
EntityField ef = new EntityField("Alias",
new ScalarQueryExpression(FormAliasFields.Alias,
(FormAliasFields.WorkflowInstanceId == FormStateTrackingFields.WorkflowInstanceId &
FormAliasFields.UserId == UserInboxFields.UserId)));
fields.DefineField(ef, 6);
fields["Status"].ExpressionToApply = new DbFunctionCall("FormTracking", "Form", "GetLastTargetState", new object[] { FormStateTrackingFields.WorkflowInstanceId });
return fields;
}
}
Here is the SQL from SQL Profiler.
exec sp_executesql N'CREATE TABLE #TempTable ([__rowcnt][int] IDENTITY (1,1) NOT NULL,[WorkflowInstanceInternalId][BigInt] NULL,[FormStateTrackingId][BigInt] NULL,[FormName]NVarChar
NULL,[Status]NVarChar NULL,[WorkflowInstanceId][UniqueIdentifier] NULL,[LastAction][DateTime] NULL,[Alias][BigInt] NULL);INSERT INTO #TempTable
([WorkflowInstanceInternalId],[FormStateTrackingId],[FormName],[Status],[WorkflowInstanceId],[LastAction],[Alias]) SELECT DISTINCT TOP 11
[FormTracking].[dbo].[WorkflowInstance].[WorkflowInstanceInternalId], [FormTracking].[Form].[FormStateTracking].[FormStateTrackingID] AS [FormStateTrackingId],
[FormTracking].[Form].[Form].[FormName], [FormTracking].[Form].GetLastTargetState([FormTracking].[Form].[FormStateTracking].[WorkflowInstanceID]) AS [Status],
[FormTracking].[Form].[FormStateTracking].[WorkflowInstanceID] AS [WorkflowInstanceId], [FormTracking].[Form].[FormStateTracking].[RecordCreationDate] AS [LastAction], (SELECT TOP 1
[FormTracking].[Form].[FormAlias].[Alias] FROM [FormTracking].[Form].[FormAlias] WHERE ( [FormTracking].[Form].[FormAlias].[WorkflowInstanceID] =
[FormTracking].[Form].[FormStateTracking].[WorkflowInstanceID] AND [FormTracking].[Form].[FormAlias].[UserID] = [FormTracking].[Form].[UserInbox].[UserID])) AS [Alias] FROM (((((
[FormTracking].[Form].[UserInbox] INNER JOIN [FormTracking].[Form].[FormStateTracking] ON
[FormTracking].[Form].[UserInbox].[FormStateTrackingID]=[FormTracking].[Form].[FormStateTracking].[ FormStateTrackingID]) INNER JOIN [FormTracking].[Form].[FormVersion] ON
[FormTracking].[Form].[FormVersion].[FormVersionID]=[FormTracking].[Form].[FormStateTracking].[FormVersionID]) INNER JOIN [FormTracking].[Form].[Form] ON
[FormTracking].[Form].[Form].[FormID]=[FormTracking].[Form].[FormVersion].[FormID]) INNER JOIN [FormTracking].[dbo].[WorkflowInstance] ON
[FormTracking].[dbo].[WorkflowInstance].[WorkflowInstanceId]=[FormTracking].[Form].[ FormStateTracking].[WorkflowInstanceID]) LEFT JOIN [FormTracking].[Form].[FormAlias] ON
[FormTracking].[Form].[FormAlias].[WorkflowInstanceID]=[FormTracking].[Form].[FormStateTracking].[WorkflowInstanceID]) WHERE ( ( [FormTracking].[Form].[UserInbox].[UserID] = @UserId1 AND
[FormTracking].[Form].[UserInbox].[Archived] = @Archived2 AND [FormTracking].[Form].[FormStateTracking].[TargetState] = @TargetState3)) ORDER BY
[FormTracking].[Form].[FormStateTracking].[RecordCreationDate] DESC;SELECT [WorkflowInstanceInternalId],[FormStateTrackingId],[FormName],[Status],[WorkflowInstanceId],[LastAction],[Alias]
FROM #TempTable WHERE [__rowcnt] > @__rownoStart AND [__rowcnt] <= @__rownoEnd ORDER BY [__rowcnt] ASC;DROP TABLE #TempTable',N'@UserId1 int,@Archived2 bit,@TargetState3
nvarchar(30),@__rownoStart int,@__rownoEnd int',@UserId1=260771,@Archived2=0,@TargetState3=N'savedstate',@__rownoStart=5,@__rownoEnd=10
Notice that the Alias column in the Temp Table is a BigInt, but the Column type is nvarchar.
Let me know if more information is needed.
). As there's already a way to obtain the right rows, i.e the temptable approach, it switches behind the scenes to the temptable approach if it needs to.