Hello. I am trying to dynamically build prefetch paths, and it is not working (nothing is prefetched). I am trying to figure out the correct way to do this. Specs and code below.

Thanks!

SD.LLBLGEN.Pro.DQE.SqlServer.NET20 SD.LLBLGEN.Pro.LinqSupportClasses.NET35 SD.LLBLGEN.Pro.OrmSupportClasses.NET20 SD.LLBLGEN.Pro.QuerySpec

Code is generated using LLBLGen Pro version: 3.5 Code is generated using templates: SD.TemplateBindings.SharedTemplates.NET20

SD.LLBLGEN.Pro.ORMSupportClasses.NET20 Runtime Version: v2.0.50727 Version: 3.5.0.0

SD.LLBLGEN.Pro.LinqSupportClasses.NET35 Runtime Version: V2.0.50727 Version: 3.5.0.0

public PersonSearchResult GetByCustomer(PersonSearchModelDto model)
        {
            var result = new PersonSearchResult();

            // TODO: Refactor this into something more efficient.
            
            if (!string.IsNullOrWhiteSpace(model.CustomerErpId))
                model.CustomerId = GetParentCustomerGuid(model.CustomerErpId);

            var query = _linqMetaData.Person
                .FilterByPrimaryCustomerGuid(model.CustomerId)
                .FilterByEmail(model.Email)
                .FilterByIsActive(model.IsActive)
                .FilterByFirstname(model.Firstname)
                .FilterByLastname(model.Lastname)
                .FilterByGroupGuid(model.GroupGuid)
                .FilterByBusinessUnit(model.BusinessUnits)
                .FilterByVoucherPrograms(model.VoucherPrograms)
                .FilterByChildCustomers(model.ChildCustomers);

            if (!string.IsNullOrWhiteSpace(model.EmployeeNumber))
                query = query.Where(p => GetPeopleMatchingEmployeeId(model.EmployeeNumber).Contains(p.Guid));

            result.Count = query.Count();
        
            if (model.IncludeMetaData) query = query.WithPath(x => x.Prefetch(p => p.PersonMetaDatas));
            if (model.IncludeBusinessUnits) query = query.WithPath(x => x.Prefetch(p => p.PersonBusinessUnits));
            if (model.IncludeChildCustomers) query = query.WithPath(x => x.Prefetch(p => p.PersonChildCustomers));
            if (model.IncludeRoles) query = query.WithPath(x => x.Prefetch(p => p.PersonRoles));
        
            var persons = query
                .OrderBy(x => x.Firstname)
                .ThenBy(x => x.Lastname)
                .Skip(model.PageSize * model.Page)
                .Take(model.PageSize)
                .Select(x => x.ProjectToDto())
                .ToList();

            result.Persons = persons;

            return result;
        }

Are there any prefetch queries executed? You can troubleshoot the queries and check them in the output window.

Btw, the runtime version provided is the .NET version number, please check the forum thread guidelines.

Walaa wrote:

Are there any prefetch queries executed? You can troubleshoot the queries and check them in the output window.

Btw, the runtime version provided is the .NET version number, please check the forum thread guidelines.

The project is using .NET 4.5.

I am using Visual Studio 2015 w/ IntelliTrace, and I can see the generated queries in the diagnostics window as they execute.

I thought maybe I had the syntax wrong for the .WithPath extension method? Chaining the WithPath methods doesn't seem to do anything.

Reading through the documentation again.. Is it because I am projecting the query into a new object?

Thanks for your help!

--- I just refactored again using the PathEdge syntax (and got rid of the projection in the query), and this code is working properly.. But I want to be able to dynamically build the collection of PathEdge objects, and pass them into the WithPath extension method.

I hope that makes sense...

    
        
            query = query.WithPath(
                 new PathEdge<CustomerEmployeeIdEntity>(PersonEntity.PrefetchPathCustomerEmployeeId),
                 new PathEdge<CustomerEntity>(PersonEntity.PrefetchPathCustomer),
                 new PathEdge<PersonMetaDataEntity>(PersonEntity.PrefetchPathPersonMetaDatas),
                 new PathEdge<PersonBusinessUnitEntity>(PersonEntity.PrefetchPathPersonBusinessUnits),
                 new PathEdge<PersonChildCustomerEntity>(PersonEntity.PrefetchPathPersonChildCustomers),
                 new PathEdge<PersonRoleEntity>(PersonEntity.PrefetchPathPersonRoles, new PathEdge<RoleEntity>(PersonRoleEntity.PrefetchPathRole)));

            var persons = query
                .OrderBy(x => x.Firstname)
                .ThenBy(x => x.Lastname)
                .Skip(model.PageSize * model.Page)
                .Take(model.PageSize)
                .ToList();

            result.Persons = persons.Select(p => p.ProjectToDto()).ToList();
            return result;

Quoting the documentation:

There are three different ways of specifying a prefetch path in Linq to LLBLGen Pro: using PathEdge instances, using Lambda expressions or by using a normal PrefetchPath(2) object like with the lower-level API. It can be that one appeals more to you than the other. They're identical in feature set (the Lambda variant is built on top of the PathEdge variant, which is more low level).

The first one you posted, WithPath using lambdas. When you use this you should put only one .WithPath construct per query, no multiple ones.

The second one (PathEdges), you used it fine: using just one .WithPath call in the query, but you can't reuse it.

The third one, using PrefetchPath objects. You could construct it and then pass it through the .WithPath call:

var path = new PrefetchPath(EntityType.Person);
path.Add(PersonEntity.PrefetchPathCustomer);
path.Add(PersonEntity.PrefetchPathEmployee);
...

query = query.WithPath(path);

This way you could construct it dinamically.