I have the unfortunate pleasure of hooking into a legacy immutable db that uses a char PK (which stores an int ironically). In one table of this db the PK has 1 trailing space. In another table, the PK has 2 trailing spaces.

I have created manual 1:m relationships, however I notice 1 major problem:

1) Using the code below:

            UserCollection uc = new UserCollection();
            IPrefetchPath prefetchPath = new PrefetchPath((int)EntityType.UserEntity);
            prefetchPath.Add(UserEntity.PrefetchPathTimekeep); // <--- THIS LINE
            uc.GetMulti(null, prefetchPath);

The <PrefetchPathTimekeep> produces this query:

SELECT   tkinit AS Tkinit, tksort AS Tksort, tklast AS Tklast, tkfirst AS Tkfirst, tktitle AS Tktitle
FROM         timekeep
WHERE    (tkinit IN
                          (SELECT    EmpNoChar
                            FROM          AttorneyReview.dbo.[User]))

That query returns the correct results. However the LLBL code does not load the User.Timekeep entity, presumably because of a PK string mismatch, presumably because of the trailing space (which SQL server ignores when doing the query).

2) If I do NOT do the prefetch, but instead lazy load, it works great, trailing space issues and all. Of course, I can't take the perf hit of lazy loading.

Problem #1 occurs for both tables I need to join across, 1 with a single trailing ' ', and another with 2 trailing ' '.

What do I do so that LLBL will internally match the results during the prefetch?

C# .Net 2, LLBL v2, self-servicing, sql 2005

Hi yogi, These threads are related to your problem: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=5405 http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3652

Both of those suggest a DB trim workaround.

daelmo wrote:

Hi yogi, These threads are related to your problem: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=5405 http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3652

Both of those suggest a DB trim workaround.

I read these, thank you. Unfortunately it is not an option to affect the Dbs that have the primary tables. I can control the FK table, however that means maintaining 2 different fields, one with 1 space, and another field with 2 spaces.

Sadly I recognize the scarce nature of this problem and wouldn't expect a core level adjustment to handle it.

Is their any chance of avoiding using 2 identical fields (not counting spaces) in the FK table?

I can control the FK table, however that means maintaining 2 different fields, one with 1 space, and another field with 2 spaces.

Why would you maintain 2 different fields? I thought you would have just updated the table to change the existing FK field to match that of the PK.

Another option is to use a database View of the FK table, where you modify the FK to match that of the PK, then you can map this view to an entity.

Walaa wrote:

Why would you maintain 2 different fields? I thought you would have just updated the table to change the existing FK field to match that of the PK.

Table 1 has the char PK with 1 space. Table 2 has the char PK with 2 spaces. So I could only ever match 1 of the 2 tables.

Walaa wrote:

Another option is to use a database View of the FK table, where you modify the FK to match that of the PK, then you can map this view to an entity.

I thought of that too however I don't want the result as a view object, I want it as an Entity object of the FK table.

If only there was a way to affect the hash match, or trim the PK field(s) after the prefetch...anyway, I know that is not realistic.

I finally decided to implement 2 FK fields and maintain them both with their respective padding. It works great, problem solved, however clumsy it may be.