EntityFramework 7 Left Join Where Select 奇怪问题

2023-08-21 14:08:22

这篇博文纪录一下：使用 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 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 等等，但都和上面比较类似，这边就不纪录了。

已提交至 EntityFramework 7 issues：Use EF7, Linq Left Join Where Select is error.

码农公寓

这篇博文纪录一下：使用 EF7，当 Linq 查询中使用 "Left Join" 语法（DefaultIfEmpty），Where Select 不同条件语法实现，出现的不同问题。

1. where b select b

2. no where select b

3. where c select b

4. no where select c

相关文章