SQL Server View Returns Different Results in Entity Framework

Question

I have a sql view on an 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 

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 as follows:

 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 idProduct for job 5000 is now 9 instead of 9 and 18.

Question:

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

Thanks at adavance

Answer 1

Make sure your View in the .edmx file has a primary key assigned. If this is not the case, add the correct entity key yourself using the edmx designer. In this case, you can include the idJob and idProduct in your entity key.

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