Skip to content
Advertisement

LINQ equivalent of this subselect

“Tasks” table:

public class Task
{
    public int ID { get; set; }
    public string name { get; set; }
}

“Task History” table:

public class TaskHistory
{
    public Guid ID { get; set; }
    public DateTime Created { get; set; }
    public DateTime LastModified { get; set; }
    public string CreatedBy { get; set; }
    public int TaskID { get; set; }
    public TaskStatus { get; set; }
}

Sql query: Which tasks were completed in the last 30 days?

select * from Tasks 
where TaskStatus in ('Completed', 'Rejected')
and ID in (
    select distinct TaskID 
    from TaskHistory 
    where LastModified >= DateAdd(day, -30, GETDATE())
)

Q: What is the equivalent LINQ expression for this SQL query?

Advertisement

Answer

This query should do what yo want:

var statuses = new [] {"Completed", "Rejected"};

var query = 
   from t in ctx.Tasks
   where statuses.Contains(t.TaskStatus)
      && ctx.TaskHistory
         .Any(th => th.LastModified <= DateTime.Now.AddDays(-30)
             && th.TaskID == t.ID)
   select t;
 
User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement