Converting a group by sql query to a linq query

Question

This is my tables definition :

Ticket 
 -------------------------------
|ID        int              PK  |
|Paid      varchar(50)          |
 -------------------------------

TicketRow
 ----------------------------------
|ID            int              PK |
|TicketID_FK   int                 |
|SHtimeID_FK   int                 |
 ----------------------------------

And this is my sql query :

select SHtimeID_FK,count(*) as cnt 
from dbo.TicketRow as tr inner join dbo.Ticket as t on tr.TicketID_FK=t.ID
where t.Paid='ok'
group by SHtimeID_FK
having count(*)>1

How should i convert this query to a Linq query?

UPDATE :

I try this query but i don't know how should i complete my query !

MelliConcertEntities db = new MelliConcertEntities();
var s = (from x in db.TicketRow
                         where x.Ticket.Paid == "ok"
                         select x).GroupBy(???);

Have you already tried anything? How is your context set? Give us something to let us help you! — MarcinJuraszek
– MarcinJuraszek, Commented Oct 16, 2013 at 23:53

Moho · Accepted Answer · 2013-10-17 00:09:46Z

1

db.TicketRow
    .Where( tr => tr.Ticket.Paid == "ok" )
    .GroupBy( tr => tr.SHtimeID_FK )
    .Where( g => g.Count() > 1 )
    .Select( g => 
        new { 
            SHtimeID_FK = g.Key, 
            cnt = g.Count() } );

or

var s = from tr in db.TicketRow
        where tr.Ticket.Paid == "ok"
        group tr by tr.SHtimeID_FK into g
        where g.Count() > 1
        select new {
            SHtimeID_FK = g.Key,
            cnt = g.Count()
        };

edited Oct 17, 2013 at 0:09

answered Oct 17, 2013 at 0:04

Moho

16.7k1 gold badge36 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Converting a group by sql query to a linq query

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related