Entity framework and Stored procedure

Question

I have a stored procedure :

CREATE PROCEDURE SELECT_Some_Data
    @Sreachstr nvarchar(200)
AS
 BEGIN
    SELECT ROW_NUMBER() OVER(ORDER BY [Document].DocNo DESC) AS Row,*
    FROM Document WHERE DocNo=@Sreachstr 
 END

when I execute it with @Sreachstr='153', it returns 15 records.

I use Entity Framework to get the data returned by the stored procedure:

public static List<DocumentInfo_Full_Data> SelectByDocNo(string SearchStr)
{
    using (LibEntities_new db = new LibEntities_new())
    {
        return SelectByDocNo(db, SearchStr);
    }
}

private static List<DocumentInfo_Full_Data> SelectByDocNo(LibEntities_new db, String SearchStr)
{
    return db.SelectByDocNo(SearchStr).ToList();
}

public ObjectResult<DocumentInfo_Full_Data> SelectByDocNo(global::System.String searchStr)
{
    ObjectParameter searchStrParameter;
    if (searchStr != null)
    {
        searchStrParameter = new ObjectParameter("SearchStr", searchStr);
    }
    else
    {
        searchStrParameter = new ObjectParameter("SearchStr", typeof(global::System.String));
    }

    return base.ExecuteFunction<DocumentInfo_Full_Data>("SelectByDocNo", searchStrParameter);
}

When I call this method with parameter SearchStr="15" , I see one record that 15 times is repeated instead of 15 different records.

What is the primary key defined on your Document table in the database? Which field is defined as the key in your EF entity? — marc_s
– marc_s, Commented Jan 4, 2014 at 8:49
And is this also your key in the Entity Framework entity class? — marc_s
– marc_s, Commented Jan 4, 2014 at 8:53
The fact you are getting 15 results exactly suggests the code displaying the results might have a bug. Does the SQL work run just against the DB directly? Does SqlProfiler show the expected SQL being run? This would eliminate the SQL itself as the source of the problem, and eliminate the wrong SQL being executed by EF as the problem. — James World
– James World, Commented Jan 4, 2014 at 8:53

marc_s · Accepted Answer · 2014-01-04 10:06:17Z

1

I had this happen to me once when I was selecting rows from a view in EF.

Since the view itself doesn't have a primary key, EF wasn't able to determine the key - instead, EF created a "guessed" key based on all non-nullable columns from the view.

My view returned four rows of data, e.g.

Col1   Col2    Col3    Col4 
  1      2     'ABC'   42
  1      2     'DEF'   57 
  1      2     'GHI'   4711 
  1      2     'JKL'   404

--> my query worked just fine in SQL Server Management Studio.

The "key" that EF had guessed was based on (Col1, Col2).

Now when I retrieved the rows using EF, this happen:

the first row got selected - EF saw it didn't have any data yet, so it stored that row in its result set
the next row was selected - EF determined that the key was the same ( (1,2) again) so it assumed this was the same row again; same key -> same row, so that same entity got stored a second, third and fourth time

So in the end, what I got back from EF was

Col1   Col2    Col3    Col4 
  1      2     'ABC'   42
  1      2     'ABC'   42
  1      2     'ABC'   42
  1      2     'ABC'   42

because the key that determines uniqueness of an entity in EF was the same for each of the four columns from the database.

So this might be happening in your case, too - especially if your created a new complex type for your data returned from the stored procedure - and if your key on the EF entity (DocumentInfo_Full_Data) is not properly set to an actual, really identifying column (or set of columns) from the database. Check it out!

edited Jan 4, 2014 at 10:06

answered Jan 4, 2014 at 9:59

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

AComputer Over a year ago

thanks for your post. I set Entity Key and so Now every things are OK

Collectives™ on Stack Overflow

Entity framework and Stored procedure

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related