CODEWITHSUNDEEP
c#linq
Salman Mushtaq
Salman Mushtaq

Reputation: 341

Conversion to LINQ query

Can anyone help me to make below query as linq

SELECT E.Cosem_Object, COALESCE(A.Explanation, 'Not Available' ) As 
Explanation ,A.Unit, E.Reading , E.Unit, E.Meter_Date, E.Meter_Time from 
ENERGY_PROFILE E LEFT OUTER JOIN ALL_COSEM_OBJECTS A on 
(SUBSTRING(E.Cosem_Object,0,CHARINDEX('*',E.Cosem_Object + '*',0))) = A.Short_Cosem_Object

Updated:

I try

var query = from o in ENERGY_PROFILE 
            join e in ALL_COSEM_OBJECTS
            on o.Short_Cosem_Object equals e.Short_Cosem_Object
            select new { 
                o.Short_Cosem_Object, o.READING, e.EXPLANATION, e.UNIT 
            };

I need to add below line

SUBSTRING(E.Cosem_Object,0,CHARINDEX('*',E.Cosem_Object + '*',0)) in linq query

Upvotes: 0

Views: 77

Answers (1)

mshwf
mshwf

Reputation: 7449

here's the exact conversion of your SQL query into LINQ, though I'm not sure if it's exactly what you are expecting since the lack of a sample and expected result:

 var set = from E in ENERGY_PROFILE
                     join A in ALL_COSEM_OBJECTS
                     on E.Cosem_Object.Substring(0, (E.Cosem_Object + "*").IndexOf('*')) equals A.Short_Cosem_Object
                     into joinedTb
                     from c in joinedTb.DefaultIfEmpty()
                     select new
                     {   
                         E.Cosem_Object,
                         Explanation = c.Explanation ?? "Not Available",
                         c.Unit,
                         E.Reading,
                         Unit2 = E.Unit,
                         E.Meter_Date,
                         E.Meter_Time
                     };

Upvotes: 1

Related Questions