C# .NET entity-framework linq-to-entities

Nullable上的 OrderBy在实体框架中具有默认值

发布于 2024-12-11 20:24:28 字数 872 浏览 0 评论 0原文

我们正在迁移一些代码以使用实体框架，并有一个查询尝试对可为 Nullable 字段进行排序，并使用 Nullable.GetValueOrDefault(T) 函数。

但是，执行后会返回以下错误：

LINQ to Entities 无法识别“Int32 GetValueOrDefault(Int32)”方法，并且此方法无法转换为存储表达式。

查询如下所示：

int magicDefaultSortValue = 250;
var query = context.MyTable.OrderBy(t => t.MyNullableSortColumn
                                          .GetValueOrDefault(magicDefaultSortValue));

来自这个答案我可以看到有一种方法可以在您的 EDMX 中提供“翻译”。我们可以为这个合并函数写一个类似的翻译吗？

注意：当我尝试时，查询中的 ?? 合并运算符而不是 GetValueOrDefault 确实有效。那么，也许无论什么因素都可以发挥作用？

原文

We are migrating some code to use Entity Framework and have a query that is trying to sort on a Nullable field and provides a default sort value is the value is null using the Nullable.GetValueOrDefault(T) function.

However, upon execution it returns the following error:

LINQ to Entities does not recognize the method 'Int32 GetValueOrDefault(Int32)' method, and this method cannot be translated into a store expression.

The query looks like:

int magicDefaultSortValue = 250;
var query = context.MyTable.OrderBy(t => t.MyNullableSortColumn
                                          .GetValueOrDefault(magicDefaultSortValue));

From this answer I can see that there is a way to provide "translations" within your EDMX. Could we write a similar translation for this coalescing function?

NOTE: When I tried, the ?? coalescing operator instead of GetValueOrDefault in the query it does work. So perhaps whatever makes that work could be leveraged?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

晚雾 2024-12-18 20:24:28

我相信你已经找到了答案。当您使用 ?? 时，EF 使用 CASE 生成 SQL 来选择排序值（如果该值为 null），然后对其进行排序。

MyTable.OrderBy (t => t.MyNullableSortColumn ?? magicDefaultSortValue).ToArray();

将生成以下 sql：

-- Region Parameters
DECLARE p__linq__0 Int = 250
-- EndRegion
SELECT 
[Project1].[MyColumn1] AS [MyColumn1], 
[Project1].[MyNullableSortColumn] AS [MyNullableSortColumn]
FROM ( SELECT 
    CASE WHEN ([Extent1].[MyNullableSortColumn] IS NULL) THEN @p__linq__0 ELSE [Extent1].[MyNullableSortColumn] END AS [C1], 
    [Extent1].[MyColumn1] AS [MyColumn1], 
    [Extent1].[MyNullableSortColumn] AS [MyNullableSortColumn]
    FROM [dbo].[MyTable] AS [Extent1]
)  AS [Project1]
ORDER BY [Project1].[C1] ASC

顺便说一句，我建议使用 LINQPad ，它可以让您使用 EF 模型并查看正在生成的sql。此外，了解 EntityFunctions 类和 SqlFunctions 类，因为它们提供对几个有用函数的访问。

I believe you found your answer. When you use ??, EF generates SQL using a CASE to select your sort value if the value is null, and then sorts on that.

MyTable.OrderBy (t => t.MyNullableSortColumn ?? magicDefaultSortValue).ToArray();

will generate the following sql:

-- Region Parameters
DECLARE p__linq__0 Int = 250
-- EndRegion
SELECT 
[Project1].[MyColumn1] AS [MyColumn1], 
[Project1].[MyNullableSortColumn] AS [MyNullableSortColumn]
FROM ( SELECT 
    CASE WHEN ([Extent1].[MyNullableSortColumn] IS NULL) THEN @p__linq__0 ELSE [Extent1].[MyNullableSortColumn] END AS [C1], 
    [Extent1].[MyColumn1] AS [MyColumn1], 
    [Extent1].[MyNullableSortColumn] AS [MyNullableSortColumn]
    FROM [dbo].[MyTable] AS [Extent1]
)  AS [Project1]
ORDER BY [Project1].[C1] ASC

As an aside, I would recommend getting LINQPad which will let you work with your EF models and view the sql being generated. Also, it is helpful to know about the EntityFunctions class and SqlFunctions class as they provide access to several useful functions.

回复收藏 0 原文

~没有更多了~