multiple identical primary key columns in the select result after use OwnsOne

[xyz91]

Describe what is not working as expected.

If you are seeing an exception, include the full exceptions details (message and stack trace).

Exception message:多次为 't' 指定了列 'Id0'。
Stack trace:
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 System.Linq.Enumerable.SelectEnumerableIterator`2.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
   at System.Collections.Generic.List`1.AddEnumerable(IEnumerable`1 enumerable)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at TCenter.SelfTrade.Infrastructure.Repositorys.BizOfferRepository.GetPageList[S](Int32 pageIndex, Int32 pageSize, Expression`1 where, Expression`1 orderBy, Boolean ascending) 

SQL created by EF is
exec sp_executesql N'SELECT [t].[Id0], [t].[CostPrice], [t].[CreatedDate], [t].[DetailContent], [t].[Feature], [t].[Images], [t].[IsDeleted], [t].[IsSalesPromotion], [t].[LastModified], [t].[OriginalPrice], [t].[PublishStatus], [t].[SKUId], [t].[SPUId], [t].[SaleNum], [t].[SalePrice], [t].[ShippingType], [t].[Tags], [t].[Title], [t].[ViewNum], [t].[Id], [t].[Supplier_SupplierGoodsId], [t].[Supplier_UserId], [t].[Supplier_UserName], [t].[CategoryInfo_BizCategoryId], [t].[CategoryInfo_BizCategoryName], [t].[CategoryInfo_GameAreaId], [t].[CategoryInfo_GameAreaName], [t].[CategoryInfo_GameId], [t].[CategoryInfo_GameName], [t].[CategoryInfo_GameServerId], [t].[CategoryInfo_GameServerName]
FROM (
SELECT [p].[Id] AS [Id0], [p].[CostPrice], [p].[CreatedDate], [p].[DetailContent], [p].[Feature], [p].[Images], [p].[IsDeleted], [p].[IsSalesPromotion], [p].[LastModified], [p].[OriginalPrice], [p].[PublishStatus], [p].[SKUId], [p].[SPUId], [p].[SaleNum], [p].[SalePrice], [p].[ShippingType], [p].[Tags], [p].[Title], [p].[ViewNum], [p].[Id], [p].[Supplier_SupplierGoodsId], [p].[Supplier_UserId], [p].[Supplier_UserName], [p].[Id] AS [Id0], [p].[CategoryInfo_BizCategoryId], [p].[CategoryInfo_BizCategoryName], [p].[CategoryInfo_GameAreaId], [p].[CategoryInfo_GameAreaName], [p].[CategoryInfo_GameId], [p].[CategoryInfo_GameName], [p].[CategoryInfo_GameServerId], [p].[CategoryInfo_GameServerName], ROW_NUMBER() OVER(ORDER BY @@rowcount) AS [RowNumber]
FROM [Goods] AS [p]
WHERE ([p].[IsDeleted] = 0)
) AS [t]
WHERE ([t].[RowNumber] > @__p_0) AND ([t].[RowNumber] <= (@__p_0 + @__p_1))',N'@__p_0 int,@__p_1 int',@__p_0=0,@__p_1=10

public class Goods{
public string Id {get;set;}
public Category CategoryInfo { get; set; }
public Supplier Supplier { get; set; }
}
public class Category{
public string GameId { get; set; }
}
public class Supplier{
public string UserId { get; set; }
}
public class BizOfferItemMap : IEntityTypeConfiguration<Goods>{
public void Configure(EntityTypeBuilder<Goods> builder){
builder.ToTable("Goods");
builder.HasKey(k => k.Id);
builder.OwnsOne(o => o.CategoryInfo);
builder.OwnsOne(o => o.Supplier);
}
}
public class BizOfferRepository{
public PagedResult<Goods> GetPageList<S>(int pageIndex, int pageSize, Expression<Func<Goods, bool>> where, Expression<Func<Goods, S>> orderBy, bool ascending = false)
        {
            pageIndex = pageIndex <= 0 ? 1 : pageIndex;
            pageSize = pageSize <= 0 ? 10 : pageSize;
            where = where ?? (t => true);

            int count = AppContext.Set<Goods>().AsNoTracking().Where(where).Count();

            var query = AppContext.Set<Goods>().AsNoTracking().Where(where);
            if (orderBy != null)
            {
                query = ascending ? query.OrderBy(orderBy) : query.OrderByDescending(orderBy);
            }
            query = query.Skip((pageIndex - 1) * pageSize).Take(pageSize);

            return new PagedResult<Goods>(pageIndex, pageSize, count, query.ToList());

        }
}

Further technical details

EF Core version: 2.1.4
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: windows server 2016
IDE: Visual Studio 2017 15.9.2

[ajcvickers]

@xyz91 I have not been able to reproduce this; my test code is below. Can you post a runnable project/solution or complete code listing like that below that demonstrates the behavior you are seeing.

public class Goods
{
    public string Id { get; set; }
    public Category CategoryInfo { get; set; }
    public Supplier Supplier { get; set; }
}

public class Category
{
    public string GameId { get; set; }
}

public class Supplier
{
    public string UserId { get; set; }
}

public class BizOfferItemMap : IEntityTypeConfiguration<Goods>
{
    public void Configure(EntityTypeBuilder<Goods> builder)
    {
        builder.ToTable("Goods");
        builder.HasKey(k => k.Id);
        builder.OwnsOne(o => o.CategoryInfo);
        builder.OwnsOne(o => o.Supplier);
    }
}
public class BizOfferRepository
{
    public List<Goods> GetPageList<S>(
        BloggingContext context,
        int pageIndex, 
        int pageSize, 
        Expression<Func<Goods, bool>> where, 
        Expression<Func<Goods, S>> orderBy, 
        bool ascending = false)
    {
        pageIndex = pageIndex <= 0 ? 1 : pageIndex;
        pageSize = pageSize <= 0 ? 10 : pageSize;
        where = where ?? (t => true);

        int count = context.Set<Goods>().AsNoTracking().Where(where).Count();

        var query = context.Set<Goods>().AsNoTracking().Where(where);
        if (orderBy != null)
        {
            query = ascending ? query.OrderBy(orderBy) : query.OrderByDescending(orderBy);
        }
        query = query.Skip((pageIndex - 1) * pageSize).Take(pageSize);

        return query.ToList();

    }
}
public class BloggingContext : DbContext
{
    private static readonly LoggerFactory Logger
        = new LoggerFactory(new[] { new ConsoleLoggerProvider((_, __) => true, true) });

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            .UseLoggerFactory(Logger)
            .EnableSensitiveDataLogging()
            .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Test;ConnectRetryCount=0");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.ApplyConfiguration(new BizOfferItemMap());
    }
}

public class Program
{
    public static void Main()
    {
        using (var context = new BloggingContext())
        {
            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();

            var results
                = new BizOfferRepository().GetPageList(
                    context,
                    1,
                    20,
                    g => g.Id == "One",
                    g => g.Id,
                    ascending: false);
        }
    }
}

[xyz91]

Sorry, I'm late for reply，No errors because of use fetch next ,This error occurs only when rownumber is used，Here's my error demo.The point of error is to use UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Test;ConnectRetryCount=0", b => b.UseRowNumberForPaging())

`namespace OwnsOneErrorDemo
{

class Program
{
    static void Main(string[] args)
    {
        using (var context = new BloggingContext())
        {
            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();

            var results
                = new BizOfferRepository().GetPageList(
                    context,
                    1,
                    20,
                    g => true,
                    g => g.CreateTime,
                    ascending: false);
        }
        Console.ReadKey();
    }
}


public class Goods
{

    public int Id { get; set; }
    public Category CategoryInfo { get; set; }
    public Supplier Supplier { get; set; }
    public DateTime CreateTime { get; set; }
}

public class Category
{
    public string GameId { get; set; }
}

public class Supplier
{
    public string UserId { get; set; }
}

public class BizOfferItemMap : IEntityTypeConfiguration<Goods>
{
    public void Configure(EntityTypeBuilder<Goods> builder)
    {
        builder.ToTable("Goods");
        builder.HasKey(k => k.Id);
        builder.OwnsOne(o => o.CategoryInfo);
        builder.OwnsOne(o => o.Supplier);
        //builder.Property(a => a.CreateTime).HasDefaultValueSql("getdate()").ValueGeneratedOnAdd();
    }
}
public class BizOfferRepository
{

    public List<Goods> GetPageList<S>(
        BloggingContext context,
        int pageIndex,
        int pageSize,
        Expression<Func<Goods, bool>> where,
        Expression<Func<Goods, S>> orderBy,
        bool ascending = false)
    {
        pageIndex = pageIndex <= 0 ? 1 : pageIndex;
        pageSize = pageSize <= 0 ? 10 : pageSize;
        where = where ?? (t => true);

        int count = context.Set<Goods>().AsNoTracking().Where(where).Count();

        var query = context.Set<Goods>().AsNoTracking().Where(where);
        if (orderBy != null)
        {
            query = ascending ? query.OrderBy(orderBy) : query.OrderByDescending(orderBy);
        }
        query = query.Skip((pageIndex - 1) * pageSize).Take(pageSize);

        return query.ToList();

    }
}
public class BloggingContext : DbContext
{
    private static readonly LoggerFactory Logger
        = new LoggerFactory(new[] { new ConsoleLoggerProvider((_, __) => true, true) });

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            .UseLoggerFactory(Logger)
            .EnableSensitiveDataLogging()
            .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Test;ConnectRetryCount=0", b => b.UseRowNumberForPaging());
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.ApplyConfiguration(new BizOfferItemMap());
    }
}

}`

[smitpatel]

Duplicate of #12880