How to determine size in bytes of a result set from LINQ to SQL

Question

When writing manual SQL its pretty easy to estimate the size and shape of data returned by a query. I'm increasingly finding it hard to do this with LINQ to SQL queries. Sometimes I find WAY more data than I was expecting - which can really slow down a remote client that is accessing a database directly.

I'd like to be able to run a query and then tell exactly how much data has been returned across the wire, and use this to help me optimize.

I have already hooked up a log using the DataContext.Log method, but that only gives me an indication of the SQL sent, not the data received.

Any tips?

Answer 1

Looks like you can grab the SqlConnection of your DataContext and turn on statistics.

One of the statistics is "bytes returned".

MSDN Reference Link

Answer 2

I found no way to grab the SqlConnection of the DataContext, so i created the SqlConnection manually:

SqlConnection sqlConnection = new SqlConnection("your_connection_string");
// enable statistics
cn.StatisticsEnabled = true;

// create your DataContext with the SqlConnection
NorthWindDataContext nwContext = new NorthWindDataContext(sqlConnection);

var products = from product in nwContext
               where product.Category.CategoryName = "Beverages"
               select product;
foreach (var product in products)
{
    //do something with product
}

// retrieve statistics - for keys see http://msdn.microsoft.com/en-us/library/7h2ahss8(VS.80).aspx
string bytesSent = sqlConnection.RetrieveStatistics()["BytesSent"].ToString();
string bytesReceived = sqlConnection.RetrieveStatistics()["BytesReceived"].ToString();

Answer 3

Note: You need to cast the connection to a SqlConnection if you have an existing DataContext

 ((SqlConnection)dc.Connection).StatisticsEnabled = true;

then retrieve the statistics with :

 ((SqlConnection)dc.Connection).RetrieveStatistics()