我有一个简单的视图定义了MSSQL2008。视图定义如下:
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
它以以下形式返回数据:
1 Carrier1 $70.00
1 Carrier2 $100.00
1 Carrier3 $150.00
3 Carrier2 $60.00
....etc
这对SQL Select语句很好。
我有一个vb linq查询,它只是基于companyID设置一个where子句。
Dim expenses = From exp In Me.vw_CarrierExpenses _
Where exp.CompanyID = companyId _
Select exp
Return expenses.ToList()
如果我根据1的companyID进行筛选,使用上面的示例数据,我会得到第一行3次:
1 Carrier1 $70.00
1 Carrier1 $70.00
1 Carrier1 $70.00
我一定错过了一些很简单的东西。它总是返回正确数量的行,但数据总是相同的。谢谢。