How to intercept and modify SQL query in Linq to SQL

Question

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?

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]'

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

Answer 1

Ok, first to directly answer your question (but read on for words of caution ;)), there is a way, albeit a finicky one, to do what you want.

// IQueryable<Customer> L2S query definition, db is DataContext (AdventureWorks)
var cs = from c in db.Customers 
         select c;
// extract command and append your stuff
DbCommand dbc = db.GetCommand(cs);
dbc.CommandText += " WHERE MiddleName = 'M.'";
// modify command and execute letting data context map it to IEnumerable<T>
var result = db.ExecuteQuery<Customer>(dbc.CommandText, new object[] { });

Now, the caveats.

1. You have to know which query is generated so you would know how to modify it, this prolongs development.
2. It falls out of L2S framework and thus creates a possible gaping hole for sustainable development, if anyone modifies a Linq it will hurt.
3. If your Linq causes parameters (has a where or other extension causing a WHERE section to appear with constants) it complicates things, you'll have to extract and pass those parameters to ExecuteQuery

All in all, possible but very troublesome. That being said you should consider using .Where() extension as Yaakov suggested. If you want to centrally controll security on object level using this approach you can create an extension to handle it for you

static class MySecurityExtensions
{
    public static IQueryable<Customer> ApplySecurity(this IQueryable<Customer> source)
    {
        return source.Where(x => x.MiddleName == "M.");
    }
} 

//...
// now apply it to any Customer query
var cs = (from c in db.Customers select c).ApplySecurity();

so if you modify ApplySecurity it will automatically be applied to all linq queries on Customer object.

Answer 2

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.

E.g.:

//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...
  }
}

When using:

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

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.

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.

Answer 3

first thing come to my mind is to modify the query and return the result in Non-LINQ format

//Get linq-query as datatable-schema
        public DataTable ToDataTable(System.Data.Linq.DataContext ctx, object query)
        {
            if (query == null)
            {
                throw new ArgumentNullException("query");
            }

            IDbCommand cmd = ctx.GetCommand((IQueryable)query);
            System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter();
            adapter.SelectCommand = (System.Data.SqlClient.SqlCommand)cmd;
            DataTable dt = new DataTable("sd");

            try
            {
                cmd.Connection.Open();
                adapter.FillSchema(dt, SchemaType.Source);
                adapter.Fill(dt);
            }
            finally
            {
                cmd.Connection.Close();
            }
            return dt;
        }

try to add your condition to the selectCommand and see if it helps.

Answer 4

Try setting up a view in the DB that applies the security filter to the records as needed, and then when retrieving records through L2S. This will ensure that the records that you need will not be returned.

Alternatively, add a .Where() to the query before it is submitted that will apply the security filter. This will allow you to apply the filter programmatically (in case it needs to change based on the scenario).