How do I implement progressive filtering in SQL, executed from C#?

Question

What is the best way to provide a "progressive filtering" system for SQL executed from C#?

I have a single table that I'd like to query by providing successive SQL statements to progressively refine the output. The key feature: Each statement would operate on the output of the previous statement. What my code got back would be the output from the last statement.

For example:

SELECT * FROM People WHERE LastName = 'Bond'
SELECT * FROM [whatever was returned from above] WHERE FirstName = 'James'

That's trivial, clearly. Suffice it to say that my real problem is considerably more complex, and it involves multiple "filters" being formed in multiple different code locations in C#. It's complex enough that using multiple WHERE statements to shoehorn all filtering into a single command has gotten hopelessly confusing.

For clarity, I'm going to call a group of these SQL statements an intention.

I think I can use temporary tables, like this:

SELECT * INTO #MyTempTable FROM People WHERE LastName = 'Bond'
SELECT * FROM #MyTempTable WHERE FirstName = 'James'

Clearly, the SELECT...FROM could be implied. This is always going to be a single table, so I really just need to specify a series of WHERE clauses:

LastName = 'Bond'
FirstName = 'James'

The first one would select from the table (People), and every other would one select from the temp table (MyTempTable in the example).

Since C# will be executing these successive statements via SqlConnection, how do I maintain temp table integrity from execution to execution in the same intention, and prevent them from spanning intentions? Additionally, how do I prevent "leftover" temporary tables from existing?

I can use the same SqlCommand object for each intention (just swapping out the CommandText each time), so an intention would share a Connection, but do I have to close the connection to remove those temporary tables?

Another hack-ish way might be to name the temporary table some random value based on a GUID, that only a single intention knows about, so I could be sure that no other intention would "cross paths" with it.

Another idea might be to somehow pass all the SQL into a stored proc that would SQLEXEC each in order (I really only need the WHERE clauses, since the table will be the same for each).

I can't be the first person to need to do this.

Edit:

A lot of people have pointed out that this should be done by simply concatenating the WHERE clauses with ANDs. However, here's an example of where it gets more complicated than this --

One example of a filter might be a windowed partition of the table that takes the output of the previous filter, groups on a specific column, calculates aggregates of other columns, and returns a virtual representation of the table for the next filter to work on.

This is just isn't going to fit into a WHERE clause.

Seriously, the simple x = 'y' examples from above were just the tip of the iceberg. Some of the filters will need to operate on the entire table to do some more arcane SQL.

I don't understand why just adding `AND FirstName = 'James'` is not a sufficient filter? Why do you need to deal with temp tables? — Siyual, Aug 23 '17 at 20:06
@Siyual As I note, this is a very, very simplified example just to illustrate the point. The actual filtering requirements are vastly more complicated. — Deane, Aug 23 '17 at 20:08
If you could use an ORM like Entity Framework. Then using Linq to Entities you can combine several .Where statements and upon execution those will be translated into a sql statement. This allows you to create and combine filters prior to executing the actual query. — Peter Bons, Aug 23 '17 at 20:35
Regardless of how complicated your filtering requirements are, the sql and c# code are both going to be infinitely simpler and your query is going to be much more efficient if you just separate your where clauses in the way that multiple where clauses were meant to be seperated (between and's). Having a bunch of subqueries on temp tables is almost certainly not the right solution. — maembe, Aug 23 '17 at 20:36
Everyone: I edited the question (at the bottom) to provide an example of a filter that is just not going to fit into a `WHERE` clause. — Deane, Aug 23 '17 at 21:15
@Deane Old style: temp tables, Better: table variables > http://odetocode.com/articles/365.aspx Best: CTE > https://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx — RandomUs1r, Aug 23 '17 at 21:22
An ORM together with SQL views, table functions, and stored procedures could be used. The ORM could allow you to build up LINQ statements, and they could be mapped to your more complex stored procs, etc. — ps2goat, Aug 23 '17 at 21:24
@RandomUs1r Yeah, wow, CTEs look like a close match. But can you nest them, so you can do a subsequent select from the output of a CTE? — Deane, Aug 23 '17 at 21:25
@Deana Looks like you can, take a look at https://stackoverflow.com/questions/27204824/sql-use-a-reference-of-a-cte-to-another-cte — RandomUs1r, Aug 24 '17 at 16:09

score 0 · Answer 1 · answered Aug 23 '17 at 21:42

Pull your first set of results down to C#. Extremely over-simplified pseudo-code:

var results = GetData(initialfilter);

Make sure whatever data type you get for the results implements the IEnumerable interface. Then you can further narrow your results by acting on that original:

results = results.Where( additionalFilter );

and later:

results = results.Where( thirdFilter );

This will also allow you to implement some of the more advanced filters that would be harder with a pure SQL solution.

score 0 · Answer 2 · answered Aug 23 '17 at 21:52

A SQL Stored Procedure would probably be the way to go.

This would be preferable to moving, say, 1,000,000 rows from SQL Server over to the .Net Framework so that you can have a LINQ query narrow that down to 25 rows.

Keep in mind you don't need to use "#" temp tables, you can use "@" tables, that would answer your question:

I can use the same SqlCommand object for each intention (just swapping out the CommandText each time), so an intention would share a Connection, but do I have to close the connection to remove those temporary tables?

With @table (table variable), NO ... they are cleaned up automatically. You can read the documentation on @ vs. # vs. ##. # is session based, ## is global, etc.

Do a SELECT/INSERT into a table variable, filter on that, and repeat as necessary.

You could theoretically also do a combination of sub-SELECT statements here but that could get messy ... stuff like:

SELECT * FROM (SELECT * FROM table2 WHERE LastName = 'Jones') WHERE FirstName = 'Bob'

score 0 · Answer 3 · answered Aug 24 '17 at 01:26

Assuming SQL Server here.

Keep a running list of the filters applied and build a multi-statement query to be run in one shot... you'll always be reading the result of the select at the bottom of the method. This approach gets you away from all that temp table business.

private string BuildMyQuery(List<string> myStatements)
{
   // myHoldingTable1 will always contain the last result

   var sb = new StringBuilder();
   sb.AppendLine("DECLARE @myHoldingTable1 TABLE (Id int, Value1 varchar(100), ...);");
   sb.AppendLine("DECLARE @myHoldingTable2 TABLE (Id int, Value1 varchar(100), ...);");
   sb.AppendLine("INSERT INTO @myHoldingTable1 (Id, Value1 ....) SELECT id, value1... from myInitialTable;");
   foreach(var myStatement IN myStatements)
   {
      sb.AppendLine($"INSERT INTO @myHoldingTable2 (Id, Value1 ....) myStatement;");
      sb.AppendLine("DELETE FROM @myHoldingTable1;");
      sb.AppendLine("INSERT INTO @myHoldingTable1 (Id, Value1 ....) SELECT id, value1... from @myHoldingTable2;");
      sb.AppendLine("DELETE FROM @myHoldingTable2;");

   }

   sb.AppendLine("SELECT * FROM @myHoldingTable1;");
   return sb.ToString();
}

How do I implement progressive filtering in SQL, executed from C#?

3 Answers3