Hello, (LLBLGenPro 4.1 - Adapter) I bit of an introduction to understand our problem: We are working on an desktop application which has input forms with lot of lookup controls. For example for one form we may have some 10 lookup controls which are loaded from various database tables. Loading all controls one by one makes the gui slow to display mainly because of the latency to the database and not the amount of data. Loading the data could be speedup by doing them async but we do not prefer this approach as it taxes the SQL server unnecessarily (10 simultaneous connections with 10 queries). What we would prefer is to do all queries at once and we know that .NET SqlClient has MARS support which is ideal for us.

Now I know that LLBLGen does not support something like this so I am thinking of directly executing SQL queries to get data and then manually load (hydrate) LLBLGen entities. The problem I'm facing at this point is how to produce the actual SQL query to load data required for LLBLGen. At this point I am thinking of simply using meta data provided by LLBLGEn entities (database table name and database table field names) to generate the SQL but I do not know how to generate SQL for predicates.

Can you point me in a right direction?

Thank you

Before we dive into this, what about loading lookups once at application restart, and cache them in the memory? Usually this has been a good practice.

Hi, First of all thank you for helping me solve this problem.

This is a multi-user application and as such we have to allow for the other users to modify the actual lookup tables while the application is working. Because of this we always load everything from database when displaying the main record which is displayed in the input form.

Now I know that LLBLGen does not support something like this so I am thinking of directly executing SQL queries to get data and then manually load (hydrate) LLBLGen entities.

The question then would be, why do you need to hydrate the fetched data into LLBLGen entities. In fact if I was going to use LLBLGen Pro for fetching readOnly data, I'd use DynamicLists rather than entities. (little bit faster). And so in your case, fetching dataTables would be ideal.

In some cases we would need some of the information from the entity selected in the lookup control. For example if I have a lookup of users and I have certain fields on the user which affect how the current control behaves then it would be easy to do:

private void OnLookupChanged(object sender, EventArgs e) { LookupControl ctrl = (LookupControl)sender; var myflag = ((UserEntity)ctrl.Selected).IsAdmin; }

In these cases it is helpful to have the full LLBLGen entity instead of just data.

Its not a problem to make it all working if we are working with dynamic lists or datatables but we do not want to do it in such a way and I think that we are straying away from my original question. If you think that the goal I stated originally (manually hydrate LLBLGen entities) is either: not feasible/very difficult/very fragile then please state it so that I may continue pursuing other solutions.

Please don't take this the wrong way and I realize that you want to help me but I do not find architectural discussions over forum or similar medium very productive because I would be forced to go into long explanations on why something is needed or not possible.

Thank you.

Writing your own SQL and Fill Entities is easy, but using all the API with your own SQL is not. For instance, using PredicateExpressions, relations, etc. So I wouldn't recommend it.

I didn't understood the argument about 'loading the data at application start' proposed by Walaa. And, just another suggestion: Have you considered Resulset Caching?

I guessed that predicates would be the hardest. But as I understand the DataAccessAdapter receives an IQuery type object to execute against the DB. It should be easy to convert IQuery to a regular SqlCommand with parameters. Is there an easy way to convert entity + predicate into IQuery?

The more closest method that do that is adapter.CreateSelectDQ that returns a IRetrievalQuery. That method is protected and the original code pre-process the elements and get the PersistenceInfo objects before calling that method.

You can download the RuntimeLibraries source code and see what you can do playing with that. I think it's possible, but I see that you will face some challenges injecting your own sql and executing that.

IMHO, the feasible options are:

A. Using LLBLGen Query cache. B. Pre-load the lookup entities in a static variable or per-session. C. Using an SP that retrieves multiples resulsets. D. Use your own SQL + SQLClient that retrieves your data using MARS and then hydrate LLBLGen entities manually.

BTW, v4.1 doesn't exist yet, I think you meant v4.0

Yes correct 4.0. Well thank you for your efforts I am going to check the IRetrievalQuery and if I am unable to do anything smart with it then I'm going to try something else. Thanks.