SQL Server stored procedure returning correct number of rows, but all rows are the same

Question

Not sure where the problem is here... I have a stored procedure written that (when executed within SQL Server Mgmt. Studio) returns results I expect to see:

USE [DataViewer]
GO
/****** Object:  StoredProcedure [dbo].[DV_GetPSCTreeNodes]    Script Date: 08/10/2014 1:48:03 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:      <Me!>
-- Description: <Used by data viewer GUI.>
-- =============================================
ALTER PROCEDURE [dbo].[DV_GetPSCTreeNodes]
    -- Add the parameters for the stored procedure here

    @iNodeLevel int,
    @iParentNodeId bigint

AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sQuery varchar (2000)

    IF @iNodeLevel >= 5

    RETURN

    ELSE

    -- Insert statements for procedure here
    SET @sQuery = 'SELECT * FROM DataView.dbo.v_Tree
    WHERE L' + CAST((@iNodeLevel + 1) AS VARCHAR(10)) + 'ID IS NULL
    AND L' + CAST((@iNodeLevel) AS VARCHAR(10)) + 'ID = ' + CAST((@iParentNodeId)
    AS VARCHAR(10))

    EXEC (@sQuery)
END

As mentioned, if I run this in SMSS, I get the expected results.

However, if I run this through the service I have set up, I get the correct amount of records back, but they are all duplicates of the first row (and I can look at the table and know I am getting duplicates). If I try new params and get 15 distinct rows back in SSMS, my webpage will show 15 rows of repeated row 1 data.

Here is the call from my Client:

List<v_PowerSystemCIMTree> list = null;
DVServiceClient proxy = new DVServiceClient();

try
{
    list = proxy.DV_GetPSCTreeNodes(2,325550).ToList(); //2 and 325550 are just hardcoded to 
                                                        //check to see if it's working
}
catch (Exception ex)
{
    Console.WriteLine(ex.GetType().FullName);
    Console.WriteLine(ex.Message);
}
finally
{
    proxy.Close();
}
return View(list);

Which calls my Service:

readonly DataViewerEntities _Context = new DataViewerEntities();

public List<DataViewer_DAL.v_PowerSystemCIMTree> DV_GetPSCTreeNodes(int firstParam,
long secondParam)
{
    return _Context.DV_GetPSCTreeNodes(firstParam,secondParam).ToList();
}

Why the heck would I get one result set in the Management Studio, but repeated data on my webpage?

Answer 1

Known issue: since you're selecting from a view which doesn't have a defined primary key, Entity Framework will use all non-nullable columns as the "entity key".

If EF reads a second row from the view that has the same entity key (same values in all non-nullable columns), it will just repeat the first row already read - it will not look at the other, remaining columns....

So you just need to either add the primary key for the underlying table(s) to the view's columns, tweak the EF data model to explicitly define an "entity key" for the view, or you need to add an artificial, unique column (like a ROW_NUMBER()) to your view and make sure it's non-nullable and thus part of the EF "derived" entity key