sql使用Func作为选择与内联选择的区别

本文关键字：选择 区别 sql Func 使用 | 更新日期: 2023-09-27 18:19:02

我试图理解为什么这两个语句的sql不同(差异似乎在于确定是否在选择或使用case语句中添加SignedXml。但是为什么?)。

myTable.Where(p => p.OwnerID.Equals(owner) && p.FolderID == folderId)
       .Select(source => new SignedNativeAnalysis
       {
          ID = source.ID,
          Name = source.Name,
          Created = source.Created,
          FolderID = source.FolderID,
          Locked = source.Locked,
          Modified = source.Modified,
          OwnerID = source.OwnerID,
          IsSigned = source.SignedXml != null
       });

生成如下:

exec sp_executesql N'SELECT 
[Extent1].[ID] AS [ID], 
[Extent1].[Name] AS [Name], 
[Extent1].[Created] AS [Created], 
[Extent1].[FolderID] AS [FolderID], 
[Extent1].[Locked] AS [Locked], 
[Extent1].[Modified] AS [Modified], 
[Extent1].[OwnerID] AS [OwnerID], 
CASE WHEN ([Extent1].[SignedXml] IS NOT NULL) THEN cast(1 as bit) WHEN ([Extent1].[SignedXml] IS NULL) THEN cast(0 as bit) END AS [C1]
FROM [dbo].[NativeAnalyses] AS [Extent1]
WHERE ([Extent1].[OwnerID] = @p__linq__0) AND ([Extent1].[FolderID] = @p__linq__1)',N'@p__linq__0 uniqueidentifier,@p__linq__1

vs当我使用select:

的函数时
myTable.Where(p => p.OwnerID.Equals(owner) && p.FolderID == folderId)
       .Select(Select);

和Select 

private SignedNativeAnalysis Select(NativeAnalysis source)
{
      return new SignedNativeAnalysis
      {
        ID = source.ID,
        Name = source.Name,
        Created = source.Created,
        FolderID = source.FolderID,
        Locked = source.Locked,
        Modified = source.Modified,
        OwnerID = source.OwnerID,
        IsSigned = source.SignedXml != null
      };
}

生成如下:

exec sp_executesql N'SELECT 
[Extent1].[ID] AS [ID], 
[Extent1].[Name] AS [Name], 
[Extent1].[ModelXml] AS [ModelXml], 
[Extent1].[Created] AS [Created], 
[Extent1].[Modified] AS [Modified], 
[Extent1].[OwnerID] AS [OwnerID], 
[Extent1].[SignedXml] AS [SignedXml], 
[Extent1].[FolderID] AS [FolderID], 
[Extent1].[Locked] AS [Locked]
FROM [dbo].[NativeAnalyses] AS [Extent1]
WHERE ([Extent1].[OwnerID] = @p__linq__0) AND ([Extent1].[FolderID] = @p__linq__1)',N'@p__linq__0 uniqueidentifier,@p__linq__1

signnednativeanalysis只是继承了NativeAnalysis的一个额外属性。

internal class SignedNativeAnalysis : NativeAnalysis
{
   public bool IsSigned { get; set; }
}

:根据建议，我试图使用表达式来实现我想要的结果，但我得到了一个异常:

Expression<Func<NativeAnalysis, SignedNativeAnalysis>> signed = p => Select(p);
myTable.Where(p => p.OwnerID.Equals(owner) && p.FolderID == folderId)
       .Select(signed)

LINQ to Entities不识别' signnednativeanalysis '方法选择(OverseerUI.EntityFramework.NativeAnalysis)'方法，然后这个方法不能转换为存储表达式。

sql使用Func作为选择与内联选择的区别

有个小技巧。实体框架选择不接受函数<T,>。它接受一个表达式<T,>>。

所以当你传递lambda时，编译器将lambda转换为表达式，传递给EF, EF将表达式转换为SQL代码。

传递一个Func<T,>时，没有办法将其转换为表达式。可列举的。选择扩展方法——它接受一个普通的Func，而不是表达式——取而代之。因此，EF在SQL中执行第一部分，直到Where。然后将结果传递给Linq to Object，由它在内存中执行投影。

你仍然可以做动态的事情，但是你必须创建一个表达式的实例，而不是一个委托。

相关文章：