c# - Linq to entities with raw stored procedure returns duplicate copies of data -

interfacing sql server 2008r2:

i have linq expression:

iqueryable<xxx> xxxresult =      (from t in _context.xxxx.asnotracking().include("yyy")      t.zzz >= lownumber            && t.zzz <= highnumber            && t.qqq == somevalue      select t);             

(it doesn't matter on exact query, it's there in case does.)

linq generated sql sql server generated terrible plan, and, since can't add index/join hints, created stored procedure wrapped sql above linq expression generated.

i know should able access stored procedure through entity framework, i'm using previous project used light code-first implementation (no .edmx file, instance) , i'm kinda new whole ef thing , didn't know how tie new procedure ef. know can done, trying call procedure directly.

i worked out:

iqueryable<xxx> xxxresult =      _context.xxxx.sqlquery("getdata @p0, @p1, @p2",  somevalue, lownumber, highnumber)                  .asnotracking().asqueryable(); 

this seems work, except 1 problem. when iterating on linq queryable, works swimmingly. but, when use stored procedure, duplicate records.

for instance, if have xxx record includes 3 yyy records in collection, single xxx record linq expression , it, expected, includes 3 yyy records in internal collection.

the stored procedure, same dataset, iterating on queryable returns 3 xxx records, each of contain same 3 yyy records.

again, stored procedure executes exact same sql linq expression generated.

why that? ideas?

(again, new ef, please forgive errors in terminology.)

i believe ef seeing results duplicate based on primary key have defined. in ef5, defined using "entity key" property on fields uniquely define entity (a multi-part primary key have set on multiple fields).

if procedure returns record matches 1 returned (based soley on primary key fields) return reference previous record.

your linq expression uses .asnotracking should prevent caching behavior.

i'm guessing .asnotracking() using stored proc occurs after has been cached , doesn't have effect looking for.

make sure have primary keys set on model.

here's article describes behavior view: http://jepsonsblog.blogspot.in/2011/11/enitity-framework-duplicate-rows-in.html should similar seeing stored procedure.

it looks in code first, use [key] annotation specify unique keys: http://msdn.microsoft.com/en-us/data/jj591583.aspx