.Net Ramblings

I’ve been working on my LinqToExcel open source project over this Christmas vacation, and currently I am trying to allow method calls to be used in the Linq statement like the example below shows

private int GetLargeEmployeeCount()
{
  return 200;
}

public Company[] GetLargeCompanies()
{
  IExcelRepository repo = new ExcelRepository("companies.xls");
  var companies = from c in repo.WorkSheet()
                  where c.EmployeeCount >= GetLargeEmployeeCount()
                  select c;

  return companies.ToArray();
}

Let’s take a quick look at the created ExpressionTree.

Now let’s look at the method that handles getting the value from the GetLargeEmployeeCount() method. The SQLExpressionVisitor class is responsible for converting the ExpressionTree created from the Linq statement into the SQL statement that will be used. SQLExpressionVisitor inherits from the common ExpressionVisitor class to aid in walking through the ExpressionTree.

The pertinent node that deals with the GetLargeEmployeeCount() method is highlighted in blue above. The return value from GetLargeEmployeeCount() is retrieved in VisitMethodCall().

protected override Expression VisitMethodCall(MethodCallExpression m)
{
  if (m.Method.Name == "Where")
  {
    _sql.Append(" WHERE ");
    this.Visit(m.Arguments[1]);
  }
  else if (m.Method.Name != "Select")
  {
    object methodObject = ((ConstantExpression)m.Object).Value;
    object methodValue = m.Method.Invoke(methodObject, GetMethodArguments(m.Arguments));
    _params.Add(new OleDbParameter("?", methodValue));
    _sql.Append("?");
  }
  return m;
}

VisitMethodCall() is responsible for dealing with ExpressionTree nodes that contain method calls. It turns out that the where in a Linq statement is actually a method call, so we append a “WHERE” to the string sql statement. _sql is a private StringBuilder object that is used to construct the sql statement based on the ExpressionTree.

But the code we’re really interested in is contained in the else if code block. The select Linq statement is also a method call and we’re not interested in that, so we ignore it in the else if condition.

Now we’re interested in getting the return value of the method. Let’s take another closer look at the Expression node for the GetLargeEmployeeCount() method.

This node contains all the information we need to use reflection to get the method return value. The first thing we need to do is get the object that contains the method, which we can get from the m.Object property with some casting of types.

object methodObject = ((ConstantExpression)m.Object).Value;

Now that we have the method object, we can use reflection to invoke the method and get the return value. I first tried to use type reflection to invoke the method.

object returnValue = methodObject.GetType().InvokeMember(m.Method.Name, BindingFlags.InvokeMethod, null, methodObject, GetMethodArguments(m.Arguments));

However, this only works when the method is public. If it isn’t then a MissingMethodException is thrown. Of course, only being able to use public methods is not acceptable, so we need to use Invoke() on the MethodInfo object to invoke the method.

object returnValue = m.Method.Invoke(methodObject, GetMethodArguments(m.Arguments));

The rest of the code block (_params and _sql) deals with generating the sql from the ExpressionTree.

One last point of interest is the GetMethodArguments(). This is a helper method that takes ReadOnlyCollection<Expression> and returns an array of corresponding objects. You can check it out in the SQLExpressionVisitor class if you’re interested in its implementation.

It turns out that it only takes a couple lines of code to get the return value for a method used in a Linq statement, but I spent a couple hours researching how to do it, so hopefully this article will be of help to others who are creating custom Linq providers.

I am continuing to play with NHibernate and wanted to use MS Access as the database back end. I thought it would be an easy migration that required only a couple NHibernate configuration settings being changed below.

<property name="dialect">
  NHibernate.JetDriver.JetDialect, NHibernate.JetDriver
</property>
<property name="connection.driver_class">
  NHibernate.JetDriver.JetDriver, NHibernate.JetDriver
</property>
<property name="connection.connection_string">
  Provider=Microsoft.Jet.OLEDB.4.0;Data Source=db1.mdb
</property>

But when I started up NHibernate, I received the following error:

System.IO.FileNotFoundException: Could not load file or assembly ‘NHibernate.JetDriver’ or one of its dependencies. The system cannot find the file specified.

File name: ‘NHibernate.JetDriver’

It turns out I needed to add the NHibernate.JetDriver DLL to the project. This DLL is not part of the main NHibernate project. It is located in the NHibernate Contrib project on Source Forge.

Building the NHibernate.JetDriver

1. Checkout the source code from Source Forge
   1. url: https://nhcontrib.svn.sourceforge.net/svnroot/nhcontrib/trunk
2. Build the NHibernate.JetDriver project
   1. Directory Location from trunk: /trunk/src/NHibernate.JetDriver
   2. The easiest way to build it is using the NAnt script included in the project (default.build)
      1. the NAnt script creates a “build” folder and places the driver in there
   3. You can also open the project and build it from there
      1. The driver will be located in: NHibernate.JetDriver\bin\Debug

Once you create the NHibernate.JetDriver all you need to do it copy it to your project directory and reference it from your project and then you should be good to use MS Access with NHibernate.

Oren Eini posted his results from typealyzer which determines your personality profile by reading your blog. So I thought I would see what the typealyzer said about me based upon my blog.

INTJ - The Scientists

The long-range thinking and individualistic type. They are especially good at looking at almost anything and figuring out a way of improving it - often with a highly creative and imaginative touch. They are intellectually curious and daring, but might be pshysically hesitant to try new things.

The Scientists enjoy theoretical work that allows them to use their strong minds and bold creativity. Since they tend to be so abstract and theoretical in their communication they often have a problem communcating their visions to other people and need to learn patience and use conrete examples. Since they are extremly good at concentrating they often have no trouble working alone.

I would have to say that description fits me pretty well.

I have always been annoyed by implementing the INotifyPropertyChange interface, which is needed if I want to take advantage of data binding for my class entities. I get annoyed because of how much code is involved in the Setter methods. Here’s an example:

public class Employee : INotifyPropertyChanged
{
  private string _name;

  public string Name
  {
    get { return _name; }
    set
    {
       if (_name != value)
       {
      	  _name = value;
          OnPropertyChanged("Name");
       }
    }
  }

  public event PropertyChangedEventHandler PropertyChanged;

  private void OnPropertyChanged(string propertyName)
  {
  	if (PropertyChanged != null)
    	  PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
  }
}

I hate all the if (_name != value) conditions that have to be in every setter, not to mention the lack of automatic refactoring since the property name is hard set. Therefore, I was really excited to see this post from my friend Jonathan Fuller and his much cleaner way of implementing INotifyPropertyChanged.

Be sure to read his entire post, but in a nutshell, by using his implementation, I would be able to cleanup the code above to the following:

public class Employee : NotifyPropertyChanged
{
  private string _name;

  public string Name
  {
     get { return _name; }
     set { SetProperty(()=> Name, ()=> _name, value); }
  }
}

Now that’s the kind of cleaning I like to see!

This past week I took the plunge and starting playing with NHibernate. I’ve wanted to use it for quite a while now, but it has a steep learning curve and I could never get a simple app to work. My main problem was that I wanted to use SQL Compact Edition as the database instead of SQL Server, and there aren’t many tutorials on getting the Compact Edition to work.

After much trial and error, I finally got NHibernate to work with SQL Compact Edition. Other than following the basic NHibernate configuration here are the additional steps I had to take to make NHibernate work with SQL Compact Edition.

• Add a reference of the System.Data.SqlServerCe to your project
  • Make sure to update its Copy Local property to True. By default it is set to False
    • When set to false, you will receive one of the two following errors:
    • The IDbCommand and IDbConnection implementation in the assembly System.Data.SqlServerCe could not be found. Ensure that the assembly System.Data.SqlServerCe is located in the application directory or in the Global Assembly Cache. If the assembly is in the GAC, use <qualifyAssembly/> element in the application configuration file to specify the full name of the assembly.
    • Could not create the driver from NHibernate.Driver.SqlServerCeDriver
      
• For computers running 64 bit processors: Make sure the project’s platform target is x86, not x64 nor Any CPU
  • This is a result of the ADO.Net provider not working with 64 bit systems
  • The setting is found in project Properties window under the Build tab.
    • Express Editions of Visual Studio do not have this drop down option in the Build tab. You will have to set the platform target by manually updating the .csproject file.
    • See this post for more information on updating the .csproj file
• Add SQL Compact Edition DLL’s to the project
  • These DLL’s are located in C:\Program Files\Microsoft SQL Server Compact Edition\v3.5
    • SQL Compact Edition must be installed on the computer
  • Add all the DLL’s starting with ‘sqlce‘
  • Set the Copy To Output Directory to true for all these files
  • This only needs done if you don’t want people to install SQL Compact Edition on their computer
• Nhibernate Configuration Settings
  • Latest version of NHibernate does not need the hibernate prefix to every setting
  • Below are 2 different ways to configure NHibernate
  • Programmatic Configuration

    IDictionary properties = new Dictionary();
    properties.Add("connection.driver_class", "NHibernate.Driver.SqlServerCeDriver");
    properties.Add("dialect", "NHibernate.Dialect.MsSqlCeDialect");
    properties.Add("connection.provider", "NHibernate.Connection.DriverConnectionProvider");
    properties.Add("connection.connection_string", "Data Source=MyDatabase.sdf; Password='some password'");
    Configuration conf = new Configuration();
    conf.Properties = properties;

  • App.config Configuration

    <?xml version="1.0" encoding="utf-8" ?>
    <configuration>
      <configSections>
        <section name="hibernate-configuration" type="NHibernate.Cfg.ConfigurationSectionHandler,NHibernate" />
      </configSections>
      <hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
        <session-factory>
          <property name="connection.provider">
            NHibernate.Connection.DriverConnectionProvider
          </property>
          <property name="dialect">
            NHibernate.Dialect.MsSqlCeDialect
          </property>
          <property name="connection.driver_class">
            NHibernate.Driver.SqlServerCeDriver
          </property>
          <property name="connection.connection_string">
            Data Source=MyDatabase.sdf; Password='some password'
          </property>
        </session-factory
      </hibernate-configuration>
    </configuration>

You can also download a small sample app that uses SQL Compact Edition.