Get the Total Count of TimeStamp in LINQ

TAN WhoAMI

13y3.6kLINQ

in SQL, I am able to get the total count of different timestamp of its date as shown below.

the data is also attached. How do I have it in C# LINQ?

I try below, it does not return value of 3, but total number of different times. I need to have the word trunc in LINQ. thanks.

int TotalDays = (from p in context.PPL_PRODUCT_TESTID2

where p.MONO == Mono && p.ITNO == PartNo && p.SYSTEMTYPE == "0036" && p.TESTFLAG == 1

select p.TIMESTAMP).Distinct().Count(); // all timestamp MO

40 Replies

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

Iftikar Hussain13y ago
Accepted answer
try like this

int TotalDays = (from p in context.PPL_PRODUCT_TESTID2
where p.MONO == Mono && p.ITNO == PartNo && p.SYSTEMTYPE == "0036" &&
p.TESTFLAG == 1
select (EntityFunctions.TruncateTime(p.TIMESTAMP))).Distinct().Count();

Regards,
Iftikar
0
TAN WhoAMI13y ago
sorry, your latest query does not work. are you able to join.me today?
0
Iftikar Hussain13y ago
you got the result?

Regards,
Iftikar
0
TAN WhoAMI13y ago
https://join.me/822-494-521
0
Iftikar Hussain13y ago
try like this

var AllTimeStamp = (from p in context.PPL_PRODUCT_TESTID2
where p.MONO == Mono && p.ITNO == PartNo && p.SYSTEMTYPE == "0036" && p.TESTFLAG == 1
select p.TIMESTAMP).Distinct().OrderBy(x => x.TIMESTAMP);

if it does not work then share your screen

Regards,
Iftikar
0
TAN WhoAMI13y ago
var AllTimeStamp = (from p in context.PPL_PRODUCT_TESTID2
where p.MONO == Mono && p.ITNO == PartNo && p.SYSTEMTYPE == "0036" && p.TESTFLAG == 1
orderby p.TIMESTAMP
select p.TIMESTAMP).Distinct(); // all timestamp MO
0
Iftikar Hussain13y ago
paste your linq statement here

Regards,
Iftikar
0
TAN WhoAMI13y ago
does not work
0
Iftikar Hussain13y ago
try without using ascending

Regards,
Iftikar
0
TAN WhoAMI13y ago
why is my orderby p.TIMESTAMP ascending not sorted in ascending order

but in LINQPAD, it is sorted in the ascending order which I want?

0
Iftikar Hussain13y ago
share your screen
0
TAN WhoAMI13y ago
0
Iftikar Hussain13y ago
try like this

List DifferentDays = (from p in context.PPL_PRODUCT_TESTID2
where p.MONO == Mono && p.ITNO == PartNo && p.SYSTEMTYPE == "0036" && p.TESTFLAG == 1
group p by EntityFunctions.TruncateTime(p.TIMESTAMP) into g
select new { k = g.Key, m = g.Count() }).Select(t = > t.m).ToList();
0
TAN WhoAMI13y ago
it would have this error:

Error1Cannot implicitly convert type 'System.Linq.IQueryable' to 'System.Collections.Generic.IEnumerable'. An explicit conversion exists (are you missing a cast?)I:\Interface2HourlyMonitoringSystem_15thAugust2013\HourlyMonitorSystem\HourlyMonitorSystem\MainWindow.xaml.cs9533HourlyMonitorSystem
0
Iftikar Hussain13y ago
don't use ToString()

IEnumerable DifferentDays = from p in context.PPL_PRODUCT_TESTID2
where p.MONO == Mono && p.ITNO == PartNo && p.SYSTEMTYPE == "0036" && p.TESTFLAG == 1
group p by EntityFunctions.TruncateTime(p.TIMESTAMP) into g
select new { k = g.Key, m = g.Count() };
0
TAN WhoAMI13y ago
do you mean this?

IEnumerable DifferentDays = from p in context.PPL_PRODUCT_TESTID2
where p.MONO == Mono && p.ITNO == PartNo && p.SYSTEMTYPE == "0036" && p.TESTFLAG == 1
group p by EntityFunctions.TruncateTime(p.TIMESTAMP) into g
select new { k = g.Key, m = g.Count() }.ToString();

it would have this error:

base {System.SystemException} = {"LINQ to Entities does not recognize the method 'System.String ToString()' method, and this method cannot be translated into a store expression."}
0
Iftikar Hussain13y ago
try like this

IEnumerable TotalDays=
0
TAN WhoAMI13y ago
Hi Hussain,

Can I check how can I extract out those 4 dates of type string from TotalDays?
Also, how can I extract out those 4 values (177, 120, 204, 1) of type int from TotalDays?

thanks.
0
TAN WhoAMI13y ago
thanks HUssain. Your knowledge of database query and LINQ is very very good. Any good recommendation books?
0
Iftikar Hussain13y ago
can you share using join-me

Regards,
Iftikar
0
TAN WhoAMI13y ago
0
Iftikar Hussain13y ago
try like this

int TotalDays = (from p in context.PPL_PRODUCT_TESTID2
group p by p.TIMESTAMP.Date into g
where p.MONO == Mono && p.ITNO == PartNo && p.SYSTEMTYPE == "0036" && p.TESTFLAG == 1
select (g).Distinct().Count();

Regards,
Iftikar
0
TAN WhoAMI13y ago
0
Iftikar Hussain13y ago
try like this

int TotalDays = (from p in context.PPL_PRODUCT_TESTID2
group p by to_char(p.TIMESTAMP, 'MM/DD/YYYY') into g
where p.MONO == Mono && p.ITNO == PartNo && p.SYSTEMTYPE == "0036" && p.TESTFLAG == 1
select (g).Distinct().Count();

Regards,
Iftikar
0
TAN WhoAMI13y ago
this error now.

0
Iftikar Hussain13y ago
sorry. I missed something

int TotalDays = (from p in context.PPL_PRODUCT_TESTID2
group p by p.TIMESTAMP.ToString("MM/dd/yyyy") into g
where p.MONO == Mono && p.ITNO == PartNo && p.SYSTEMTYPE == "0036" &&
p.TESTFLAG == 1
select (g).Distinct().Count();
0
TAN WhoAMI13y ago
sorry, does not work.
0
Iftikar Hussain13y ago
try like this

int TotalDays = (from p in context.PPL_PRODUCT_TESTID2
group p by p.TIMESTAMP.ToString("MM/dd/yyyy")
where p.MONO == Mono && p.ITNO == PartNo && p.SYSTEMTYPE == "0036" && p.TESTFLAG == 1
select (p.TIMESTAMP.ToString("MM/dd/yyyy"))).Distinct().Count();

Regards,
Iftikar
0
TAN WhoAMI13y ago
above would be useful for me in later part too. how to put your above SQL in LINQ?

your above gives this output which is useful for me.
I would need my C# program to get the value of 3 too.
0
Iftikar Hussain13y ago
try like this

SELECT count(to_char(TIMESTAMP, 'MM/DD/YYYY')) as Cnt as TIMESTAMP
FROM pfods.PPL_PRODUCT_TESTID2
WHERE (MONO = '5755481') AND (SYSTEMTYPE = '0036') AND (TESTFLAG = '1') AND (ITNO = '132960')
GROUP BY to_char(TIMESTAMP, 'MM/DD/YYYY')

Regards,
Iftikar
0
TAN WhoAMI13y ago
sorry, I do not really understand...
even with say

SELECT DISTINCT MAINSERNO, to_char((TIMESTAMP), 'MM/DD/YYYY HH24:MI:SS') as TIMESTAMP
--COUNT(DISTINCT MAINSERNO) AS TOTAL
--trunc(TIMESTAMP) AS DATES
--COUNT(DISTINCT trunc(TIMESTAMP)) AS DAYS
FROM pfods.PPL_PRODUCT_TESTID2
WHERE (MONO = '5755481') AND (SYSTEMTYPE = '0036') AND (TESTFLAG = '1') AND (ITNO = '132960')
--AND (TIMESTAMP >= to_timestamp('08/19/2013 07:30:00', 'MM/DD/YYYY HH24:MI:SS'))
--AND (TIMESTAMP <= to_timestamp('08/19/2013 08:30:00', 'MM/DD/YYYY HH24:MI:SS'))
--AND (TIMESTAMP > to_timestamp('12:30:00', 'HH24:MI:SSFF2'))
--AND (TIMESTAMP > to_timestamp('13:30:00', 'HH24:MI:SSFF2'))
GROUP BY MAINSERNO, TIMESTAMP
Order by TIMESTAMP ASC

it displays all those 3 different dates...

What I want to do is to get the value of 3 in C# LINQ.
0
Iftikar Hussain13y ago
try with group by in your SQL first.

Regards,
Iftikar
0
TAN WhoAMI13y ago
https://join.me/808-857-446
0
Iftikar Hussain13y ago
share me the output of both sql and linq
0
TAN WhoAMI13y ago
does not work.

int TotalDays = (from p in context.PPL_PRODUCT_TESTID2
where p.MONO == Mono && p.ITNO == PartNo && p.SYSTEMTYPE == "0036" && p.TESTFLAG == 1
select (p.TIMESTAMP.ToString("MM/dd/yyyy"))).Distinct().Count(); // total days for this MO
0
Iftikar Hussain13y ago
convert the TIMESTAP value in the below format in LINQ using

p.TIMESTAMP.ToString("MM/dd/yyyy")

Regards,
Iftikar
0
TAN WhoAMI13y ago
it consists of rows of those 3 dates (8/17/2013, 8/18/2013, 8/19/2013),
as seen below:

the problem is C# does not recognize the word Truncate. Do we need to add any reference?
0
Iftikar Hussain13y ago
can you give me some output of select trunc(TIMESTAMP) from sql

Regards,
Iftikar
0
TAN WhoAMI13y ago
int TotalDays = (from p in context.PPL_PRODUCT_TESTID2
where p.MONO == Mono && p.ITNO == PartNo && p.SYSTEMTYPE == "0036" && p.TESTFLAG == 1
select Truncate(p.TIMESTAMP)).Distinct().Count(); // all timestamp MO

above does not work.
0
Iftikar Hussain13y ago
Hi,
In linq use Truncate(p.TIMESTAMP)

Regards,
Iftikar
0