Hi ,

I am using LLBLGen 2.5 version. We are using Projection to extract the details from the database.

The above method is used to bind grid in UI with paging enabled. Whenever we try to fetch data actually two queries are generated by the LLBLGen runtime with

1. Query that fetches all the data
2. Query with TOP 7(Our page size) had been executed subsequently.

Please let me know whether it is by design?

Please find below the sample projection method we are using.

public List<InvoiceListInfo> GetInvoiceListsInfo(OptimaSearchCriteria filter, int pageNumber, int pageSize) { int netId = 0; List<InvoiceListInfo> rowsToReturn = null; //To check whether network is select or not to compute allocated value netId = filter.TerritoryId.HasValue ? filter.TerritoryId.Value : 0;

        List<InvoiceListInfo> collInvoiceListInfo = null;
        try
        {
            //Define Projector

            collInvoiceListInfo = new List<InvoiceListInfo>();
            DataProjectorToCustomClass<InvoiceListInfo> projector = new DataProjectorToCustomClass<InvoiceListInfo>(collInvoiceListInfo);

            //Define projector Fields
            ResultsetFields fields;
            if (netId > 0)
            {
                fields = new ResultsetFields(13);
            }
            else
            {
                fields = new ResultsetFields(12);
            }

            fields.DefineField(InvoiceHeaderFields.Id, 0, "InvoiceId");
            fields.DefineField(InvoiceHeaderFields.InvoiceNumber, 1, "InvoiceNo");
            fields.DefineField(InvoiceHeaderFields.InvoiceDate, 2, "InvoiceDate");
            fields.DefineField(LocationFields.Id, 3, "LocationId");
            fields.DefineField(LocationFields.Name, 4, "LocationName");
            fields.DefineField(ItemFields.Id, 5, "ItemId");
            fields.DefineField(InvoiceDetailFields.InvoiceValue, 6, "InvoiceValue", string.Empty, AggregateFunction.Sum);
            fields.DefineField(InvoiceDetailFields.BonusQuantity, 7, "BonusQuantity", AggregateFunction.Sum);
            fields.DefineField(InvoiceDetailFields.OrderedQuantity, 8, "OrderedQuantity", AggregateFunction.Sum);
            fields.DefineField(InvoiceDetailFields.ShippedQuantity, 9, "TenderQuantity");
            fields.DefineField(InvoiceDetailFields.ShippedQuantity, 10, "SampleQuantity");
            fields.DefineField(ProductResourceDetailFields.Value, 11, "ItemName");
            if (netId > 0)
            {
                fields.DefineField(InvoiceDetailFields.InvoiceValue, 12, "AllocatedValue");
                fields[12].ExpressionToApply = new DbFunctionCall("(ISNULL(sum(isnull(case when {0} = 'N' or {0} = 'T' or {0} = 'O' then {1}  end, 0) ), 0)*1.00/ max({2})) * max({3})/100", new object[] { SalesTypeFields.Type, InvoiceDetailFields.InvoiceValue, SalesTargetAllocationFields.TerritoryId, SalesTargetAllocationFields.Target });
            }

            fields[9].ExpressionToApply = new DbFunctionCall("ISNULL(sum(isnull(CASE when {0} = 'T' then IsNull({1},0) else 0 end,0)), 0)*1.00/ Max({2})", new object[] { SalesTypeFields.Type, InvoiceDetailFields.ShippedQuantity, SalesTargetAllocationFields.TerritoryId });
            fields[10].ExpressionToApply = new DbFunctionCall("ISNULL(sum(isnull(CASE when {0} = 'S' then IsNull({1},0) else 0 end,0)), 0)*1.00/ Max({2})", new object[] { SalesTypeFields.Type, InvoiceDetailFields.ShippedQuantity, SalesTargetAllocationFields.TerritoryId });

            //Define value projector fields
            List<IDataValueProjector> valueProjector = new List<IDataValueProjector>();
            valueProjector.Add(new DataValueProjector("InvoiceId", 0, typeof(int)));
            valueProjector.Add(new DataValueProjector("InvoiceNo", 1, typeof(string)));
            valueProjector.Add(new DataValueProjector("InvoiceDate", 2, typeof(DateTime?)));
            valueProjector.Add(new DataValueProjector("LocationId", 3, typeof(int)));
            valueProjector.Add(new DataValueProjector("LocationName", 4, typeof(string)));
            valueProjector.Add(new DataValueProjector("ItemId", 5, typeof(int)));
            valueProjector.Add(new DataValueProjector("InvoiceValue", 6, typeof(decimal?)));
            valueProjector.Add(new DataValueProjector("BonusQuantity", 7, typeof(decimal?)));
            valueProjector.Add(new DataValueProjector("OrderedQuantity", 8, typeof(decimal?)));
            valueProjector.Add(new DataValueProjector("TenderQuantity", 9, typeof(decimal?)));
            valueProjector.Add(new DataValueProjector("SampleQuantity", 10, typeof(decimal?)));
            valueProjector.Add(new DataValueProjector("ItemName", 11, typeof(string)));
            if (netId > 0)
            {
                valueProjector.Add(new DataValueProjector("AllocatedValue", 12, typeof(decimal?)));
            }
            //Define Group by clause
            IGroupByCollection groupByClause = new GroupByCollection();
            groupByClause.Add(fields[0]);
            groupByClause.Add(fields[1]);
            groupByClause.Add(fields[2]);
            groupByClause.Add(fields[3]);
            groupByClause.Add(fields[4]);
            groupByClause.Add(fields[5]);
            groupByClause.Add(fields[11]);

            //Define Prefetchpath, predicate expression
            IPrefetchPath2 prefetchPath = null;
            if (netId > 0)
            {
                prefetchPath = GetInvoiceListInfoPrefetch();
            }
            else
            {
                prefetchPath = GetInvoiceListInfoWONetworkPrefetch();
            }

            IRelationPredicateBucket bucket = GetFilterBucket(prefetchPath);
            FillPredicateExpressionFromSearchCriteriasForInvoiceList(bucket.PredicateExpression, filter);

            SortExpression sort = new SortExpression();
            sort.Add(new SortClause(InvoiceHeaderFields.InvoiceDate, null, SortOperator.Ascending));
            sort.Add(new SortClause(InvoiceHeaderFields.InvoiceNumber, null, SortOperator.Ascending));

            //Execute FetchProjection
            objDAF.FetchProjection(valueProjector, projector, fields, bucket, 0, sort, groupByClause, false, pageNumber, pageSize);
            rowsToReturn = projector.Destination;
        }
        catch (Exception ex)
        {
            OptimaExceptionHandler.HandleException(ex);
        }
        return rowsToReturn;
    }

Whenever we try to fetch data actually two queries are generated by the LLBLGen runtime with

1. Query that fetches all the data
2. Query with TOP 7(Our page size) had been executed subsequently.

Please check the SQL Profiler for the executed queries, coz I think only the second one is executed.

Waala,

I had checked in the output window of VS2005 by putting the following diagnostics information in the configuration file.

<system.diagnostics> <switches> <add name="SqlServerDQE" value="4" /> </switches> </system.diagnostics>

Regards

Prabhu

Not all queries in the output are actually executed against the database. So the SQL Profiler will be a good judge here.

Additionally, you could instead switch on the ORMPersistence tracer, which does show the query executed (though not with typedlists as these queries are executed by the ado.net provider internally)