Bee Eee Blog

I’ve often been frustrated by the sql that linq produces.  Usually it’s really good, but occasionally it produced monstrosities.   The hard part is getting at the sql that’s produced by a function.  Hovering over the statement in the idea is a real pain so instead I created the following class that dumps the sql to a file

public class DCLogger : IDisposable
    {
        private StringWriter writer;

        public DCLogger(System.Data.Linq.DataContext dc)
        {
            writer = new StringWriter();
            dc.Log = writer;
        }

        #region IDisposable Members

        public void Dispose()
        {
            writer.Flush();
            System.IO.File.WriteAllText( "c:\\temp\\sql.log", writer.ToString() );
        }

        #endregion
    }

To use it just create it passing it you data context. The results are written to the file when it’s disposed.

  using( DCLogger( dc ) )
  {
    // ... some linq statement that executes
  }

Here’s a helpful, and perhaps dangerous, little tidbit of code for SQL Server 2005. It deletes all of the data from all of the tables. To be completely effective, the command may need to be run more than once because of foreign keys. Basically run it until there aren’t any errors. Then the db is clean and empty of all data.

exec sp_MSForeachTable "delete from ?"

the sp_MSForeachTable is a very useful system stored procedure.

There are four different string comparisons for handling string fields with linq to sql.  They are as follows:

The exact macth

DataContext dc = new DataContext(...)
var results =
  from row in dc.table
  where row.string_column == "Exact Match"
  select row;

Begins With:

DataContext dc = new DataContext(...)
var results =
  from row in dc.table
  where row.string_column.StartsWith("Ex")
  select row;

produces an sql match string “Ex%”

Contains

DataContext dc = new DataContext(...)
var results =
  from row in dc.table
  where row.string_column.Contains("Ex")
  select row;

Which is equivalent to “%Ex%”

Ends With

DataContext dc = new DataContext(...)
var results =
  from row in dc.table
  where row.string_column.EndsWith("Ex")
  select row;

Which is equivalent to “%Ex”

I’ve recently been writing an application that builds a linq to sql query dynamically.  For instance:

MyDataContext dc() = new MyDataContext;
var query = dc.MyTables;
query = query.Where( t=>t.id > 10);

and so I want to order it by a certain column:

query = query.OrderBy( t=>t.id )

but the problem is the order direction isn’t know before hand so at first I was stuck with the following:

if ( descending )
    query = query.OrderByDescending( t=>t.id);
else
    query = query.OrderBy(t=>t.id);

Now if there is only a few possible columns to sort against it isn’t too bad.  But when you have 10 different possible columns it really makes a lot of extra work just to control to sort direction.  So I made a little helper class that allows the following.  It works with linq and linq to sql:

query = query.OrderByAscDec( t=>t.id, descending );

here’s the static class that implments the OrderByAscDec function:

static class VPLinqExtensions
{
	public static IOrderedQueryable<TSource> OrderByAscDes<TSource, TKey>(
		this IQueryable<TSource> source,
		System.Linq.Expressions.Expression<Func<TSource,
		TKey>> keySelector, bool IsDescending)
	{
		if (IsDescending)
			return source.OrderByDescending(keySelector);
		else
			return source.OrderBy(keySelector);
	}
}