Join with Count?

[bgwalker]

Good evening...

I'm relatively new to LINQ and am trying to figure out how to do a percentage calculation using Count() on a join. My original SQL query looks like this:

SELECT
c.[ID], c.

Code: Select all

,
     ISNULL(p.[ActualTotalCount],0) AS ActualTotalCount,
     ISNULL(p.[CodeTotalCount],0) AS CodeTotalCount,
     CAST(ISNULL(p.[CompletionPercentage],0) AS Integer) AS CompletionPercentage
FROM deployment_codes AS c
     LEFT OUTER JOIN
          (SELECT
               [CodeID],
               ISNULL(COUNT([Actual]),0) As ActualTotalCount,
               ISNULL(COUNT([CodeID]),0) As CodeTotalCount,
               (CAST(ISNULL(COUNT([Actual]),0) AS Float) / CAST(ISNULL(COUNT([CodeID]),1) AS Float))*100 AS CompletionPercentage
          FROM
               deployment_plan
          WHERE [Group] = 'LA'
          GROUP BY 
               [CodeID]) AS p
          ON c.[ID] = p.[CodeID]
WHERE (c.[code] LIKE 'N%')
[/quote]

[bgwalker]

After playing around in LINQPad, I've come up with the following LINQ to SQL:

Code: Select all

From c In deployment_codes _
Group Join p In deployment_plans.Where(Function(g) g.Group = "LA") on c.ID Equals p.CodeID _
Into CodePlan = Group _
Where (c.Code.StartsWith("N")) _
Select c.ID, c.Code, TotalCount = CodePlan.Count

I can get the "CodeTotalCount" by doing a "CodePlan.Count", but now I need the "ActualTotalCount", which is a count of non null items in the "Actual" column in "deployment_plans". After I get the count, I need to do some math to figure out the percentage (("ActualTotalCount" / "TotalCount") * 100) with avoiding a "divide by zero" error.

Please help!

[AndreyR]

A simpler solution is to use the DataContext.ExecuteQuery method to send a SQL query directly to database.