LINQ - IQueryable multiple where condition-CodePudding

I need to add 1 extra condition in where based on the if-else condition, but the code gets redundant. Please help to create a common query.

if (isFailure)
{
    var result = (from account in context.Account
                  join filetrans in context.FILE_TRANSACTION on account.ACCOUNTID equals filetrans.ACNT_ID
                  where accountIds.Contains(account.ACCOUNTID) && filetrans.PROCESS_STRT_TIME >= fromDateFilter && filetrans.PROCESS_STRT_TIME <= toDateFilter **&& failureStatus.Contains((decimal)filetrans.CRNT_FILE_STATUS_ID)**
                  select new
                              {
                                  account.ACCOUNTINFO,
                                  filetrans.ACNT_ID,
                                  filetrans.FILE_TRANID,
                                  filetrans.FILE_NM,
                                  filetrans.PROCESS_STRT_TIME,
                                  filetrans.CRNT_FILE_STATUS_ID
                              } )
                    .OrderByDescending(o => o.PROCESS_STRT_TIME)
                    .Skip(skip).Take(pageSize).ToList();                   
}
else
{
    var result = (from account in context.Account
                  join filetrans in context.AFRS_FILE_TRANSACTION on account.ACCOUNTID equals filetrans.ACNT_ID
                  where accountIds.Contains(account.ACCOUNTID) && filetrans.PROCESS_STRT_TIME >= fromDateFilter && filetrans.PROCESS_STRT_TIME <= toDateFilter
                  select new
                              {
                                  account.ACCOUNTINFO,
                                  filetrans.ACNT_ID,
                                  filetrans.FILE_TRANID,
                                  filetrans.FILE_NM,
                                  filetrans.PROCESS_STRT_TIME,
                                  filetrans.CRNT_FILE_STATUS_ID
                              })
                      .OrderByDescending(o => o.PROCESS_STRT_TIME)
                      .Skip(skip).Take(pageSize).ToList();                   
}

CodePudding user response：

Main benefit of LINQ that queries can be composed. Extract common part and apply needed filters later.

var query =
    from account in context.Account
    join filetrans in context.FILE_TRANSACTION on account.ACCOUNTID equals filetrans.ACNT_ID
    where accountIds.Contains(account.ACCOUNTID) && 
        filetrans.PROCESS_STRT_TIME >= fromDateFilter && 
        filetrans.PROCESS_STRT_TIME <= toDateFilter
    select new { account, filetrans };

if (isFailure)
{
    query = query.Where(x => failureStatus.Contains((decimal)x.filetrans.CRNT_FILE_STATUS_ID))
}

var result = query
    .Select(x => new 
    {
        x.account.ACCOUNTINFO,
        x.filetrans.ACNT_ID,
        x.filetrans.FILE_TRANID,
        x.filetrans.FILE_NM,
        x.filetrans.PROCESS_STRT_TIME,
        x.filetrans.CRNT_FILE_STATUS_ID
    })
    .OrderByDescending(o => o.PROCESS_STRT_TIME)
    .Skip(skip).Take(pageSize)
    .ToList();