Mark

Mark

  • 2.1k
  • 28
  • 3.9k

Need help converting CASE statement in LINQ

May 31 2022 10:44 PM

I am trying to convert a few CASE statements in a SQL query to LINQ and in need of assistance in doing so.

Here is part of the SQL statement with the CASE statements:

 

       SELECT
         dt.DisplayText,
      CASE WHEN fsh.FieldName = 'SiteId' THEN 'SiteName' ELSE fsh.FieldName END as FieldName,
         fsh.FieldName,
         fsh.[Action],
      CASE WHEN fsh.FieldName = 'SiteId' THEN so.SiteName ELSE fsh.OldValue END as OldValue, 
      CASE WHEN fsh.FieldName = 'SiteId' THEN sn.SiteName ELSE fsh.NewValue END as NewValue,
         fsh.OldValue,
         fsh.NewValue,
         u.UserName,
         fsh.ModifiedDate,
         fs.ProgramGroupYear,
         fs.CustomerId

Part of LINQ with the CASE statements. I put the CASE statements in here as placeholders. Obviously those statements in LINQ won't work.

public async Task<IEnumerable<HistoryRecord>> GetHistoryRecords(int documentTypeId, int 
  programGroupYear, int customerId, DateTime? from, DateTime? to, CancellationToken 
  cancellationToken)
{
    Guard.Zero(documentTypeId, nameof(documentTypeId));

    var query = _context.FileStoreHistories
    .Join(_context.FileStores, fsh => fsh.FileStoreId, fs => fs.FileStoreId,
                        (fsh, fs) => new
                        {
                            fs.CustomerId,
                            fs.ProgramGroupYear,
                            fs.DisplayText,
                            **CASE WHEN fsh.FieldName = 'SiteId' 
                  THEN 'SiteName' ELSE 
                    fsh.FieldName END as FieldName,**
                            fsh.FieldName,
                            fsh.Action,
                            *****CASE WHEN fsh.FieldName = 'SiteId' 
                  THEN so.SiteName ELSE 
                    fsh.OldValue END as OldValue, 
                            CASE WHEN fsh.FieldName = 'SiteId' THEN 
                    sn.SiteName ELSE 
                    fsh.NewValue END as NewValue,*****
                            fsh.OldValue,
                            fsh.NewValue,
                            fsh.ModifiedBy,
                            fsh.ModifiedDate,
                            fs.DocumentTypeId
                        }
                   ....

If this is not enough code I can provide more. I appreciate any assistance I can get. Thank you


Answers (1)