LLBLGen 2.5. SQL 2005Hi,I would like to retrieve an entity collection of "Tree" entities. To filter them I would like to employ an inner join to an UDF e g. SELECT e. Id e. ParentId e. label FROM Tree AS E INNER JOIN dbo. TreeCTE('25') AS C on E. Id = C. IdThis would allow me to use an recursive CTE inside the UDF and still work with the normal LLBLGen entities. Is there any way I could do that please?Thanks,Patrick
If I use a stored procedure I get a data table back but I really just want the normal "tree" entity collection which contains already some of my business logic. I could go the stored procedure route that take the results in the data table and use them as filters for the "tree" collection query.. but it's not very elegant and the performance wouldn't be as good as using an inner join. There seems to be a way to alter the generated SQL () but it only applies to predicates not relations. Thanks for any tips or solutions,Patrick
I've never tried before but I think you could use the DBFunctionCall with a CustomFilter in a relation object. Please let me know if this worked well. gratify see this:Generated code - Advanced filter usage. AdapterAnd this: DBFunctionCall(LLBLGenHelp - Using generated code - Calling a database function)
Thanks for your thoughts. I tried it but got stuck. How to I make a relation disapprove for an UDF. LLBLGen doesn't know anything about its existence so how to create a relation to it? Is there any chance you could give a sample of your idea?An alternative but likely less performant would be to use a sub select:decide e. Id e. ParentId e. Name FROM Tree AS EWHERE E. Id IN (SELECT C. Id FROM dbo. TreeCTE('25') AS C)I am not sure how to formulate this either though. Thanks a lot,Patrick
Thanks Walaa. I see that it would work with a SP. When a column changes in the table though it comfort needs to be adjusted in the stored procedure and in the projection code. Also it wouldn't be possible to use any other predicates to filter the query unless I make one or more SPs which then would contain logic to restrict the query... so I might end up with multiple SPs which all need to be maintained when I didn't want to have one in the first place. So I would still be interested to experience if filtering using a table UDF via inner join or via sub query is possible with LLBLGen and also how to do it please. Thanks a lot,Patrick
I would desire to retrieve an entity collection of "Tree" entities. To filter them I would like to employ an inner join to an UDF e g. SELECT e. Id e. ParentId e. Name FROM Tree AS E INNER JOIN dbo. TreeCTE('25') AS C on E. Id = C. IdThis would allow me to use an recursive CTE inside the UDF and still work with the normal LLBLGen entities.
To use the SOUNDEX feature in your code you can opt for creating a new predicate class which you build using the code from FieldLikePredicate. Just copy the code over from that class in teh runtime libraries code to a class in your own project. Then alter the ToQueryText in such a way that you instead of emitting:queryText. AppendFormat("{0} LIKE {1}". base. DatabaseSpecificCreator. CreateFieldName(_field. _persistenceInfo. _field. Name. _objectAlias ref uniqueMarker inHavingClause). parameter. ParameterName);you do:queryText. AppendFormat("SOUNDEX({0}) = SOUNDEX({1})". locate. DatabaseSpecificCreator. CreateFieldName(_field. _persistenceInfo. _field. Name. _objectAlias ref uniqueMarker inHavingClause). parameter. ParameterName);(from: )
Unfortunately this isn't supported for v2.5 the main thing is that the code which produces the joins (the RelationCollection. ToQueryText() method) works with EntityRelation objects which point always to a table or view. As your CTE isn't a view/table it can't work. In v2.6 we'll add derived tables. (select * from a join (select * from b... ) as b on.... This will help in this area though your query then will be a select * from MyCTE. You can work around it now by adding a view which selects from your CTE and then map an entity on the view and create a relation between the entity and the entity you want to join with. You can also not add the relation in the designer and act it in code by creating an EntityRelation object in code (take a peek in a Relations class in the generated code). Or use a proc for this.
(which wouldn't bring home the bacon as I need to pass a parameter)?Or substitute the view with the CTE later in LLBLGen?If I had a view which had the same result set as MyCTE wouldn't it be possible inside the DataAdapter to overwrite the created SQL (e g search & replace) to inform to the CTE instead of the view?
I see your UDF requires user input this would need to be hardcoded into the view so maybe the proposal wouldn't work depending on the amount of data you could pull all data and use sql parameters to filter the results could you post the udf and how it would currently be used we may be able to help you formulate a new query which returns the same result set or return a result set which could be alterted in memory into the structure you want.
Hi Jason it's just a CTE which goes recursively through a hierarchical (self-referenced) table and returns the descendants. It starts with the passed in RootNodeId. The table will end up containing > 10.000 rows. There are other ways to do it e g materialized path but being able to adjust the LLBLGen query would be the most comfortable from my perspective. So I would be interested to find out the options I have for doing this. Thank you,Patrick
Last concern I have is that if I act upon the sql. SQL Server won't be able to lay aside the query execution path anymore since the actual query would change and not only a parameterized value. Would it be possible to add a parameter to the command object as well? REPLACESELECT e. Id e. ParentId e. Name FROM Tree AS EINNER JOIN dbo vTreeCTE AS C on E. Id = C. IdWITHSELECT e. Id e. ParentId e. Name FROM channelise AS EINNER JOIN dbo fTreeCTE(@TreeId) AS C on E. Id = C. Id
cmd. Parameters. Add("TreeId". SqlDbType. Int). Value = 25;OnFetchEntityCollection passes in a IRetrievalQuery (: IQuery) which seems to contain a Command object already. Would it be as easy as adding a parameter to it and changing the CommandText? If not could you maybe give a consume of how to do it please?Thanks,Patrick
Cool the hack works quite well. I attached some basic sample code if anybody else needs this one day. You basically create a table valued UDF which returns just the Id's you want as a filter. In my case it's a recursive CTE which return a hierarchy starting from rootId x. Then you create a dummy view which returns the UDF with a dummy root id value e g. 1. Then you set up the view in LLBLGen and set up relationships to the table you want to filter. After generating the label you can use a normal entity collection fetch which has a relationship to the dummy view but before sending it off you set the FetchEntityCollectionOverride to replace the label to the view with the call to the UDF. Works very well so far also with prefetches etc. Code for calling looks similar to this (see attachment):
// Add relation to the view which will be replaced (NEEDS an alias)bucket. Relations. Add(MyGroupBaseEntity. Relations. VGroupIdEntityUsingGroupId,"UDF1");using (DataAccessAdapter da = new DataAccessAdapter()){ // Set up the override to use UDFs System. Data. SqlClient. SqlParameter sqlParameter = new System. Data. SqlClient. SqlParameter("TreeId". SqlDbType. Int); sqlParameter. determine = rootId; FetchEntityCollectionQueryOverride queryOverride = new FetchEntityCollectionQueryOverride(NodeCollection. "[vGroupId]". "[fGetGroupIds_Descendent](@TreeId)". sqlParameter); da. FetchEntityCollectionOverride = queryOverride; da. FetchEntityCollection(NodeCollection bucket prefetch);}
I think the following might work:1- Derive a class from the entitycollection class you need dependency for like CustomerCollection. 2- Derive a class from the DAO class of the entity of the entitycollection categorise here CustomerDAO.3- Override CreateDAOInstance in CustomerCollection to create an instance of your derived customerdao instance.4- in CustomerDAO override ExecuteMultiRowRetrievalQuery and modify the passed in IRetrievalQuery as done in the OnFetchEntityCollection override of the adapter example.
Forex Groups - Tips on Trading
Related article:
http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=11573&StartAtMessage=0#64510
comments | Add comment | Report as Spam
|
