Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> Llblgen and FOR XML PATH
 

Pages: 1 2
LLBLGen Pro Runtime Framework
Llblgen and FOR XML PATH
Page:1/2 

  Print all messages in this thread  
Poster Message
bobsibobita
User



Location:

Joined on:
01-Mar-2012 15:04:56
Posted:
13 posts
# Posted on: 19-Jun-2013 11:18:34.  
How can I use "FOR XML PATH" with adapter mode?
  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14464 posts
# Posted on: 19-Jun-2013 18:12:12.  
Do you want to fetch data in XML format?

  Top
bobsibobita
User



Location:

Joined on:
01-Mar-2012 15:04:56
Posted:
13 posts
# 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.
  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14464 posts
# Posted on: 20-Jun-2013 18:43:08.  
Why are you using an object relational mapper, if you are going to return XML?

  Top
bobsibobita
User



Location:

Joined on:
01-Mar-2012 15:04:56
Posted:
13 posts
# 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.
  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14464 posts
# 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.
Quote:
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.


  Top
bobsibobita
User



Location:

Joined on:
01-Mar-2012 15:04:56
Posted:
13 posts
# 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 Regular Smiley
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?
  Top
daelmo
Support Team



Location:
Guatemala City
Joined on:
28-Nov-2005 23:35:24
Posted:
8048 posts
# 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'ing (articles and code snippets) | linkedin | twitter
 
Top
bobsibobita
User



Location:

Joined on:
01-Mar-2012 15:04:56
Posted:
13 posts
# 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.
  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37377 posts
# 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
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
bobsibobita
User



Location:

Joined on:
01-Mar-2012 15:04:56
Posted:
13 posts
# 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 Cool .
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.

  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37377 posts
# Posted on: 29-Jun-2013 16:01:40.  
I wanted to see the code which creates this:
Code:
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
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
bobsibobita
User



Location:

Joined on:
01-Mar-2012 15:04:56
Posted:
13 posts
# Posted on: 01-Jul-2013 16:21:19.  
'stuff' is not important, I can live without that Laugh

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.

    
  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14464 posts
# Posted on: 01-Jul-2013 21:40:16.  
Which LLBLGen version are you using?

  Top
bobsibobita
User



Location:

Joined on:
01-Mar-2012 15:04:56
Posted:
13 posts
# Posted on: 02-Jul-2013 09:01:56.  
LLBLGen Pro v3.5

  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37377 posts
# 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
Code:
(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
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
bobsibobita
User



Location:

Joined on:
01-Mar-2012 15:04:56
Posted:
13 posts
# 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, UniqueList`1 distinctViolatingTypes, IRetrievalQuery query, UniqueList`1& 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 Laugh

  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14464 posts
# Posted on: 05-Jul-2013 11:23:03.  
Please try this code:
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);
            }


  Top
bobsibobita
User



Location:

Joined on:
01-Mar-2012 15:04:56
Posted:
13 posts
# 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);
  Top
daelmo
Support Team



Location:
Guatemala City
Joined on:
28-Nov-2005 23:35:24
Posted:
8048 posts
# 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:
Code:
public MyExpression(ScalarQueryExpression scExpr)
    : base(scExpr, ExOp.None, (IExpression) null)
{
    scE = scExpr;
}


David Elizondo
LLBLGen'ing (articles and code snippets) | linkedin | twitter
 
Top
bobsibobita
User



Location:

Joined on:
01-Mar-2012 15:04:56
Posted:
13 posts
# 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.
  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37377 posts
# 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
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
bobsibobita
User



Location:

Joined on:
01-Mar-2012 15:04:56
Posted:
13 posts
# 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.
  Top
TomDog
User



Location:
Wellington, New Zealand
Joined on:
25-Oct-2005 22:21:17
Posted:
559 posts
# 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
Code:
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
VS 2017 C#, LLBLGen v5.4, Winforms, WPF and ASP.NET MVCLLBL & LinqPad
 
Top
daelmo
Support Team



Location:
Guatemala City
Joined on:
28-Nov-2005 23:35:24
Posted:
8048 posts
# 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'ing (articles and code snippets) | linkedin | twitter
 
Top
Pages: 1 2  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.