如何在Linq to SQL中拦截和修改SQL查询

问题描述

我想知道在发送查询之前是否有任何方法可以拦截和修改从linq到Sql生成的sql?

I was wondering if there is any way to intercept and modify the sql generated from linq to Sql before the query is sent off?

基本上，我们有一个记录安全层，给定类似"select * from records"的查询，它将把查询修改为类似于"select * from records WHERE [somesecurityfilter]"

Basically, we have a record security layer, that given a query like 'select * from records' it will modify the query to be something like 'select * from records WHERE [somesecurityfilter]'

我试图在linq to sql provider执行sql之前找到拦截和修改sql的最佳方法.

I am trying to find the best way to intercept and modify the sql before its executed by the linq to sql provider.

推荐答案

如果您想拦截L2S生成的SQL并对其进行修饰，则最好的选择是为SqlConnection，SqlCommand，DbProviderFactory等创建包装类. SqlConnection的包装实例到需要db连接的L2S datacontext构造函数重载.在包装的连接中，您可以将DbProviderFactory替换为您自己的自定义DbProviderFactory派生的类，该类返回SqlCommand等的包装版本.

If you want to intercept the SQL generated by L2S and fiddle with that, your best option is to create a wrapper classes for SqlConnection, SqlCommand, DbProviderFactory etc. Give a wrapped instance of SqlConnection to the L2S datacontext constructor overload that takes a db connection. In the wrapped connection you can replace the DbProviderFactory with your own custom DbProviderFactory-derived class that returns wrapped versions of SqlCommand etc.

例如:

//sample wrapped SqlConnection:
public class MySqlConnectionWrapper : SqlConnection
{
  private SqlConnecction _sqlConn = null;
  public MySqlConnectionWrapper(string connectString)
  {
    _sqlConn = new SqlConnection(connectString);
  }

  public override void Open()
  {
    _sqlConn.Open();
  }

  //TODO: override everything else and pass on to _sqlConn...

  protected override DbProviderFactory DbProviderFactory
  {
    //todo: return wrapped provider factory...
  }
}

使用时:

using (SomeDataContext dc = new SomeDataContext(new MySqlConnectionWrapper("connect strng"))
{
  var q = from x in dc.SomeTable select x;
  //...etc...
}

那就是说，您真的想走那条路吗?您将需要能够解析L2S生成的SQL语句和查询，以便对其进行适当的修改.如果您可以修改linq查询以附加要添加的内容，那可能是一个更好的选择.

That said, do you really want to go down that road? You'll need to be able to parse the SQL statements and queries generated by L2S in order to modify them properly. If you can instead modify the linq queries to append whatever you want to add to them, that is probably a better alternative.

请记住，Linq查询是可组合的，因此，如果要添加到许多查询中，可以在单独的方法中添加"extras".

Remember that Linq queries are composable, so you can add 'extras' in a separate method if you have something that you want to add to many queries.

这篇关于如何在Linq to SQL中拦截和修改SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！