随笔 - 3  文章 - 0 评论 - 0 trackbacks - 0

使用EF实现多查询,暂未考虑性能问题

2015年10月6日22:26:51

=====正文=====

我在数据库有三个表,如下图:

sysMenus与sysFunction有主外键关系,而sysUserInfo则与其他二表并无关联。

 我现在使用这三个表针对多种情况使用EF实现多种查询手段

方法一: include方法(或EF自身的导航属性)

  查询,要的就是出数据罢了。所以我先说EF自身的导航属性,因为这也算常用方法。

  我所说的导航属性就是:

模型表下方显示的,可以看到只有拥有主外键关联的表才会有导航属性。

使用起来也特别简单,上代码

 

直接点出来就是表,真是不要太简单了,但是这样查询是有缺点的,因为它不想include方法那样全部查出来,是当你使用导航属性点出字段是再去数据库查一次,因此推荐使用include方法。

 

  而使用include方法则是:

除了多了一个include方法,其他一致的。

区别在于,sql语句生成的不同,使用sql检测工具便知

 

SELECT TOP (1) 
    [Extent1].[fID] AS [fID], 
    [Extent1].[mID] AS [mID], 
    [Extent1].[fName] AS [fName], 
    [Extent1].[fFunction] AS [fFunction], 
    [Extent1].[fPicname] AS [fPicname], 
    [Extent1].[fStatus] AS [fStatus], 
    [Extent1].[fCreatorID] AS [fCreatorID], 
    [Extent1].[fCreateTime] AS [fCreateTime], 
    [Extent1].[fUpdateID] AS [fUpdateID], 
    [Extent1].[fUpdateTime] AS [fUpdateTime], 
    [Extent2].[mID] AS [mID1], 
    [Extent2].[mParentID] AS [mParentID], 
    [Extent2].[mName] AS [mName], 
    [Extent2].[mUrl] AS [mUrl], 
    [Extent2].[mArea] AS [mArea], 
    [Extent2].[mController] AS [mController], 
    [Extent2].[mAction] AS [mAction], 
    [Extent2].[mSortid] AS [mSortid], 
    [Extent2].[mStatus] AS [mStatus], 
    [Extent2].[mPicname] AS [mPicname], 
    [Extent2].[mLevel] AS [mLevel], 
    [Extent2].[mExp1] AS [mExp1], 
    [Extent2].[mExp2] AS [mExp2], 
    [Extent2].[mCreatorID] AS [mCreatorID], 
    [Extent2].[mCreateTime] AS [mCreateTime], 
    [Extent2].[mUpdateID] AS [mUpdateID], 
    [Extent2].[mUpdateTime] AS [mUpdateTime]
    FROM  [dbo].[sysFunction] AS [Extent1]
    INNER JOIN [dbo].[sysMenus] AS [Extent2] ON [Extent1].[mID] = [Extent2].[mID]

 

include方法会inner join 表查询出所有数据,而直接使用导航属性的仅仅只是单表查询(没有连sysMenus表怎么与mName呢?疑问点)

 所以我还是使用include

但是没有主外键关系的表模型并没有导航属性,于是include并不能使用在表之间没有主外键关系的表中!

 

方法二:使用join方法

有主外键关系

生成的sql语句

SELECT 
    [Extent1].[mID] AS [mID], 
    [Extent1].[mName] AS [mName], 
    [Extent2].[fName] AS [fName]
    FROM  [dbo].[sysMenus] AS [Extent1]
    INNER JOIN [dbo].[sysFunction] AS [Extent2] ON [Extent1].[mID] = [Extent2].[mID]

无主外键关系

sql语句

SELECT 
    [Extent1].[mID] AS [mID], 
    [Extent1].[mName] AS [mName], 
    [Extent2].[uLoginName] AS [uLoginName]
    FROM  [dbo].[sysMenus] AS [Extent1]
    INNER JOIN [dbo].[sysUserInfo] AS [Extent2] ON [Extent1].[mID] = [Extent2].[uID]

由生成的SQL语句可知,join也可完美连表,不管两表是否有主外键关联。但join方法有一缺点,无法使用多个join连表,要连三个表怎么做呢?答:join做不到啊

 

方法三:使用类SQL语句(如何读?)

SELECT TOP (1) 
    [Extent1].[mID] AS [mID], 
    [Extent1].[mParentID] AS [mParentID], 
    [Extent1].[mName] AS [mName], 
    [Extent1].[mUrl] AS [mUrl], 
    [Extent1].[mArea] AS [mArea], 
    [Extent1].[mController] AS [mController], 
    [Extent1].[mAction] AS [mAction], 
    [Extent1].[mSortid] AS [mSortid], 
    [Extent1].[mStatus] AS [mStatus], 
    [Extent1].[mPicname] AS [mPicname], 
    [Extent1].[mLevel] AS [mLevel], 
    [Extent1].[mExp1] AS [mExp1], 
    [Extent1].[mExp2] AS [mExp2], 
    [Extent1].[mCreatorID] AS [mCreatorID], 
    [Extent1].[mCreateTime] AS [mCreateTime], 
    [Extent1].[mUpdateID] AS [mUpdateID], 
    [Extent1].[mUpdateTime] AS [mUpdateTime]
    FROM   [dbo].[sysMenus] AS [Extent1]
    INNER JOIN [dbo].[sysFunction] AS [Extent2] ON [Extent1].[mID] = [Extent2].[mID]
    INNER JOIN [dbo].[sysUserInfo] AS [Extent3] ON [Extent1].[mID] = [Extent3].[uID]

就像写SQL语句一样,但我感觉跟include是一样的,因为没有导航属性,查出来也用不了

 

如有错误之处,望各位看管能不吝指出。

posted on 2015-10-06 23:56 hellomark 阅读(...) 评论(...) 编辑 收藏