LINQ increment value doesn't work

Question

I have this Linq

var company = db.Companies.Where(x => x.id == Id).Select(x => new Company
        {
            Id = x.id,
            Description = x.Description,
            Website = x.Website,
            Zip = x.Zip,
            Actions = db.Actions.Where(a => a.UserCompanyId == Id).Select((a, index) => new CompanyActions
            {
                Id = index + 1,
                Name = a.Name,
                Duration = (int)a.Duration
            }).ToList()
        })
            .FirstOrDefault();

I want to get Id of Actions - 1,2,3,4,5,6,7,8......

But in this case index doesn't work.

Error - + $exception {"LINQ to Entities does not recognize the method 'System.Linq.IQueryable1 Select(System.Linq.IQueryable1, System.Linq.Expressions.Expression1[System.Func3])' method, and this method cannot be translated into a store expression."} System.NotSupportedException

All question is how can I increment (+1) Id in Actions? Want to see 1- Name 2 - Name 3-Name

this work fine

  var company = db.Companies.Where(x => x.id == Id).AsEnumerable().Select(x => new Company
        {
            Id = x.id,
            Description = x.Description,
            Website = x.Website,
            Zip = x.Zip,
            Actions = db.Actions.Where(a => a.UserCompanyId == Id).AsEnumerable().Select((a, index) => new CompanyActions
            {
                Id = index + 1,
                Name = a.Name,
                Duration = (int)a.Duration
            }).ToList()
        })
            .FirstOrDefault();

Related: https://stackoverflow.com/questions/7259567/linq-to-entities-does-not-recognize-the-method — Hans Keﬆing, Mar 09 '18 at 14:01
you are trying to convert `db.Actions.Where` into a delegate(?), which is what I assume Actions will hold? That is really funky. What is it that you are trying to do? — , Mar 09 '18 at 14:02
**Auto increment id's must be managed by the database** or else multiple software will generate duplicated id's (which results in errors) — Jeroen van Langen, Mar 09 '18 at 14:07
LINQ to Entities queries are translated to SQL. You can't use expressions with no SQL equivalent. The [Enumerable.Select((IEnumerable, Func)](https://msdn.microsoft.com/en-us/library/bb534869(v=vs.110).aspx) method *after* you load the data, eg `var items=query.AsEnumerable().Select((item,idx)=>...)` — Panagiotis Kanavos, Mar 09 '18 at 14:08
@OlegLyahovetskyi you can't use it this way. The expression you wrote can't be translated to SQL. Load the data first with `AsEnumerable()` or `ToArray()`. — Panagiotis Kanavos, Mar 09 '18 at 14:12
@OlegLyahovetskyi *ranking* in SQL Server is provided by functions like ROW_NUMBER(), RANK(), DENSE_RANK() that require an ordering and optionally partitioning clause. Only the functions exposed by [EntityFunctions](https://msdn.microsoft.com/en-us/library/system.data.objects.entityfunctions.aspx) can be called in a LINQ to entities query. No ranking functions are included — Panagiotis Kanavos, Mar 09 '18 at 14:16
Yes. AsEnumerable() - it's work. I add AsEnumerable() - db.Companies.Where(x => x.id == Id).AsEnumerable() and db.Actions.Where(a => a.UserCompanyId == Id).AsEnumerable() and all If fine. Thank's to Panagiotis Kanavos and Kilazur — ewqewqewqe, Mar 09 '18 at 14:21
@OlegLyahovetskyi it looks like you are trying to use your *data model* as your *display/viewmodel*. That only works in demos, never in real life. Specify proper relations between the Company and Action DTOs so you *don't* have to use joins - that's what your inner SELECT actually does. Then map your DTOs to CompanyViewModel, ActionViewModel objects adding the row index you want to the actions. — Panagiotis Kanavos, Mar 09 '18 at 14:21
@OlegLyahovetskyi before you do that check the previous comment. You probably just added an `N+1` bug - you execute 1 extra query per company — Panagiotis Kanavos, Mar 09 '18 at 14:22
@OlegLyahovetskyi besides various UI controls allow you to specify row numbers as a rendering property or calculated column (for grids) — Panagiotis Kanavos, Mar 09 '18 at 14:24
I using MVC WebGrid. It's looks like there is no row number. — ewqewqewqe, Mar 09 '18 at 14:27
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/166542/discussion-between-oleg-lyahovetskyi-and-panagiotis-kanavos). — ewqewqewqe, Mar 09 '18 at 14:28

Kilazur · Accepted Answer · 2018-12-19T15:53:50.717

1

The exception is pretty clear: you're using a method in your query that is not recognized by Entity.

Entity framework has to translate your query for the database; but a database, typically running on SQL, doesn't understand the more complicated methods, like Select (which is by no mean equivalent to a SQL SELECT).

So at start, you're using Linq-to-Sql, but afterwards you want to use Linq-to-Objects.

To do so, you have to convert your first part to an enumerable, then continue on with your query.

Something like:

db.Companies.Where(x => x.id == Id).AsEnumerable().Select(x => new Company... //etc

edited Dec 19 '18 at 15:53

answered Mar 09 '18 at 14:03

Kilazur

3,089
1
22
48

1

The problem with this is that *in this context* it would result in N+1 queries. The OP is probably using the DTOs as ViewModel objects. A better idea would be to *separate* them, load companies and actions DTOs *without* the implicit join and map the DTOs to display objects with row numbers, emojis whatever – Panagiotis Kanavos Mar 09 '18 at 14:23
@Panagiotis This is totally correct; but I think it's outside the scope of the question. Food for thoughts though, that OP should indeed read about. – Kilazur Mar 09 '18 at 14:29

score 0 · Answer 2 · answered Mar 09 '18 at 14:02

0

You need to move the counter increment outside the LINQ expression because it does not support on-the-fly calculations and/or some string operations.

try adding to the end of your query:

.Select( new { Id = Id++, /* plus the rest of your columns */ });

answered Mar 09 '18 at 14:02

Ted

3,985
1
20
33

I think Id is an identity of the column. If so, you are just incrementing Id to 1. – Win Mar 09 '18 at 14:03
@Win - yea, not sure what the OP is trying to accomplish ... – Ted Mar 09 '18 at 14:04

LINQ increment value doesn't work

2 Answers2