convert Oracle query (to_timestamp) into LINQ statement

TAN WhoAMI

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 < to_timestamp('08/06/2013 00:30:00', 'MM/DD/YYYY HH24:MI:SSFF2'))
Order by TIMESTAMP ASC

13 Replies

Know the answer? Post it — somebody with the same question will find it here.

Iftikar Hussain13y ago
Accepted 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 Hussain13y ago
no problem.

Regards,
Iftikar
TAN WhoAMI13y ago
oh ya, thanks, it works perfectly now. thanks for your tips.
Iftikar Hussain13y ago
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 <= timeCal.EndTime
select p.MAINSERNO).Distinct().Count();

Regards,
Iftikar
TAN WhoAMI13y ago
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 <= timeCal.EndTime
select p).Distinct().Count();

Is my Distinct used correctly above?
Iftikar Hussain13y ago
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 WhoAMI13y ago
below show the wrong count.

while below shows the correct count.
TAN WhoAMI13y ago
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 Hussain13y ago
now is it working as you expected or not?

Regards,
Iftikar
TAN WhoAMI13y ago
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 < to_timestamp('08/10/2013 15:30:00', 'MM/DD/YYYY HH24:MI:SS'))

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 <= timeCal.EndTime
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?
13y ago
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 Hussain13y ago
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
13y ago
Try to convert both the timestamp columns into datatime and try.