CODEWITHSUNDEEP
c#sql-serverentity-frameworkstored-procedures
AComputer
AComputer

Reputation: 529

Entity framework and Stored procedure

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.

Upvotes: 1

Views: 270

Answers (1)

marc_s
marc_s

Reputation: 755321

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!

Upvotes: 1

Related Questions