CODEWITHSUNDEEP
c#linqentity-frameworksql-server-2008-r2linq-to-entities
Marc L. Allen
Marc L. Allen

Reputation: 410

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

Interfacing to SQL Server 2008R2:

I have a linq expression:

IQueryable<xxx> xxxResult =
     (from t in _context.xxxx.AsNoTracking().Include("yyy")
     where t.zzz >= lowNumber
           && t.zzz <= highNumber
           && t.qqq == someValue
     select t);

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

Linq generated SQL that the SQL Server generated a terrible plan, and, since I can't add index/join hints, I created a stored procedure that wrapped the SQL that the above Linq expression generated.

I know I should be able to access the stored procedure through Entity Framework, but I'm using a previous project that used a very light code-first implementation (no .edmx file, for instance) and I'm kinda new to the whole EF thing and didn't know how to tie the new procedure into EF. I know it can be done, but I am trying to call the procedure directly.

I worked this out:

IQueryable<xxx> xxxResult = 
    _context.xxxx.SqlQuery("GetData @p0, @p1, @p2",  someValue, lowNumber, highNumber)
                 .AsNoTracking().AsQueryable();

This seems to work, except for one problem. When iterating over the linq queryable, everything works swimmingly. But, when I use the stored procedure, I get duplicate records.

For instance, if I have an xxx record that includes 3 yyy records in a collection, I get a single xxx record from the linq expression and it, as expected, includes 3 yyy records in the internal collection.

The stored procedure, for the same dataset, iterating over the queryable returns three xxx records, EACH of which contain the same 3 yyy records.

Again, the stored procedure executes the exact same SQL that the linq expression generated.

Why is that? Any ideas?

(Again, new to EF, so please forgive errors in terminology.)

Upvotes: 1

Views: 1940

Answers (1)

Mark Peters
Mark Peters

Reputation: 17755

I believe that EF is seeing your results as duplicate based on the primary key you have defined. In EF5, this would be defined using the "Entity Key" property on the fields which uniquely define the entity (a multi-part primary key would have this set on multiple fields).

If your procedure returns a record that matches one that it already returned (based soley on the primary key fields) then it will return a reference to the previous record.

Your LINQ expression uses .AsNoTracking which should prevent this caching behavior.

I'm guessing that the .AsNoTracking() using the stored proc occurs after it has been cached and doesn't have the effect you are looking for.

Make sure that you have your primary keys set properly on your model.

Here's an article that describes the behavior with a view: http://jepsonsblog.blogspot.in/2011/11/enitity-framework-duplicate-rows-in.html which should be similar to what you are seeing with the stored procedure.

It looks like in Code First, you would use the [Key] annotation to specify your unique keys: http://msdn.microsoft.com/en-us/data/jj591583.aspx

Upvotes: 3

Related Questions