CODEWITHSUNDEEP
sql-serverlinq-to-sql
user1443098
user1443098

Reputation: 7635

LINQ to SQL anomaly

I'm trying to learn LINQ to SQL. I've run into something I just don't get. Here's the LINQ program (vb.net):

Imports System.IOModule Module1    
Sub Main()
        Dim crs = New DataClasses1DataContext()
        Dim sw As New StringWriter()
        crs.Log = sw
        Dim reports = From report In crs.CRS_Report_Masters
                      Group report By report_id = report.Report_ID Into grouped = Group
                      Select New With {
                          .reportId = report_id,                          
                         .two = grouped.Sum(
                              Function(row) row.active_report * row.Report_ID)
                          }
        For Each report In reports
            Console.WriteLine("{0} {1}", report.reportId, report.two)
        Next        
MsgBox(sw.GetStringBuilder().ToString())    
End Sub
End Module

Here's the SQL it produces:

SELECT SUM([t1].[value]) AS [two], [t1].[Report_ID] AS [reportId]  FROM (
      SELECT (-(CONVERT(Float,[t0].[active_report]))) *
            (CONVERT(Float,CONVERT(Float,[t0].[Report_ID]))) AS [value], [t0].[Report_ID]
      FROM [dbo].[CRS_Report_Master] AS [t0]
      ) AS [t1]  GROUP BY [t1].[Report_ID]
  -- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1

What I don't get is why there is a minus sign in the SQL before the math in the parentheses. I didn't specify that in the LINQ query.

Upvotes: 2

Views: 222

Answers (2)

user1443098
user1443098

Reputation: 7635

So, now I see what's going on. The LINQ to SQL definition of the column corresponding to a SQL bit is a .net Boolean. Fair enough. When doing math using it, however, the compiler negates the value as described by ekolis. I did not expect that -- in fact I think it's an error, since I do not get the results I expect. e.g. if active_report=1 and report_id=123, I expect to get "123" but I'm getting "-123". I modified the query like this:

Dim reports = (From report In crs.CRS_Report_Masters
                  Group report By report_id = report.Report_ID Into grouped = Group
                  Select New With {
                      .reportId = report_id,
                      .two = grouped.Sum(
                          Function(row) If(row.active_report, 1, 0) * CInt(row.Report_ID))
                      })

which changed the generated SQL to:

SELECT SUM([t1].[value]) AS [two], [t1].[Report_ID] AS [reportId]  FROM (
      SELECT (
          (CASE
              WHEN (COALESCE([t0].[active_report],@p0)) = 1 THEN @p1
              ELSE @p2
           END)) * (CONVERT(Int,[t0].[Report_ID])) AS [value], [t0].[Report_ID]
      FROM [dbo].[CRS_Report_Master] AS [t0]
      ) AS [t1]  GROUP BY [t1].[Report_ID]
  -- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [0]
  -- @p1: Input Int (Size = -1; Prec = 0; Scale = 0) [1]
  -- @p2: Input Int (Size = -1; Prec = 0; Scale = 0) [0]
  -- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1

Makes sense I suppose, though it's a roundabout way get the desired result. In T-SQL, a simple SELECT [BOOLEAN]*[INTEGER] will achieve the same result, I believe. Lesson learned: Don't trust LINQ to always do the right thing. Check its work!

Upvotes: 1

ekolis
ekolis

Reputation: 6776

If I recall correctly, VB.NET treats -1 as Boolean True, while SQL treats +1 as Boolean True. Therefore the minus sign is necessary for VB.NET to properly interpret the active_report field.

Upvotes: 2

Related Questions