Ufdjrw

I need help with the following SQL query to write in linq or extension methods query if both can provide both examples would be great for me.

This query is returning the last id entered by the user and will return an material id which is what I need for my c# program will use it.

SELECT TOP 1
 CASE 
 WHEN s.Material = 'P4861VAPF' THEN 4578
 WHEN s.Material = 'P2871VAPF' THEN 4579
 WHEN s.Material = 'P2231VAPF' THEN 4580
 ELSE NULL 
 END AS MaterialSelected
FROM
 dbo.Shipments s
WHERE
 s.material <> ''
ORDER BY
 s.Id

I wrote this linq extension method in C# to re-create my SQL query is not working correctly is return the Id instead my select case statement

public int GetLastShipmentMaterialEntry()

 var lastShipmentMaterialEntry = BarcodeReceivingDbContext.Shipments
 .Where(s => s.Material != "")
 .OrderBy(s => s.Id)
 .Select(s =>
 s.Material == "P4861VAPF" ? 4578 :
 s.Material == "P2871VAPF" ? 4579 :
 s.Material == "P2231VAPF" ? 4580 : 0) 
 .FirstOrDefault();

 return lastShipmentMaterialEntry;

I have seen other query examples from other question here in stack overflow and google but not to exact of my current query. I also tried to use linqpad to see if there it could translate it but no luck so any help is appreciate it.

I was able to re-create my SQL query using the Linq extension methods, if anyone thinks and sees a a better solution please let me know.

 public int GetLastShipmentMaterialEntry()
 
 var lastShipmentMaterialEntry = BarcodeReceivingDbContext.Shipments
 .Where(s => s.Material != "")
 .OrderByDescending(s => s.Id)
 .Select(s =>
 s.Material == "P4861VAPF" ? 4578 :
 s.Material == "P2871VAPF" ? 4579 :
 s.Material == "P2231VAPF" ? 4580 : 0) 
 .FirstOrDefault();

 return lastShipmentMaterialEntry;