CODEWITHSUNDEEP
c#linq
Sukhendu Chakraborty
Sukhendu Chakraborty

Reputation: 17

How to convert the following SQL query to a Linq query in C#

select 
    t.Ticketid, t.subject, t.createdby,
    count(TicketConversationId) as counts 
from 
    dbo.ticket t 
join 
    dbo.TicketConversation tc on t.ticketid = tc.ticketid
group by 
    t.Ticketid, t.subject, t.createdby

Code:

var Result = from t1 in Tickets 
             join t2 in TicketConversations on t1.TicketId equals t2.TicketId 
             select new { t1.TicketId, t1.Subject , comments = t2.TicketConversationId };

Upvotes: 0

Views: 61

Answers (1)

Gilad Green
Gilad Green

Reputation: 37299

Ok so to add the counting and grouping for your query use GroupBy:

var result = from t1 in Tickets 
             join t2 in TicketConversations on t1.TicketId equals t2.TicketId 
             group t2.TicketConversationId by new { t.Ticketid, t.Subject, t.createdby } into g
             select new { 
                 t1.TicketId, 
                 t1.Subject , 
                 t1.CreatedBy, 
                 Count = g.Count() };

Notice that as you are using EF, if you properly define the Navigation Properties you will not need to explicitly write the join but just t1.Conversations.Count() or similar.

As all you need is to count the number of records in t2 that match you can use group join

var result = from t1 in Tickets
             join t2 in TicketConversations on t1.TicketId equals t2.TicketId into con
             select new {
                 t1.TicketId,
                 t1.Subject,
                 t1.CreatedBy,
                 Count = con.Count()
             };

Upvotes: 1

Related Questions