如何在LINQ to Entities查询中实现查询拦截？ （C＃） [英] How can I implement Query Interception in a LINQ to Entities query? (c#)

问题描述

我试图在EF4中实现加密列，并使用CTP5功能来简单地使用POCO来查询数据库。对不起，这是很多话，但我希望以下给出足够的解释需要和问题！

所以，一点背景，我的进步到目前为止：

意图是，如果您在不使用DAL的情况下查询表，那么数据是垃圾，但是我不希望开发人员担心是否/数据如何加密。

为了简单起见，在这个阶段我正在处理任何字符串列被加密的假设。

现在，我已经成功实现了使用Objectmaterialized事件返回数据，并使用SavingChanges事件提交数据。

所以给出以下内容类：

  public class Thing 
 {
 
 public int ID {get;组; 
 [必需] 
 public string Name {get;组; } 
 public DateTime Date {get;组; } 
 public string OtherString {get;组; } 
} 
  

以下查询返回所有必需的值，并且物化的POCO具有清晰的数据在其中。

  var things = from t in myDbContext.Things 
 select t; 
  

其中myDbContext.Things是一个 DbSet< Thing>

同样，将 Thing 的实例传递给 Things.Add（）

（在Name和/或OtherString值中有明确的字符串数据）

然后调用 myDbContext.SaveChanges（）在字符串进入数据存储之前进行加密。

现在，我在这个查询中遇到的问题是：

  var things = from t in myDbContext.Things 
其中t.Name ==Hairbrush
 select t; 
  

这将导致未加密的值与数据库中的加密值进行比较。显然，我不想从数据库获取所有记录，实现它们，然后根据任何提供的Where子句过滤结果...所以我需要做的是：截取该查询并重写它加密Where子句中的任何字符串。
所以我看过：

• 编写查询提供者，但这似乎不是正确的解决方案... （是吗？）


• 为DbSet编写自己的IQueryable包装器，它将捕获表达式，使用表达式树访问者运行它，然后将新的表达式转发到DbSet ...

尝试在两个都让我有点丢失！我更喜欢我认为的第二个解决方案，因为它感觉有点整洁，并且将来可能对其他开发人员更清楚。但是我很高兴与另一个更好的选项一起去

我正在努力的主要事情是什么时候/如何LINQ表达式被应用于对象...我觉得我有点混淆了表达式在IQueryable对象中执行的位置，所以我不知道我需要在我的包装器中实现哪个方法，然后抓住并操纵表达式被传入...

我相信我在这里缺少一些相当明显的东西，我正在等待那个灯泡的时刻...但它不是来了!!

任何帮助将非常感谢！

解决方案

想想我会让你知道我的最终解决方案是什么。
最后我已经去了一个实现了Where方法的包装类，但没有完全实现IQueryable的程度。 LINQ仍然会针对类执行（至少在我想/需要它的程度上），并且将使用LINQ中的表达式调用Where方法。然后我将遍历这个ExpressionTree，并将我的字符串替换为加密的值，然后将新的表达式转换到内部的DbSet。然后返回结果。

它的原型很有限，但有其局限性，但对我们的特殊情况没有任何问题。

谢谢，
本

I'm trying to implement encrypted columns in EF4 and using the CTP5 features to allow simple use of POCO's to query the database. Sorry that this is a lot of words, but I hope the below gives enough to explain the need and the problem!

So, bit of background, and my progress so far:

The intention is that if you query the tables without using our DAL then the data is rubbish, but I don't want the developers to worry about if/when/how the data is encrypted.

For simplicity, at this stage I'm working on the assumption any string column will be encrypted.

Now, I have successfully implemented this for returning the data using the Objectmaterialized event, and for data commits using the SavingChanges event.

So given the following class:

public class Thing
{

    public int ID { get; set; }
    [Required]
    public string Name { get; set; }
    public DateTime Date { get; set; }
    public string OtherString { get; set; }
}

The below query returns all the required values and the POCO materialized has clear data in it.

var things = from t in myDbContext.Things
             select t;

where myDbContext.Things is a DbSet<Thing>

Likewise, passing an instance of Thing to Things.Add()
(with clear string data in the Name and/or OtherString values)
and then calling myDbContext.SaveChanges() encrypts the strings before it gets to the data store.

Now, the problem I have is in this query:

var things = from t in myDbContext.Things
             where t.Name == "Hairbrush"
             select t;

This results in the unencrypted value being compared to the encrypted value in the DB. Obviously I don't want to get all the records from the database, materialize them, and then filter the results based on any supplied Where clause... so what I need to do is: intercept that query and rewrite it by encrypting any strings in the Where clause. So I've looked at:

• writing a query provider, but that doesn't seem like the right solution... (is it?)
• writing my own IQueryable wrapper for the DbSet which will capture the expression, run over it using an expression tree visitor and then forward the new expression to the DbSet...

Attempts at both have left me somewhat lost! I prefer the second solution i think since it feels a bit neater, and is probably clearer to other developers in future. But I'm happy to go with either or another better option!!

The main thing I am struggling with is when/how the LINQ expression is applied to the object... I think i've got myself a bit confused as to where the expression executes in the IQueryable object thus I'm not sure which method I need to implement in my wrapper to then grab and manipulate the expression being passed in...

I'm sure I'm missing something fairly obvious here and I'm waiting for that light bulb moment... but its not coming!!

Any help will be very gratefully received!

解决方案

Thought I'd let you know what my final solution was. In the end I have gone a wrapper class which implements a Where method, but without going to the extent of implementing IQueryable entirely. LINQ will still execute against the class (at least to the extent that I want/need it to) and will call the Where method with the expression from the LINQ.

I then traverse this ExpressionTree and replace my strings with encrypted values before forwarding the new expressiontree to the internal DbSet. and then returning the result.

Its pretty crude, and has its limitation, but works for our particular circumstance without problem.

Thanks, Ben

这篇关于如何在LINQ to Entities查询中实现查询拦截？ （C＃）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！