c# - 用于确定 EF4 或直接 T-SQL 中的 "Navigation"属性的自定义函数

Question

[为冗长的解释道歉]

我从一个(非常!)旧产品继承了一个带有 SQL 数据库的旧应用程序，我需要直接对数据库进行一些报告。不完全确定为什么，但这是我发现的结构:

我有一个组表和一个报告表。

Groups 有一个 type 列，'R' 或 'G'，一个整数 numElements 和一个名为 elements 的 image 列

类型“R”表示该组包含报告，“G”表示它包含更多组。

type = 'G'
elements = 0x2C002600
numelements = 2
result = take chunks of 4 characters, 
         reverse the last and first two character pairs to get 002C and 0026
convert hex to int to get 44 and 38

44 和 38 是两个组 ID - 我可以在应用程序本身中验证这一点。

这同样适用于类型“R”，其中 ID 的结果是报告

有什么方法可以在 EF4 OR T-SQL 中映射此行为，以便我可以开始执行一些合理的查询？例如

If type = G and numElements = 3
    split the hex string into 3 chunks of 4 characters, 
    reverse character pairs, 
    convert to int, 
    map to Groups

If type = R and numElements = 11
    split the hex string into 11 chunks of 4 characters, 
    reverse character pairs, 
    convert to int, 
    map to Reports

有数千个报告和数百个组，因此虽然在应用程序中我可以看到分组，但手动映射它们将花费很长时间。没有 TreeView ，所以我必须打开一个组以查看它包含的内容，然后打开子组(如果存在)等等。

我知道我可以手动编写代码并逐行提取、拆分、反转、转换...但想知道是否有办法让 T-SQL 或 EF 为我执行此操作？

---

编辑:

请注意，numElements 是可变的，因为一个组可以包含 1:许多组或 1:许多报告(但不是它们的混合)。因此十六进制字符串总是:"0x"+ "4 x numElements"个字符长

这意味着“hex to int”函数需要能够返回一个数组或类似的数组，以便 1 个组可以链接到多个组/报告。

Answer 1

唯一能帮助您完成这两项工作的是创建一个标量函数，它可以进行拆分、反转和转换。
我会创建三个函数:

dbo.fnSplitHex(varchar hex, int chunks)  -- the actual split etc

dbo.fnSplitGHex(varchar hex) return dbo.fnSplitHex(hex, 3) -- pass 3 for chunks
dbo.fnSplitRHex(varchar hex) return dbo.fnSplitHex(hex, 11) -- pass 11 for chunks

不幸的是，您不能仅基于此创建导航属性。但是你可以创建一个 View :

  select *, dbo.fnSplitHex(..) from table1

使用 LINQ 2 SQL 我可以在 View 和另一个表之间创建导航属性，只要我在模型中为 View 定义主键即可。 不确定 EF... EF also

回复问题编辑

由于转换函数依赖于 numElements，这是一个外部输入，不像 hex 参数那样是主表中的一列，可以从那里读取，你没有在主表和组或报告之间创建 NavigationProperty 的选项。

不幸的是，您的问题不止于此。这是一个表函数(返回一个具有单个 int 类型列的表)，EF 不能将它用作“可组合”函数，如以下示例所示:

  from c in Groups
  from sg in fnGetIntsFromHex('G', 3, c.hex) into groups from sg in groups.DefaultIfEmpty()
  join g in Groups on sg.GroupId equals g.GroupId
  select g

这将不得不生成带有 CROSS APPLY 运算符的 SQL 语句，但现在它不能这样做(EF 2011 June CTE 可以，但是是 CTE)。

还剩下什么？我认为回到存储过程并不是一件坏事。其他选项取决于您的 numElements 集。如果此集合以合理的方式受到限制，您仍然可以创建如下 View :

create view dbo.vwSubgropus3
as
begin

select f.GroupId, f.Value = SubGroupId
from group g
where g.Type = 'G'
cross apply fnGetIntsFromHex(g.Hex, 3) f


create view dbo.vwSubgropus11
as
begin

select f.GroupId, f.Value as SubGroupId
from group g
where g.Type = 'G'
cross apply fnGetIntsFromHex(g.Hex, 11) f


create view dbo.vwReports3
as
begin

select f.GroupId, f.Value as ReportId
from group g
where g.Type = 'R'
cross apply fnGetIntsFromHex(g.Hex, 3) f