pat wrote:

I would like to retrieve an entity collection of hierarchical 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.

Hi,

cool that LLBLGen 2.6 is released!

In the handbook there is a section "Linq to LLBLGen Pro, Function mappings" which made me wonder if the problem I had before could be solved better now? Using an UDF in a join with SQL 2005. http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=11573

Thanks a lot for your help, Patrick

LLBLGen 2.6 (June-6-2008 ), SQL 2005, C#, Adapter Template

I think this is now possible using the new Derived table support feature.

Please check the 2.6 manual "Using the generated code -> Derived tables and dynamic relations"

Walaa wrote:

I think this is now possible using the new Derived table support feature.

Please check the 2.6 manual "Using the generated code -> Derived tables and dynamic relations"

Hi Walaa,

thanks for answering. I can't really see how the derived table would help here as a normal join would be sufficient. Could you maybe give an example of how using an UDF now works with LLBLGen 2.6? And also how I could use an UDF in a join operation?

Thanks a lot, Patrick

Well you can do this in two ways: 1) use the DynamicRelation class which is new in v2.6 and use normal LLBLGen Pro code OR 2) use Linq and map a method onto a DB function and simply use that method on the join clause of the linq query

Walaa wrote:

Well you can do this in two ways:

Still don't see the light

Walaa wrote:

use the DynamicRelation class which is new in v2.6 and use normal LLBLGen Pro code

Normal LLBLGen code never used to support UDF's until I found the hack the link above points to (not really sure anymore who found it)

Any chance for some code regarding the sample SQL above (can also be pseudo code)?

Thank you very much, Patrick

V2.6 has a DynamicRelation class, which allows you to specify a join between two entities (or a derived table and an entity or two derived tables) over a predicate you define. This means you can set the predicate to be a field compare expression predicate which compares a field to a dbfunction call.

Another way, if you're able to use Linq (so you're on .NET 3.5), is to map a .net method onto the db function you want to call (see documentation how to do that) and then use it like: here I added a static method Foo to the class Mappings (assume it's mapped onto a DB function)

var q = from a in metaData.SomeEntityA join b in metaData.SomeEntityB on Mappings.Foo(a.Field) equals b.Field ...

Otis wrote:

This means you can set the predicate to be a field compare expression predicate which compares a field to a dbfunction call. ... Another way, if you're able to use Linq (so you're on .NET 3.5), is to map a .net method onto the db function you want to call (see documentation how to do that) and then use it like:

Hm both examples above and the documentation give only examples for scalar values while the SQL UDF would return a table.

Is there any chance you could show me with some code how to make this query work with .NET 3.5, LLBLGen Pro 2.6?

pat wrote:

I would like to retrieve an entity collection of hierarchical entities. To filter them I would like to use an inner join to a UDF (User Defined Function which uses a CTE (common table expression))

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.

Thanks a lot! Patrick

pat wrote:

Otis wrote:

This means you can set the predicate to be a field compare expression predicate which compares a field to a dbfunction call. ... Another way, if you're able to use Linq (so you're on .NET 3.5), is to map a .net method onto the db function you want to call (see documentation how to do that) and then use it like:

Hm both examples above and the documentation give only examples for scalar values while the SQL UDF would return a table.

My bad, I should have anticipated on that.

Is there any chance you could show me with some code how to make this query work with .NET 3.5, LLBLGen Pro 2.6?

pat wrote:

I would like to retrieve an entity collection of hierarchical entities. To filter them I would like to use an inner join to a UDF (User Defined Function which uses a CTE (common table expression))

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.

Thanks a lot! Patrick

There are two things: 1) make the query compile 2) make sure the right sql is generated.

The first one is a big bottleneck, as the method stub which is used in the Linq query (and which is used to obtain the db function etc.) has to return an object with fields which is then usable in the 'on clause' of the join statement in the linq query. Without that, the query won't compile.

I'll do some tests to see if I can get this working.

Ok, it is currently not supported by the Linq provider. I could get as far as mapping the method call onto a function and return an IQueryable, but the join handler expects a set and not a methodcall (which is faked to be a set but which isn't handled at that stage).

If that code is changed it will end later on in the dynamic relation which isn't supporting a call to a table function. So this isn't supported. Sorry, I should have seen this earlier.

The workaround I see is to create a view with the CTE, but that of course implies a fixed input value for the CTE.

Thanks for your reply Frans.

Otis wrote:

The workaround I see is to create a view with the CTE, but that of course implies a fixed input value for the CTE.

I wrote a little workaround using a view and replacing it with a call to the UDF before the query was executed a while ago: http://www.llblgen.com/TinyForum/GotoMessage.aspx?MessageID=64617&ThreadID=11573

Could you please have a look if this would still work in LLBLGen 2.6?

Thank you, Patrick

That would still work indeed.