exclude a list from another list in linq (linq2db)

Question

I am doing two fetches from database by linq2db. I have two list TaskDone and TaskNotAccomplished. I want to have the rows which are not in TaskDone List

 taskDone = from t in db.tblTasks join a in db.tblTaskResult on t.TaskId equals a.TaskId 
            where 
            a.UserId == tUserId
            select new taskDone 
            {
                TaskId = t.TaskId.ToString(),
                Subject = t.Subject
            }).ToList());

 taskNotAccomplished= from t in db.tblTasks  where 
               t.IsActive == true 
               select new TaskNotAccomplished
               {
                   TaskId = t.TaskId .ToString(),
                   Subject= t.Subject,                                                                       
                   EndDate = t.DateTaskEnd,                                                                      
                   TaskTime= t.TaskTime,
               }).ToList());

how can I not fetch the rows that are in taskDone (TaskId) List?

Why lists, why not queries? – Svyatoslav Danyliv Jan 30 '22 at 15:20 — Svyatoslav Danyliv, Jan 30 '22 at 15:20

score 0 · Answer 1 · answered Jan 30 '22 at 10:00

you can create a list of taskDone Ids :

var taskDoneIdLst = (from x in taskDone select x.TaskId).ToList();

if TaskId in database is an integer column you have to :

var taskDoneIdLst = (from x in taskDone select Convert.ToInt64(x.TaskId)).ToList();

then filter it in taskNotAccomplished query by (&& !taskDoneLst.Contains(t.TaskId)):

taskNotAccomplished= from t in db.tblTasks  where 
               t.IsActive == true 
              && !taskDoneLst.Contains(t.TaskId)
               select new TaskNotAccomplished
               {
                   TaskId = t.TaskId .ToString(),
                   Subject= t.Subject,                                                                       
                   EndDate = t.DateTaskEnd,                                                                      
                   TaskTime= t.TaskTime,
               }).ToList());

score 0 · Answer 2 · answered Jan 30 '22 at 15:33

Not sure that I have understand question correctly, but you can do that by LEFT JOIN, if you do not put everything into lists.

Note that I have removed TaskId.ToString() for avoiding table scan:

var taskDoneQuery = 
    from t in db.tblTasks 
    join a in db.tblTaskResult on t.TaskId equals a.TaskId 
    where a.UserId == tUserId
    select new 
    {
        TaskId = t.TaskId,
        Subject = t.Subject
    };
            
var taskNotAccomplished = 
    from t in db.tblTasks 
    where t.IsActive == true 
    from td in taskDoneQuery.LeftJoin(td => td.TaskId == t.TaskId)
    where td == null
    select new TaskNotAccomplished
    {
        TaskId = t.TaskId.ToString(),
        Subject = t.Subject,                                                                       
        EndDate = t.DateTaskEnd,                                                                      
        TaskTime = t.TaskTime,
    };

Also you can speedup your query, because there is no need to join to the same table twice.

var taskNotAccomplished = 
    from t in db.tblTasks 
    where t.IsActive == true 
    from a in db.tblTaskResult.LeftJoin(a => a.TaskId == t.TaskId)
    where a == null
    select new TaskNotAccomplished
    {
        TaskId = t.TaskId.ToString(),
        Subject = t.Subject,                                                                       
        EndDate = t.DateTaskEnd,                                                                      
        TaskTime = t.TaskTime,
    };

exclude a list from another list in linq (linq2db)

2 Answers2