LINQ Line Number

Question

I have a linq request, for example:

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 the current row number in the collection returned by LINQ. How can I get the line number?

Answer 1

Line number is not supported in linq-to-entity. You must first get the records from the database without a line number, and then add the line number using linq-to-objects. Sort of:

 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 for objects has this built-in for any enumerator:

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

Change Although IQueryable also supports it ( here and here ) it was mentioned that this , unfortunately, does 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 (such as .Where ) with the additional load of the index parameter

Answer 3

Try using let as follows:

 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 + ": " + rI); } 

I cannot test it with real LINQ to SQL or Entity Framework right now. Please note that the above code will save the counter value between several query executions.

If this is not supported by your specific provider, you can always select foreach (thus forcing the request) and assign the number manually in the code.

Answer 4

Since the query inside the question is filtered by a single identifier, I think the answers received will not help. Of course, you can do all this on the client side of the memory, but depending on how large the data set is and whether the network is connected, this can be a problem.

If you need the SQL equivalent of ROW_NUMBER [..] OVER [..] , the only way I know is to create a view on your SQL server and query for it.

Answer 5

This is tested and works:

Change the code as follows:

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

Hope this helps. Although late :)