Query: Inner Join not generated when using custom projections or wrong alias when omitting custom projections

[adzhiljano]

Recently, I saw that one of my queries actually sends two separate queries to the server, instead of sending one with an inner join in it. First, I thought that it was because of the projections, so I removed them, but got the following results:

Steps to reproduce

    public class Blog
    {
        public int BlogId { get; set; }
        public string Url { get; set; }
    }

    public class Post
    {
        public int PostId { get; set; }
        public int BlogId { get; set; }
        public string Content { get; set; }
        public string Title { get; set; }
    }

    public class BlogDto
    {
        public int BlogId { get; set; }
        public string Url { get; set; }
    }

    public class PostDto
    {
        public int BlogId { get; set; }
        public string Content { get; set; }
    }

    public class MixedDto
    {
        public int BlogId { get; set; }
        public string Url { get; set; }
        public string Content { get; set; }
    }

    public class MyContext : DbContext
    {
        public DbSet<Blog> Blogs { get; set; }
        public DbSet<Post> Posts { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(@"Data Source=.;Initial Catalog=Blogging;Integrated Security=True;MultipleActiveResultSets=True");
            //optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=CollectionsTest;Trusted_Connection=True;ConnectRetryCount=0");
        }
    }

    public class Program
    {
        private static void Main()
        {
            using (var context = new MyContext())
            {
                var query1 = context.Blogs
                    .Select(t => new
                    {
                        BlogId = t.BlogId,
                        Url = t.Url
                    });

                var query2 = context.Posts
                    .Select(t => new
                    {
                        BlogId = t.BlogId,
                        Content = t.Content
                    });

                var results = (from q1 in query1
                               join q2 in query2 on q1.BlogId equals q2.BlogId
                               select new
                               {
                                   BlogId = q1.BlogId,
                                   Url = q1.Url,
                                   Content = q2.Content
                               }).ToList();
            }
        }
    }

the following query is sent to the server :

SELECT [t].[BlogId], [t].[Url], [t2].[Content]
FROM [Blogs] AS [t]

and then comes the exception:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in Microsoft.EntityFrameworkCore.dll

Additional information: The multi-part identifier "t2.Content" could not be bound.

StackTrace:
   at System.Data.SqlClient.SqlConnection.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.ExecuteReader(CommandBehavior behavior)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, String executeMethod, IReadOnlyDictionary`2 parameterValues, Boolean closeConnection)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable.Enumerator.BufferlessMoveNext(Boolean buffer)
   at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](Func`2 operation, Func`2 verifySucceeded, TState state)
   at Microsoft.EntityFrameworkCore.Query.QueryMethodProvider.<_ShapedQuery>d__3`1.MoveNext()
   at System.Linq.Enumerable.<JoinIterator>d__80`4.MoveNext()
   at System.Linq.Enumerable.SelectEnumerableIterator`2.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)

Which is expected, given that the [t2] alias is wrong in SELECT [t].[BlogId], [t].[Url], [t2].[Content]

Further, if you add the projections

new BlogDto
new PostDto
new MixedDto

in query1, query2 and result the following queries are sent to the server:

SELECT [t].[BlogId], [t].[Url]
FROM [Blogs] AS [t]

SELECT [t0].[BlogId], [t0].[Content]
FROM [Posts] AS [t0]

instead of one query with a single inner join. Is that by design?

Maybe those are two separate issues.

BUT, If you omit the query1 and query2 variables, like:

                var results = (from q1 in context.Blogs
                               join q2 in context.Posts on q1.BlogId equals q2.BlogId
                               select new
                               {
                                   BlogId = q1.BlogId,
                                   Url = q1.Url,
                                   Content = q2.Content
                               }).ToList();

everything is fine and the following query is generated:

SELECT [q1].[BlogId], [q1].[Url], [q2].[Content]
FROM [Blogs] AS [q1]
INNER JOIN [Posts] AS [q2] ON [q1].[BlogId] = [q2].[BlogId]

Further technical details

EF Core version: 1.1.1 (tried with 1.1.0 - same result)
Database Provider: Microsoft.EntityFrameworkCore.SqlServer 1.1.1 (tried with 1.1.0 - same result)
Operating system: Win 8.1 64bit
IDE Visual Studio 2015 Update 3

I also tried those queries with EF 6.1.3 with and without custom projections, and the result was:

SELECT 
[Extent1].[BlogId] AS [BlogId], 
[Extent1].[Url] AS [Url], 
[Extent2].[Content] AS [Content]
FROM  [dbo].[Blogs] AS [Extent1]
INNER JOIN [dbo].[Posts] AS [Extent2] ON [Extent1].[BlogId] = [Extent2].[BlogId]

[maumar]

This is fixed in the current bits - we produce the following SQL:

SELECT [t1].[BlogId], [t1].[Content], [t].[BlogId], [t].[Url]
FROM [Blogs] AS [t]
INNER JOIN (
    SELECT [t0].[BlogId], [t0].[Content]
    FROM [Posts] AS [t0]
) AS [t1] ON [t].[BlogId] = [t1].[BlogId]