none
EF char relation RRS feed

  • Question

  • Hi,

    For historical reasons we have a lot of databases using CHAR-keys. In EF and SQL the following inconsistency has been found: There is one table a1 with the key: col1 defined as (Char(18) not null). There is another table a2 with a column col5 as (Char(18), null). The columns has TrimTrailingBlanks set (which doesn't affect the key). In EF there is a relation defined between these tables using these columns. The following is true:
    select * from a1, a2 where a1.col1=a2.col5 (300 records)
    select * from a1, a2 where a1.col1=a2.col5 and datalength(rtrim(a2.col5))=18 (100 records)
    select * from a1, a2 where a1.col1=a2.col5 and datalength(rtrim(a2.col5))<18 (200 records)

    Based on EF relation: var result = (from ca in context.a2s.Include("a1")
    where ca.a2key == 1
    select ca).SingleOrDefault();

    Only those records of a2 containing a fulllength col5, ie. length=18 will get related to a1. The EF relation doesn't work for those having space at the end of the key of a1.
    If this is the correct behaviour of EF, is there an easy workaround to relate those with datalength<18, I mean as even the SQL-query seems to automatically relate these records?

    Regards Roger.

    Monday, August 22, 2011 9:55 AM

All replies

  • Will trimming the values before comparing won't help here..?


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Monday, August 22, 2011 10:06 AM
  • I don't think I can trim anywhere in the associations made in the ConceptualEntityModel (Model1.edmx) ?

    Monday, August 22, 2011 11:56 AM
  • This does leave a1 as null:
    var result = (from ca in context.a2s.Include("a1")
                        where ca.a2key == 1
                          select ca).SingleOrDefault();
     
    This fetches a1:
    var result2 = (from ca in context.a2s
                         where ca.a2key == 1
                          select new
                         {
                           ca,
                           ca.a1
                         }).SingleOrDefault();

    Isn't there an incosistency one working and the other not?

    Regards Roger.

    Wednesday, August 24, 2011 1:57 PM
  • Hi RogerRab,

    Welcome!

    I am trying to involve someone familiar with this topic to further look at this issue. There might be some time delay. Appreciate your patience.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, August 26, 2011 2:07 PM
    Moderator
  • Hi Roger,

    Alan forwarded this case to me.   After some research, I think we cannot repro the issue or maybe we do not understand your scenario very clearly.   So if it is convenient, could you please share us a demo for further investigation?   You can ping me directly, misun@microsoft.com or upload the demo to http://skydrive.live.com

    Have a nice weekend!

    Thanks


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Friday, August 26, 2011 2:31 PM
    Moderator
  • Hi Michael,

    I'll consider a demo. However I'll now try to explain once more:
    There is an association between two tables. The association is made by using a primary key of type Char(18). EF is comparing both sides of the association. As TrimTrailingBlanks is set on the tables, the "foreign key" is trimmed, but the primary key isn't (because it's a primary key). So EF doesn't relate  "1234567890        " and "1234567890".

    The inconsistency is that an usual sql-clause will relate these columns. So would probably EF if I would remove the TrimTrailingBlank property from the "foreign key"-table. I'm not sure that EF should be changed in any way, however I think it should be easy to change the behaviour of EF to resemble Sql-Server, or am I wrong? 

    Regards Roger.



    Char(n) and binary(n) columns that allow nulls are padded to the length of the column when SET ANSI_PADDING is set to ON, but trailing blanks and zeros are trimmed when SET ANSI_PADDING is OFF. Char(n) and binary(n) columns that do not allow nulls are always padded to the length of the column.

    Monday, August 29, 2011 8:09 AM
  • Hi Roger,

    Thanks for the following up!   We can repro the issue based on your detailed description and find that the two queries in your third post return different results.  Unfortunately, I cannot find the root cause after some research.  I will consult the product team for some ideas and get back to you once I receive any feedbacks. 

    Good day!

    Thanks


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Tuesday, August 30, 2011 9:43 AM
    Moderator