Llblgen and FOR XML PATH

Posts   
1  /  2
 
    
Posts: 13
Joined: 01-Mar-2012
# Posted on: 19-Jun-2013 11:18:34   

How can I use "FOR XML PATH" with adapter mode?

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 19-Jun-2013 18:12:12   

Do you want to fetch data in XML format?

Posts: 13
Joined: 01-Mar-2012
# Posted on: 20-Jun-2013 14:43:35   

Yes. Actually I'm returning records grouped by a column and I want to return also the id's of the other rows that have same value like in my group by column. In sql this can be easily performed using "FOR XML PATH" sintax.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 20-Jun-2013 18:43:08   

Why are you using an object relational mapper, if you are going to return XML?

Posts: 13
Joined: 01-Mar-2012
# Posted on: 21-Jun-2013 08:09:24   

I'm using LLBLGEN for my entire project. The main search on the site is dynamically build depending on user choices and it's pretty complex, I'm not going to construct that query from strings manually when I can benefit from the simplicity of an object relational mapper like LLBLGEN. In my return columns only one will be an XML, rest of them are normal columns. If this is not supported by LLBLGEN please just tell me and not answer to a question by another question.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 21-Jun-2013 11:10:56   

If you want a quick easy answer: FOR XML PATH is not supported out of the box. You may just stop here and close the thread.

If you want to follow up on the subject with possibly more questions, then be my guest.

Actually I'm returning records grouped by a column and I want to return also the id's of the other rows that have same value like in my group by column. In sql this can be easily performed using "FOR XML PATH" sintax.

I might be mistaken, but I don't think that's what FOR XML PATH is used for. AFAIK, FOR XML, returns the resultSet in an XML format, that's it, regardless of what the nature or the complexity of the query, grouping or not is not the issue. So bottom line, if I'm not mistaken, I don't think you need to use FOR XML to formulate a query.

If you want you may share the tables structure and what do you want to retrieve, so we can help you formulate the query.

Posts: 13
Joined: 01-Mar-2012
# Posted on: 21-Jun-2013 13:29:05   

Lets say I have datatable House HouseId int, City varchar(100)

Here I have some test data simple_smile HouseId City 1 London 2 London 3 Paris 4 Paris 5 London 6 London 7 London

I want to retrieve one house per city and in the same row other 2 houses for same city:

Normally my query will return this: HouseId City OtherHouseIds 1 London 2, 5 3 Paris 4

I can do this with this query:

select MAX(h.houseid), h.city, stuff((select top 2 ',' + CAST(h1.houseid as varchar) from House h1 where h1.City = h.City and h1.HouseID<>max(h.houseid) for XML path('')) ,1,1,'') RestOfIds from House h group by h.City

If "for XML" is not supported, how can I write this differently?

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 22-Jun-2013 08:27:37   

The only thing I can think of that you can use and that is supported is: write an StoredProcedure that receive your params and execute the FOR XML query. Then map that SP resulset into a LLBLGen TypedList. You can query that TypedList in LLBLGen Framework.

David Elizondo | LLBLGen Support Team
Posts: 13
Joined: 01-Mar-2012
# Posted on: 24-Jun-2013 09:20:44   

The problem is that the query is more complex in reality, I have to dynamically construct the "where" clause taking into consideration user choices. The "where" clause I will have to add also to the "for xml" sub query(so to the SP), and it will be more work and error prone. I was hoping that I can somehow inject "for XML path('')" at the end of the query.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 24-Jun-2013 11:50:55   

Please show the code you have which should produce your query so we know what objects you're using. The FOR XML fragment is not injectable by default, but perhaps with subclassing one of the classes you're using (e.g. a derived table class, or a scalar query class) it might be possible to do.

Frans Bouma | Lead developer LLBLGen Pro
Posts: 13
Joined: 01-Mar-2012
# Posted on: 27-Jun-2013 10:49:08   

This is a short version of the method.

public static DataTable GetHouses(SearchTerms searchTerms, int pageNo, int pageSize) { using (DataAccessAdapter adapter = CreateAdapter()) { EntityFields2 fields = new EntityFields2(2); fields.DefineField(HouseFields.HouseId, 0, "HouseId", AggregateFunction.Max); fields.DefineField(HouseFields.City, 1, "City");

            GroupByCollection groupBy = new GroupByCollection();
            groupBy.Add(HouseFields.City);

            IRelationPredicateBucket bucket = new RelationPredicateBucket();
            /// get search result relations
            bucket.Relations.AddRange(GetSearchResultsRelations(searchTerms));
            /// get searchresult predicate
            bucket.PredicateExpression.Add(GetSearchResultsPredicate(searchTerms));

            SortExpression sort = new SortExpression();
            sort.Add(HouseFields.City | SortOperator.Ascending);

            DataTable dt = new DataTable();             
            adapter.FetchTypedList(fields, dt, bucket, 0, sort, true, groupBy, pageNo, pageSize);
        }
    }

GetSearchResultsRelations and GetSearchResultsPredicate are two methods that are returning the relations and where clauses, depending on some paramaters from searchTerms. I didn't want to post this, because they have in total 500 code lines sunglasses . What I want to achieve is to return also a column with the first two HouseId for same city. In subquery I also have to add the RelationPredicateBucket from the main query.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 29-Jun-2013 16:01:40   

I wanted to see the code which creates this:

 stuff((select top 2 ',' + CAST(h1.houseid as varchar)
             from House h1 where h1.City = h.City and h1.HouseID<>max(h.houseid)
             for XML path(''))

No idea what 'stuff' means in this context. What I was referring to is that this is done in a ScalarQueryExpression. You can subclass that class to append For XML PATH if you want to, as you can override the the ToQueryText method.

Frans Bouma | Lead developer LLBLGen Pro
Posts: 13
Joined: 01-Mar-2012
# Posted on: 01-Jul-2013 16:21:19   

'stuff' is not important, I can live without that smile

I've tried to inherit from ScalarQueryExpression, the problem is that I cannot override ToQueryText, because it is not marked virtual, abstract, or override. Instead I've manage to inherit from PredicateExpression and there I can override ToQueryText. The downside of this approach is that I cannot use group by.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 01-Jul-2013 21:40:16   

Which LLBLGen version are you using?

Posts: 13
Joined: 01-Mar-2012
# Posted on: 02-Jul-2013 09:01:56   

LLBLGen Pro v3.5

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 02-Jul-2013 15:49:09   

You can do it like this: Define your own Expression class, either by implementing IExpression, or by deriving from Expression. That class accepts in its constructor a ScalarQueryExpression (The part before FOR XML ..) and the arguments you want to pass to FOR XML ('...').

Then in ToQueryText of your expression class, first call ToQueryText() of the scalarqueryexpression, and then append the FOR XML () string. Be sure to copy parameters of the ScalarQueryExpression to your own expression class' parameters collection.

Then, assign an instance of your expression class to a field's ExpressionToApply property and set that property as the 3rd field in the fields object of your query.

That way, when the query is constructed, your expression class' ToQueryText is called, which will then produce the

(select top 2 ',' + CAST(h1.houseid as varchar)
             from House h1 where h1.City = h.City and h1.HouseID<>max(h.houseid)
             for XML path(''))

fragment, and place it at that location in the select projection as it's assigned as expression to the 3rd field.

Frans Bouma | Lead developer LLBLGen Pro
Posts: 13
Joined: 01-Mar-2012
# Posted on: 03-Jul-2013 14:51:35   

Hi Otis, this is the class:

public class MyExpression : SD.LLBLGen.Pro.ORMSupportClasses.Expression
{
    ScalarQueryExpression scE;      
    public MyExpression(ScalarQueryExpression scExpr)
    {
        scE = scExpr;
    }
    public override string ToQueryText(bool inHavingClause)
    {           
        Parameters.AddRange(scE.Parameters);
        scE.DatabaseSpecificCreator = base.DatabaseSpecificCreator;         
        return scE.ToQueryText(inHavingClause) + " FOR XML path('')";
    }
}

and this is how I add it: ScalarQueryExpression expr = new ScalarQueryExpression(HouseFields.HouseId.SetObjectAlias("HI").SetFieldAlias("HId"), new PredicateExpression(pp), rc);

EntityField2 countField = new EntityField2("HIIII", new MyExpression(expr));

The problem is that I get "Object reference not set to ...." and this is the StackTrace:

at SD.LLBLGen.Pro.DQE.SqlServer.SqlServerSpecificCreator.CreateObjectName(IFieldPersistenceInfo persistenceInfo) at SD.LLBLGen.Pro.ORMSupportClasses.RelationCollection.PrepareJoinableFragments(StringBuilder queryText, IEntityRelation relationAsEntityRelation, IDynamicRelation relationAsDynamicRelation, Boolean relationIsDynamicRelation, Boolean& isSingleOperandDynamicRelation, String& pkElement, String& fkElement, String& aliasPKSide, String& aliasFKSide, String& pkElementReference, String& fkElementReference) at SD.LLBLGen.Pro.ORMSupportClasses.RelationCollection.ToQueryTextInternal(Boolean ansiJoins, String& nonAnsiWhereClause, String nonAnsiRootTableReference, String nonAnsiFieldSuffix) at SD.LLBLGen.Pro.ORMSupportClasses.RelationCollection.ToQueryText() at SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IRetrievalQuery query, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Boolean relationsSpecified, Boolean sortClausesSpecified) at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, DbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause) at SD.LLBLGen.Pro.ORMSupportClasses.DbSpecificCreatorBase.CreateSubQuery(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldPersistenceInfos, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, IGroupByCollection groupByClause, Boolean allowDuplicates) at SD.LLBLGen.Pro.DQE.SqlServer.SqlServerSpecificCreator.CreateSubQuery(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldPersistenceInfos, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, IGroupByCollection groupByClause, Boolean allowDuplicates) at SD.LLBLGen.Pro.ORMSupportClasses.DbSpecificCreatorBase.CreateSubQuery(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldPersistenceInfos, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, IGroupByCollection groupByClause) at SD.LLBLGen.Pro.ORMSupportClasses.ScalarQueryExpression.ToQueryText(Boolean inHavingClause) at Bungalow.Common.Managers.MyScalarQueryExpression2.ToQueryText(Boolean inHavingClause) in d:\work\BungalowSiteeeeee\BungalowEu.All\Bungalow.Common\Managers\MgrHouse.cs:line 53 at SD.LLBLGen.Pro.ORMSupportClasses.DbSpecificCreatorBase.ConvertFieldToRawName(IEntityFieldCore fieldCore, IFieldPersistenceInfo persistenceInfo, String fieldName, String objectAlias, Boolean applyAggregateFunction) at SD.LLBLGen.Pro.ORMSupportClasses.DbSpecificCreatorBase.CreateFieldName(IEntityFieldCore fieldCore, IFieldPersistenceInfo persistenceInfo, String fieldName, String objectAlias, Boolean applyAggregateFunction) at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.AppendResultsetFields(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IRelationCollection relationsToWalk, DelimitedStringList projection, Boolean sortClausesSpecified, Boolean allowDuplicates, Boolean allowAliasesInSubQuery, UniqueList1 distinctViolatingTypes, IRetrievalQuery query, UniqueList1& fieldNamesInSelectList, Boolean& distinctViolatingTypesFound, Boolean& pkFieldSeen) at SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IRetrievalQuery query, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Boolean relationsSpecified, Boolean sortClausesSpecified) at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, DbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause) at SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.CreatePagingSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, DbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize) at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, DbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.CreateSelectDQ(IEntityFields2 fieldsToFetch, IFieldPersistenceInfo[] persistenceInfoObjects, IPredicateExpression filter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.CreateQueryFromElements(IEntityFields2 fieldCollectionToFetch, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize, IFieldPersistenceInfo[]& persistenceInfo, IRetrievalQuery& selectQuery) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchTypedList(IEntityFields2 fieldCollectionToFetch, DataTable dataTableToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize)

I've manage to create the query with the help of .NET Reflector, copied all the code ScalarQueryExpression class and modify the end like this: return string.Format("({0} FOR XML path(''))", query.Command.CommandText); but I don't want to do this, is not nice smile

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 05-Jul-2013 11:23:03   

Please try this code:

            using (DataAccessAdapter adapter = CreateAdapter())
            {
                EntityFields2 fields = new EntityFields2(2);
                fields.DefineField(HouseFields.HouseId, 0, "HouseId", AggregateFunction.Max);
                fields.DefineField(HouseFields.City, 1, "City");
                fields.DefineField(HouseFields.City, 2, "RestOfIds");

                ScalarQueryExpression expr = new ScalarQueryExpression(HouseFields.HouseId.SetObjectAlias("HI").SetFieldAlias("HId"),
                            new PredicateExpression(pp), rc);
                fields[3].ExpressionToApply = new MyExpression(expr);

                GroupByCollection groupBy = new GroupByCollection();
                groupBy.Add(HouseFields.City);

                IRelationPredicateBucket bucket = new RelationPredicateBucket();
                /// get search result relations
                bucket.Relations.AddRange(GetSearchResultsRelations(searchTerms));
                /// get searchresult predicate
                bucket.PredicateExpression.Add(GetSearchResultsPredicate(searchTerms));

                SortExpression sort = new SortExpression();
                sort.Add(HouseFields.City | SortOperator.Ascending);

                DataTable dt = new DataTable();             
                adapter.FetchTypedList(fields, dt, bucket, 0, sort, true, groupBy, pageNo, pageSize);
            }
Posts: 13
Joined: 01-Mar-2012
# Posted on: 05-Jul-2013 12:58:31   

Same error, my code was almost the same:

fields.Expand(1); ScalarQueryExpression expr = new ScalarQueryExpression(HouseFields.HouseId.SetObjectAlias("HI").SetFieldAlias("HId"), new PredicateExpression(pp), rc);

EntityField2 customField = new EntityField2("HIIII", new MyExpression(expr));

fields.DefineField(customField, fields.Count - 1);

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 08-Jul-2013 08:12:26   

I think that your custom expression is missing the LeftOperand. This will fail when the adapter tries to set the persistenceInfo for the expression's contents (the scalarQueryExpression). Try this at your MyExpression's ctor:

public MyExpression(ScalarQueryExpression scExpr)
    : base(scExpr, ExOp.None, (IExpression) null)
{
    scE = scExpr;
}
David Elizondo | LLBLGen Support Team
Posts: 13
Joined: 01-Mar-2012
# Posted on: 08-Jul-2013 10:15:19   

Indeed that was the problem, thanks. The query is generated correctly now, the only problem is that I miss all the parameters of the subquery.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 08-Jul-2013 10:50:27   

Be sure to copy the parameters of the scalarqueryexpression to the MyExpression's parameters in its ToQueryText method.

Frans Bouma | Lead developer LLBLGen Pro
Posts: 13
Joined: 01-Mar-2012
# Posted on: 08-Jul-2013 11:00:41   

It's working:

public override string ToQueryText(bool inHavingClause) { scE.DatabaseSpecificCreator = base.DatabaseSpecificCreator;

 string temp = scE.ToQueryText(inHavingClause);
 temp = temp.Insert(temp.Count() - 1, " FOR XML path('')");

 Parameters.AddRange(scE.Parameters);
 return temp;

}

thanks a lot for the help.

TomDog
User
Posts: 618
Joined: 25-Oct-2005
# Posted on: 31-Jan-2019 04:41:34   

How can I get FOR XML path working for LINQ?

Currently I have this inside a projection

BarrierDepartments = AQD.Helpers.GeneralHelper.JoinAsString(p__0.Barrier.BarrierDepartments.Select(bd => bd.Department.OrgItemDescription))

//JoinAsString ultimately calls string.Join

This sort of works now as LLBL does it all in memory but it would be better if where done in the DB using FOR XML path so it can be sorted and filtered in the DB.

Jeremy Thomas
daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 31-Jan-2019 08:17:59   

Hi Jeremy,

What your helper class do here? What it produces? How the code looks like?

btw, please don't reopen old threads, instead you could open a new one, referencing this.

David Elizondo | LLBLGen Support Team
1  /  2