How to apply case when function in Linq

Question

I have a Linq query to fetch data from databse using entity framework. I have to apply the given sql condetion to the linq query.My sample sql query is

 DECLARE @EmrId NVARCHAR(50)
 set @EmrId='784197621725304'
SELECT 
   [ftp_imptlg_ImprtTyp_id],[ftp_imptlg_daypath],[ftp_imptlg_subfolderpath]
  ,[ftp_imptlg_emirates_id],[ftp_imptlg_srcfile_name],[ftp_imptlg_filereadon]
  ,[ftp_imptlg_opr_status],[ftp_imptlg_doc_no],[ftp_imptlg_emp_id]
  ,[ftp_imptlg_upld_filename] ,[ftp_imptlg_doctype_id]
FROM [C3KYC].[dbo].[ftp_import_logs] AS c
inner join tm_doc_type as d on c.[ftp_imptlg_doctype_id]=d.doc_typeid
inner join tm_import_type as e on c.[ftp_imptlg_ImprtTyp_id]=e.imprttype_id

WHERE c.ftp_imptlg_emirates_id = case when @EmrId ='0' then 
 c.ftp_imptlg_emirates_id else @EmrId end  
AND 
c.ftp_imptlg_opr_status='failed'
and d.doc_typeid=6
and e.imprttype_id='2'

I tried the below linq query

string strEmrid = EmiratesId != null ? EmiratesId : "0";
        using (var db = new DB_KYC3Entities())
        {
            db.Configuration.ProxyCreationEnabled = false;
            List<ImportLogDetails> listofLogDetails = (from c in db.ftp_import_logs
                                                       join d in db.tm_doc_type on c.ftp_imptlg_doctype_id equals d.doc_typeid
                                                       join e in db.tm_import_type on c.ftp_imptlg_ImprtTyp_id equals e.imprttype_id
                                                       where c.ftp_imptlg_ImprtTyp_id == ImportTypeId
                                                       && c.ftp_imptlg_emirates_id == strEmrid
                                                       && c.ftp_imptlg_opr_status== "Failed"
                                                       && d.doc_typeid== 6
                                                       && e.imprttype_id== "2"
                                                       select new ImportLogDetails
                                                       {
                                                           ImportType = e.imprtTye_name,
                                                           SourcePath = c.ftp_imptlg_subfolderpath,
                                                           DateOfAction = c.ftp_imptlg_filereadon,
                                                           DocumentType = d.doctype_name,
                                                           EmiratesId = c.ftp_imptlg_emirates_id,
                                                           Status = c.ftp_imptlg_opr_status,
                                                           KycEmployeeId = c.ftp_imptlg_emp_id,
                                                           DocumentTypeId = c.ftp_imptlg_doctype_id
                                                       }).ToList();

            return listofLogDetails;

read this: https://stackoverflow.com/questions/936028/linq-case-statement — Masoud Keshavarz, Jan 17 '18 at 06:49
Do you even need the case when? the statement will allways fetch be true.. c.ftp_imptlg_emirates_id = c.ftp_imptlg_emirates_id — Marcus Höglund, Jan 17 '18 at 07:00

Akash Kava · Answer 1 · 2018-01-17T13:39:12.763

You can use ?: operator in LINQ,

 x.Where( x => x.ftp_imptlg_emirates_id == (
       x.ftp_imptlg_emirates_id == "0" ? "0" : x.ftp_imptlg_emirates_id
 ))

Your query,

string strEmrid = EmiratesId != null ? EmiratesId : "0";
    using (var db = new DB_KYC3Entities())
    {
        db.Configuration.ProxyCreationEnabled = false;
        List<ImportLogDetails> listofLogDetails = (from c in db.ftp_import_logs
            join d in db.tm_doc_type on c.ftp_imptlg_doctype_id equals d.doc_typeid
            join e in db.tm_import_type on c.ftp_imptlg_ImprtTyp_id equals e.imprttype_id
            where c.ftp_imptlg_ImprtTyp_id == ImportTypeId
            && 

           c.ftp_imptlg_emirates_id == (strEmirId == "0" ? c.ftp_imptlg_emirates_id : strEmirid)


            && c.ftp_imptlg_opr_status== "Failed"
            && d.doc_typeid== 6
            && e.imprttype_id== "2"
            select new ImportLogDetails
            {
                ImportType = e.imprtTye_name,
                SourcePath = c.ftp_imptlg_subfolderpath,
                DateOfAction = c.ftp_imptlg_filereadon,
                DocumentType = d.doctype_name,
                EmiratesId = c.ftp_imptlg_emirates_id,
                Status = c.ftp_imptlg_opr_status,
                KycEmployeeId = c.ftp_imptlg_emp_id,
                DocumentTypeId = c.ftp_imptlg_doctype_id
            }).ToList();

    return listofLogDetails;

here x means my table name right.? how to apply the x.where condition inside my existing where condition — Jzl, Jan 17 '18 at 06:52
I think that it is not the same as OP wants. His predicate is: if `incoming_param` equals "0", then `c.ftp_imptlg_emirates_id == c.ftp_imptlg_emirates_id`, else `c.ftp_imptlg_emirates_id == incoming_param`. So you must write it as `c.ftp_imptlg_emirates_id == EmiratesId == "0" ? c.ftp_imptlg_emirates_id : EmiratesId` — Hemid Abbasov, Jan 17 '18 at 12:49
@HemidAbbasov he changed that expression after I had answered. — Akash Kava, Jan 17 '18 at 13:38

How to apply case when function in Linq

1 Answers1