Invalid Column Name when using Skip and Take on working query

Question

Invalid Column Name when using Skip and Take on working query

Billy Jacobs 6

I have the following ef core 5.0 query. It works as expected until I add the last line which adds a skip and take. With this last line it throws a Microsoft.Data.SqlClient.SqlException: 'Invalid column name ... specifying numerous columns.

I did a bit more investigating and the issue is related to the lines that do the following:

If I remove the .Take(1) on those lines then it works. Any help would be greatly appreciated.

Daniel Zhang-MSFT 9,651 Reputation points

2021-07-29T01:21:03.813+00:00

Hi @Billy Jacobs ,
If you convert the result before adding the last row into a list, then do skip and take on the list still have errors?
Best Regards,
Daniel Zhang
Billy Jacobs 6 Reputation points

2021-07-29T14:33:35.63+00:00

Are you saying to execute the query first by doing a ToList(), for example, and then do a skip and take?

Wouldn't that defeat the purpose of adding the skip and take so that it occurs at the db query level instead of fetching all the records and then doing a skip and take? Or maybe I am misunderstanding what you are asking?

However if that IS what you are asking then there is no error in that case. However, it returns all the results from the db instead of the limited number I want. The primary reason I am adding this is to improve performance.

NOTE: I have worked around this issue by removing the take in the subquery, but I was curious as to why this behavior occurs when there is a take on an included list and also a take on the entire list.

Thanks
Daniel Zhang-MSFT 9,651 Reputation points

2021-07-30T07:42:35.523+00:00

Hi @Billy Jacobs ,
Operations such as Skip, Take, etc. are applied to the collection navigation in the lambda passed to the Include method.
And I sugges you follow this document to check your eager loading.
Best Regards,
Daniel Zhang

Your answer

Daniel Zhang-MSFT 9,651 Reputation points

2021-07-29T01:21:03.813+00:00

Hi @Billy Jacobs ,
If you convert the result before adding the last row into a list, then do skip and take on the list still have errors?
Best Regards,
Daniel Zhang
Billy Jacobs 6 Reputation points

2021-07-29T14:33:35.63+00:00

Are you saying to execute the query first by doing a ToList(), for example, and then do a skip and take?

Wouldn't that defeat the purpose of adding the skip and take so that it occurs at the db query level instead of fetching all the records and then doing a skip and take? Or maybe I am misunderstanding what you are asking?

However if that IS what you are asking then there is no error in that case. However, it returns all the results from the db instead of the limited number I want. The primary reason I am adding this is to improve performance.

NOTE: I have worked around this issue by removing the take in the subquery, but I was curious as to why this behavior occurs when there is a take on an included list and also a take on the entire list.

Thanks
Daniel Zhang-MSFT 9,651 Reputation points

2021-07-30T07:42:35.523+00:00

Hi @Billy Jacobs ,
Operations such as Skip, Take, etc. are applied to the collection navigation in the lambda passed to the Include method.
And I sugges you follow this document to check your eager loading.
Best Regards,
Daniel Zhang

Share via

Invalid Column Name when using Skip and Take on working query

Your answer