Get the Total Count of TimeStamp in LINQ

Question

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

Iftikar Hussain · 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

TAN WhoAMI · Answer

sorry, your latest query does not work. are you able to join.me today?

Iftikar Hussain · Answer

you got the result? Regards, Iftikar

TAN WhoAMI · Answer

https://join.me/822-494-521

Iftikar Hussain · Answer

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

TAN WhoAMI · Answer

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

Iftikar Hussain · Answer

paste your linq statement here Regards, Iftikar

TAN WhoAMI · Answer

does not work

Iftikar Hussain · Answer

try without using ascending Regards, Iftikar

TAN WhoAMI · Answer

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?

Iftikar Hussain · Answer

share your screen

TAN WhoAMI · Answer

Iftikar Hussain · Answer

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();

TAN WhoAMI · Answer

it would have this error: Error 1 Cannot 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.cs 95 33 HourlyMonitorSystem

Iftikar Hussain · Answer

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() };

TAN WhoAMI · Answer

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."}

Iftikar Hussain · Answer

try like this IEnumerable TotalDays=

TAN WhoAMI · Answer

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.

TAN WhoAMI · Answer

thanks HUssain. Your knowledge of database query and LINQ is very very good. Any good recommendation books?

Iftikar Hussain · Answer

can you share using join-me Regards, Iftikar

TAN WhoAMI · Answer

Iftikar Hussain · Answer

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

TAN WhoAMI · Answer

Iftikar Hussain · Answer

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

TAN WhoAMI · Answer

this error now.

Iftikar Hussain · Answer

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();

TAN WhoAMI · Answer

sorry, does not work.

Iftikar Hussain · Answer

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

TAN WhoAMI · Answer

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.

Iftikar Hussain · Answer

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

TAN WhoAMI · Answer

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 --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.

Iftikar Hussain · Answer

try with group by in your SQL first. Regards, Iftikar

TAN WhoAMI · Answer

https://join.me/808-857-446

Iftikar Hussain · Answer

share me the output of both sql and linq

TAN WhoAMI · Answer

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

Iftikar Hussain · Answer

convert the TIMESTAP value in the below format in LINQ using p.TIMESTAMP.ToString("MM/dd/yyyy") Regards, Iftikar

TAN WhoAMI · Answer

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?

Iftikar Hussain · Answer

can you give me some output of select trunc(TIMESTAMP) from sql Regards, Iftikar

TAN WhoAMI · Answer

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.

Iftikar Hussain · Answer

Hi, In linq use Truncate(p.TIMESTAMP) Regards, Iftikar