I'm using 1.0.20051.60207, I found a case which cause prefetch path not fetch all the records in child table into child collection.

For example, I want to fetch a order with its order lines. I have the following data in database.

[Order:] OrderNo: "SO0001__" CustomerNo: "C01"

[Order Lines] OrderNo: "SO0001__ " LineNo: "1"

OrderNo: "SO0001__" LineNo: "2"

OrderNo: "SO0001" LineNo: "3"

_ is a space

Data type of OrderNo is a nvarchar( 8 ). Some data is inserted by a old system which is written by a old language, it would add space to my data if length of data is less than field length. In this case two space is added to "SO0001".

When I fetch data with new .Net system, by the following code, only the first two order lines (1 and 2) is fetched. I checked the sql statement generated by LLBLGen in sql profiler, it can fetch all 3 lines, but LLBLGen did not add line 3 into the collection. I think it may be problems of tailing space.

string orderNo = "SO0001";
IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.SalesOrderEntity);
prefetchPath.Add(SalesOrderEntity.PrefetchPathDetails);

SalesOrderEntity salesOrder = new SalesOrderEntity(orderNo);
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchEntity(salesOrder, prefetchPath);
}

Can't be !!

If OrderNo is a nvarchar( 8 ), then spaces should be trimmed by the databse. So you won't have these values stored "SO0001__" only "SO0001"

Please confirm the DataType in the [Order Lines] table as well as the [Order] table.

Thanks.

Data type is the same in both tabe.

I open table in SQL server and highlight the text in cell, it really have trailing spaces. And I clear data in cell and input some spaces, it saved the spaces and did not trim spaces.

I was mistaken,

I used len() function to test for the length of varchar fields after insterting some values with trailing spaces, and this function output is missleading.

len() ignores spaces at the end, instead I used DATALENGTH() which was better indicative

What's your database Collation, and version number (service pack)?

Another thing why don't you unify the data stored, I mean why not to enter trailing spaces for all the values in the database?

Database and server collation is SQL_Latin1_General_CP1_CI_AS, I've tested on SQL 2000 SP4 and SQL2005 and got the same reult.

Since data with trailing spaces is inserted by an old system, and I'm now writing a new system to migrate to .Net, which won't insert trailing space to data.

If all data is stored with trailing spaces, it's very trouble to compare string with trailing spaces in .Net, for exmple, user input "SO0001", and I need to execute TrimEnd() on string retrieved from database, or pad the input string to add trailing spaces in order to match with the data in database.

I elminate all the trailing space in my new system, all my data entry controls will trim input data before setting value to dataset or objects generated by LLBLGen, therefore, all data inserted by new system won't have trailing spaces.

I've done some tests on fetching entity collection. I fetch sales order line to an entity collection by the following code, it can fetch all 3 lines of record and populate all of them into entity collection, it does not affected by the tailing spaces in data. I think fetching on prefetch path should behave the same as fetching on entity collection.

string orderNo = "SO0001";
EntityCollection collection = new EntityCollection(new SalesOrderDetailEntityFactory());
IRelationPredicateBucket filterBucket = new RelationPredicateBucket(SalesOrderDetailFields.OrderNo == orderNo);
using (DataAccessAdapter adapter = GetCompanyDataAccessAdapter())
{
    adapter.FetchEntityCollection(collection, filterBucket);
}

Barry wrote:

I'm using 1.0.20051.60207, I found a case which cause prefetch path not fetch all the records in child table into child collection.

For example, I want to fetch a order with its order lines. I have the following data in database.

[Order:] OrderNo: "SO0001__" CustomerNo: "C01"

[Order Lines] OrderNo: "SO0001__ " LineNo: "1"

OrderNo: "SO0001__" LineNo: "2"

OrderNo: "SO0001" LineNo: "3"

_ is a space

Data type of OrderNo is a nvarchar( 8 ). Some data is inserted by a old system which is written by a old language, it would add space to my data if length of data is less than field length. In this case two space is added to "SO0001".

When I fetch data with new .Net system, by the following code, only the first two order lines (1 and 2) is fetched. I checked the sql statement generated by LLBLGen in sql profiler, it can fetch all 3 lines, but LLBLGen did not add line 3 into the collection. I think it may be problems of tailing space.

string orderNo = "SO0001";
IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.SalesOrderEntity);
prefetchPath.Add(SalesOrderEntity.PrefetchPathDetails);

SalesOrderEntity salesOrder = new SalesOrderEntity(orderNo);
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchEntity(salesOrder, prefetchPath);
}

This is caused indeed by the trailing space. LLBLGen pro calculates hashvalues for the FK field values and hashvalues for the PK field values and then seeks which fk field values match which pk field values using the hashes and if a match is found, the fk belongs to the pk. The space comes back in PK and FK for 1 and 2, but not for the FK in 3. This thus results in a different hash, as the FK WITH the space isn't the same string as the FK without the space.

The only way to solve this is to effectively make fk field values the same as PK field values. You get trailing spaces by converting a char(n) column to varchar(n) (in certain cases), and you can correct this with a trim query in an update statement.