I'm trying to replicate a SQL statement in EF Core but cant seem to find a way to do it, to set the scene I have the following table structure

Slot -> SlotInstance -> SlotInstanceUser (a Slot can have many SlotInstances, a SlotInstance can have many SlotInstanceUsers)

When a user registers for a SlotInstance a record is created in SlotInstanceUsers storing the SlotInstanceId and UserId - all good there.

I'm able to write SQL to get a list of slot instances which the user has not registered for e.g.

    SELECT
        S.StartDate, S.EndDate, S.StartTime, S.EndTime, S.DayOfWeek,
        SI.Date
    FROM
        Slot S WITH (NOLOCK)
    INNER JOIN
        SlotInstance SI WITH (NOLOCK) ON S.Id = SI.SlotId
    WHERE
        SI.ID not in (  
                        SELECT 
                            SlotInstanceId 
                        FROM 
                            SlotInstanceUser SIU WITH (NOLOCK) 
                        WHERE 
                            SIU.UserId = @UserID
                    )   
    ORDER BY
        SI.Date

But I just cant seem to replicate this in EF core - what am I missing?

CodePudding user response：

You can write the LINQ query pretty much the same way as the SQL query. Just remember that in LINQ select is last, variables (aliases) are mandatory, and the equivalent of SQL NOT IN is !Contains. e.g.

var query =
from s in db.Slots
join si in db.SlotInstances on s.Id equals si.SlotId
where !(from siu in db.SlotInstanceUsers
        where siu.UserId == userId)
        select siu.SlotInstanceId).Contains(si.Id)
orderby si.Date
select new
{
    s.StartDate, s.EndDate, s.StartTime, s.EndTime, s.DayOfWeek,
    si.Date       
};

But in EF Core you have more options, especially for joins, since normally the relationships (and associated joins) are encapsulated with navigation properties. So the model you are describing with words in EF Core/C# terms is something like

public class Slot
{
    public int Id { get; set; }
    // Other properties...
    public ICollection<SlotInstance> SlotInstances { get; set; }
}

public class SlotInstance
{
    public int Id { get; set; }
    // Other properties...
    public Slot Slot { get; set; }
    public ICollection<SlotInstanceUser> SlotInstanceUsers { get; set; }
}

public class SlotInstanceUser
{
    public int Id { get; set; }
    // Other properties...
    public SlotInstance SlotInstance { get; set; }
}

and the query would be like

var query =
from s in db.Slots
from si in s.SlotInstances
where !si.SlotInstanceUsers.Any(siu => siu.UserId == userId)
orderby si.Date
select new
{
    s.StartDate, s.EndDate, s.StartTime, s.EndTime, s.DayOfWeek,
    si.Date       
};

(this actually translates to SQL NOT EXISTS, but that's not essential).

And if you don't need projection, but simply slot instances (with slot info) which the user has not registered for, then it would be simply

var query = db.SlotInstances
    .Include(si => si.Slot)
    .Where(si => !si.SlotInstanceUsers.Any(siu => siu.UserId == userId))