Hello,

We have an 'Employee'(Parent) and 'Language'(Child) tables. For each employee there are many records in Language table. We are displaying Employee profile information on the page. I looked at SQL Profiler and I saw there are 4 SQL statements getting executed for each language to display language information. I don't think this is normal. What is the best way to get the related information in one query?

Thank you.

Most probably these have been called by Lazy Loading. And this means you are using SelfServicing.

Best way is to use Prefetch Paths

Thank you. We are using Adapter. I also would like to get Phone numbers from 'PhoneNumbers' table. How can I add phonenumber filter to get the collection? Basically I am trying to get 1:n records. I don't think this is right. Here is my sample code.

EntityCollection employee = new EntityCollection(new EmployeeEntityFactory()); IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.EmployeeEntity); prefetchPath.Add(EmployeeEntity.PrefetchPathEmployeeLanguageByEmployeeId); IRelationPredicateBucket filter = new RelationPredicateBucket(); //Language table filter.PredicateExpression.Add(EmployeeLanguageFields.SysDeleteInd == false));

//PhoneNumber table filter.PredicateExpression.Add(EmployeePhoneFields.SysDeleteInd == false));

DataAccessAdapter adapter = new DataAccessAdapter(); adapter.FetchEntityCollection(employee , filter, prefetchPath);

Please suggest..

What is the best way to get the related information in one query?

If you want to get it all in one query you probably don't want to use entities, but typed lists or typed views instead. When using entities a query is run for the parent entity and others for the prefetch path entities. If it was done in one query the data for the parent entity would be repeated for each of the child entities and would have to be sorted out after retrieval. Llblgen doesn't do that.

Don't assume that the multiple queries are inefficient. You'll see, particularity with deep prefetch chains, that the performance is fine.

Currently I am trying this way. Is this right way of doing? Please suggest. This one executing query for each child record.

        EmployeeEntity employee = new EmployeeEntity(contract.EmployeeId);
        DataAccessAdapter adapter = LLBLGenAdapterUtility.GetAdapter();

        IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.EmployeeEntity);

// filter for Phone IPredicateExpression phoneFilter = new PredicateExpression(); phoneFilter.AddWithAnd(EmployeePhoneFields.SysDelete == false); prefetchPath.Add(EmployeeEntity.PrefetchPathEmployeePhoneCollectionByEmployeeId, 0, phoneFilter);

// filter for language IPredicateExpression languageFilter = new PredicateExpression(); languageFilter.AddWithAnd(EmployeeLanguageFields.SysDelete == false); prefetchPath.Add(EmployeeEntity.PrefetchPathEmployeeLanguageByEmployeeId, 0, languageFilter);

adapter.FetchEntity(Employee, prefetchPath);

         foreach (EmployeePhoneEntity phone in employee.EmployeePhoneCollectionByEmployeeId)
            {
                prefetchPath = new PrefetchPath2((int)EntityType.EmployeePhoneEntity);
                prefetchPath.Add(EmployeePhoneEntity.PrefetchPathEmployeeTypeRefByPhoneTypeId);

                adapter.FetchEntity(phone, prefetchPath);
            }


            foreach (EmployeeLanguageEntity language in employee.EmployeeLanguageByEmployeeId)
            {
                prefetchPath = new PrefetchPath2((int)EntityType.EmployeeLanguageEntity);
                prefetchPath.Add(EmployeeLanguageEntity.PrefetchPathLanguageRefByLanguageId);

                adapter.FetchEntity(language, prefetchPath);
            }

Your first part is ok, when you try to fetch the 1-level prefetch. However you then you fetch that and interate through the results to add more prefetchs and do more fetchs. That of course is not efficient. See, instead of the second part, you just need to add a subpath to the existing prefetchspaths objects. Here is an approximate code:

DataAccessAdapter adapter = LLBLGenAdapterUtility.GetAdapter();
EmployeeEntity employee = new EmployeeEntity(contract.EmployeeId);

-- phone path
IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.EmployeeEntity);
IPredicateExpression phoneFilter = new PredicateExpression(EmployeePhoneFields.SysDelete == false);
prefetchPath.Add(EmployeeEntity.PrefetchPathEmployeePhoneCollectionByEmployeeId, 0, phoneFilter)
     .SubPath.Add(EmployeePhoneEntity.PrefetchPathEmployeeTypeRefByPhoneTypeId);

// language filter
IPredicateExpression languageFilter = new PredicateExpression(EmployeeLanguageFields.SysDelete == false);
prefetchPath.Add(EmployeeEntity.PrefetchPathEmployeeLanguageByEmployeeId, 0, languageFilter)
     .SubPath.Add(EmployeeLanguageEntity.PrefetchPathLanguageRefByLanguageId);

// fetch al graph
adapter.FetchEntity(Employee, prefetchPath);

I wrote a post about all these things with PrefetchPaths, maybe it's helpful to you.