Invalid Sql with RowNumberPaging when projecting multiple owned entities

[SharpReact]

Query to model with single owned type or query for select only one owned type working correct, but when you make query to model with multiply owned types, generated sql query has invalid indexes.

Steps to reproduce

We have a model like

public partial class Program
{
	public int Id { get; set; }

	public int Priority { get; set; }

	public string Slug { get; set; }

	public LocalizedString Name { get; set; }
	public LocalizedString Description { get; set; }

	public Date Date { get; set; }
	public Options Options { get; set; }


	public Status Status { get; set; }

	public LanguageType Language { get; set; }
}

which contais 3 owned types

[Owned]
public class Date
{
	public DateTime Created { get; set; }
	public DateTime Modified { get; set; }
	public DateTime Published { get; set; }
}

[Owned]
public class Options
{
	public bool BlockMobile { get; set; }
	public bool BlockAds { get; set; }
	public bool BlockComments { get; set; }
}


[Owned]
public class LocalizedString
{
	public string RU { get; set; }
	public string KZ { get; set; }
}

We get top 20 programs from context

var model = _context.Programs
	.Select(p => new
	{
		p.Id,
		p.Priority,
		p.Slug,
		p.Status,
		p.Language,
		p.Date,
	})
	.Take(20);

We go to the Sql Server Prifiler and can see this request, generated by ef core:

SELECT TOP(@__p_0) 
	[p].[Id],
	[p].[Date_Created],
	[p].[Date_Modified],
	[p].[Date_Published],
	[p].[Id] AS [Id0],
	[p].[Priority],
	[p].[Slug],
	[p].[Status],
	[p].[Language]
FROM [Programs] AS [p]

ok, it's correct.
Now let's add more owed type into select expression.

var model = _context.Programs
	.Select(p => new
	{
		p.Id,
		p.Priority,
		p.Slug,
		p.Status,
		p.Language,
		p.Name,
		p.Description,
		p.Date,
		p.Options,
	})
	.Take(20);

Sql request in profiler

SELECT TOP(@__p_0) 
	[p].[Id],
	[p].[Options_BlockAds],
	[p].[Options_BlockComments],
	[p].[Options_BlockMobile],
	[p].[Id],
	[p].[Date_Created],
	[p].[Date_Modified],
	[p].[Date_Published],
	[p].[Id],
	[p].[Description_KZ],
	[p].[Description_RU],
	[p].[Id],
	[p].[Name_KZ],
	[p].[Name_RU],
	[p].[Id] AS [Id0],
	[p].[Priority],
	[p].[Slug],
	[p].[Status],
	[p].[Language]
FROM [Programs] AS [p]

The [p].[Id], repeat multiply times. In simply request it's not make some exceptions but if we use Sql Server 2008R2, we can have many troubles.

services.AddDbContext<Data.Context>(options => options.UseSqlServer(Configuration.GetConnectionString("Connection"), opt => opt.UseRowNumberForPaging()));

Make select operation with offset

var model = _context.Programs
	.Select(p => new
	{
		p.Id,
		p.Priority,
		p.Slug,
		p.Status,
		p.Language,
		p.Name,
		p.Description,
		p.Date,
		p.Options,
	})
	.Skip(20);
	.Take(20);

Sql request in profiler

SELECT 
	[t].[Id1],
	[t].[Options_BlockAds],
	[t].[Options_BlockComments],
	[t].[Options_BlockMobile],
	[t].[Id],
	[t].[Date_Created],
	[t].[Date_Modified],
	[t].[Date_Published],
	[t].[Id0],
	[t].[Description_KZ],
	[t].[Description_RU],
	[t].[Name_KZ],
	[t].[Name_RU],
	[t].[Priority],
	[t].[Slug],
	[t].[Status],
	[t].[Language]
FROM (
    SELECT 
		[p].[Id] AS [Id1],
		[p].[Options_BlockAds],
		[p].[Options_BlockComments],
		[p].[Options_BlockMobile],
		[p].[Id],
		[p].[Date_Created],
		[p].[Date_Modified],
		[p].[Date_Published],
		[p].[Id] AS [Id0],
		[p].[Description_KZ],
		[p].[Description_RU],
		[p].[Id] AS [Id1],
		[p].[Name_KZ],
		[p].[Name_RU],
		[p].[Id] AS [Id0],
		[p].[Priority],
		[p].[Slug],
		[p].[Status],
		[p].[Language],
		ROW_NUMBER() OVER(ORDER BY @@RowCount) AS [__RowNumber__]
	FROM [Programs] AS [p]
) AS [t]
WHERE ([t].[__RowNumber__] > @__p_0) AND ([t].[__RowNumber__] <= (@__p_0 + @__p_1))

And as result we have Exception

System.Data.SqlClient.SqlException (0x80131904): The column 'Id1' was specified multiple times for 't'.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.BufferlessMoveNext(DbContext _, Boolean buffer)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider._TrackEntities[TOut,TIn](IEnumerable`1 results, QueryContext queryContext, IList`1 entityTrackingInfos, IList`1 entityAccessors)+MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
   at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeList(JsonWriter writer, IEnumerable values, JsonArrayContract contract, JsonProperty member, JsonContainerContract collectionContract, JsonProperty containerProperty)
   at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeValue(JsonWriter writer, Object value, JsonContract valueContract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerProperty)
   at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.Serialize(JsonWriter jsonWriter, Object value, Type objectType)
   at Newtonsoft.Json.JsonSerializer.SerializeInternal(JsonWriter jsonWriter, Object value, Type objectType)
   at Newtonsoft.Json.JsonSerializer.Serialize(JsonWriter jsonWriter, Object value)
   at Microsoft.AspNetCore.Mvc.Formatters.JsonOutputFormatter.WriteObject(TextWriter writer, Object value)
   at Microsoft.AspNetCore.Mvc.Formatters.JsonOutputFormatter.WriteResponseBodyAsync(OutputFormatterWriteContext context, Encoding selectedEncoding)
   at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeResultAsync(IActionResult result)
   at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeNextResultFilterAsync[TFilter,TFilterAsync]()
   at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.Rethrow(ResultExecutedContext context)
   at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.ResultNext[TFilter,TFilterAsync](State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeResultFilters()
   at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeNextResourceFilter()
   at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.Rethrow(ResourceExecutedContext context)
   at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeFilterPipelineAsync()
   at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeAsync()
   at Microsoft.AspNetCore.Builder.RouterMiddleware.Invoke(HttpContext httpContext)
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.StaticFiles.StaticFileMiddleware.Invoke(HttpContext context)
   at NSwag.AspNetCore.Middlewares.SwaggerUiIndexMiddleware`1.Invoke(HttpContext context)
   at NSwag.AspNetCore.Middlewares.RedirectMiddleware.Invoke(HttpContext context)
   at NSwag.AspNetCore.Middlewares.WebApiToSwaggerMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Server.Kestrel.Core.Internal.Http.HttpProtocol.ProcessRequests[TContext](IHttpApplication`1 application)

--

Further technical details

EF Core version: 2.1.1
Database Provider: MS SQL SERVER
Operating system: WINDOWS SERVER 2012R2
IDE: (e.g. Visual Studio 2017 15.4)

[ajcvickers]

@SharpReact Could you please comment on issue #13959 and let us know why you are using RowNumberForPaging?

[ajcvickers]

Closing old issue as this is no longer something we intend to implement.