Row number in LINQ

Question

I have a linq query like this:

var accounts =
    from account in context.Accounts
    from guranteer in account.Gurantors
     where guranteer.GuarantorRegistryId == guranteerRegistryId
    select new AccountsReport
    {
        recordIndex = ?   
        CreditRegistryId = account.CreditRegistryId,
        AccountNumber = account.AccountNo,
    }

I want to populate recordIndex with the value of current row number in collection returned by the LINQ. How can I get row number ?

Answer 1

Row number is not supported in linq-to-entities. You must first retrieve records from database without row number and then add row number by linq-to-objects. Something like:

var accounts =
    (from account in context.Accounts
     from guranteer in account.Gurantors
         where guranteer.GuarantorRegistryId == guranteerRegistryId
     select new
         {  
             CreditRegistryId = account.CreditRegistryId,
             AccountNumber = account.AccountNo,
         })
    .AsEnumerable()  // Moving to linq-to-objects 
    .Select((r, i) => new AccountReport
         {
             RecordIndex = i,  
             CreditRegistryId = r.CreditRegistryId,
             AccountNumber = r.AccountNo,
         });

Answer 2

LINQ to objects has this builtin for any enumerator:

http://weblogs.asp.net/fmarguerie/archive/2008/11/10/using-the-select-linq-query-operator-with-indexes.aspx

Edit: Although IQueryable supports it too (here and here) it has been mentioned that this does unfortunately not work for LINQ to SQL/Entities.

new []{"aap", "noot", "mies"}
    .Select( (element, index) => new { element, index });                                  

Will result in:

{ { element = aap, index = 0 }, 
  { element = noot, index = 1 }, 
  { element = mies, index = 2 } }

There are other LINQ Extension methods (like .Where) with the extra index parameter overload