views:

23

answers:

1

I have a simple view defined MSSQL 2008. The view is defined as follows:

    SELECT     dbo.tblCompany.CompanyID, dbo.tblAccount.Carrier, SUM(ISNULL(dbo.tblLine.LineCharge, 0)) + SUM(ISNULL(dbo.tblLine.FeatureCharge, 0)) 
                      + SUM(ISNULL(dbo.tblLine.AccessCharge, 0)) AS SumOfCharges
FROM         dbo.tblCompany LEFT OUTER JOIN
                      dbo.tblCompany_Location LEFT OUTER JOIN
                      dbo.tblAccount LEFT OUTER JOIN
                      dbo.tblLine LEFT OUTER JOIN
                      dbo.tblBill_Data ON dbo.tblLine.LineID = dbo.tblBill_Data.LineID ON dbo.tblAccount.AccountID = dbo.tblLine.AccountID ON 
                      dbo.tblCompany_Location.LocationID = dbo.tblAccount.LocationID ON dbo.tblCompany.CompanyID = dbo.tblCompany_Location.CompanyID
GROUP BY dbo.tblCompany.CompanyID, dbo.tblAccount.Carrier

Which returns data in the form of:

1    Carrier1          $70.00
1    Carrier2         $100.00
1    Carrier3         $150.00
3    Carrier2          $60.00
....etc

This works fine with an SQL select statement.

I have a VB linq query that just sets a where clause based on the CompanyID.

Dim expenses = From exp In Me.vw_CarrierExpenses _
                       Where exp.CompanyID = companyId _
                       Select exp

        Return expenses.ToList()

If I filter based on a CompanyID of 1, using the example data above, I get the first row 3 times:

1    Carrier1          $70.00
1    Carrier1          $70.00
1    Carrier1          $70.00

I must be missing something very simple here. It always returns the correct amount of rows but the data is always identical. Thanks.

A: 

I keep forgetting that LINQ needs to have a true unique primary key field in query model. I added the following 'fake' primary key to my view by adding:

 ROW_NUMBER() OVER (ORDER BY dbo.tblCompany.CompanyID) As 'CarrierExpenseID'

to my view fields and then adding this as my primary key in my view model.

Nate