CODEWITHSUNDEEP
sqllinq
Kevin
Kevin

Reputation: 4848

What am I doing wrong in this SQL to LINQ conversion?

I'm using LINQPad in my effort to convert SQL to LINQ and learn LINQ in the process. I'm running into some problems, though, and was hoping someone could look at my resulting LINQ code to see if I'm on the right track?

Here's my SQL code:

Select  Count(Convert(varchar(250), 
        Comment.CommentId)) as Num, 
        DiscussionBoard.ItemName, 
        Status.Status, 
        Status.Sequence

From    Comment inner join status on Comment.StatusID = Status.StatusID 
        inner join DiscussionBoard on Comment.DiscussionBoardID 
        = DiscussionBoard.DiscussionBoardID

WHERE   discussionboard.DiscussionBoardID 
        = '3ab7c139-317c-4450-9823-45a40ea6d0ff'

Group By status.Status, 
         Status.Sequence, 
         DiscussionBoard.ItemName
ORDER BY Status ASC

Here is the LINQ code that I've come up with so far. Bear with me, I'm just now learning LINQ and I haven't quite got my head around it. Any pointers would be greatly appreciated:

from  Comment in Comments
where DiscussionBoard.DiscussionBoardID == '3ab7c139-317c-4450-9823-45a40ea6d0ff'

join  Status in Statuses on Comment.StatusID equals Statuses.StatusID
join  DiscussionBoard in DiscussionBoards on Comment.DiscussionBoardID equals DiscucussionBoard.DiscussionBoardID

group CountGroup by new {
                         Status.Status,
                         Status.Sequence,
                         DiscussionBoard.DiscussionBoardID
                        }

select new
{
    Count = CountGroup.Count(),
    DiscussionBoard.ItemName,
    Status.Status,
    Status.Sequence
}

Upvotes: 2

Views: 61

Answers (1)

Amy B
Amy B

Reputation: 110111

group x by y

This fragment ends a query.

I think you meant:

group x by y into z

This fragment continues the query with z in scope. and removes all prior range variables from scope. z is an IGrouping<y, x>, which is to say, the key type is y's type and the group element type is x's type.

Here's my stab at your query:

from comment in Comments
where comment.DiscussionBoard.DiscussionBoardID == '3ab7c139-317c-4450-9823-45a40ea6d0ff'
let status = comment.Status
let board = comment.DiscussionBoard
group comment by new {
  status.Status,
  status.Sequence,
  board.ItemName
} into g
select new
{
    Count = g.Count(),
    ItemName = g.Key.ItemName,
    Status = g.Key.Status,
    Sequence = g.Key.Sequence
}

Another way to open this query would be:

from board in DiscussionBoards
where board.DiscussionBoardID == '3ab7c139-317c-4450-9823-45a40ea6d0ff'
from comment in board.Comments

Upvotes: 3

Related Questions