none
EntityFramwork 查询视图和查询表生成的SQL不一致。 RRS feed

  • 问题

  • 你好:

    我查询一个视图:

    ntities.V_DeptDcInfo
                                        .Where(d => d.DeptFid == deptMsgData.DeptFid)
                                        .SingleOrDefault();

    我发现他生成的SQL语句执行了两次SELECT。V_DeptDcInfo是一个视图。

    SELECT TOP (2) 
        [Extent1].[DeptFid] AS [DeptFid], 
        [Extent1].[DcGuid] AS [DcGuid], 
        [Extent1].[GroupDcGuid] AS [GroupDcGuid], 
        [Extent1].[ParentDcGuid] AS [ParentDcGuid], 
        [Extent1].[ParentGroupDcGuid] AS [ParentGroupDcGuid]
        FROM (SELECT 
        [V_DeptDcInfo].[DeptFid] AS [DeptFid], 
        [V_DeptDcInfo].[DcGuid] AS [DcGuid], 
        [V_DeptDcInfo].[GroupDcGuid] AS [GroupDcGuid], 
        [V_DeptDcInfo].[ParentDcGuid] AS [ParentDcGuid], 
        [V_DeptDcInfo].[ParentGroupDcGuid] AS [ParentGroupDcGuid]
        FROM [dbo].[V_DeptDcInfo] AS [V_DeptDcInfo]) AS [Extent1]
        WHERE [Extent1].[DeptFid] = @p__linq__0

    我把查询视图,改成查询数据库表,我发现他只执行了一次Select。有办法优化第一个么?


    ...

    2018年6月8日 8:54

全部回复

  • 可否将where子句变成一个常量查询 ,比如先查出 deptMsgData.DeptFid的值,再赋值给where子句

    .Where(d => d.DeptFid == deptMsgData.DeptFid)

    .Where(d => d.DeptFid == "ABC")


    专注于.NET ERP/CRM开发框架,C/S架构,SQL Server + ORM(LLBL Gen Pro) + Infragistics WinForms

    2018年6月11日 0:18
  • 你好,

    这个是EF生存SQL的机制,你如果执行一次select 的话, 可以直接使用原生的SQL. 

    var query = db.Database.SqlQuery<V_DeptDcInfo>(string.Format("select top 1 * from V_DeptDcInfo where DeptFid = '{0}'", deptMsgData.DeptFid)).SingleOrDefault();

    或者不使用view 在做,直接使用LINQ来查询。比如下面的例子:

    var result = (from b in db.Blogs
                                  join p in db.Posts on b.BlogId equals p.BlogId
                                  where p.Title == "AAA"
                                  select new
                                  {
                                      blogId = b.BlogId,
                                      title = p.Title,
                                      url = b.Url
                                  }).SingleOrDefault();

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2018年6月11日 8:57
    版主