Say I have an event that has a list of staff tasks:

public class Event()
{
  public Guid? StaffId { get; set; }
}

public class StaffTask()
{
  public Guid StaffId { get; set; }
  public Guid TaskId { get; set; }
}

How would I do something like this where I get all the events for a list of staff members?

var staffTasks = new List<StaffTasks>() 
{ 
  new StaffTask () { StaffId = "guid1", TaskId = "guid2" },
  new StaffTask () { StaffId = "guid3", TaskId = "guid4" }
};

queryable = _db.Events.AsQueryable()
  .Where(event => 
      staffTasks.Any(st => st.StaffId == event.StaffId)
  );

I currently get this error when running the above:

The LINQ expression 'DbSet<Event>()
    .Where(e => __staffTasks
        .Any(or => (Nullable<Guid>)or.StaffId == e.StaffId))' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'.

The goal would be to have this return only the second and third event here

var events = new List<Event>() {
  new Event() { StaffId = null },
  new Event() { StaffId = "guid1" },
  new Event() { StaffId = "guid2" },
  new Event() { StaffId = "guid20" },
  new Event() { StaffId = null }
}

CodePudding user response：

Try adding additional condition in where clause to check StaffId in event class is null or not,

Usage:

queryable = _db.Events.AsQueryable()
         .Where(event =>  event.StaffId.HasValue && 
        staffTasks.Any(st => st.StaffId.HasValue  && st.StaffId == event.StaffId));

or with null coalescing operator ??

queryable = _db.Events.AsQueryable()
        .Where(event => 
        staffTasks.Any(st => (st.StaffId ?? Guid.Empty) == event?.StaffId));

Try Online

CodePudding user response：

this seemed to get the job done, though i'm still not sure why @Prasad's answer didn't work

var staffTasks = new List<StaffTasks>() 
{ 
  new StaffTask () { StaffId = "guid1", TaskId = "guid2" },
  new StaffTask () { StaffId = "guid3", TaskId = "guid4" }
};

var staff = staffTasks.Select(st => st.StaffId).ToList();

queryable = _db.Events.AsQueryable()
  .Where(event => staffTasks.Contains(event.StaffId ?? Guid.Empty));