在上篇文章中,我们通过WithRequiredDependent或WithRequiredPrincipal实现了“双向一对一”关系,但是Entity Framework生成的SQL语句很糟糕。
在上篇文章发布一个多小时之后,我们找到了解决之道。这就是写博客带来的好处,逼着你静下心来深入思考。
问题的原因在于我们向Entity Framework传递了不合情理的“一对一”关系信息,把Entity Framework搞得晕头转向。BlogSite中有UserID,BlogUser中却没有BlogID,这是一个不平等的“一对一”。“两情相悦”本来就是相互的、平等的,不存在谁依赖谁、谁主宰谁。男人心中有爱情密码,女人为什么不能有?男人主动追求女人,女人为什么只能被动挨追?两情相悦是两颗心的交互,谁先感觉到,谁就主动些。
我们先回顾一下之前不平等的两情相悦(一对一关系):
注:BlogUser类中缺少BlogID属性,BlogUser表中缺少BlogID字段。
看看真正的两情相悦:
注:BlogUser类增加了BlogID属性,BlogUser表中增加了BlogID字段,其他都没动。
然后在OnModelCreating中通过Fluent API进行如下定义:
modelBuilder.Entity < BlogSite > () .HasRequired(b => b.BlogUser) .WithMany() .HasForeignKey(b => b.UserID); modelBuilder.Entity < BlogUser > () .HasRequired(u => u.BlogSite) .WithMany() .HasForeignKey(u => u.BlogID);
运行测试,看看是否真的两情相悦了:
测试通过!
接着,我们要看一看是否是完美的“两情相悦”。打开Server Server Profiler,揭开“两情”的真谛:
获取一个BlogSite列表时执行的SQL:
SELECT [ Extent1 ] . [ BlogID ] AS [ BlogID ] , [ Extent1 ] . [ BlogApp ] AS [ BlogApp ] , [ Extent1 ] . [ IsActive ] AS [ IsActive ] , [ Extent1 ] . [ UserID ] AS [ UserID ] , [ Extent2 ] . [ UserID ] AS [ UserID1 ] , [ Extent2 ] . [ Author ] AS [ Author ] , [ Extent2 ] . [ BlogID ] AS [ BlogID1 ] FROM [ dbo ] . [ BlogSite ] AS [ Extent1 ] INNER JOIN [ dbo ] . [ BlogUser ] AS [ Extent2 ] ON [ Extent1 ] . [ UserID ] = [ Extent2 ] . [ UserID ] WHERE 1 = [ Extent1 ] . [ IsActive ]
取一个BlogUser列表时执行的SQL:
SELECT [ Extent1 ] . [ BlogID ] AS [ BlogID ] , [ Extent1 ] . [ UserID ] AS [ UserID ] , [ Extent1 ] . [ Author ] AS [ Author ] , [ Extent2 ] . [ BlogID ] AS [ BlogID1 ] , [ Extent2 ] . [ BlogApp ] AS [ BlogApp ] , [ Extent2 ] . [ IsActive ] AS [ IsActive ] , [ Extent2 ] . [ UserID ] AS [ UserID1 ] FROM [ dbo ] . [ BlogUser ] AS [ Extent1 ] INNER JOIN [ dbo ] . [ BlogSite ] AS [ Extent2 ] ON [ Extent1 ] . [ BlogID ] = [ Extent2 ] . [ BlogID ]
这才是完美的两情相悦!这才是“双向一对一”关系的完美实现!