加入两者之间的功能 [英] Joining with a function in between

问题描述

我正在尝试使用LINQ to SQL（第一次）从两个表中获取对象列表。这是IMO一个设计得很好的数据库，没有我无法控制的主键。假设TableKeys包含两个字段--Name和EncryptedKey，TableB包含未加密的密钥和我需要的其余数据。

I'm trying to use LINQ to SQL (for the first time) to get a list of objects from two tables. This is IMO an awfully designed database with no primary keys which I have no control over. Assume TableKeys contains two fields - Name and EncryptedKey, TableB contains the unencrypted Key and the rest of the data I need.

我需要搜索具有某些名称的所有记录并返回数据来自TableB。我试图通过首先在TableKeys中找到我需要的项目的加密密钥，然后查找加密（密钥）在返回列表中的所有记录来尝试这样做。但是，我得到以下异常：方法'System.String EncryptData（System.String，System.String）'没有支持的SQL转换。

I need to search for all records which have certain names and return the data from TableB. I'm trying to do this by first finding the encrypted keys of the items I need in TableKeys, then finding all records where encrypt(key) is in the returned list. However, I get the following exception: Method 'System.String EncryptData(System.String, System.String)' has no supported translation to SQL.

  var dbContext = new SecretDatabaseDataContext();
  var relevantKeys = from keys in dbContext.TableKeys
                     select Utils.EncryptData("secretKey", keys.Key);
  var data = from s in dbContext.TableBs
             where relevantKeys.Contains(s.EncryptedKey)
             select s;

所以伪sql会

So the pseudo-sql would be

SELECT * FROM TableB
RIGHT JOIN TableKeys ON TableB.EncryptedKey = encrypt(TableKeys.Key) /* obviously not valid */
WHERE TableKeys.Key IN ('name1', name2', 'name3');

如果在LINQ to SQL中无法做到这一点我会感到惊讶，但我还是比较新，所以我我不确定。感谢任何指针。

I'd be surprised if this isn't possible in LINQ to SQL, but I'm fairly new so I'm unsure. Thanks for any pointers.

推荐答案

LINQ to SQL的工作原理是将LINQ语句转换为随后执行的SQL查询。它没有一些特殊的后门进入SQL，它可以将本机LINQ发送到服务器。

这意味着如果你想做什么没有有效的SQL，你就不会能够在LINQ中执行此操作（除非您将一些处理转移到客户端，除非在最终投影中完成，否则效率很低）。

您最好的办法是将EncryptData方法实现为SQL服务器标量函数（您可以使用CLR集成）。然后，您可以将此函数映射到LINQ to SQL DataContext并进行如下查询：来自dbContext.TableKeys中的键的var relatedKeys =

选择dbContext.EncryptData（" secretKey" ;，keys.Key）;

乔

LINQ to SQL works by translating LINQ statements into SQL queries which it then executes. It doesn't have some special back-door into SQL where it can send native LINQ to the server.

This means that if there's no valid SQL for what you want to do, you won't be able to do it in LINQ (unless you transfer some of the processing to the client, which is inefficient unless it's done in the final projection).

Your best bet is to implement the EncryptData method as a SQL Server scalar function (you can use CLR integration for this). You can then map this function to the LINQ to SQL DataContext and query like this:

   var relevantKeys =
      from keys in dbContext.TableKeys
      select dbContext.EncryptData ("secretKey", keys.Key);

Joe

这篇关于加入两者之间的功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！