Edit Question - KnowledgeHub

Title Body

I've stumbled upon a very strange LINQ to SQL behaviour / bug, that I just can't understand. Let's take the following tables as an example: Customers -> Orders -> Details. Each table is a subtable of the previous table, with a regular Primary-Foreign key relationship (1 to many). If I execute the follow query: var q = from c in context.Customers select (c.Orders.FirstOrDefault() ?? new Order()).Details.Count(); Then I get an exception: Could not format node 'Value' for execution as SQL . But the following queries do not throw an exception: var q = from c in context.Customers select (c.Orders.FirstOrDefault() ?? new Order()).OrderDateTime; var q = from c in context.Customers select (new Order()).Details.Count(); If I change my primary query as follows, I don't get an exception: var q = from r in context.Customers.ToList() select (c.Orders.FirstOrDefault() ?? new Order()).Details.Count(); Now I could understand that the last query works, because of the following logic: Since there is no mapping of "new Order()" to SQL (I'm guessing here), I need to work on a local list instead. But what I can't understand is why do the other two queries work?!? I could potentially accept working with the "local" version of context.Customers.ToList() , but how to speed up the query? For instance in the last query example, I'm pretty sure that each select will cause a new SQL query to be executed to retrieve the Orders. Now I could avoid lazy loading by using DataLoadOptions , but then I would be retrieving thousands of Order rows for no reason what so ever (I only need the first row)... If I could execute the entire query in one SQL statement as I would like (my first query example), then the SQL engine itself would be smart enough

Tags (comma-separated) Cancel