locked
LINQ DataTable Query RRS feed

  • Question

  • I've come up with various solutions but they all seem convoluted. I have a DataTable in a dataset that looks like
    Key
    EntityId
    TimeStamp
    Value

    where there will be multiple values per EntityId. I need the latest DataRow (based on Timestamp) for each (distinct) EntityId.

    Thanks!
    Thursday, October 29, 2009 3:01 PM

Answers

  • DataTable table = new DataTable();
    table.Columns.Add("Key", typeof (string));
    table.Columns.Add("EntityId", typeof (int));
    table.Columns.Add("TimeStamp", typeof (DateTime));
    table.Columns.Add("Value", typeof (string));

    table.LoadDataRow(new object[] {"Key1", 1, DateTime.Today, "Value1"}, true);
    table.LoadDataRow(new object[] { "Key2", 1, DateTime.Today.AddDays(-1), "Value2" }, true);
    table.LoadDataRow(new object[] { "Key3", 2, DateTime.Today.AddDays(-2), "Value3" }, true);
    table.LoadDataRow(new object[] { "Key4", 2, DateTime.Today.AddDays(-3), "Value4" }, true);
    table.LoadDataRow(new object[] { "Key5", 3, DateTime.Today.AddDays(-4), "Value5" }, true);
    table.LoadDataRow(new object[] { "Key6", 3, DateTime.Today.AddDays(-5), "Value6" }, true);
    table.LoadDataRow(new object[] { "Key7", 4, DateTime.Today.AddDays(-6), "Value7" }, true);
    table.LoadDataRow(new object[] { "Key8", 4, DateTime.Today.AddDays(-7), "Value8" }, true);
    table.LoadDataRow(new object[] { "Key9", 5, DateTime.Today.AddDays(-8), "Value9" }, true);
    table.LoadDataRow(new object[] { "Key10", 5, DateTime.Today.AddDays(-9), "Value10" }, true);
    table.LoadDataRow(new object[] { "Key11", 6, DateTime.Today.AddDays(-10), "Value11" }, true);
    table.LoadDataRow(new object[] { "Key12", 6, DateTime.Today.AddDays(-11), "Value12" }, true);


    var rows = from row in table.AsEnumerable()
               orderby row["TimeStamp"] descending
               group row by row["EntityId"]
               into groups
               select groups.First();

    foreach (var row in rows)
        Console.WriteLine(row["Key"]);


    Coding Light - Illuminated Ideas and Algorithms in Software
    Coding Light WikiLinkedInForumsBrowser
    • Marked as answer by holtd Thursday, October 29, 2009 3:44 PM
    Thursday, October 29, 2009 3:11 PM
    Moderator

All replies

  • DataTable table = new DataTable();
    table.Columns.Add("Key", typeof (string));
    table.Columns.Add("EntityId", typeof (int));
    table.Columns.Add("TimeStamp", typeof (DateTime));
    table.Columns.Add("Value", typeof (string));

    table.LoadDataRow(new object[] {"Key1", 1, DateTime.Today, "Value1"}, true);
    table.LoadDataRow(new object[] { "Key2", 1, DateTime.Today.AddDays(-1), "Value2" }, true);
    table.LoadDataRow(new object[] { "Key3", 2, DateTime.Today.AddDays(-2), "Value3" }, true);
    table.LoadDataRow(new object[] { "Key4", 2, DateTime.Today.AddDays(-3), "Value4" }, true);
    table.LoadDataRow(new object[] { "Key5", 3, DateTime.Today.AddDays(-4), "Value5" }, true);
    table.LoadDataRow(new object[] { "Key6", 3, DateTime.Today.AddDays(-5), "Value6" }, true);
    table.LoadDataRow(new object[] { "Key7", 4, DateTime.Today.AddDays(-6), "Value7" }, true);
    table.LoadDataRow(new object[] { "Key8", 4, DateTime.Today.AddDays(-7), "Value8" }, true);
    table.LoadDataRow(new object[] { "Key9", 5, DateTime.Today.AddDays(-8), "Value9" }, true);
    table.LoadDataRow(new object[] { "Key10", 5, DateTime.Today.AddDays(-9), "Value10" }, true);
    table.LoadDataRow(new object[] { "Key11", 6, DateTime.Today.AddDays(-10), "Value11" }, true);
    table.LoadDataRow(new object[] { "Key12", 6, DateTime.Today.AddDays(-11), "Value12" }, true);


    var rows = from row in table.AsEnumerable()
               orderby row["TimeStamp"] descending
               group row by row["EntityId"]
               into groups
               select groups.First();

    foreach (var row in rows)
        Console.WriteLine(row["Key"]);


    Coding Light - Illuminated Ideas and Algorithms in Software
    Coding Light WikiLinkedInForumsBrowser
    • Marked as answer by holtd Thursday, October 29, 2009 3:44 PM
    Thursday, October 29, 2009 3:11 PM
    Moderator
  • David,
    Are you using 'non-strong-typing' because an in memory datatable does not support 'strong-typing'?

    My question is due to, I have a project where I am reading a text file into a datatable, which I've created in my dataset designer. However, my LINQ's don't recognize any of the column names. Is it because it's NOT connected to a 'database(SQL-sever/Access)'?

    I have to use a text, because it needs to be editable by the user. I'm wanting to load it into a sql-type table in order to extract the data easier.


    Rick
    Wednesday, December 30, 2009 10:04 PM