Enumerating IQueryable got from FromSqlRaw throws exception

[Criperum]

Enumerating IQueryable got from FromSqlRaw throws exception when fed with the sql genrated by EFCore itself.

System.ArgumentException HResult=0x80070057 Message=An item with the same key has already been added. Key: Id Source=System.Private.CoreLib StackTrace: at System.ThrowHelper.ThrowAddingDuplicateWithKeyArgumentException[T](T key) at System.Collections.Generic.Dictionary2.TryInsert(TKey key, TValue value, InsertionBehavior behavior)
at System.Linq.Enumerable.ToDictionary[TSource,TKey,TElement](IEnumerable1 source, Func2 keySelector, Func2 elementSelector, IEqualityComparer1 comparer)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable1.BuildIndexMap(IReadOnlyList1 columnNames, DbDataReader dataReader)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable1.Enumerator.InitializeReader(DbContext _, Boolean result) at Microsoft.EntityFrameworkCore.Storage.Internal.NoopExecutionStrategy.Execute[TState,TResult](TState state, Func3 operation, Func3 verifySucceeded) at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable1.Enumerator.MoveNext()
at System.Collections.Generic.List1..ctor(IEnumerable1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at ODataConverter.Program.Main(String[] args) in C:\Users\Admin\source\repos\ODataConverter\ODataConverter\Program.cs:line 50

This exception was originally thrown at this call stack:
System.ThrowHelper.ThrowAddingDuplicateWithKeyArgumentException(T)
System.Collections.Generic.Dictionary<TKey, TValue>.TryInsert(TKey, TValue, System.Collections.Generic.InsertionBehavior)
System.Linq.Enumerable.ToDictionary<TSource, TKey, TElement>(System.Collections.Generic.IEnumerable, System.Func<TSource, TKey>, System.Func<TSource, TElement>, System.Collections.Generic.IEqualityComparer)
Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable.BuildIndexMap(System.Collections.Generic.IReadOnlyList, System.Data.Common.DbDataReader)
Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable.Enumerator.InitializeReader(Microsoft.EntityFrameworkCore.DbContext, bool)
Microsoft.EntityFrameworkCore.Storage.Internal.NoopExecutionStrategy.Execute<TState, TResult>(TState, System.Func<Microsoft.EntityFrameworkCore.DbContext, TState, TResult>, System.Func<Microsoft.EntityFrameworkCore.DbContext, TState, Microsoft.EntityFrameworkCore.Storage.ExecutionResult>)
Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable.Enumerator.MoveNext()
System.Collections.Generic.List.List(System.Collections.Generic.IEnumerable)
System.Linq.Enumerable.ToList(System.Collections.Generic.IEnumerable)
ODataConverter.Program.Main(string[]) in Program.cs
`

Steps to reproduce

1. I've created the following classes:
   [Table("RootTable")] public class Root { [Key] public int Id { get; set; } public string Name { get; set; } public int Value { get; set; } public List<First> Firsts { get; set; } } [Table("FirstTable")] public class First { [Key] public int Id { get; set; } public int RootId { get; set; } [ForeignKey("RootId")] public Root Root { get; set; } }
2. Used the following code
   var r1 = context.Roots.Include(root => root.Firsts).ToList(); to get SQL logged into debug window in VS.
   Result: SELECT "r"."Id", "r"."Name", "r"."Value", "f"."Id", "f"."RootId" FROM "RootTable" AS "r" LEFT JOIN "FirstTable" AS "f" ON "r"."Id" = "f"."RootId" ORDER BY "r"."Id", "f"."Id"
3. used
   context.Roots.FromSqlRaw(the_query_from_prev_step).ToList()
4. got the exception

Further technical details

EF Core version: 3.1.2
Database provider: (e.g. Microsoft.EntityFrameworkCore.Sqlite)
Target framework: (e.g. .NET Core 3.1)
Operating system: Windows 10
IDE: (e.g. Visual Studio 2019 16.4.2)

[smitpatel]

The SQL generated by EF Core in step 2 is SQL to populate graph, Roots and Firsts navigation. To populate graph apart from SQL there is a lot of client side logic which also happens. If you put the same SQL in step 3 for Roots entity then it is not correct SQL. Since Firsts is collection navigation, in EF Core generated SQL, Roots are repeated for each associated First. When you use that to materialize just Roots, you get duplicated rows and it will throw tracking error. You can only use EF generated SQL when projection in original query is simple Roots entity. Any custom projection or Include will have client component which will fail any FromSql attempt by copy-pasting it.

Related #14525

[Criperum]

Since there was no log in step 3 before the exception i thought that it was caused by two "id" columns in SQL