CODEWITHSUNDEEP
sqllinqentity-framework
Saman Gholami
Saman Gholami

Reputation: 3512

Converting a group by sql query to a linq query

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(???);

Upvotes: 0

Views: 132

Answers (1)

Moho
Moho

Reputation: 16553

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()
        };

Upvotes: 1

Related Questions