Using LINQ to Access SQL Server Compact Directly – A follow up

You may recall my webcast from about 6/7 weeks ago where I talked about how to use custom extension methods to allow your mobile device applications to efficiently query a SQL Server Compact (SSC) databases directly.

I received a question today regarding the webcast. The question relates to the verbosity of returning anonymous types when using the technique discussed in the webcast. For example…

var records = from order in resultSet
where (string)order["Ship Country"] == "UK"
select new
{
ShipName = (string)order["Ship Name"],
ShipAddress = (string)order["Ship Address"],
ShipCity = (string)order["Ship City"],
ShipPostalCode = (string)order["Ship Postal Code"],
ShipCountry = (string)order["Ship Country"],
ShipVia = (int)order["Ship Via"]
};

The verbosity is necessary because "order" in the above LINQ statement is of type SqlCeUpdatableRecord. Although Visual Studio supports generating typed-wrappers for SqlCeResultSet, it doesn't generate wrappers for the SqlCeUpdatableRecord corresponding to the SqlCeResultSet. With that being the case, specifying the individual column values within the anonymous type declaration requires you to use either the indexer (or Getxxx functions).

Ultimately there's nothing we can do about the way the column values are accessed (unless you write your own typed-wrapper generator for SqlCeUpdatableRecord). What we can do is move the code that creates the anonymous type into a separate function.

private object ShippingColumns(SqlCeUpdatableRecord order)
{
return new
{
ShipName = (string)order["Ship Name"],
ShipAddress = (string)order["Ship Address"],
ShipCity = (string)order["Ship City"],
ShipPostalCode = (string)order["Ship Postal Code"],
ShipCountry = (string)order["Ship Country"],
ShipVia = (int)order["Ship Via"]
};
}

With that, the LINQ statement becomes very simple.

var records = from order in resultSet
where (string)order["Ship Country"] == "UK"
select ShippingColumns(order);

Once we move the anonymous type declaration to a separate function we can take things one step further and optimize the column access by caching the column ordinals and then retrieving the column values using the Getxxx functions.

private object ShippingColumns(SqlCeUpdatableRecord order)
{
if (_nameIndex == -1)
InitializeIndexes(order);

return new
{
ShipName = order.GetString(_nameIndex),
ShipAddress = order.GetString(_addressIndex),
ShipCity = order.GetString(_cityIndex),
ShipPostalCode = order.GetString(_postalCodeIndex),
ShipCountry = order.GetString(_countryIndex),
ShipVia = order.GetInt32(_viaIndex)
};
}

private void InitializeIndexes(SqlCeUpdatableRecord record)
{
_nameIndex = record.GetOrdinal("Ship Name");
_addressIndex = record.GetOrdinal("Ship Address");
_cityIndex = record.GetOrdinal("Ship City");
_postalCodeIndex = record.GetOrdinal("Ship Postal Code");
_countryIndex = record.GetOrdinal("Ship Country");
_viaIndex = record.GetOrdinal("Ship Via");
}

private int _nameIndex = -1;
private int _addressIndex = -1;
private int _cityIndex = -1;
private int _postalCodeIndex = -1;
private int _countryIndex = -1;
private int _viaIndex = -1;

Although not a huge performance increase, storing the column indices does eliminate the overhead of the indexer looking up the column name each time. More importantly, using the strongly-typed Getxxx functions eliminates the overhead of boxing any column values that are value-types. As you know, excessive boxing creates a lot of scrap objects which leads to increased memory and garbage collection overhead.

Using regular class methods like those above work just fine; however, if you find that you use a common anonymous type throughout different parts of your application, you may want to use an extension method – extension methods are also nice just because of their class-member-like syntax.

public static class ResultSetExtension
{
public static IEnumerable<SqlCeUpdatableRecord> Where(
this SqlCeResultSet resultSet, Func<SqlCeUpdatableRecord, bool> theFunc)
{
return new PrepAwareEnumerableWrapper(resultSet, theFunc);
}

public static object ShippingColumns(this SqlCeUpdatableRecord order)
{
if (_nameIndex == -1)
InitializeIndexes(order);

return new
{
ShipName = order.GetString(_nameIndex),
ShipAddress = order.GetString(_addressIndex),
ShipCity = order.GetString(_cityIndex),
ShipPostalCode = order.GetString(_postalCodeIndex),
ShipCountry = order.GetString(_countryIndex),
ShipVia = order.GetInt32(_viaIndex)
};
}

private void InitializeIndexes(SqlCeUpdatableRecord record) { ... }

With the extension method, the LINQ statement becomes…

var records = from order in resultSet
where (string)order["Ship Country"] == "UK"
select order.ShippingColumns();

A couple of notes before I finish up…

One thing to keep in mind is that all of these functions that create the anonymous type have a return type of object. This means that the IEnumerable<T> collection that is created by the LINQ statement will be of type IEnumerable<object> rather than IEnumerable<compiler_generated_type>. In most cases this difference does not matter but it is something to be aware of.

And for a final note, if you do find yourself returning the same anonymous type construct from a number of LINQ statements, you might want to consider defining an explicit type and then constructing instances of that type within the LINQ statement. In my experience, anytime I find myself using an anonymous type/function/etc. more than once or twice that I ultimately end up needing access to it in my code in a non-anonymous fashion.

To return a specific type from a LINQ statement, simply define a type that has a constructor that accepts a SqlCeUpdatableRecord and assigns the desired columns to the corresponding type members – basically the constructor will look like the ShippingColumns methods shown above. Assuming that you've defined a class named ShipInfo with the appropriate constructor, your LINQ statement would look like the following…

var records = from order in resultSet
where (string)order["Ship Country"] == "UK"
select new ShipInfo(order);

I've updated one of the samples from the original webcast to include examples of what we've talked about in this post. If you'd like the updated sample, you can download it from here. The methods you'll want to look at in the download are menuRedefineType_Click, menuDynamicType_Click, menuDynamicTypeExtMethod_Click all of which are in the Form1.cs source file.