In the not-too-distant past, VB (and C#, etc.) programs routinely built SQL SELECT statements in code. It was a straightforward, effective way of asking for a result set to meet the user's conditions. Match some textboxes, create a WHERE clause, presto, you're done.
I'll give my examples today in C#:
string sql = "SELECT * FROM OrderDetail WHERE ProdType = '" + cboType.SelectedText + "'";
// take only future and not fully paid.
if (chkOpenOnly.Checked) sql += " AND (LineAmount != PayAmount OR DelDate > GetDate())";
However, SQL in code doesn't get validated, and it doesn't show up in database reference tools. Keeping schema changes in sync with unattached executables can be a challenge. And because of vulnerability to SQL injection, many security-conscious DBAs now deny all access to dynamic SQL.
I recently needed to do some work on a website that did all its database access through a DataContext and LINQ. Repeater controls were bound to queries. Using the above technique was not an option.
This was my first (successful) attempt at the above logic:
var odet = (chkOpenOnly.Checked
? context.OrderDetails.Where(d ==> d.ProdType == cboType.SelectedText
&& (d.LineAmount != d.PayAmount || d.DelDate > DateTime.Now))
: context.OrderDetails.Where(d ==> d.ProdType == cboType.SelectedText)
The question mark/colon notation is the equivalent of VB's IF(condition, result, elseResult) function. I needed to do it this way rather than in an if/else structure, because query variables must be initialized in the same statement as their definitions. As you can imagine, this syntax can get really ugly if you have a lot of conditions.
But an interesting feature of LINQ is that your query can call functions elsewhere in your code. So here's another way:
var odet = context.OrderDetails.Where(d ==> d.ProdType == cboType.SelectedText
&& MyFilterFunction(chkOpenOnly.Checked, d.LineAmount, d.PayAmount, d.DelDate))
private bool MyFilterFunction(bool useFilter, decimal amt, decimal pay, DateTime dt)
if (!useFilter) return true; // no filter, then everything passes.
if (amt != pay) return true;
return (dt > DateTime.Now);
This logic gives the desired result and is a bit easier to read. Note, however, that the filtering happens on the client rather than at the database. For large recordsets, you should build database functions and procedures, and add those to your DataContexts.