Hi, I have promoted two fields from a related entity. So (for example) I have an Orders table with a UserId that maps to the Users table. I have manually added two fields UsersFirstName and UsersLastName to the Orders entity that map to the First/Last name fields in the Users entity. When I look at the query that gets generated I see that there is a full select from the Users table with the list of UserIDs in the select clause. When the first query (that returns Orders) returns a large number of rows, the second query that prefetches users will become very inefficient. Is it possible to structure the query in such a way that the Fields mapped on related entities are fetched in one shot in the main query, so that the select would look like: Select Orders.OrderId, Orders.OrderDate, .... Users.FirstName as USersFirstName, Users.LastName as USersLastName from Orders inner (or left outer) join Users on orders.UserId = Users.UserId where ...... If I could structure the query this way the performance will greatly improve..
Please let me know if such a thing is possible?

Thank you!! Filipp

One approach to this is to replace the entity you are adding the related fields to with an entity created from a view. The view would then do the joins you want and the user name would be an entity field not a related field.

You also can do that extending <youEntity>EntityFactory** for retrieval purposes**. Read this Fran's article: http://weblogs.asp.net/fbouma/archive/2006/06/09/LLBLGen-Pro-v2.0-with-ASP.NET-2.0.aspx

Thank you for your comments!
I was aware of the projections, but that is not a very straight forward solution, and the column names need to be hardcoded, what beats the purpose of llblgen (I could just right the sql directly in that case). What would be nice is to have a flag that when turned on makes the adapter include all of the 'promoted' fields in the select list, so that when I include a Relationship in the predicate, those fields are populated from the JOINed table. Hope something like that can become available in the future..

Thank you! Filipp

When I look at the query that gets generated I see that there is a full select from the Users table with the list of UserIDs in the select clause. When the first query (that returns Orders) returns a large number of rows, the second query that prefetches users will become very inefficient.

In v.2.5 currently in beta, and soon will be released: You can execlude fields from being fetched. For example if you are using a PrefetchPath to fetch users along with Orders, you may add a List of fields to be execluded from the prefetched entity (only leave the LastName and FirstName, and execlude the rest). This parameter is now available in an overload of the prefetchPath.Add() method.

I was aware of the projections, but that is not a very straight forward solution, and the column names need to be hardcoded, what beats the purpose of llblgen (I could just right the sql directly in that case).

You can always use entityFields to get column names as: So instead of writieing "StartDate" you can write => EmployeeFields.StartDate.Name

Anyway If you still want to use one query for fetching fields from more than one entity, I'd recommend using a TypedList or a DynamicList. That's why they were invented