I'm using LLBLGen Pro 2.6 with adapter and .NET 3.5/c# connecting to a SQL Server 2008 dB.

I have a charge table/entity that has a one to many relationship to a rate table/entity as multiple rates can exist for each for charge. For an individual user selecting a charge, only one of the rates is valid based on their location and the date. So when fetching a charge I am also querying the rate table.

What I'd like to know is, how can I populate my charge entity object and its related rate entity object from my single query that performs a join and selects the top record?

I'm familiar with prefetchs but as I am already querying the rate table this seems like an unnecessary overhead (plus a simple prefetch will return all the related rates).

        public ChargeEntity Fetch(Guid chargeId, DateTime validDate)
        {
            ChargeEntity charge = null;

            using (DataAccessAdapter adapter = new DataAccessAdapter(m_ConnectionString))
            {
                LinqMetaData metaData = new LinqMetaData(adapter);

                var charges = (from ac in metaData.Charge
                               join rt in metaData.Rate on ac.ChargeId equals rt.ChargeId
                               orderby rt.AreaType descending
                               where ac.ChargeId == chargeId
                               && ((rt.AreaId == m_CompanyId && rt.AreaType == (byte)AreaType.Company)
                                    || (rt.AreaId == m_StoreId && rt.AreaType == (byte)AreaType.Store))
                               && rt.ValidFrom <= validDate
                               && rt.ValidTo >= validDate
                               select ac)
                                 .TakePage(1, 1) // first page, one record
                                 .WithPath(
                                    chargePath => chargePath
                                        .Prefetch(ac => ac.Rate));

                charge = charges.ToList()[0];
            }

            return charge;
        }

So you don't want to use a prefetchpath dven if you can pass a filter to return the specific rate only.

Thanks for your reply Walaa.

I was pretty sure that I could filter the prefetchpath but I've actually simplified the query somewhat as the real one uses 3 tables. I'm really wondering if it's possible to populate the related entity(s) using a query that joins the related tables?

Hierarchical fetches in linq are always using multiple queries, at least if you want to fetch a 1:n relationship, as you can't assign an entity in the projection to a collection.

I tried the other way around:

var q = from o in metaData.Order
        join c in metaData.Customer on o.CustomerId equals c.CustomerId
        where o.OrderId == 10254
        select new OrderEntity()
        {
            OrderId = o.OrderId,
            EmployeeId = o.EmployeeId,
            Customer = new CustomerEntity() { CustomerId = c.CustomerId, CompanyName = c.CompanyName }
        };

This however doesn't instantiate the customer entity although it does fetch the proper info in the query. I'll look into this.

To be able to do what you want, please use our own query api: formulate a projection using resultsetfields and then do the projection manually using your own projector class. There's no other way to do it.

The problem the example query I gave above doesn't work comes from the fact that the engine decides to use the DataProjectorToIEntityCollection(2) class. When I hard-code the choice to use the generic DataProjectorToObjectList<> class it works. There's a problem though... it breaks code:

ILLBLGenProQuery q = (ILLBLGenProQuery)(from od in metaData.OrderDetail
                        where od.ProductId < 4
                        select new OrderDetailEntity{ OrderId = od.OrderId, UnitPrice = (decimal)od.UnitPrice, ProductId = od.ProductId });

EntityCollection<OrderDetailEntity> result = (EntityCollection<OrderDetailEntity>)q.Execute();

won't work after that.

I'll see if I can work around this.

Found it (was not that hard ). So first, you've to rewrite your query in such a way that you fetch the 'n' side of the 1:n (so the rate) and in the projection assign the charge to it. As it's a m:1 relationship. Now, I don't know if this suits your particular situation, but if it does, you can now use it. See attached dll.

Many thanks for the feedback Otis. I will look to use projection and see how I get on.