Numbering the rows per group after an orderby in LINQ

Say you have columns AppleType, CreationDate and want to order each group of AppleType by CreationDate. Furthermore, you want to create a new column which explicitly ranks the order of the CreationDate per AppleType.

So, the resulting DataSet would have three columns, AppleType, CreationDate, OrderIntroduced.

Is there a开发者_运维知识库 LINQ way of doing this? Would I have to actually go through the data programmatically (but not via LINQ), create an array, convert that to a column and add to the DataSet? I have there is a LINQ way of doing this. Please use LINQ non-method syntax if possible.

So are the values actually appearing in the right order? If so, it's easy - but you do need to use method syntax, as the query expression syntax doesn't support the relevant overload:

var queryWithIndex = queryWithoutIndex.Select((x, index) => new
                                              {
                                                  x.AppleType,
                                                  x.CreationDate,
                                                  OrderIntroduced = index + 1,
                                              });

(That's assuming you want OrderIntroduced starting at 1.)

I don't know offhand how you'd then put that back into a DataSet - but do you really need it in a DataSet as opposed to in the strongly-typed sequence?

EDIT: Okay, the requirements are still unclear, but I think you want something like:

var query = dataSource.GroupBy(x => x.AppleType)
     .SelectMany(g => g.OrderBy(x => x.CreationDate)
                       .Select((x, index ) => new {
                               x.AppleType,
                               x.CreationDate,
                               OrderIntroduced = index + 1 }));

Note: The GroupBy and SelectMany calls here can be put in query expression syntax, but I believe it would make it more messy in this case. It's worth being comfortable with both forms.

If you want a pure Linq to Entities/SQL solution you can do something like this:

Modified to handle duplicate CreationDate's

var query = from a in context.AppleGroup
            orderby a.CreationDate
            select new
            {
              AppleType = a.AppleType,
              CreationDate = a.CreationDate,
              OrderIntroduced = (from b in context.AppleGroup
                                 where b.CreationDate < a.CreationDate
                                 select b).Count() + 1
            };