LINQ to SQL中的子字符串和case语句

本文关键字：字符串 case 语句 to SQL LINQ | 更新日期: 2023-09-27 18:00:00

我正在将SQL转换为LINQ。有人能帮助如何在LINQ中实现下面这样的子字符串和case语句吗。

SELECT p.goalcommitment, 
       g.goalmeasurement,
       CASE g.goalmeasurement 
           WHEN 'Count' THEN CAST(p.goalcommitmentnumber AS VARCHAR)
           WHEN 'Currency' THEN '$' + CAST(p.goalcommitmentcurrency AS VARCHAR)
           WHEN 'Percentage' THEN RTRIM(CAST(p.goalcommitmentpercentage AS VARCHAR)) + '%'
           ELSE p.pamwb_goalcommitment 
       END AS goalcommitment,
       SUBSTRING(p.domainname, CHARINDEX(''', p.domainname, 0) + 1, LEN(p.domainname) - CHARINDEX(''', p.domainname, 0))    
FROM Filtered_psp p
JOIN Filtered_goalsubtype g 
ON g.goalsubtypeid = p.goalsubtype

这就是您的sql在linq:中的样子

var query = from p in Filtered_psp
            join g in Filtered_goalsubtype on p.goalsubtype equals g.goalsubtypeid
            let slashLocation = p.domainname.IndexOf(@"'")
            select new
            {
                Field1 = p.goalcommitment,
                Field2 = g.goalmeasurement,
                Field3 = (p.goalmeasurement == "Count" ? p.goalcommitmentnumber.ToString() :
                          p.goalmeasurement == "Currency" ? "$" + p.goalcommitmentnumber.ToString() :
                          p.goalmeasurement == "Percentage" ? p.goalcommitmentnumber.ToString() + "%" : p.pamwb_goalcommitment),
                Field4 = slashLocation == -1 ? (p.domainname : p.domainname.Substring(slashLocation + 1, p.domainname.Length - slashLocation - 1)))
            };