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 ?

Solution 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,
         });

Solution 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

Solution 3

Try using let like this:

int[] ints = new[] { 1, 2, 3, 4, 5 };
int counter = 0;
var result = from i in ints
             where i % 2 == 0
             let number = ++counter
             select new { I = i, Number = number };

foreach (var r in result)
{
    Console.WriteLine(r.Number + ": " + r.I);
}

I cannot test it with actual LINQ to SQL or Entity Framework right now. Note that the above code will retain the value of the counter between multiple executions of the query.

If this is not supported with your specific provider you can always foreach (thus forcing the execution of the query) and assign the number manually in code.

Comments

[sehe]

Why not use the LINQ builtin support?

[Magnus]

Don't think this will work with Linq to entities or Linq to SQL

[alex]

@Magnus: you're right, it's not available for LINQ to SQL or LINQ to EF.

[sehe]

@Magnus: why wouldn't it (IQueryable.Where), and have you tested it?

[sehe]

@alex: ok, that was a surprise to me. Sry about that, will flag warning in answer

[Stilgar]

Because I didn't know about it :) Thanks for pointing it out.

[Alok]

your solution doesnt work also it have mistake at recordIndex = counter++ should be recordIndex = counter++, and yet its not working tried entity and linqtosql