LLBLGen 2.5, SQL 2005

Hi,

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.Name FROM Tree AS E INNER JOIN dbo.TreeCTE('25') AS C on E.Id = C.Id

This 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

You can call Database functions and user defined functions to be used as an expression in your query. But I don't think you can join to.

I'd say you will have to use a stored procedure.

Walaa wrote:

You can call Database functions and user defined functions to be used as an expression in your query. But I don't think you can join to.

In T-SQL you can. With LLBLGen you might not be able to.

Walaa wrote:

I'd say you will have to use a stored procedure.

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 (http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7731) 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.

Please see this: Generated code - Advanced filter usage, Adapter

And this: DBFunctionCall(LLBLGenHelp - Using generated code - Calling a database function)

goose wrote:

I've never tried before but I think you could use the DBFunctionCall with a CustomFilter in a relation object.

Thanks for your thoughts. I tried it but got stuck. How to I make a relation object 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: SELECT e.Id, e.ParentId, e.Name FROM Tree AS E WHERE 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

The easiest thing to do is to use a SP.

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

Read results from an SP into a DataReader and then Project the results back into an EntityCollection.

Please check this out in the LLBLGen Pro manual: Using the generated code -> Adapter/SelfServicing -> Fetching DataReaders and projections

Walaa wrote:

The easiest thing to do is to use a SP.

Thanks Walaa. I see that it would work with a SP.

When a column changes in the table though it still 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 know 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

v2.x does not support derived tables in the from clause. you can use scalar sub-queries in the select/update/where/group clauses.

v2.6 (linq to llbl) has support for derived tables. they were needed for linq support. I think this will be in beta q4 07 or q1 08

jmeckley wrote:

v2.x does not support derived tables in the from clause. you can use scalar sub-queries in the select/update/where/group clauses.

Ok thanks let's take the sub-queries out then.

This leads then back to my original question:

pat wrote:

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.Name FROM Tree AS E INNER JOIN dbo.TreeCTE('25') AS C on E.Id = C.Id

This would allow me to use an recursive CTE inside the UDF and still work with the normal LLBLGen entities.

Is there any way of doing this with LLBLGen 2.5 please?

Otis wrote:

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})", base.DatabaseSpecificCreator.CreateFieldName(_field, _persistenceInfo, _field.Name, _objectAlias, ref uniqueMarker, inHavingClause), parameter.ParameterName);

(from: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7731)

Would something along these lines work? If yes how would I apply this to be able to make an inner join to a table UDF?

Thanks a lot, Patrick

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 create 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.

Thanks for your answer Otis.

Otis wrote:

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.

Not sure if I understand it correctly. Do you mean to just use a view onto the CTE and lose the ability to pass a parameter (which wouldn't work 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 point to the CTE instead of the view?

REPLACE SELECT e.Id, e.ParentId, e.Name FROM Tree AS E INNER JOIN dbo.vTreeCTE AS C on E.Id = C.Id

WITH SELECT e.Id, e.ParentId, e.Name FROM Tree AS E INNER JOIN dbo.fTreeCTE('25') AS C on E.Id = C.Id

If yes what would I need to overwrite in the DataAdapter?

It would be a bit of a hack but it would be at least working

Thanks, Patrick

I believe what he means is 1. create a view to select the columns from the UDF 2. map and LLBL entity to the view 3. use the LLBL entity to form the query

Thanks but these steps wouldn't work.

pat wrote:

Do you mean to just use a view onto the CTE and lose the ability to pass a parameter (which wouldn't work as I need to pass a parameter)

It would still be helpful to know if I can alter the query before it's send to the db server via search & replace. And if yes how?

Thanks, Patrick

why won't the proposal work?

jmeckley wrote:

why won't the proposal work?

How can I pass a parameter into the view? Since I need a parameter to be passed in to run the CTE I need a function. Or do I misunderstand something about the proposal?

Thanks

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.

jmeckley wrote:

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

You can manipulate the sql, by deriving a class from DataAccessAdapter and override OnFetchEntityCollection, which gets the query passed in.

Otis wrote:

You can manipulate the sql, by deriving a class from DataAccessAdapter and override OnFetchEntityCollection, which gets the query passed in.

Cool thank you

Last concern I have is that if I manipulate the sql, SQL Server won't be able to cache 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?

REPLACE SELECT e.Id, e.ParentId, e.Name FROM Tree AS E INNER JOIN dbo.vTreeCTE AS C on E.Id = C.Id

WITH SELECT e.Id, e.ParentId, e.Name FROM Tree AS E INNER JOIN dbo.fTreeCTE(@TreeId) AS C on E.Id = C.Id AND 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 sample of how to do it please?

Thanks, Patrick

Would it be as easy as adding a parameter to it and changing the CommandText? If not could you maybe give a sample of how to do it please?

I think it would be easy. You can change the entire command, you can create a SqlCommand for instance and you can fill its Parameters.

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 code 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 call 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.Value = rootId;
    FetchEntityCollectionQueryOverride queryOverride = new FetchEntityCollectionQueryOverride(NodeCollection, 
                                                           "[vGroupId]", 
                                                           "[fGetGroupIds_Descendent](@TreeId)", 
                                                           sqlParameter);
    da.FetchEntityCollectionOverride = queryOverride;
    da.FetchEntityCollection(NodeCollection, bucket, prefetch);
}

All the best, Patrick

Thanks for the feedback. I'm sure it wil help others taking into the same path.

pat wrote:

Cool the hack works quite well. I attached some basic sample code if anybody else needs this one day.

I was wondering if it would be possible to use the same approach with SelfServicing? If yes how and where would I do it please?

Thanks, Patrick

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 class, 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.

Walaa wrote:

I think the following might work:

Thanks for the quick answer. I will try it out.

Patrick