I have a UDF that returns a comma-delimited string of values that I use to indicate a one-to many relationship in my resultset without returning multiple rows that would be duplicates other than the foreign key.

For example, instead of this:

TaskName........AssignedTo

Task A..............Joe Task B..............Joe Task A..............Mary

I want to end up with this:

TaskName........AssignedTo

Task A..............Joe, Mary Task B..............Joe

SQL-wise, I can accomplish this by doing a normal select and using the following UDF for the AssignedTo column:

dbo.ConcatTaskMembers(dbo.Tasks.TaskID) AS AssignedTo

This works great in query analyzer, but I cannot find any information on whether or not this is supported in the LLBL designer and how I would implement it. It doesn't seem like you can map a non-entity value to a typed list column. I briefly looked at custom properties but that did not seem to be what I want.

By the way, I can get the UDF part to work in LLBL using a view, but the problem I'm having there is that since the relationships are merged in the view, I get all tasks related to multiple members as duplicates. I have to be able to filter on MemberID, and since it's in the view it always makes the otherwise duplicate rows distinct. In the example above, I would always get all 3 rows returned like so:

TaskName........AssignedTo......MemberID

Task A..............Joe, Mary.........1 Task B..............Joe..................1 Task A..............Joe, Mary.........2

In LLBL there does not seem to be a way to filter on a typed view column but NOT return that column as part of the result fields. Or maybe I'm missing something. It seems like you have to do this:

IEntityFields2 fields = tasks.GetFieldsInfo();

when what I really want is to either specify my own fields or do something like this:

IEntityFields2 fields = tasks.GetFieldsInfo();
fields.Remove(FieldIndex.MemberID);

I looked at the dynamic list field syntax but could not get that to work with my typed view. Basically, I don't care HOW to get this done -- I just want to end up with my list with the members delimited and not duplicated as specified in the examples above.

Any ideas?

UDF's are not supported directly. You can implement an IExpression implementation which emits a call to your UDF. See this thread for an example how that's done (the example uses a system function, but that's the same thing).

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3829

Otis wrote:

UDF's are not supported directly. You can implement an IExpression implementation which emits a call to your UDF. See this thread for an example how that's done (the example uses a system function, but that's the same thing).

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3829

Hmmmm. I understand the post you referenced, but I'm not quite sure that solves my issue. The query in that article is:

select * from xxx where month(fieldname) = 5 and year(fieldname2) = 2005

Since the function is in the WHERE you are extending the base Predicate clasee. My example is slightly different. I don't need to filter using a function -- I want to transform the foreign-key column (MemberID) with a UDF but not actually return it directly in the results (so that the DISTINCT will work) like so:

SELECT DISTINCT 
    dbo.Tasks.TaskID, dbo.ConcatTaskMembers(dbo.Tasks.TaskID) AS AssignedTo -- etc.
FROM        
    dbo.Tasks INNER JOIN
    dbo.TaskMembers ON dbo.Tasks.TaskID = dbo.TaskMembers.TaskID
WHERE
    dbo.TaskMembers.MemberID in (1,2)

The UDF finds all members assigned to a given TaskID and outputs a single string with the members' names comma-delimited. In the case where members 1 and 2 are assigned to the same task, this query returns one row. Extending Predicate does not help me to get the UDF into the return fields collection. Am I missing something?

Man, I am really stuck on this. If I don't figure it out pretty soon I'm going to have to probably just implement it as a stored proc, but I've been avoiding doing that thus far. It's kind of disappointing that I can't incorporate a UDF the way I want. I'm sure there's a good reason (right?), but it's still frustrating.

It's the same, though in an object fetch it is indeed problematic, at least in a collection fetch, because the fields used to produce the query aren't passed in... If that's the case, then you're out of luck, if you're fetching a typedlist/dynamic list, then it can be done, because you can set the particular field's ExpressionToUse property to an instance of your IExpression object.

If you're fetchign a list, could you please post your IExpression implementation so far, so I or a support team member can have a look to see where it might need a little adjustment?

Dont know if this will help but last year i wrote a add-on that allows the use of UDFs for SqlServer/SelfServicing it is in the 3rd party download section. I can't exactly remember how i implemented it anymore but i am sure you can look at it and come up with something.