none
Cannot retrieve all results from string navigation properties when case differ

    Question

  • Edit: reproducible behavior using the Northwind database: see posts below.

    We have a database (let's call it 'test') with case insensitive collation (COLLATE Latin1_General_CI_AS):

    CREATE TABLE t_one (
    	[name] [nchar](10) NOT NULL,
     CONSTRAINT [PK_t_one] PRIMARY KEY
     (
    	[name] ASC
    )
    )
    GO
    
    CREATE TABLE t_many (
    	[t_one_name] [nchar](10) NOT NULL,
    	[name] [nchar](10) NOT NULL,
     CONSTRAINT [PK_t_many] PRIMARY KEY CLUSTERED
    (
    	[t_one_name] ASC,
    	[name] ASC
    )
    )
    GO
    
    /****** Object:  ForeignKey [FK_test_procedure]    Script Date: 04/10/2010 14:46:54 ******/
    ALTER TABLE t_many WITH CHECK
    ADD CONSTRAINT [FK_t_many_t_one] FOREIGN KEY ([t_one_name])
    REFERENCES t_one ([name])
    GO
    
    ALTER TABLE t_many CHECK CONSTRAINT FK_t_many_t_one
    GO
    
    INSERT t_one ([name]) VALUES (N'A         ')
    INSERT t_one ([name]) VALUES (N'B         ')
    
    INSERT t_many ([t_one_name], [name]) VALUES (N'a         ', N'a1        ')
    INSERT t_many ([t_one_name], [name]) VALUES (N'A         ', N'a2        ')
    INSERT t_many ([t_one_name], [name]) VALUES (N'B         ', N'b1        ')
    INSERT t_many ([t_one_name], [name]) VALUES (N'B         ', N'b2        ')
    

    After creating the entity objects in Visual Studio, running the following LINQ to Entities snippet ...

    using (var db1 = new testEntities())
    {
        var t = (from x in db1.t_many
                 where x.t_one_name == "A" && x.name == "a1"
                 select x).First();
    
        Console.WriteLine(t.t_one.name);
    }
    

    ... throws the following exception:

    System.Transactions Critical: 0 : <TraceRecord xmlns="http://schemas.microsoft.com/2004/10/E2ETraceEvent/TraceRecord" Severity="Critical"><TraceIdentifier>http://msdn.microsoft.com/TraceCodes/System/ActivityTracing/2004/07/Reliability/Exception/Unhandled</TraceIdentifier><Description>Exception non gérée</Description><AppDomain>ConsoleApplication2.vshost.exe</AppDomain><Exception><ExceptionType>System.NullReferenceException, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</ExceptionType><Message>La référence d'objet n'est pas définie à une instance d'un objet.</Message><StackTrace>   à ConsoleApplication2.Program.Main(String[] args) dans D:\Projets\vs2010test\ConsoleApplication2\Program.cs:ligne 18
       à System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
       à System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       à Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       à System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       à System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       à System.Threading.ThreadHelper.ThreadStart()</StackTrace><ExceptionString>System.NullReferenceException: La référence d'objet n'est pas définie à une instance d'un objet.
       à ConsoleApplication2.Program.Main(String[] args) dans D:\Projets\vs2010test\ConsoleApplication2\Program.cs:ligne 18
       à System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
       à System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       à Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       à System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       à System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       à System.Threading.ThreadHelper.ThreadStart()</ExceptionString></Exception></TraceRecord>
    An unhandled exception of type 'System.NullReferenceException' occurred in ConsoleApplication2.exe
    

    As a comparison, the same query using LINQ to SQL runs flawlessly:

    using (var db2 = new DataClasses1DataContext())
    {
        var t = (from x in db2.t_manies
                 where x.t_one_name == "A" && x.name == "a1"
                 select x).First();
    
        Console.WriteLine(t.t_one.name);
    }
    

    Is it a known issue ? Is there a workaround on the Entity Framework ?

    Saturday, April 10, 2010 7:24 PM

All replies

  • I've heard of this, but never personally experienced it.  It makes sense though. Hmmm.

    How about approaching it from the SQL end (if that is an option).  Create a computed column that is the column in question in upper case.  Then always upper case your query string for comparison and you should be good.  I know it's a total hack, but I don't know of another way.

     

     

     

    Sunday, April 11, 2010 3:32 AM
  • In our real-world application, I'm afraid that the approach from the SQL end is not an option (legacy database, which is why EF was created for), but as an exercize, I've tried it.

    So I've modified the t_many table as follows (computed u_one_name column added):

    CREATE TABLE t_many(
    	[t_one_name] [nchar](10) NOT NULL,
    	[name] [nchar](10) NOT NULL,
    	[u_one_name]  AS (upper([t_one_name])),
     CONSTRAINT [PK_t_many] PRIMARY KEY CLUSTERED 
    (
    	[t_one_name] ASC,
    	[name] ASC
    )
    )

    then changed the LINQ to Entities statement:

    var t = (from x in db1.t_many
             where x.u_one_name == "A" && x.name == "a1"
             select x).First();
    
    

    However, the following line ...

    Console.WriteLine(t.t_one.name);
    
    

    ... still throws the NullReferenceException because the 't_one' navigation property is set to null by the RelationshipManager, certainly due tu a bad comparison inside the EF internal code (I've tried to use Reflector but got lost quickly :).

    Sunday, April 11, 2010 12:04 PM
  • Did you try Including or Loading "t_one" explicitly?

     

    Sunday, April 11, 2010 1:04 PM
  • Yes, with the same result.

    I've streamlined the tesing environment to use the Northwind database, in which I simply change the CustomerID of Order #10248 to "VINEt" (instead of "VINET").

    Doing so does not break database relational integrity (because of collation rules). Here is now a bit of code:

    using (var db3 = new NorthwindEntities()) // LINQ to Entities
    {
        var cus = from x in db3.Customers.Include("Orders")
                  where x.CustomerID == "VINEt" // or whatever case style you want
                  select x;
    
        if (!cus.First().Orders.IsLoaded)
            cus.First().Orders.Load();
    
        var orders = cus.First().Orders;
    
        Console.WriteLine(orders.Count()); // returns 4 (wrong)
    }
    
    using (var db4 = new DataClasses2DataContext()) // LINQ to SQL
    {
        var cus = from x in db4.Customers
                  where x.CustomerID == "VINEt" // or whatever case style you want
                  select x;
    
        var orders = cus.First().Orders;
    
        Console.WriteLine(orders.Count()); // returns 5 (good)
    }
    

    'NorthwindEntities' uses LINQ to Entities. Its LINQ statement explicitely includes 'Orders' and Orders is also explicitely loaded, but orders.Count() returns 4 (should be 5).

    'DataClasses2DataContext' uses LINQ to SQL, and in this case, orders.Count() returns 5.

    SQL server databases are created case insensitive unless their collation is explicitely changed. Does this mean that the current EF behavior with string navigation properties should not be trusted blindly ?

    There is also an adverse impact of this behavior on scaffolding in "ASP.NET Dynamic Data Entities Web Application" projects...

     

    Sunday, April 11, 2010 7:04 PM
  • An update: after manually removing the FK_Orders_Customers stuff in the .edmx file (step not needed in .Net 4), and querying the related entities with a join...

    using (var db3 = new NorthwindEntities())
    {
        var orders = from x in db3.Customers
                     where x.CustomerID == "VINEt" // or whatever case style you want
                     join y in db3.Orders
                     on x.CustomerID equals y.CustomerID
                     select y;
    
        Console.WriteLine(orders.Count());
    }
    
    ...then orders.Count() returns 5.

    The more I work on this issue, the more I feel navigation properties definitely need to be fixed...

    Sunday, April 11, 2010 8:05 PM