We have run into an issue where white space at the end of key values is causing the related objects not to return. Here is an example:

Table1: "12345ABCD" Table2: "12345ABCD "

While the generated SQL does relate and return the data, the object graph reports that the related entity (i.e. Table1Entity.Table2Entity) is NULL.

Is there a setting that is causing/would prevent this? Or has it been fixed in newer versions of LLBL? Or are we forced to clean up the data (I admit, our data is 10-year-old, dirty data)?

Version info: LLBL Gen Pro V. 2.5, Final Build Runtime = 2.5.7.1019 Database = MS SQL 2005 (Build 3790: Service Pack 2)

Let me know if there's anything else you need from me.

Thanks in advance. Jerad

Hi Jerad,

Weird, normally SQLServer truncate the remaining spaces in the update where the field doesn't belong to a explicit relation.

Do you have an explicit relation at DB or just at LLBLGen project? Are you trying to relate the two entities? or Are you prefetching the second entity?

Anyway, I think the best option is fix your data. Could be that possible? If not, please attach some repro project (DDL Sql + LLBLGen project) so we can reproduce your scenario.

Thanks for your response, David.

daelmo wrote:

Weird, normally SQLServer truncate the remaining spaces in the update where the field doesn't belong to a explicit relation.

Unfortunately, this database is huge and used by many applications, most of which are very old and do not incorporate LLBLGen. So the data can come in from multiple locations, and most of those have poor validation to trim strings.

daelmo wrote:

Do you have an explicit relation at DB or just at LLBLGen project? Are you trying to relate the two entities? or Are you prefetching the second entity?

This is an ASP.NET application, and I'm using the LLBLGenProDataSource2 control to bind the data to a GridView. See this thread to see the code and how it is set up:

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=14326

In this example, the problem is trying to access the ContractMasterEntity.CustMaster object -- this is returning NULL whenever the CustMaster key has spaces (and the ContractMaster key does not have spaces).

daelmo wrote:

Anyway, I think the best option is fix your data. Could be that possible?

This is certainly possible, but I'm looking more for a fix on the LLBLGen side, since it is unknown which keys exist across our database that could present this problem, and it would be hard to keep this data clean. Again, I realize this database design is not ideal (to put it nicely ), and that ideally this would be prevented in the first place (I don't like non-integer keys anyway), but since the database is so old and fixing all applications at this point would be a year-long project, I'm trying to figure out a solution on the LLBLGen side.

daelmo wrote:

If not, please attach some repro project (DDL Sql + LLBLGen project) so we can reproduce your scenario.

Here's an easy example using AdventureWorks.

Step 1: Set up an LLBL project on AdventureWorks w/ Person.StateProvince and Person.CountryRegion entities.

Step 2: Set up test data:

INSERT INTO Person.CountryRegion (
    CountryRegionCode,
    [Name],
    ModifiedDate
) VALUES ( 
    'AA ',
    'Test',
    '2008-9-25 11:5:29.532' ) 

UPDATE Person.StateProvince SET Person.StateProvince.CountryRegionCode = 'AA' WHERE Person.StateProvince.StateProvinceID = 1

Step 3: Set up sample console project:

using System;

using SD.LLBLGen.Pro.ORMSupportClasses;
using AdventureWorks.DatabaseSpecific;
using AdventureWorks.EntityClasses;
using AdventureWorks.HelperClasses;
using AdventureWorks;

namespace TestConsole
{
    class Program
    {
        static void Main(string[] args)
        {
            using (DataAccessAdapter adapter = new DataAccessAdapter("Server=033-709;Database=AdventureWorks;Trusted_Connection=True;"))
            {
                EntityCollection<StateProvinceEntity> states = new EntityCollection<StateProvinceEntity>();

                int[] stateProvinceIDs = new int[3] { 1,2,3};

                IPredicateExpression filter = new PredicateExpression(StateProvinceFields.StateProvinceId == stateProvinceIDs);
                RelationPredicateBucket bucket = new RelationPredicateBucket(filter);

                IPrefetchPath2 prefetch = new PrefetchPath2((int)EntityType.StateProvinceEntity);
                prefetch.Add(StateProvinceEntity.PrefetchPathCountryRegion);

                adapter.FetchEntityCollection(states, bucket, prefetch);

                foreach (StateProvinceEntity state in states)
                {
                    Console.WriteLine("State = {0}; Country code = {1}", state.StateProvinceCode, state.CountryRegion == null ? "NULL" : state.CountryRegion.CountryRegionCode);
                }
            }
        }
    }
}

Here are the results:

State = AB ; Country code = NULL
State = AK ; Country code = US
State = AL ; Country code = US

But SQL is able to still establish the join:

SELECT  Person.StateProvince.CountryRegionCode
,   '"' + Person.StateProvince.CountryRegionCode + '"'
,   DATALENGTH(Person.StateProvince.CountryRegionCode)
FROM    Person.StateProvince
WHERE   Person.StateProvince.StateProvinceID = 1

-- returns AA, "AA", 4

SELECT  Person.CountryRegion.CountryRegionCode
,   '"' + Person.CountryRegion.CountryRegionCode + '"'
,   DATALENGTH(Person.CountryRegion.CountryRegionCode)
FROM    Person.CountryRegion
WHERE   Person.CountryRegion.CountryRegionCode = 'AA'

-- returns AA, "AA ", 6

SELECT  COUNT(*)
FROM    Person.StateProvince
JOIN    Person.CountryRegion
ON  Person.StateProvince.CountryRegionCode = Person.CountryRegion.CountryRegionCode
WHERE   Person.StateProvince.StateProvinceID = 1

-- returns 1

Thanks again for any suggestions. Jerad

Ok. I reproduced it (build 2.5.8.804). We will look into it.

This isn't fixable on our side unfortunately. We can't decide for users to trim off spaces or not. The Prefetch paths use hash values to compare FK with PK value, and "AA " is different from "AA" in such a way that it produces a different hashcode.

See: http://support.microsoft.com/kb/316626 and http://decipherinfosys.wordpress.com/2007/02/15/ansi_padding-and-trailing-spaces-in-sql-server/

for more info about ANSI_PADDING.

There's something you can do however. Create a type converter . You should create a typeconverter which simply converts from string to string (so it's really simple) and during the conversion from db value to .net value, you should trim off trailing spaces.

Then, you should apply this type converter to the fields which are FKs and which contain trailing spaces.

To prevent this crap from continuing, you should at least make sure that your application doesn't save trailing spaces into the db