CODEWITHSUNDEEP
c#entity-framework
corix010
corix010

Reputation: 571

Select Latest Item for Each Type

This is based on a previous question by Todilo.

The following accepted answer works great except I am required to return all records where type is null in addition to the latest for each type:

var query = Posts.GroupBy(p => p.Type)
              .Select(g => g.OrderByDescending(p => p.Date)
                            .FirstOrDefault()
               )

The scenario is as follows:

+----+--------------------------+-------+------------+
| id |         content          | type  |    date    |
+----+--------------------------+-------+------------+
|  0 | Some text                | TypeA | 2013-04-01 |
|  1 | Some older text          | TypeA | 2012-03-01 |
|  2 | Some even older texttext | TypeA | 2011-01-01 |
|  3 | Sample                   |       | 2013-02-24 |
|  4 | A dog                    | TypeB | 2013-04-01 |
|  5 | And older dog            | TypeB | 2012-03-01 |
|  6 | An even older dog        | TypeB | 2011-01-01 |
|  7 | Another sample           |       | 2014-03-06 |
|  8 | Test                     |       | 2015-11-08 |
+----+--------------------------+-------+------------+

The result should be

Some text      | TypeA
Sample         |
A dog          | TypeB
Another sample |
Test           |

Upvotes: 1

Views: 128

Answers (3)

code4life
code4life

Reputation: 15794

I'm thinking that a LINQ union is the only way to do it, primarily because of how you are sorting the output by date.

The first query should look like this:

var nullTypes = from p in Posts
                     where p.Type == null
                     select p;

The primary query should just filter out the nulls:

var query = Posts.Where(p => p.Type != null).GroupBy(p => p.Type)
          .Select(g => g.OrderByDescending(p => p.Date)
                        .FirstOrDefault()
           )

Union to your primary query:

var unionQuery = query.Union(nullTypes).OrderBy(p => p.Date);

The output will match what you are expecting, only that the first two lines will be inverted in terms of order:

Sample         |
Some text      | TypeA
A dog          | TypeB
Another sample |
Test           |

This is because the line item containing "Sample" has an older date than "Some text".

You shouldn't need to do any SelectMany - the original Select will projects a single item, so this works out into an IEnumerable that can fit the union.

Upvotes: 1

jdweng
jdweng

Reputation: 34421

Try code below. Because of the grouping the order isn't the same

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;


namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {

            DataTable dt = new DataTable();
            dt.Columns.Add("id", typeof(int));
            dt.Columns.Add("content", typeof(string));
            dt.Columns.Add("type", typeof(string));
            dt.Columns["type"].AllowDBNull = true;
            dt.Columns.Add("date", typeof(DateTime));

            dt.Rows.Add(new object[] { 0, "Some text", "TypeA", DateTime.Parse("2013-04-01")});
            dt.Rows.Add(new object[] { 1, "Some older text", "TypeA", DateTime.Parse("2012-03-01")});
            dt.Rows.Add(new object[] { 2, "Some older texttext", "TypeA", DateTime.Parse("2011-01-01")});
            dt.Rows.Add(new object[] { 3, "Sample", null, DateTime.Parse("2013-02-24")});
            dt.Rows.Add(new object[] { 3, "A dog", "TypeB", DateTime.Parse("2013-04-01")});
            dt.Rows.Add(new object[] { 4, "And older dog", "TypeB", DateTime.Parse("2012-03-01")});
            dt.Rows.Add(new object[] { 5, "An even older dog", "TypeB", DateTime.Parse("2011-01-01")});
            dt.Rows.Add(new object[] { 4, "Another sample", null, DateTime.Parse("2014-03-06")});
            dt.Rows.Add(new object[] { 5, "Test", null, DateTime.Parse("2015-11-08")});

            var results = dt.AsEnumerable()
                .GroupBy(x => x.Field<string>("type"))
                .Select(x => x.Key == null ? x.ToList() : x.Select(y => new {date = y.Field<DateTime>("date"), row = y}).OrderByDescending(z => z.date).Select(a => a.row).Take(1))
                .SelectMany(b => b).Select(c => new {
                    content = c.Field<string>("content"), 
                    type = c.Field<string>("type") 
                }).ToList();


        }
    }
}
​

Upvotes: 1

Sebastian Schumann
Sebastian Schumann

Reputation: 3446

What about that:

var query = Posts
              .GroupBy(p => p.Type)
              .Select(g => g.OrderByDescending(p => p.Date).FirstOrDefault()).ToList()
var lostNullItems = Posts.Where(p => p.Type == null && !query.Contains(p));
var newQuery = query.Union(lostNullItems);

If you don't need the order of the items you can use:

var query = Posts
              .GroupBy(p => p.Type)
              .SelectMany(g => 
                   {
                      var result = g.OrderByDescending(p => p.Date);
                      return g.Key == null ? result ? Enumerable.Repeat(result.First(), 1);
                   });

This code is not tested.

Upvotes: 2

Related Questions