Substring and case statements in LINQ to SQL

Question

I am converting SQL to LINQ. Can someone help how to achieve the substring and case statements like below in LINQ.

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

Answer 1

This is how your sql will look like in 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)))
            };