CODEWITHSUNDEEP
c#asp.netlinqlinq-to-sql
Tom Gullen
Tom Gullen

Reputation: 61727

Help with simple Linq count statement

var q = dc.tblHelpCentreQuestions.Where(c => c.userID == UserID);
q.OrderByDescending(c => c.dateSubmitted);

This works fine, but I also need to return the count of records returned from tblHelpCentreReplies where QuestionID equals tblHelpCentreQuestions.ID. This is easy enough for me in SQL, can someone show me how this is done in LINQ to SQL?

Edit

I've got as far as this:

var q = 
from question in dc.tblHelpCentreQuestions
join replies in dc.tblHelpCentreReplies on question.ID
    equals replies.ticketID
where question.userID == UserID
orderby question.dateSubmitted descending
select new { question, replies.Count() };

But replies.Count() throws:

Invalid anonymous type member declarator. Anonymous type members must be declared with a member assignment, simple name or member access.

Upvotes: 1

Views: 2830

Answers (3)

dh.
dh.

Reputation: 1511

the linq query would look like this:

var q =
    dc.tblHelpCentreQuestions.
        Where(question => question.userID == UserID).
        OrderByDescending(question => question.dateSubmitted).
        GroupJoin(
            dc.tblHelpCentreReplies,
            question => question.ID,
            replies => replies.ticketID,
            (question, replies) => new {Question = question, RepliesCount = replies.Count()});

update if you have a relation mapping than that could be a bit easier

var q =
    dc.tblHelpCentreQuestions.
        Where(question => question.userID == UserID).
        OrderByDescending(question => question.dateSubmitted).
        Select(question => new {Question = question, RepliesCount = question.Replies.Count()});

Upvotes: 3

vvk
vvk

Reputation: 823

var q = 
from question in dc.tblHelpCentreQuestions
join replies in dc.tblHelpCentreReplies on question.ID equals replies.ticketID
where question.userID == UserID
orderby question.dateSubmitted descending
select new { Question = question, RepliesCount = replies.Count()};

Upvotes: 1

Steven
Steven

Reputation: 172646

This is easier than you might imagine :-)

var q = 
    from question in dc.tblHelpCentreQuestions
    where question.userID == UserID
    orderby question.dateSubmitted desc
    select new { question, question.Replies.Count() };

Upvotes: 1

Related Questions