MVC-EF Reverse Engineer Code first include another

Question

I was having trouble with the syntax for the .Include() and this related question got me to the point where it both compiled and ran:

MVC-EF Reverse Engineer Code first include another table

I use:

 [ForeignKey("Id")]    
 public OtherTableModel OtherTableModel {get; set;}

Both tables have the exact same primary key:

 string id {get; set} 

In my controller the code is:

ourList = db.Table1.Include(t => t.OtherTableModel)

Unfortunately, though this works in the sense of compiling and even running, sometimes the results from the "Include" statement are null when there should be valid data. The "Id" used as a "foreign key" is such that in the actual SQL server it is not specified as a foreign key, though the same string will be the primary key to that table. This SQL issue may be part of the problem.

When I use a stored procedure invoked by MVC to retrieve the other table data, it works, as does a SQL query directly in SSMS (i.e. there are valid data in all rows, and not nulls) but the include returns some valid data rows, but not all of the data rows (which I really don't understand at all, I'd think it would always work or always fail, however since originally posting I've found more insight, please see below). There are possible data rows in the table where a null would be valid, however, there are many times when the data returned is null when it shouldn't be. Since originally posting, I have found this out:

In this case, the string for the key id [both tables] is limited in length to up to 8 characters (i.e. a maximum). When it is exactly 8 characters, the .include loading from the other table is correct, when it is not exactly 8 characters (i.e. less than 8), a null is returned. Perhaps please someone could elaborate on why this is and suggest a fix. Assume that the table cannot be changed and we have to live with id's of varying lengths, please.

Does Anyone have any insight?

Thanks in advance!

Answer 1

If your database column(s) are char or nchar, that may be the issue. In SQL Server, both char and nchar will right-pad the data with whitespace. So if your value is "1234567", the actual value stored in the database is "1234567 " (note the trailing space). This is assuming a char(8) or nchar(8).

For column data types varchar and nvarchar, no padding will occur.