I am returning a List<> of entities that I'm fetching via LINQ to LLBL. My LINQ query specifies a prefetch path.

The odd thing is that some of my entities are missing their related entities that should have been fetched via the prefetch.

I can see the two queries LLBL executes via Profiler, and I see data coming back for the entities that should be prefetched.

To illustrate: DB: Contract 1 FK-> Customer 1 Contract 2 FK-> Customer 2

SQL executed by LLBL: First query returns Contract 1 and Contract 2 Second query returns Customer 1 and Customer 2

LLBL: Contract 1.Customer = Customer 1 Contract 2.Customer = NULL

If the query LLBL executes to fetch the entities I've specified in a prefetch, and data is being returned, why aren't the entities being associated with the parent entities?

Thanks for any insight into what's going on!

Here's the method that's failing (it's not returning a CustMasterEntity for all ContractMasterEntities):

Edit simplified test case posted below.

Please can you also post the generated SQL ?

Thanks

Matt

Here are the two queries:

-- First query executed by LLBL
exec sp_executesql N'SELECT [LPA_L1].[ContractNumber], ...OTHER COLUMNS... FROM ((SELECT [LPLA_1].[Contract_Number] AS [ContractNumber] ...OTHER COLUMNS... FROM [DB].[dbo].[Contract_Master] [LPLA_1] (nolock)) [LPA_L1] LEFT JOIN [DB].[dbo].[Cust_Master] [LPA_L2] 
(nolock) ON  [LPA_L2].[Customer_ID]=[LPA_L1].[CustomerId]) WHERE ( ( ( ( ( 
[LPA_L1].[DealerId] IN (@DealerId1))) AND ( [LPA_L2].[Last_Name] IN (@LastName2))))) ORDER BY [LPA_L2].[City] DESC',N'@DealerId1 
varchar(18),@LastName2 varchar(25)',@DealerId1='ABC123',@LastName2='Smith'

-- Second query executed by LLBL
exec sp_executesql N'SELECT [DB].[dbo].[Cust_Master].[Customer_ID] AS [CustomerId], ...OTHER COLUMNS...
FROM [DB].[dbo].[Cust_Master] (nolock) WHERE ( ( ( 
[DB].[dbo].[Cust_Master].[Customer_ID] = @CustomerId1 OR ( 
[DB].[dbo].[Cust_Master].[Customer_ID] = @CustomerId2 OR ( 
[DB].[dbo].[Cust_Master].[Customer_ID] = @CustomerId3 OR ( 
[DB].[dbo].[Cust_Master].[Customer_ID] = @CustomerId4)))',N'@CustomerId1 
varchar(18),@CustomerId2 varchar(18),@CustomerId3 varchar(18),@CustomerId4 varchar(18)',@CustomerId1='123',@CustomerId2='456',@CustomerId3='789',@CustomerId4='101'

Here's the method that's failing (it's not returning a CustMasterEntity for all ContractMasterEntities): Code: public static List<ContractMasterEntity> SearchForContracts(string[] dealerId, object[] accountId, string[] firstName, string[] lastName, string[] city, string[] state, string[] zip, string[] phone, string[] email, string[] contractNumber, string[] clientContractNumber, string[] contractStatus, string[] serialNumber, string[] modelNumber, string[] claimNumber, string[] productCode, string[] clientProductCode, decimal[] equipmentRetail, int? startAt, int? count, ContractSearchPrefetches? prefetches, IEntityField2 orderByField, SortOperator? sortOrder) { List<ContractMasterEntity> foundItems = new List<ContractMasterEntity>(); . . .

That's a lot to look at, could you please provide a much shorter repro code. Remove all irrelevant code, and test it before posting, it might be that some of the removed code caused the problem.

Here's a simplified version using the LLBL querying API that returns a collection whose entities are still sometimes missing their related entities that should have been prefetched.

Thanks.

        public static EntityCollection Search2()
        {
            EntityCollection results = new EntityCollection(new ContractMasterEntityFactory());
            IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.ContractMasterEntity);
            prefetchPath.Add(ContractMasterEntity.PrefetchPathCustMaster);
            IRelationPredicateBucket filter = new RelationPredicateBucket();
            filter.PredicateExpression.Add(ContractMasterFields.DealerId == "ABC123");
            filter.PredicateExpression.Add(CustMasterFields.LastName == "Smith");
            filter.Relations.Add(ContractMasterEntity.Relations.CustMasterEntityUsingCustomerIdDataSource);
            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                adapter.FetchEntityCollection(results, filter, prefetchPath);
            }
            return results;
        }

We figured out what was going on, and it's not an LLBL issue.

One of the columns used in our composite foreign key has unnecessary whitespace for some records.

Here's the same issue: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=14364

Would it be possible for LLBLGen to include an option for trimming whitespace when comparing two columns, so that it behaves like SQL does?

I understand that this is an issue with our data, and not with LLBL, but I think it would be nice to have the ability to have LLBL behave exactly like SQL with regard to string comparisons.

Thanks for the assistance!

Hi Alex,

Yes, as I mentioned on the comment response in llblgening.com the problem is due to the data (trailing spaces or case sensitive collation).

Unfortunately this isn't solvable in a direct way. As I see you have two options:

A. Use a TypeConverter. http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=14364&StartAtMessage=0&#80131

B. Write a DB View that query that table and remove the trailing spaces. Then you can map that View as an Entity (in LLBLGen Pro Designer) and create the appropriate relations for it. You can use that view instead of your normal entity.

Let us know if you need further help on this