将简单的SQL查询转换为LINQ

Question

这个sql查询非常简单。它检索所有预订艺术家生病的所有音乐会的id（不包括某些特定类型的音乐会）。

我曾试图将其转换为LINQ表达式，但我完全被卡住了。如果有人能帮我，我将不胜感激。

（表ArtistConcert只是将Concert与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 
    )

Answer 1

假设您的数据上下文是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;