EF预编译查询并返回标量值 [英] EF Pre Compile query and return of a scalar value

问题描述

我使用asp.net 4 c＃和ef4。

我有这个代码，它应该编译一个查询并返回一个标量值（我使用匿名类型）

我的代码没有明显的错误，但是因为是第一次编写一个编译的查询，我想知道是否写得好，或者可以改进一个性能提升。

  var query = CompiledQuery.Compile（（CmsConnectionStringEntityDataModel ctx）
 => from ct in ctx。 CmsOptions 
其中o.OptionId == 7 
选择新
 {
 Value = o.Value 
}）; 
 
 uxHtmlHead.Text = query（context）.FirstOrDefault（）。Value; //我在一个Label中打印结果
  

---

SQL配置文件输出：

 选择顶部（1）
 [Extent1]。[OptionId] AS [OptionId]，
 [Extent1]。[Value] AS [Value] 
 FROM [dbo]。[CmsOptions] AS [ Extent1] 
 WHERE 7 = [Extent1]。[OptionId] 
  

>

---

Wouter建议结果（请再次双重检查）：

  static readonly Func< CmsConnectionStringEntityDataModel，int，string> compileQueryHtmlHead = 
 CompiledQuery.Compile< CmsConnectionStringEntityDataModel，int，string>（
（ctx，id）=> ctx.CmsOptions.FirstOrDefault（o => o.OptionId == id）.Value）;使用（var context = new CmsConnectionStringEntityDataModel（））
 
 
 
 {
 int id = 7; 
 uxHtmlHead.Text = compiledQueryHtmlHead.Invoke（context，id）; 
} 
  

---

产生的SQL不明白为什么用LEFT JOIN）

  exec sp_executesql N'SELECT 
 [Project1]。[Value] AS [值] 
 FROM（SELECT 1 AS X）AS [SingleRowTable1] 
 LEFT OUTER JOIN（SELECT 
 [Extent1]。[Value] AS [Value] 
 FROM [dbo] [CmsOptions] AS [Extent1] 
 WHERE [Extent1]。[OptionId] = @ p__linq__0）AS [Project1] ON 1 = 1'，N'@ p__linq__0 int'，@ p__linq__0 = 7 
  




解决方案

有两件事你可以改善。

首先，预编译查询绝对是一个好主意，但是如果你看看你的代码，你会看到它预编译查询每次而不是一次。

您需要将预编译的查询移动到仅初始化一次的静态变量。

另一件需要注意的是当预编译查询您不应该在执行之前修改查询。

您正在构建一个预编译的查询，它将选择所有行，然后说firstordefault，它会更改预编译查询到一个SELECT TOP（1），并且你失去了预编译的好处。您需要在预编译查询中移动FirstOrDefault部分，并返回一个结果。

查看这个文档。如果您查看示例，您可以看到他们如何使用静态字段来保存编译的查询，以及它们如何指定返回值。

I use asp.net 4 c# and ef4.

I have this code, it should compile a query and return a single scalar value (I use anonymous type).

My code does not have apparently errors, but because is the first time I write a compiled query I would like to know if is well written or could be improved for a performance boost.

   var query = CompiledQuery.Compile((CmsConnectionStringEntityDataModel ctx)
                        => from o in ctx.CmsOptions
                           where o.OptionId == 7
                           select new
                           {
                               Value = o.Value
                           });

                    uxHtmlHead.Text = query(context).FirstOrDefault().Value;// I print the result in a Label

---

SQL Profile Output:

SELECT TOP (1) 
[Extent1].[OptionId] AS [OptionId], 
[Extent1].[Value] AS [Value]
FROM [dbo].[CmsOptions] AS [Extent1]
WHERE 7 = [Extent1].[OptionId]

Many Thanks

---

Result after Wouter advice (please guys have a double check again):

        static readonly Func<CmsConnectionStringEntityDataModel, int, string> compiledQueryHtmlHead =
    CompiledQuery.Compile<CmsConnectionStringEntityDataModel, int, string>(
            (ctx, id) => ctx.CmsOptions.FirstOrDefault(o => o.OptionId == id).Value);


using (var context = new CmsConnectionStringEntityDataModel())
            {
                int id = 7;
                uxHtmlHead.Text = compiledQueryHtmlHead.Invoke(context, id);
            }

---

Resulting SQL (I do not understand why with a LEFT JOIN)

exec sp_executesql N'SELECT 
[Project1].[Value] AS [Value]
FROM   ( SELECT 1 AS X ) AS [SingleRowTable1]
LEFT OUTER JOIN  (SELECT 
    [Extent1].[Value] AS [Value]
    FROM [dbo].[CmsOptions] AS [Extent1]
    WHERE [Extent1].[OptionId] = @p__linq__0 ) AS [Project1] ON 1 = 1',N'@p__linq__0 int',@p__linq__0=7

解决方案

There are 2 things you can improve on.

First, precompiling a query is definitely a good idea but if you have a look at your code you will see that it precompiles the query each and every time instead of only once.

You need to move the precompiled query to a static variable that is initialized only once.

Another thing you need to be careful of is that when precompiling a query you shouldn't modify the query anymore before executing it.

You are building a precompiled query that will select all rows and then you say 'firstordefault' which changes the precompiled query to a SELECT TOP (1) and you lose the benefit of precompiling. You need to move the FirstOrDefault part inside your precompiled query and return only one result.

Have a look at this documentation. If you look at the examples you can see how they use a static field to hold the compiled query and how they specify the return value.

这篇关于EF预编译查询并返回标量值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！