SQL server view return different results in Entity Framework

Question

I have a sql view in SQl server:

SELECT        dbo.job.idJob, SUM(dbo.tracking.iQty) AS TotalOrdered, dbo.tracking.idProduct
FROM            dbo.tracking INNER JOIN
                         dbo.job ON dbo.tracking.idJob = dbo.job.idJob
GROUP BY dbo.tracking.idAction, dbo.tracking.idProduct, dbo.job.idJob

In SQL Server it returns:

idJob       TotalOrdered idProduct
----------- ------------ -----------
5000        150          9
5000        75           18
5006        20           3

THE PROBLEM:

When I access this view through Entity Framework 6 in a WPF 4.5 project it returns different results. I add the view to the edmx file and then call the view in the following way:

Public Function GetTracking_Ordered(idJob As Integer) As Collection(Of vw_Tracking_Ordered) Implements ITrackingDataService.GetTracking_Ordered
            Try
                Using context = _ModelService.NewContext

                    Dim trackingList = (From recs In context.vw_Tracking_Ordered Where recs.idJob = idJob Select recs).ToList
                    Return New Collection(Of vw_Tracking_Ordered)(trackingList)

                End Using
            Catch ex As Exception
                Return Nothing
            End Try
        End Function

The following are the results:

idJob       TotalOrdered idProduct
----------- ------------ -----------
5000        150          9
5000        75           9
5006        20           3

NOTICE the idProduct for the 5000 job are now both 9 instead of 9 and 18.

QUESTION:

Can anyone help me debug why this result changes from SQL server to Entity Framework?

Thanks in adavance

Answer 1

Make sure that your View in the .edmx file has a Primary Key assigned. If it hasn't, add the proper entity key yourself using the edmx designer. I this case you may want to include columns idJob and idProduct in your entity key.

Read here also: http://msdn.microsoft.com/en-us/library/vstudio/dd163156(v=vs.100).aspx

Answer 2

I appreciate @Vland, his answer helped to understand the problem is about missing key! So I solved to problem from the root! I know this is not a best practice, but these steps solved mine!

1. I added a row to my view by ROW_NUMBER()

   select ROW_NUMBER() over(order by FieldA asc) as id, FieldA, FieldB from MyView

you can put the above code inside your view

I have a rowNumber column for indexing the view rows

2. At visual studio, I updated the model in my .edmx diagram and added my new view

3. Check if id column is not signed as Key, right click on id field an click "Entity Key" remember to uncheck other columns if they are key sign.

4. rebuild & lunch the project, you can see the result is correct as you can see in sql server