SQL CLR Objects for Heavy Operations

Question

Here is my scenario:

I have implemented set logic in my C# framework. Sets can contain a large quantity of objects, perhaps even up to 1 million in the worse case. Assume sets just contain lists of objects called Doc. Because of the potential large number of objects, I would like to give the developer a choice of how to create and use sets.

For smaller sets, the developer can just create a new set and add doc objects to the set. Normal set logic is available such as union, intersect, etc. The developer can then at any point persist the set with my OR layer in which the set will have a primary ID assigned to it.

For very large sets which are persisted in the database, I would like to circumvent the process of extracting the IDs of the doc objects from the database and then saving the IDs back into the set object by allowing manipulation of the set in the SQL 2008 CLR. I would like to have a simple function such as this signature:

public void AddSetMembers(int setID, Expression<Func<Doc, bool>> Where)

This would allow the developer to add arbitrary documents to the set matching the Expression without extracting all of the IDs from the database with the only purpose of adding them to the set.

My question is how best to approach this implementation in a stored procedure? Can I somehow pass a lambda to a stored procedure? I had thought about implementing an IDoc interface and loading it into the CLR and having a signature such as:

public void AddSetMembers(int setID, Expression<Func<IDoc, bool>> Where)

however I would still need to pass the lambda to the SP. I want to make the framework flexible enough so that the developer can use different conditions to create sets.

Answer 1

You could pass a string from your SQL to your SQL CLR method containing the lambda. Then compile that string into a function/method using the framework's compilation tools and then invoke it.