.netvb.netentity-frameworkentity-framework-6sql-view

SQL server view return different results in Entity Framework


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


Solution

  • 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