为了正确反应测试 EF7 所出现的问题，我同时也用 EF6 进行测试，测试代码都是一样的，然后使用 SQL Server Profiler 抓取 EF6、EF7 所生成的 SQL 代码。
测试环境说明：
- EF6 版本：6.1.2-beta1
- EF7 版本：7.0.0-beta1
- 开发环境：VS2015 Preview
- SQL Server Profiler 对应版本：SQL Server 2014
四种测试场景（b、c 表示对应关联实体。）：
- where b select b
- no where select b
- where c select b
- no where select c
BloggingContext 配置代码：
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; }
}
}OnModelCreating 中的映射配置代码并没有对 Blog 和 BlogCate 进行 OneToMany 关联配置，这样方便我们使用 Linq 进行 Left Join 语法编写，EF6 测试项目我使用的是 Model First，因为 EF7 测试项目中的模型更改，在 EF6 测试项目中，只要一个“Update Model from Database”命令就可以了，这样方便进行测试，当然项目中建议不要使用 Model First，EF7 的测试项目使用的是 Xunit，EF6 的测试项目直接是控制台应用程序，因为在 VS2015 中，对于非 ASP.NET 5 Class Library 项目，使用 Xunit 暂无法实现测试。下面贴一下，针对 Where Select 不同条件语法实现，EF6、EF7 所出现的具体问题？
1. where b select b
测试代码：
[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 b.BlogId;
var result = query.ToList();
}
}EF6 测试结果：
EF6 生成 SQL 代码：
SELECT
[Extent1].[BlogId] AS [BlogId]
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 测试结果：
详细异常信息：
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
2. no where select b
测试代码：
[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()
select b.BlogId;
var result = query.ToList();
}
}EF6 测试结果：
EF6 生成 SQL 代码：
SELECT
[Extent1].[BlogId] AS [BlogId]
FROM [dbo].[Blog] AS [Extent1]
LEFT OUTER JOIN [dbo].[BlogCate] AS [Extent2] ON [Extent1].[BlogCateId] = [Extent2].[CateId]EF7 测试结果：
EF7 生成 SQL 代码：
SELECT [b].[BlogCateId], [b].[BlogId]
FROM [Blog] AS [b]3. where c select b
测试代码：
[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 c.CateName == "EF7"
select b.BlogId;
var result = query.ToList();
}
}EF6 测试结果：
EF6 生成 SQL 代码：
SELECT
[Extent1].[BlogId] AS [BlogId]
FROM [dbo].[Blog] AS [Extent1]
INNER JOIN [dbo].[BlogCate] AS [Extent2] ON [Extent1].[BlogCateId] = [Extent2].[CateId]
WHERE N'EF7' = [Extent2].[CateName]EF7 测试结果：
详细异常信息：
Object reference not set to an instance of an object.
Anonymously Hosted DynamicMethods Assembly
at lambda_method(Closure , QuerySourceScope )
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.TestWithLeftJoinExption() in C:\Users\yuezhongxin\Desktop\EF7\src\EF7.Tests\EF7_Test.cs:line 63
4. no where select c
测试代码：
[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()
select c;
var result = query.ToList();
}
}EF6 测试结果：
EF6 生成 SQL 代码：
SELECT
[Extent2].[CateId] AS [CateId],
[Extent2].[CateName] AS [CateName]
FROM [dbo].[Blog] AS [Extent1]
LEFT OUTER JOIN [dbo].[BlogCate] AS [Extent2] ON [Extent1].[BlogCateId] = [Extent2].[CateId]EF7 测试结果：
EF7 生成 SQL 代码：
SELECT [c].[CateId], [c].[CateName]
FROM [BlogCate] AS [c]
SELECT [b].[BlogCateId]
FROM [Blog] AS [b]总结：
- where b select b（EF6 √，EF7 X）
- no where select b（EF6 √，EF7 √）
- where c select b（EF6 √，EF7 X）
- no where select c（EF6 √，EF7 √）
除了这几种场景，当然还有其他的比如 where b c select b 等等，但都和上面比较类似，这边就不纪录了。
本文转自田园里的蟋蟀博客园博客，原文链接：http://www.cnblogs.com/xishuai/p/ef7-linq-left-join-where-select-error.html，如需转载请自行联系原作者
