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:
![](http://images0.cnblogs.com/blog/435188/201412/061641282808823.png)
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:
![](http://images0.cnblogs.com/blog/435188/201412/061650280612151.png)
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:
![](http://images0.cnblogs.com/blog/435188/201412/061711450925043.png)
However, using SQL Server Profiler to get SQL code is so that:
![](http://images0.cnblogs.com/blog/435188/201412/061714032804227.png)
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!
相关文章
- licecap软件——简单做出app的效果gif图
- 【转】三个案例带你看懂LayoutInflater中inflate方法两个参数和三个参数的区别
- 好用的开源库(一)——MaterialEditText
- 【转】NotificationCopat.Builder全部设置
- 安卓项目五子棋代码详解(五)
- 安卓项目五子棋代码详解(四)
- 技术分享 | 缓存穿透 - Redis Module 之布隆过滤器
- Docker高级篇:Redis集群实战!从4主4从缩容到3主3从,该怎么处理?
- Redis 单线程模型 精讲
- Redis6.0使用了多线程还能保证线程安全么?-Redis6.0 多线程精讲
- Redis 搞懂缓存击穿、缓存穿透、缓存雪崩 产生原因及产线常用的解决方案
- 这样讲Redis哨兵机制Sentinel的工作原理,或许你真的能听懂~
- 这样讲Redis Cluster的工作原理,或许你真的能听懂~
- 实战:常见的延时队列解决方案及代码实现,真的很全:MQ、Redis、JDK队列、Netty时间轮~
- 这样讲Redis 主从复制的工作原理,或许你真的能听懂~
- 为什么有的人学完Netty 都还不知道BIO|NIO|AIO 的区别?
- 10分钟快速入门Netty 比写NIO爽百倍
- 熬夜手绘netty线程模型图 如果还不懂的话,那我...
- Netty 是如何解决拆包和 粘包问题 ?最后一种方案最香
- Netty 如何通过心跳检测机制实现空闲自动断开