自定义扩展方法可简化LINQ to SQL语句

问题描述

我有很多查询需要使用的LINQ代码片段

I have a snippet of LINQ code that I need to use in a lot of queries

let catchmentId = Convert.ToInt32(
        phy.PhysicalProperty_binData.Substring(offset + 3, 1) +
        phy.PhysicalProperty_binData.Substring(offset + 2, 1) +
        phy.PhysicalProperty_binData.Substring(offset + 1, 1) +
        phy.PhysicalProperty_binData.Substring(offset, 1))

这很好地转换为SQL(对于我的特定需求，它的性能更高)，但是使我的查询看起来很丑陋.有什么办法可以使我变得更简单些吗?也许是一个返回Expression>的扩展方法?请注意，我不想将数据获取到客户端(作为SQL在服务器上运行)，这会使事情变得更加复杂.例如:

This translates well to SQL (and is more than performant for my specific needs), but makes my queries look ugly. Is there any way I can make this less verbose? Perhaps an extension method that returns an Expression> ? Note that I don't want to fetch the data to the client side (run it on the server as SQL), which makes things a little more complicated. For example:

let catchmentId = phy.PhysicalProperty_binData.AnExtensionMethodHere<int>(offset)

我尝试编写一个扩展方法来执行此操作，但是提供程序只是忽略了该调用，或者抛出了一个异常，指出它不知道如何将该调用转换为SQL.我们是否可以通过某种方式扩展SQL生成器来为我们执行此操作，或以某种方式生成可以生成相同SQL的内容?

I tried writing an extension method that does this, but the provider simply ignores that call or throws an exception stating it doesn't know how to translate that call to SQL. Can we somehow extend the SQL generator to do this for us or provide it something in a way that will generate the same SQL?

提前谢谢！

推荐答案

此页面将向您展示如何使用自定义属性将扩展方法与数据库中用户定义的函数相关联.

The links on this page will show you how you can use custom attributes to tie your extension method to a user-defined function in your database.

当然，这意味着您需要在SQL中创建用户定义的函数，以执行与扩展方法所需的逻辑相同的逻辑.

Of course, that will mean that you need to create a user-defined function in SQL to do the same logic that your extension method wants to do.

另一种选择是让您的扩展方法产生一个Expression，并使用 LINQKit 来分析查询树，找到对该表达式的.Compile()调用，并在查询中内联该表达式.

Another option is to have your extension method produce an Expression, and use LINQKit to parse through the query tree, finding the call to .Compile() off of that expression, and inlining that expression in the query.

这篇关于自定义扩展方法可简化LINQ to SQL语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！