LLBLGen Version: 2.0.0.0 Library: LLBLGen Pro .NET 1.1 ORM Support Classes Library Library File Version: 2.0.07.0209 Adapter

When I pass a subset of a TypedView's fields to the FetchTypedView method the SQL generated is incorrect.

For instance, TypedView A has 5 fields: 1. varchar natural key 2. money field1 3. money field2 4. money field3 5. money field4

If I create a new EntityFields2 object that only contains fields 2 - 5 with SUM as the expression to apply on each field, the selection list appears similar to..

SUM(varchar natural key) AS field1, SUM(money field1) AS field2, SUM(money field2) AS field3, SUM(money field3) AS field4

After looking at the code for FetchTypedView in Reflector and comparing it with how the TypedList works I think the issue is with the fact that FetchTypedView gets persistence info based on the following code...

this.GetFieldPersistenceInfos(fieldCollectionToFetch[0].ContainingObjectName);

I think this means that the collection of persistence info objects can be out of sync with the collection of fields passed to FetchTypedView.

FetchTypedList (actually, it's one of the methods it calls into) goes through each field in the passed in fields collection and gets just the PersistenceInfo for the requested fields.

If I'm not way off on the above, is there a chance of getting a fix for this that gathers persistence info for just those fields in the EntityFields2 passed or is this a limitation of FetchTypedView that I'll have to live with?

Hi,

could you post a code snippet that fetch the typeview?

I'd have to post too much code to put our FetchTypedView call into context. What I mean is that I think it would just lead to a lot more questions that would really be about explaining helper classes we've written for LLBLGen rather than revealing anything particularly helpful to the issue at hand.

It's actually easier to post the code I used to overcome the problem described above.

I hope to get a fix to the actual library but for now I've defined the include file for the DataAccessAdapter and added the following code. The code below effectively replaces the definition of FetchTypedView in the DataAccessAdapterBase class. This code fixes the problem described in my original post.

        public override void FetchTypedView(IEntityFields2 fieldCollectionToFetch, DataTable dataTableToFill, IRelationPredicateBucket filterBucket, int maxNumberOfItemsToReturn, ISortExpression sortClauses, bool allowDuplicates, IGroupByCollection groupByClause, int pageNumber, int pageSize)
        {
            TraceHelper.WriteLineIf(TraceHelper.PersistenceExecutionSwitch.TraceInfo, "DataAccessAdapterBase.FetchTypedView(9)", "Method Enter");           
            
// #### This is the way it is currently coded in the ORM support library
//          IFieldPersistenceInfo[] persistenceInfoObjects = this.GetFieldPersistenceInfos(fieldCollectionToFetch[0].ContainingObjectName);
            for (int i = 0; i < fieldCollectionToFetch.Count; i++)
            {
                this.InsertPersistenceInfoObjects(fieldCollectionToFetch[i]);
            }
            
// #### This is the code I use to fix the issue ####            
// #### START
            IFieldPersistenceInfo[] persistenceInfoObjects = new FieldPersistenceInfo[fieldCollectionToFetch.Count];
            for (int i = 0; i < fieldCollectionToFetch.Count; i++)
            {
                persistenceInfoObjects[i] = this.GetFieldPersistenceInfo(fieldCollectionToFetch[i]);
            }
// #### FINISH
            
            bool relationsPresent = false;
            IPredicateExpression expressionToPass = null;
            this.InterpretFilterBucketLocal(filterBucket, ref relationsPresent, ref expressionToPass);
            this.InsertPersistenceInfoObjects(sortClauses);
            this.InsertPersistenceInfoObjects(groupByClause);
            IRetrievalQuery selectQuery = null;
            if (relationsPresent)
            {
                selectQuery = this.CreateSelectDQ(fieldCollectionToFetch, persistenceInfoObjects, expressionToPass, (long) maxNumberOfItemsToReturn, sortClauses, filterBucket.Relations, allowDuplicates, groupByClause, pageNumber, pageSize);
            }
            else
            {
                selectQuery = this.CreateSelectDQ(fieldCollectionToFetch, persistenceInfoObjects, expressionToPass, (long) maxNumberOfItemsToReturn, sortClauses, null, allowDuplicates, groupByClause, pageNumber, pageSize);
            }
            try
            {
                this.OnFetchTypedView(selectQuery, fieldCollectionToFetch, dataTableToFill);
                this.ExecuteMultiRowDataTableRetrievalQuery(selectQuery, this.CreateNewPhysicalDataAdapter(), dataTableToFill, persistenceInfoObjects);
                this.OnFetchTypedViewComplete(selectQuery, fieldCollectionToFetch, dataTableToFill);
            }
            finally
            {
                selectQuery.Dispose();
            }
            TraceHelper.WriteLineIf(TraceHelper.PersistenceExecutionSwitch.TraceInfo, "DataAccessAdapterBase.FetchTypedView(9)", "Method Exit");
        }

// #### Had to define this renamed copy of InterpretFilterBucket b/c it's 
// #### used by FetchTypedView but it's inaccesible to inheritors of 
// #### DataAccessAdaptorBase.
        private void InterpretFilterBucketLocal(IRelationPredicateBucket filterBucket, ref bool relationsPresent, ref IPredicateExpression expressionToPass)
        {
            relationsPresent = false;
            expressionToPass = null;
            if (filterBucket != null)
            {
                if (((RelationCollection) filterBucket.Relations).Count > 0)
                {
                    this.InsertPersistenceInfoObjects(filterBucket.Relations);
                    relationsPresent = true;
                }
                if ((filterBucket.PredicateExpression != null) && (filterBucket.PredicateExpression.Count > 0))
                {
                    expressionToPass = filterBucket.PredicateExpression;
                    this.InsertPersistenceInfoObjects(expressionToPass);
                }
            }
        }

When I pass a subset of a TypedView's fields to the FetchTypedView method the SQL generated is incorrect.

I think you need to fetch a dynamicList rather than a TypedView. A dynamicList is used if you want to fetch sum subset of fields.

I guess I'm a bit confused by the dynamic lists suggestion.

I mean, the method signatures of at least half of the FetchTypedView overrides include the ability to pass in an EntityFields2 object. If developers aren't intended to override the field list of the TypedView then why provide overrides with these signatures?

If, on the other hand, the intention was to allow the developer to specify a subset of the TypedView's fields in a custom EntityFields2 object then I believe the code of FetchedTypedView as it currently is may be flawed.

I suppose it's possible that the FetchTypedView overrides that allow a EntityFields2 object to be passed could be for expressions on existing fields and/or addition of 'calculated' fields.

Even if the original intent behind the ability to pass an EntityFields2 object to FetchTypedView did not include subsets of fields it does open the door to it and suggest that it 'should' be possible. The change shown above to make it possible seems pretty tightly scoped and is basically taken directly from how the typed lists do it. So, I would think this change should be a reasonable one to make (but my opinion isn't really the one that matters most in the end )

Would you please post the code that constructs the entityFields collection and that calls the FetchTypedView method and passes those entityFields to it?

As I said before posting the exact code is almost surely going to be counter productive which means I'll have to create some test code that demonstrates the problem. I'll post that as soon as I get the chance to write it.

I'm wondering if anyone has looked at the code I've posted and can offer any feedback?

I have a feeling you are passing fields from entities rather than typed view fields. For example, passing EmployeeFields.EmployeeId, rather than EmployeeViewFields.EmployeeId

I'm not above that kind of mistake but I usually figure those out on my own. As you can see below, I use the EntityFields2 object returned by the GetFieldsInfo method of the TypedView so the fields are definately the TypedView's own fields.

            PubCaseCostTypedView view = new PubCaseCostTypedView();
            IEntityFields2 baseFields = view.GetFieldsInfo();
            EntityFields2 newFields = new EntityFields2(4);

            newFields.DefineField(
                baseFields[PubCaseCostTypedView.FieldNames.OwedAmount],
                0, 
                AggregateFunction.Sum
                );

            newFields.DefineField(
                baseFields[PubCaseCostTypedView.FieldNames.SuspendedAmount],
                1, 
                AggregateFunction.Sum
                );

            newFields.DefineField(
                baseFields[PubCaseCostTypedView.FieldNames.CreditAmount],
                2, 
                AggregateFunction.Sum
                );

            newFields.DefineField(
                baseFields[PubCaseCostTypedView.FieldNames.PaidAmount],
                3, 
                AggregateFunction.Sum
                );

            _manager.DalContext.FetchTypedView(newFields, view, false);

Here's what the trace shows. Note, I didn't include Case_Number in the EntityFields2 object passed to FetchTypedView above.

Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT DISTINCT SUM([PubCaseCost].[case_number]) AS [OwedAmount], SUM([PubCaseCost].[owed_amount]) AS [SuspendedAmount], SUM([PubCaseCost].[suspended_amount]) AS [CreditAmount], SUM([PubCaseCost].[credit_amount]) AS [PaidAmount] FROM [PubCaseCost] 

Method Exit: CreateSelectDQ

If you look at the code I posted a couple days ago you'll see that the stock approach taken by FetchTypedView gets the FieldPersistenceInfo objects based on entity name which means it's going to get objects for each and every field in the TypedView. If the EntityFields2 object passed in doesn't contain all the fields from the TypedView in the same order that they were added to the persistence info the CreateSelectDQ can generate bogus SQL.

I've attached the code for PubCaseCostTypedView for reference.

Same issue: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=9238

This has been fixed in v2.5 beta.

Excellent!

I searched the forums for just such a thread but I guess I didn't look hard enough.

Any sense for when v2.5 could possibly be out of beta?

Somewhere in August I believe.

Walaa wrote:

Somewhere in August I believe.

Yeah, mid-august is the target now. We had some delays in doc-writing which just started very recently, and had to redesign some api. It's pretty stable actually.