这篇博文纪录一下:使用 EF7,当 Linq 查询中使用 "Left Join" 语法(DefaultIfEmpty),Where Select 不同条件语法实现,出现的不同问题。
为了正确反应测试 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 DbSetBlogs { get; set; } public DbSet 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 () .Key(b => b.BlogId); builder.Entity () .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 等等,但都和上面比较类似,这边就不纪录了。
已提交至 EntityFramework 7 issues: