我如何转换SQL查询与case切换到Linq Lambda

本文关键字:case Lambda Linq 查询 SQL 何转换 转换 | 更新日期: 2023-09-27 18:15:21

我正在Visual Studio 2015中构建一个rest api,使用MVC 6和EF7。我有一个SQL查询,返回一些特定的数据,我需要。它看起来像这样:

    SELECT jf.JournalID, jf.FileID, 
       CASE WHEN jf.Acknowledgment = 1 THEN 'Acknowledgment'
              WHEN jf.Foreword = 1 THEN 'Foreword'
              WHEN jf.Preface = 1 THEN 'Preface'
              WHEN jf.Logo = 1 THEN 'Logo'
              WHEN jf.Specialty = 1 THEN 'Specialty'
              WHEN jf.ContentsTable = 1 THEN 'Table of Contents'
              WHEN jf.TitlePage = 1 THEN 'Title Page'
              WHEN jf.AuthorIndex = 1 THEN 'Author Index'
              ELSE NULL 
               END AS ArticleType  
    FROM JournalFile jf 
    WHERE jf.JournalID = 19 --PARAMETER

我需要将这个查询转换为Linq,像这样:

  var results = (from s in _context.JournalFile
      where s.JournalID == jID
      select new IATTJService.Models.JournalFileTopLevel
          {
              JournalID = s.JournalID,
              FileID = s.FileID,
              ArticleType = ( 
                        << Case options Here >>
                             )
           }).ToList();

Case语句中的每个字段都是字节字段,返回true或false。我不知道如何在Linq中执行SQL的Case部分。

我如何转换SQL查询与case切换到Linq Lambda

ArticleType = (
    jf.Acknowledgment == 1 ? 'Acknowledgment' :
    jf.Foreword == 1 ?  'Foreword' :
     // ect 
                         )
var results = (from s in _context.JournalFile
      where s.JournalID == jID
      select new IATTJService.Models.JournalFileTopLevel
          {
              JournalID = s.JournalID,
              FileID = s.FileID,
              ArticleType = 
                 s.Acknowledgment ? "Acknowledgment"
                 : s.Foreword     ? "Foreword"
                 // ..
                 : AuthorIndex    ? "Author Index"
                 : null
           }).ToList();