SQL to LINQ

Question

How to write this sql query in Linq below

SELECT distinct Fom_Dato As OpenDate FROM

(SELECT Selskapskode, Periode_Selskap,Foringsvaluta, Konsol_Valuta,

CASE

WHEN Regnaar3_Kode IS NOT NULL THEN (

SELECT MIN(Periode) FROM PERIODE WHERE Periode_Sel = Periode_Selskap AND Periode > Regnaar3_Slutt)

WHEN Regnaar2_Kode IS NOT NULL THEN Regnaar3_Start

WHEN Regnaar1_Kode IS NOT NULL THEN Regnaar2_Start

WHEN Regnaar0_Kode IS NOT NULL THEN Regnaar1_Start

ELSE

Regnaar0_Start END As StartPeriod FROM Selskap) VT, Periode

WHERE Periode_Sel = Periode_Selskap AND StartPeriod = Periode and selskapskode in ('ACHE','DAM')

Rgds,

Anitha

Anitha · Answer

Thanks much Shambhu . Your code helps in a better way. Rgds Anitha

Shambhu · Answer

Hi Above query can be split into two. first query for inline table and then join with main table and then write condition. There is written below using sample data. List WHList = new List { new Warehouse{ WHCode = W001 WHLocation = Bangalore WHName = WH001} new Warehouse{ WHCode = W002 WHLocation = Delhi WHName = WH001} new Warehouse{ WHCode = W003 WHLocation = Kolkata WHName = WH003} }; List SupList = new List { new Supplier { SupName =S001 SupAddress = Bangalore1 SupContact = 100 WHCode = W001 } new Supplier { SupName =S002 SupAddress = Bangalore2 SupContact = 200 WHCode = W002 }  new Supplier { SupName =S003 SupAddress = Bangalore3 SupContact = 300 WHCode = W003 } }; //inline table with case var InnerQuery = from n in SupList select new { WHCode = n.WHCode warehouseRange = n.SupName == null ? S001 : n.SupName == S002 ? S002 : n.SupName == S003 ? S003 : S000 }; var MainQuery = from a in InnerQuery join b in WHList on a.WHCode equals b.WHCode where a.WHCode == W001 select new { a.warehouseRange b.WHCode }; class Warehouse { public string WHName { get; set; } public string WHCode { get; set; } public string WHLocation { get; set; } } class Supplier { public string SupName { get; set; } public string SupAddress { get; set; } public string SupContact { get; set; } public string WHCode { get; set; } } May this linq helps u better... Regards Shambhu.

Anuja Pawar · Answer

Refer this thread might help you http://stackoverflow.com/questions/209924/switch-statement-in-linq