EntityFramework 7 Left Join Where is error(Test record)

First of all, my English is very poor, so I may not be a very good expression, very sorry!

In this blog: EntityFramework 7 Left Join Where Select 奇怪问题, Were left join query using Linq, I probably had four different grammar test, but I tested may be some messy, so it would be more difficult to analyze, and here I only tested a grammar, this is my project is used to test the following elements:

BloggingContext configuration code：

using Microsoft.Data.Entity;
using Microsoft.Data.Entity.Metadata;
using System.Collections.Generic;

namespace EF7
{
    public class BloggingContext : DbContext
    {
        public DbSet<Blog> Blogs { get; set; }
        public DbSet<BlogCate> BlogCates { get; set; }

        protected override void OnConfiguring(DbContextOptions builder)
        {
            builder.UseSqlServer(@"Server=.;Database=Blogging;Trusted_Connection=True;");
        }

        protected override void OnModelCreating(ModelBuilder builder)
        {
            builder.Entity<Blog>()
                .Key(b => b.BlogId);
            builder.Entity<BlogCate>()
                .Key(b => b.CateId);
        }
    }

    public class Blog
    {
        public int BlogId { get; set; }
        public string Url { get; set; }
        public int BlogCateId { get; set; }
    }
    public class BlogCate
    {
        public int CateId { get; set; }
        public string CateName { get; set; }
    }
}

Note that in OnModelCreating, I did not make for BlogCate OneToMany configuration and Blog, that they do not exist ForeignKey relationship, so I did not use Include grammar.

Test code(The most important):

[Fact]
public void TestWithLeftJoin()
{
    using (var context = new BloggingContext())
    {
        var query = from b in context.Blogs
                    join c in context.BlogCates on b.BlogCateId equals c.CateId into left
                    from c in left.DefaultIfEmpty()
                    where b.Url == "http://www.cnblogs.com/"
                    select new { b.BlogId, c.CateName };
        var result = query.ToList();
    }
}

EF6 Test Results:

SQL Server Profiler to get SQL code:

SELECT 
    [Extent1].[BlogId] AS [BlogId], 
    [Extent2].[CateName] AS [CateName]
    FROM  [dbo].[Blog] AS [Extent1]
    LEFT OUTER JOIN [dbo].[BlogCate] AS [Extent2] ON [Extent1].[BlogCateId] = [Extent2].[CateId]
    WHERE N'http://www.cnblogs.com/' = [Extent1].[Url]

EF7 Test Results:

Error details:

The multi-part identifier "b.Url" could not be bound.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 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, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader()
at Microsoft.Data.Entity.Relational.Query.QueryingEnumerable1.Enumerator.MoveNext()
at System.Linq.Lookup2.CreateForJoin(IEnumerable1 source, Func2 keySelector, IEqualityComparer1 comparer)
at System.Linq.Enumerable. d__6a4.MoveNext()
at System.Linq.Enumerable. d__142.MoveNext()
at System.Linq.Enumerable.WhereSelectEnumerableIterator2.MoveNext()
at Microsoft.Data.Entity.Query.EntityQueryExecutor.EnumerableExceptionInterceptor1.EnumeratorExceptionInterceptor.MoveNext()
at System.Collections.Generic.List1..ctor(IEnumerable1 collection)
at System.Linq.Enumerable.ToList [TSource] (IEnumerable1 source)
at EF7.Tests.EF7_Test.TestWithLeftJoin() in C:\Users\yuezhongxin\Desktop\EF7\src\EF7.Tests\EF7_Test.cs:line 47

Use EF6 generated SQL code is what I want, but it is wrong to use EF7. From the test code you can see that I am asking for Url for "http://www.cnblogs.com/" The BlogId and CateName information, in Blog, BlogCateId value may be Null, so just use the Left Join, rather Inner Join. The above test code is just an example, but my project is the same, I do not know if you can understand what I mean?

Also, if I put Where conditions are removed, use EF7 is right, like this:

However, using SQL Server Profiler to get SQL code is so that:

In EF7, I personally feel that there are some problems using the Join, like this same problem: Use EF7, Linq Join Count is error. I feel they are essentially a problem.

I'm sorry I can only use these English expressions, thanks!

本文转自田园里的蟋蟀博客园博客，原文链接：http://www.cnblogs.com/xishuai/p/ef7-linq-left-join-where-is-error.html，如需转载请自行联系原作者