Click here to Skip to main content
15,908,020 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
ID    Status      Date
-------------------------------------------

2706	Y	2015-08-27 14:26:25.103
2706	N	2015-08-27 14:33:42.053
2707	Y	2015-08-27 14:26:22.227
2707	N	2015-08-27 14:27:11.000
2710	Y	2015-08-25 17:07:01.937
2710	N	2015-08-27 14:16:42.420
2710	Y	2015-08-27 14:18:56.347
2710	N	2015-08-27 14:27:07.753
2755	Y	2015-08-25 17:06:59.210
2755	N	2015-08-26 11:27:28.643
2755	Y	2015-08-26 11:27:35.143
2755	N	2015-08-27 14:16:39.917
2755	Y	2015-08-27 14:18:53.477
2755	N	2015-08-27 14:26:49.217



Hello, Here is my DataTable (gDataset12.Tables[0]) above ... from this i want in c# as the max date record by ID and its status
the result should be the last updated status of the for each ID for each day


2706	N	2015-08-27 14:33:42.053
2707	N	2015-08-27 14:27:11.000
2710	N	2015-08-27 14:27:07.753
2755	N	2015-08-27 14:26:49.217
2755	Y	2015-08-26 11:27:35.143
2710	Y	2015-08-25 17:07:01.937
2755	Y	2015-08-25 17:06:59.210



LINQ m using ...

C#
var myLINQ = from dt in gDataset12.Tables[0].AsEnumerable()
                                 group dt by dt.Field<string>("ID") into grp
                                 select new
                                 {
                                     ID = grp.Max(T => T.Field<int>("ID")),
                                     Status = grp.Key,
                                     Date = grp.Max(T => T.Field<datetime>("Date"))
                                 };
Posted
Updated 27-Aug-15 22:36pm
v3
Comments
CPallini 28-Aug-15 2:45am    
And... What is the question?
Devraj Kapdi 28-Aug-15 2:45am    
i didnt get the resulting output
Herman<T>.Instance 28-Aug-15 4:35am    
You TAG the question as SQL, where is the SQL?

I don't use linq syntax. I have never got on with it. I use the linq extension instead, so my answer will be in that format.

If anyone can translate it then feel free ^_^

This is the query using extension methods:
C#
var myLINQ = gDataset12.Tables[0].AsEnumerable()
            .GroupBy(r=>new {
                  ID = r.Field<int>("ID"),                 //each ID
                  Date = r.Field<datetime>("Date").Date})  //each Day
            .SelectMany(g=> g.Max(r=>.Field<datetime>("Date")) //item in group with max time
            .Select(r=> new {
                        ID = r.Field<int>("ID"),
                        Status = r.Field<int>("Status"),
                        Date = r.Field<datetime>("Date")
                     });


I haven't tested this so there will probably be a mistake somewhere. I hope this helps you work out how to fix your query.

let me know if you need anything ^_^
Andy


UPDATE: ok - got it working. Max was not the way to go, instead I ordered the groups my date descending and took the first of each group.

There query I used is in there but here is the test I set up, just to excuse the reason I was a bit hesitant to test earlier:

C#
internal class Program
{
    private static void Main()
    {
        DataTable dt = new DataTable("table");

        dt.Columns.AddRange(
            new DataColumn[]
            {
                new DataColumn("ID", typeof (int)),
                new DataColumn("Status", typeof (string)),
                new DataColumn("Date", typeof (DateTime)),

            });

        dt.Rows.Add(NewRow(2706, "Y", "2015-08-27 14:26:25.103", dt));
        dt.Rows.Add(NewRow(2706, "N", "2015-08-27 14:33:42.053", dt));
        dt.Rows.Add(NewRow(2707, "Y", "2015-08-27 14:26:22.227", dt));
        dt.Rows.Add(NewRow(2707, "N", "2015-08-27 14:27:11.000", dt));
        dt.Rows.Add(NewRow(2710, "Y", "2015-08-25 17:07:01.937", dt));
        dt.Rows.Add(NewRow(2710, "N", "2015-08-27 14:16:42.420", dt));
        dt.Rows.Add(NewRow(2710, "Y", "2015-08-27 14:18:56.347", dt));
        dt.Rows.Add(NewRow(2710, "N", "2015-08-27 14:27:07.753", dt));
        dt.Rows.Add(NewRow(2755, "Y", "2015-08-25 17:06:59.210", dt));
        dt.Rows.Add(NewRow(2755, "N", "2015-08-26 11:27:28.643", dt));
        dt.Rows.Add(NewRow(2755, "Y", "2015-08-26 11:27:35.143", dt));
        dt.Rows.Add(NewRow(2755, "N", "2015-08-27 14:16:39.917", dt));
        dt.Rows.Add(NewRow(2755, "Y", "2015-08-27 14:18:53.477", dt));
        dt.Rows.Add(NewRow(2755, "N", "2015-08-27 14:26:49.217", dt));

        new Program().TestId(dt);

    }

    private void TestId(DataTable dt)
    {
         ////////////////////
        // Here is it ^_^ //
       ////////////////////
       var myLinq = dt.AsEnumerable()
        .GroupBy(r=>new {
              ID = r.Field<int>("ID"),               //each ID
              Date = r.Field<DateTime>("Date").Date})//each Day
        .Select(g=> g.OrderByDescending(r=>r.Field<DateTime>("Date")).First() ) //item in group with max time
        .Select(r=> new {
                    ID = r.Field<int>("ID"),
                    Status = r.Field<string>("Status"),
                    Date = r.Field<DateTime>("Date")
        });

       //Result output
       Console.WriteLine("{0}\t{1}\t{2}", "ID","Status","Date");
        myLinq.ToList().ForEach(i =>
        {
            Console.WriteLine("{0}\t{1}\t{2}", i.ID, i.Status, i.Date.ToString("G"));
        });

        Console.Read();
    }


    private static DataRow NewRow(int id, string status, string date, DataTable dt)
    {
        return NewRow(id, status, DateTime.Parse(date), dt);
    }
    private static DataRow NewRow(int id, string status, DateTime date, DataTable dt)
    {
        DataRow dr = dt.NewRow();
        dr["ID"] = id;
        dr["Status"] = status;
        dr["Date"] = date;

        return dr;
    }

}




UPDATE: OP requires a return type of DataTable.

There are two ways we'll look at here:

1: The simple way:
C#
//The return datatable will look just like the input dt
DataTable result = dt.Clone();

//Loop through the list of anon items and populate the new table
myLinq.ToList().ForEach(a =>
{
    DataRow row = dt.NewRow();
    row["ID"] = a.ID;
    row["Status"] = a.Status;
    row["Date"] = a.Date;
    result.Rows.Add(row);
});

//return the new table
return result;


This is simple and easy to understand. The only issue is that it is VERY specific to this single case. The best way IMHO is to use Extension methods:

2: The best way:
C#
//The class must be public static
public static class Extensions
  {
      // the method must be public static and use "this [type] [name]" as the first parameter
      public static DataTable ToDataTable<T>(this IOrderedEnumerable<T> source)
      {
          //T could be misleading so return null
          if (source == null)
              return null;

          DataTable result = new DataTable();

          var properties = typeof(T).GetProperties().ToList();

          properties.ForEach(p =>
          {
              DataColumn column = new DataColumn(p.Name,p.PropertyType);
              result.Columns.Add(column);
          });

          //source is empty so return formated datatable
          if (!source.Any())
              return result;

          source.ToList().ForEach(s =>
          {
              DataRow row = result.NewRow();
              properties.ForEach(p =>
              {
                  row[p.Name] = p.GetValue(s);
              });
              result.Rows.Add(row);
          });

          return result;
      }
  }


Once we have our Extension method set, we can use it to convert ANY IOrderedEnumerable object to a datatable:

C#
//Make sure you have a "using" ref for the Extension class
DataTable result = myLinq.ToDataTable();
//return the new table
return result;
 
Share this answer
 
v4
Comments
Devraj Kapdi 28-Aug-15 4:46am    
Hello Andy i am getting this error :

Error 13 'System.DateTime' does not contain a definition for 'Select' and no extension method 'Select' accepting a first argument of type 'System.DateTime' could be found (are you missing a using directive or an assembly reference?)
Andy Lanng 28-Aug-15 4:51am    
hmm - ok - i'll test it and update soon ^_^
Andy Lanng 28-Aug-15 5:22am    
Got it working ^_^
Devraj Kapdi 31-Aug-15 3:41am    
Hello sir ,,, m getting "Specified cast is not valid". error while converting myLinq to datatable.
Andy Lanng 31-Aug-15 6:15am    
Odd, I don't cast myLing to datatable anywhere is my code.

It'll have to wait unit tomorrow, now. It's a bank holiday in the UK.

Maciej seems to have some good ideas, though
I would do that this way:

C#
var result = dt.AsEnumerable()
        .GroupBy(r=>new{ID = r.Field<int>("ID"), Day = r.Field<DateTime>("Date").Date.ToString("yyyy-MM-dd")})
        .Select(grp=>new
        {
            ID = grp.Key.ID,
            Day = grp.Key.Day,
            Date = grp.Max(d=>d.Field<DateTime>("Date")),
            Status = grp.Where(p=>p.Field<DateTime>("Date")==grp.Max(d=>d.Field<DateTime>("Date"))).Select(s=>s.Field<string>("Status")).FirstOrDefault()
        })
        .OrderBy(a=>a.ID)
        .ThenBy(b=>b.Date);

Result:
ID   Day        Date                Status
2706 2015-08-27 2015-08-27 14:33:42 N
2707 2015-08-27 2015-08-27 14:27:11 N
2710 2015-08-25 2015-08-25 17:07:01 Y
2710 2015-08-27 2015-08-27 14:27:07 N
2755 2015-08-25 2015-08-25 17:06:59 Y
2755 2015-08-26 2015-08-26 11:27:35 Y
2755 2015-08-27 2015-08-27 14:26:49 N
 
Share this answer
 
Comments
Devraj Kapdi 31-Aug-15 3:48am    
Hello Maciej ,
How can i get this result in datatable from "result" LINQ
Maciej Los 31-Aug-15 4:17am    
Sorry, i don't get you... ;(
If you want to "convert" result to datatable, use CopyToDataTable extension method.
Devraj Kapdi 31-Aug-15 4:30am    
hello ... and i didnt get the result it showing me Specified cast is not valid". error
Maciej Los 31-Aug-15 4:32am    
On which line?
Devraj Kapdi 31-Aug-15 4:32am    
this is the data table m getting from SQL

CNTM_CONTACT_ID | CNTM_ISACTIVE | ActiveDate
-------------------------------------------

2706 Y 2015-08-27 14:26:25.103
2706 N 2015-08-27 14:33:42.053
2707 Y 2015-08-27 14:26:22.227
2707 N 2015-08-27 14:27:11.000
2710 Y 2015-08-25 17:07:01.937
2710 N 2015-08-27 14:16:42.420
2710 Y 2015-08-27 14:18:56.347
2710 N 2015-08-27 14:27:07.753
2755 Y 2015-08-25 17:06:59.210
2755 N 2015-08-26 11:27:28.643
2755 Y 2015-08-26 11:27:35.143
2755 N 2015-08-27 14:16:39.917
2755 Y 2015-08-27 14:18:53.477
2755 N 2015-08-27 14:26:49.217



This is the LINQ i modified ....

var result = dtFinal.AsEnumerable()
.GroupBy(r => new { ID = r.Field<int>("CNTM_CONTACT_ID"), Day = r.Field<datetime>("ActiveDate").Date.ToString("yyyy-MM-dd") })
.Select(grp => new
{
ID = grp.Key.ID,
Day = grp.Key.Day,
Date = grp.Max(d => d.Field<datetime>("ActiveDate")),
Status = grp.Where(p => p.Field<datetime>("ActiveDate") == grp.Max(d => d.Field<datetime>("ActiveDate"))).Select(s => s.Field<string>("CNTM_ISACTIVE")).FirstOrDefault()
})
.OrderBy(c => c.ID)
.ThenBy(b => b.Date);


m not getting the result ...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900