convert Oracle query (to_timestamp) into LINQ statement

Question

I realize I need this for LINQ as from http://stackoverflow.com/questions/11135820/convert-oracle-query-as-of-timestamp-to-linq SELECT --DISTINCT MAINSERNO to_char(TIMESTAMP 'MM/DD/YYYY HH24:MI:SS') as TIMESTAMP COUNT(DISTINCT MAINSERNO) AS TOTAL FROM PPL_PRODUCT_TESTID2 WHERE (MONO = '5750327') AND (ITNO = '132959') AND (SYSTEMTYPE = '0036') AND (TESTFLAG = '1') AND (TIMESTAMP > to_timestamp('08/05/2013 23:30:00' 'MM/DD/YYYY HH24:MI:SSFF2')) AND (TIMESTAMP Order by TIMESTAMP ASC

Iftikar Hussain · Answer

try like this var query = from c in context.PPL_PRODUCT_TESTID2 where c.TIMESTAMP >= DateTime.ParseExact('08/05/2013 23:30:00'  MM/dd/yyyy HH:mm:ss CultureInfo.InvariantCulture) select c; Regards Iftikar

Iftikar Hussain · Answer

no problem. Regards Iftikar

TAN WhoAMI · Answer

oh ya thanks it works perfectly now. thanks for your tips.

Iftikar Hussain · Answer

you need to use distinct for that particular column int count = (from p in context.PPL_PRODUCT_TESTID2 where p.MONO == Mono && p.ITNO == PartNo && p.SYSTEMTYPE ==0036 && p.TESTFLAG ==1 && p.TIMESTAMP >=(timeCal.StartTime) && p.TIMESTAMP select p.MAINSERNO ).Distinct().Count(); Regards Iftikar

TAN WhoAMI · Answer

yes I did use DISTINCT in my C# Linq didn't I? I get a value greater than expected. int count = (from p in context.PPL_PRODUCT_TESTID2 where p.MONO == Mono && p.ITNO == PartNo && p.SYSTEMTYPE ==0036 && p.TESTFLAG ==1 && p.TIMESTAMP >=(timeCal.StartTime) && p.TIMESTAMP select p).Distinct().Count(); Is my Distinct used correctly above?

Iftikar Hussain · Answer

yes since in your LINQPAD you have used DISTINCT so in your c# linq also need to use. your query is correct Regards Iftikar

TAN WhoAMI · Answer

below show the wrong count. while below shows the correct count.

TAN WhoAMI · Answer

it is working as expected but some of its count is greater when I compared with LINQPAD.. it is to do with the Distinct().Count() I believe.... you can join.in if you want I can show you....a short while..:)

Iftikar Hussain · Answer

now is it working as you expected or not? Regards Iftikar

TAN WhoAMI · Answer

Hi Hussain this is my SQL below: SELECT COUNT(DISTINCT MAINSERNO) AS TOTAL FROM PPL_PRODUCT_TESTID2 WHERE (MONO =5749743) AND (ITNO = 233408) AND (SYSTEMTYPE = '0036') AND (TESTFLAG = '1') AND (TIMESTAMP > to_timestamp('08/10/2013 14:30:00' 'MM/DD/YYYY HH24:MI:SS')) AND (TIMESTAMP In my C# application code I have this: int count = (from p in context.PPL_PRODUCT_TESTID2 where p.MONO == Mono && p.ITNO == PartNo && p.SYSTEMTYPE ==0036 && p.TESTFLAG ==1 && p.TIMESTAMP >=(timeCal.StartTime) && p.TIMESTAMP select p).Distinct().Count(); This application code would sometime give a count that is greater than what is the correct value I have though the Distinct().Count() would have taken care of that?

Muralidharan Deenathayalan · Answer

Please try something similar to this. var result = from x in times wheren (TIMESTAMP > ConvertToTimeStamp ( '08/05/2013 23:30:00'  'MM/DD/YYYY HH24:MI:SSFF2' ) select x; Create a new method to convert datetime into timestamp.

Iftikar Hussain · Answer

Hi try like this Datetime FilterStartDate = DateTime.Now; var query = from c in context.PPL_PRODUCT_TESTID2 where (c.TIMESTAMP >= DateTime.ParseExact('08/05/2013 23:30:00' MM/dd/yyyy HH:mm:ss CultureInfo.InvariantCulture) : false) == true select c; Regards Iftikar

Muralidharan Deenathayalan · Answer

Try to convert both the timestamp columns into datatime and try.