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
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.
Can anyone help me debug why this result changes from SQL server to Entity Framework?
Thanks in adavance
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