1

I am having a very strange issue on my application when deploying on a Windows 2008 R2 Server.

Given this very simple LINQ snippet:

return From
    invoice In Me.Invoices
Where
    loggedCustomerID.Contains(invoice.Contract.CustomerID)
Order By
    invoice.Date Descending

The application works, but in some cases (on the customer server), the generated T-SQL is very strange:

 SELECT 
    [Extent1].[ContractID] AS [ContractID], ...
 FROM
    [dbo].[Invoice] AS [Extent1]
    INNER JOIN [dbo].[Contract] AS [Extent2] ON 
             [Extent1].[ContractID] = [Extent2].[ContractID]
    LEFT OUTER JOIN [dbo].[Contract] AS [Extent3] ON 
             [Extent1].[ContractID] = [Extent3].[ContractID]

 WHERE 
      [Extent2].[CustomerID] = 482283 
   OR [Extent3].[CustomerID] IN (498565,482282,498564,498566)
  • Table is linked multiple times (?)
  • The IN statement is broken into multiple T-SQL conditions instead of a single IN statement

Obviously the same package runs fine on my system generating the correct query without linking the same table multiple times and creating only a single IN statement.

I am using EF 5.0 but with compatibility against the Framework 4.0 (so the assembly shows version 4.4). Is this the root cause of this issue?

What could be wrong here?

It looks like the behavior is related to this? Too Many Left Outer Joins in Entity Framework 4?

Community
  • 1
  • 1
adrianot75
  • 21
  • 7

2 Answers2

1

I've met analogous behavior in NHibernate, maybe you find any similarity.

We got simple parent -> children relation. The point was that the relation was bidirectional so parent referenced all children and each of them has reference back to parent.

NHibernate supports lazy fetching which I switch off. Thus it produced interesting query once I decided to fetch the whole aggregate, I mean parent with all children.

Something like:

select ... from parent ... left join ... children ... left join parent

As Nhibernate uses first level cache under the hood, it was not able to discover automatically that those parent instances are equal as the cache of parent was empty in the time of query execution.

Later during parsing of the resulting database dataset, NHibernate found out that parent entities have same primary identifier and returned only one real instance.

Martin Podval
  • 1,097
  • 1
  • 7
  • 16
  • Hi Martin, and thank you for your answer! I'll try to remove the lazy loading option and see what happens, however what is strange here is that on my machine (.NET Framework 4.5 vs 4.0 ?) all runs fine, it's on a 2003 R2 Box as well as on a 2008 R2 box that I've this strange behavior... I'm going to try by upgrading to .NET 4.5 on the 2008 Box since on the 2003 is not supported and come back with some feedbacks! – adrianot75 Aug 19 '13 at 07:39
0

Finally I was able to perform some testing. It appears that EF T-SQL generation depends a lot on the undelying framework. In fact, the above issue is fixed just by upgrading the framework to 4.5.

adrianot75
  • 21
  • 7