I'm a new user to LLBL Gen. Love the product! I'm starting to use related fields to pull in data from related tables.

What I am trying to do now is pull in data from a related table based on a dynamic query.

For example, the relation would be a sub-query like the following:

SELECT Top 1 FieldName FROM TableName where IsPrimary = 1

This will always return just a single row so it's kind of like a one to one relationship. I want to pull the results of the entire set of data into a DetailsView.

Is it possible to do this from with the LLBLGen designer? Or is this something that needs to be done in a wrapper class? Can you point me in the right direction?

Thanks,

Scott

OWScott wrote:

I'm a new user to LLBL Gen. Love the product! I'm starting to use related fields to pull in data from related tables.

What I am trying to do now is pull in data from a related table based on a dynamic query.

For example, the relation would be a sub-query like the following:

SELECT Top 1 FieldName FROM TableName where IsPrimary = 1

This will always return just a single row so it's kind of like a one to one relationship. I want to pull the results of the entire set of data into a DetailsView.

Is it possible to do this from with the LLBLGen designer? Or is this something that needs to be done in a wrapper class? Can you point me in the right direction?

This isn't possible at the moment, it would result in a query like: SELECT (SELECT top 1 FieldName FROM TableName WHERE ISPrimary=1) AS FieldName, -- otherfields FROM Table2 WHERE...

and that's not supported.

If you add that in an entity, and you pull 100 entities frm the db, you'll get 100 subqueries being executed, which is slowing down the code a lot. Are you using the data for a read-only purpose? If that's the case, you can create a dynamic list, use a join and a subquery filter (If I understand hte problem correctly).

Thanks. You understood it right. I will only pull one entity so I'm not worried about performance on the database.

Can this be done from the LLBL Gen GUI as an Entity? Or is a dynamic list the only option. A dynamic list should be ok, I just like the way an Entity with related fields works if it's possible.

On a side note, the Best practises - How do I ... ? article has a mistake. Go to the section with "use a group by clause in a dynamic list ?" and both C# and VB.NET SelfServicing examples have dynamicList instead of tlist as one of the variables.

Thanks,

Scott

I think you may also create a database View that holds your sub-query, then you might use the LLBLGen Pro Designer to add this View as an Entity, then you may start using this Entity and define relations with other Entities.

OWScott wrote:

On a side note, the Best practises - How do I ... ? article has a mistake. Go to the section with "use a group by clause in a dynamic list ?" and both C# and VB.NET SelfServicing examples have dynamicList instead of tlist as one of the variables.

corrected in the docs for next build

Walaa wrote:

I think you may also create a database View that holds your sub-query, then you might use the LLBLGen Pro Designer to add this View as an Entity, then you may start using this Entity and define relations with other Entities.

Ah, that's simple and a good idea. I'll do that direction then.

Thanks,

Scott