locked
Rownumber and linq RRS feed

  • Question

  • Is there any way of using rownumber() type functionality in linq to entities queries. Essentially I would like to project into a named type and have "rownumber" incrementing for each row in the query?

    Monday, October 6, 2008 7:38 PM

Answers

  • This is not supported by LINQ to Entities.  Your best bet might be to set the rownumber as a post processing step after running your query.  One way to do this would be to use a LINQ to Objects query:

     

    int i = 0;

    var ordersAsList = (from o in context.Orders select o).ToList();

    var orders = from o in ordersAsList select new { Name = o.ShipName, RowNumber = i++ };

     

    Thanks,

    Arthur

    Tuesday, October 21, 2008 7:46 PM

All replies

  • This is not supported by LINQ to Entities.  Your best bet might be to set the rownumber as a post processing step after running your query.  One way to do this would be to use a LINQ to Objects query:

     

    int i = 0;

    var ordersAsList = (from o in context.Orders select o).ToList();

    var orders = from o in ordersAsList select new { Name = o.ShipName, RowNumber = i++ };

     

    Thanks,

    Arthur

    Tuesday, October 21, 2008 7:46 PM
  • Wondering if there is any new ability for LINQ to Entities to handle this since this was answered back in 08?  The reason I don't like the solution above is that my app is already fluent in passing around POCO entities.  If I use an anon type, I have to change a lot of plumbing throughout the app.  Any way to utilize an unmapped entity field and set the value there rather than use a different type (anonymous)?
    Wednesday, March 23, 2011 11:39 PM
  • One more option here.  I'm admitting it's a little hackish, but it will work for my purposes and thought I would share.  Since I wanted to work with my entities exclusively, and not project into anonymous or other types, I simply added a dummy field to the database for the entity type I wanted the ROWNUMBER type sequence on.  I made this column a nullable int in the database, then set the StoreGeneratedPattern property on the entity field to Computed.  That way, EF stays out of the way and doesn't try to update its value, but you can then iterate over the entities and set the value yourself on the client, and still use the entity.
    • Proposed as answer by JimJalinsky Thursday, March 24, 2011 12:24 AM
    Thursday, March 24, 2011 12:24 AM
  • Here's another option to consider, but it is still a two-step process. Instead of applying ToList() and using an external variable, you can also use AsEnumerable() and the index-overload of Select() to fake a row number:

    var ordersAsEnumerable = (from o in context.Orders select o).AsEnumerable().Select((order, index) => new { Order = order, RowNumber = index + 1 });

    This will return a sequence of anonymous objects containing an order in the Order member and the synthesized row number in the RowNumber member. The sequence will be implemented with deferred execution: the database query will be executed before the enumeration takes place, but the final results will be materialized and returned one row at a time.



    • Edited by Ed Eaglehouse Wednesday, January 4, 2017 4:50 PM clarify row number as "synthesized"
    Wednesday, January 4, 2017 3:51 PM