I currently have a CTE view, which uses recursion to calculate the quantity of items from an order (or invoice). The view works, but because there are quite a bit of line item rows to deal with, it takes about 16 seconds to run. I ran the tuning wizard, but its recommendations only shaved three seconds off of the time.
So in an effort to improve performance, I created a stored proc which executes and returns the same CTE, but in the anchor expression I can add a where clause to filter for only the specific order or invoice I'm interested in. This works great, it returns immediately with the correct results.
The problem is Linq to Sql; for some reason, when it thinks its look at a view, it doesn't seem to matter that there's no primary key column. But when I try to use the stored proc and the Results class which is returned (via ISingleResult<apResults>), I get a dictionary exception, that the key already exists. I've hit this before, and it was because I forgot to define a primary key when inserting a new row into a table, but I don't see why Linq wants a primary key here.
My information is EF-related, so it may not directly apply, but it still might (hopefully) help.
In EF, when building an entity from a view, EF sets every non-nullable column as part of the view's primary key. I'm guessing that is has no other choice; views can't return key-related metadata, and Microsoft's products in this arena require a primary key of some sort. A similar situation would likely exist when calling a SP that returns that entity. I'm wondering if that's what is causing your problems.
The flip side is that, at least in EF, the primary-key representation it builds is changeable. So if you know what the "primary key" of your view is, you can change the entity to match that. I'm pretty sure you can do something similar in L2S.
I actually found the exception is in code where I am building a dictionary; however, I think the issue is the same, as the query involve a union, and since there's no key, the union doesn't exclude duplicates. Your explaination would explain why it works against the view though, so I'll set every column as a key and see where that takes me.
OK... the problem was incorrectly taking everything the query against the view was doing and adding it to the proc; there was an additional where filter needed, so its a user problem not a Linq problem!
Copyright (c) Marimer LLC