CODEWITHSUNDEEP
sqllinq
user3510519
user3510519

Reputation: 3

Convert simple SQL query to LINQ

This sql query is quite simple. It retrieves the id of all concerts where all of the booked artist are sick (excluding some of the concerts of a specific type).

I have tried to convert this to a LINQ expression but I am totally stuck. Would appreciate if someone could help me out.

(The table ArtistConcert simply connects Concert with Artist)

select ac.concertID
from 
    ArtistConcert ac
    join Concert c on c.ConcertID = ac.ConcertID
    join Artist a on a.artistID = ac.artistID
where 
    a.IsSick = 1 and c.TypeID not in (1,2,3)
    and
    c.StartTime > getdate() 
    and not
    exists
    (
        select _a.artistID from ArtistConcert _ac
        join Concert _c on _c.ConcertID = _ac.ConcertID
        join Artist _a on _a.artistID = _ac.artistID        
        where _c.concertID = c.concertID and _a.IsSick = 0 
    )

Upvotes: 0

Views: 78

Answers (1)

Bob Vale
Bob Vale

Reputation: 18474

assuming your datacontext is ctx

var typeIds = new[] {1,2,3};

var query = from ac in ctx.ArtistConcert
            join c in ctx.Concert on ac.ConcertID equals c.ConcertId
            join a in ctx.Artist on ac.artistID equals a.artistID
            where a.IsSick == 1 && !typeIds.Contains(c.TypeId)
            && c.StartTime > DateTime.Now
            && !ctx.ArtistConcert.Any(_ac => _ac.ConcertID == c.ConcertId 
                                             && ctx.Artist.Any(_a => _a.artistID == _ac.artistId 
                                                                     && _a.isSick == 0))
            select ac.ConcertId;

Upvotes: 1

Related Questions