Archive for the ‘C#’ Category

Access resources within WMI commands in a Windows service

Friday, September 24th, 2010

This is a little obscure. I’m writing a Windows service that can be controlled via WMI. I use Log4Net to log all of the service’s activities. I can see logs from the service starting up and doing its regular processing, but I can’t see logs from WMI commands.

In addition, when the WMI command tells the service to access a resource (eg. load a configuration file or walk a directory tree), the command fails. WMI error messages aren’t much help (“Not found” anybody? How about “The RPC server is unavailable.”) And with logging not working, I have to attach the debugger to see what’s happening.

Cancel impersonation

The reason for both of these problems is that the WMI user doesn’t have access to either the log file or the resource I’m trying to access. The solution, cancel impersonation:

public class CancelImpersonation : IDisposable
{
    private WindowsImpersonationContext _impersonationContext;

    public CancelImpersonation()
    {
        _impersonationContext = WindowsIdentity.Impersonate(IntPtr.Zero);
    }

    public void Dispose()
    {
        _impersonationContext.Undo();
    }
}

Wrap one of these in a using statement, then anything you do in the brackets is running as local system (or whatever user the service is configured to run as).

Easy .NET directory helper

Wednesday, June 23rd, 2010

Add this class to your C# project:

public class Directory
{
    private string _path;

    private Directory(string path)
    {
        _path = path;
    }

    public static Directory ApplicationData
    {
        get
        {
            return new Directory(Environment.GetFolderPath(
                Environment.SpecialFolder.ApplicationData));
        }
    }

    public static Directory CommonApplicationData
    {
        get
        {
            return new Directory(Environment.GetFolderPath(
                Environment.SpecialFolder.CommonApplicationData));
        }
    }

    public static Directory operator /(Directory root, string folder)
    {
        return new Directory(Path.Combine(root._path, folder));
    }

    public static implicit operator string(Directory directory)
    {
        return directory._path;
    }
}

Now you can express directories naturally:

string databaseFileName = Directory.ApplicationData / "MichaelLPerry" / "CorrespondenceIM" / "Correspondence.sdf";

I don’t have a good place for code like this to live. What do you do with these kinds of utilities?

Java/WCF Interop

Monday, February 8th, 2010

As of today (February 2010), the story of Java/WCF interoperability is fair. That wasn’t always the case. In the past, I’ve struggled to get Java and .NET to play nice. Today, I was able to make a .NET WCF client talk to a Java CXF web service with just a little coaxing. Here’s how I did it.

Contract first
The first step to successful interoperability is to define the contract. Somehow you need to generate the WSDL, and you need to tightly control what it looks like. Use tools to help you, but keep a close eye on what those tools do.

I started with a WCF service contract. This is a .NET interface that uses the [ServiceContract] and [OperationContract] attributes. Put this interface and all of the data types it uses into a class library project. Here’s an example:

[ServiceContract(Namespace = "http://correspondence.updatecontrols.com")]
public interface ISynchronizationService
{
    [OperationContract]
    FactTree Get(FactTree pivotTree, long pivotId, long timestamp);

    [OperationContract]
    void Post(FactTree messageBody);
}

The FactTree data type used by this interface is decorated with the [DataContract] and [DataMember] attributes.

[DataContract(Namespace = "http://correspondence.updatecontrols.com")]
public class FactTree
{
    [DataMember]
    public long DatabaseId { get; set; }

    [DataMember]
    public List<Fact> Facts { get; set; }

    [DataMember]
    public List<FactRole> Roles { get; set; }

    [DataMember]
    public List<FactType> Types { get; set; }
}

Create a WCF service

Even though we want to end up with a Java web service, an intermediate step is to implement this service in WCF.

  1. Create a new project in Visual Studio using the template Visual C#: Web: WCF Service Application.
  2. Add a reference to the class library that defines the interface.
  3. Change the name of the generated service from Service1 to something meaningful.
  4. Delete the generated IService1 interface.
  5. Use your own interface instead.
  6. Add a [ServiceBehavior] attribute to set the Namespace.

At this point, the service looks something like this:

// NOTE: If you change the class name "Service1" here, you must also update the reference to "Service1" in Web.config and in the associated .svc file.
[ServiceBehavior(Namespace = "http://correspondence.updatecontrols.com")]
public class SynchronizationService : ISynchronizationService
{
    public FactTree Get(FactTree pivotTree, long pivotId, long timestamp)
    {
        throw new NotImplementedException();
    }

    public void Post(FactTree messageBody)
    {
        throw new NotImplementedException();
    }
}

Like the comment says, we need to edit the svc file and the web.config. Right-click the svc file in the project tree and select “View Markup”. Change the Service attribute to the fully qualified name of the service class.

<%@ ServiceHost Language="C#" Debug="true"
  Service="UpdateControls.Correspondence.WebService.SynchronizationService"
  CodeBehind="SynchronizationService.svc.cs" %>

The web.config change is slightly more complicated. There’s a lot of junk in web.config that you don’t need to worry about. The section you want is all the way at the bottom. Look for the <service> tag. It has two attributes: name and behaviorConfiguration. Also look for the <endpoint> tag right below it. It has three attributes: address, binding, and contract.

  1. Change the service name to the fully qualified name of your service class.
  2. Change the endpoint binding from wsHttpBinding to basicHttpBinding.
  3. Change the endpoint contract from IService1 to the fully qualified name of your interface.

Here’s a trick to getting the fully qualified names. Delete the text between the quotes of the attributes. Open the Class View by hitting Ctrl+Shift+C in Visual Studio. Expand the tree to find your service class and interface. Drag them onto the web.config file between the quotes.

You can also change the name of the service behavior, but that’s not necessary for this intermediate step.

Examine the WSDL

These steps ensure that we have nice clean WSDL to work from. Take a look at it by running your WCF service application. A directory listing will open in the browser. Click on the svc file. If you get a yellow screen, please double-check your steps.

Click on the link to see the WSDL you’ve created. Different browsers react differently to raw XML. IE and Firefox will show it to you, but Chrome will give you a blank screen. You’ll have to view source to see the WSDL in Chrome.

On this first page, you’ll see all of the input and output messages, and the operations, and the service itself. Double-check that the service uses binding="i0:BasicHttpBinding_...".

Hack the url to look at more detailed information. Change the query string to “?wsdl=wsdl0” to see the declaration for the binding. It uses “http://schemas.xmlsoap.org/soap/http” with the “document” style.

Hack the url again with “?xsd=xsd0” to see the data types. You should recognize these data types as the ones you wrote in C#. Notice that it turns all of your List<T>s into ArrayOfTs. When we import these into Java, they will become classes containing List<T>.

Create the Java contract project

Create a Java project in your favorite IDE (mine is Eclipse). Open a command prompt and go to the source directory of that project (probably ends in “src”). Download Apache CXF and unzip it to your hard drive (mine is in “c:\apache-cxf-2.2.6”).

Go back to the first WSDL page, the one with the “?wsdl” query string. This is the URL that we are going to generate Java files from. Copy this URL and use it at the command line:

\apache-cxf-2.2.6\bin\wsdl2java.bat http://localhost:3642/SynchronizationService.svc?wsdl

CXF will generate a bunch of class files. Most will be in a package derived from your namespace. One will be in “com.microsoft.schemas._2003._10.serialization”. If you find one in org.tempuri package, you forgot a Namespace setting in one of your attributes. These class files are decorated with enough annotations to make them compatible with the WCF service.

Create the Java service project

Although you could put your service implementation and contract in the same project, I prefer to keep them separate. You can use the contract project to write a different service implementation, or even to write a client.

Create a new Dynamic Web Project. Add to the new project a reference to the contact project. You will also need to add this reference to the Java EE Module Dependencies in the project properties. Otherwise it won’t copy the contract jar file to the service lib directory, resulting in a NoClassDefFoundError at runtime. Then add a class that implements the service contract. Copy the @WebService annotation from the interface to the class. The service looks something like this:

@WebService(targetNamespace = "http://correspondence.updatecontrols.com", name = "ISynchronizationService")
public class SynchronizationService implements ISynchronizationService {

    @Override
    public FactTree get(FactTree pivotTree, Long pivotId, Long timestamp) {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public void post(FactTree messageBody) {
        // TODO Auto-generated method stub

    }

}

The service project needs the CXF jar files. Copy them from the CXF install folder (C:\apache-cxf-2.2.6\lib) into the project’s library folder (WebContent\WEB-INF\lib). This is the minimal set that you will need:

  • jaxb-api-2.1.jar
  • jaxb-impl-2.1.12.jar
  • wsdl4j-1.6.2.jar
  • XmlSchema-1.4.5.jar
  • cxf-2.2.6.jar

Now we need to publish this web service as a servlet. The quickest way to do that is to derive a class from CXFNonSpringServlet. Right-click the project and select "New: Servlet”. Change the servlet base class to “org.apache.cxf.transport.servlet.CXFNonSpringServlet”. Uncheck the boxes to implement doGet and doPost. The base class handles those for you. Once the class is created, override the loadBus method.

package com.updatecontrols.correspondence.service;

import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.xml.ws.Endpoint;

import org.apache.cxf.transport.servlet.CXFNonSpringServlet;

public class SynchronizationServlet extends CXFNonSpringServlet {
    @Override
    public void loadBus(ServletConfig servletConfig) throws ServletException {
        super.loadBus(servletConfig);        

        Endpoint.publish("/SynchronizationService", new SynchronizationService());
    }
}

Open the web.xml file. You will notice that a servlet mapping was created for you. This mapping is set up to handle URLs that directly address the servlet, but the CXF servlet adds the service name to the URL. Add a “/*” to the end of the URL pattern to direct all such addresses to the servlet.

<?xml version="1.0" encoding="UTF-8"?>
<web-app id="WebApp_ID" version="2.4" xmlns="http://java.sun.com/xml/ns/j2ee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">
    <display-name>correspondence_sync_service</display-name>
    <servlet>
        <description>
        </description>
        <display-name>SynchronizationServlet</display-name>
        <servlet-name>SynchronizationServlet</servlet-name>
        <servlet-class>
        com.updatecontrols.correspondence.service.SynchronizationServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>SynchronizationServlet</servlet-name>
        <url-pattern>/SynchronizationServlet/*</url-pattern>
    </servlet-mapping>
</web-app>

Run the project in Tomcat to make sure the servlet is published correctly. Point a browser at the servlet (in my case http://localhost:8080/correspondence_sync_service/SynchronizationServlet) and you should see a listing of available SOAP services. Append the service name to the URL (http://localhost:8080/correspondence_sync_service/SynchronizationServlet/SynchronizationService) and you will get a 500 error. If you get a 404, you haven’t modified the web.xml file correctly.

Create a WCF client

The last step is the easiest. Since we started by creating a WCF service contract, we can ask WCF to create a client proxy. I documented this technique in .NET to .NET web services without WSDL. It turns out that this trick works equally well for .NET to Java web services.

Add an endpoint to the app.config of your client. The URL should be the servlet name followed by the service name. For example:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <system.serviceModel>
    <bindings>
      <basicHttpBinding>
        <binding name="SynchronizationServiceSoapBinding" closeTimeout="00:01:00"
            openTimeout="00:01:00" receiveTimeout="00:10:00" sendTimeout="00:01:00"
            allowCookies="false" bypassProxyOnLocal="false" hostNameComparisonMode="StrongWildcard"
            maxBufferSize="65536" maxBufferPoolSize="524288" maxReceivedMessageSize="65536"
            messageEncoding="Text" textEncoding="utf-8" transferMode="Buffered"
            useDefaultWebProxy="true">
          <readerQuotas maxDepth="32" maxStringContentLength="8192" maxArrayLength="16384"
              maxBytesPerRead="4096" maxNameTableCharCount="16384" />
          <security mode="None">
            <transport clientCredentialType="None" proxyCredentialType="None" realm=""/>
            <message clientCredentialType="UserName" algorithmSuite="Default" />
          </security>
        </binding>
      </basicHttpBinding>
    </bindings>
    <client>
      <endpoint address="http://localhost:8080/correspondence_sync_service/SynchronizationServlet/SynchronizationService"
          binding="basicHttpBinding" bindingConfiguration="SynchronizationServiceSoapBinding"
          contract="UpdateControls.Correspondence.WebService.Contract.ISynchronizationService" name="SynchronizationService" />
    </client>
  </system.serviceModel>
</configuration>

And with that you have a .NET WCF client communicating with a Java CXF server. I expect that similar strategies could be used to go the other direction, although I haven’t tried yet.

My wish for C#: class parameters

Friday, September 11th, 2009

How C# works today:

public class Order
{
    private Customer _customer;

    public Order(Customer customer)
    {
        _customer = customer;
    }

    public Customer Customer
    {
        get { return _customer; }
    }

    public void Fulfill()
    {
        Package.ShipTo(_customer.ShippingAddress);
    }
}

How I wish it worked:

public class Order(Customer _customer)
{
    public Customer Customer
    {
        get { return _customer; }
    }

    public void Fulfill()
    {
        Package.ShipTo(_customer.ShippingAddress);
    }
}

Advantages:

  • Class parameters would be immutable.
  • Classes could only have one constructor.
  • Constructors could not have side effects.
  • Constructors could not throw exceptions.
  • Class parameters would only have to be declared once instead of 3 times (counting the assignment).

Anonymous delegates vs. lambdas

Thursday, September 10th, 2009

An interesting question came up during last week's Q.E.D. discussion on closure. Why are there two language features in C# that do the same thing?

Lambdas:

public Order GetOrder(string orderId)
{
    return GetOrderRepository()
        .GetSatisfying(o => o.OrderId == orderId)
        .Query()
        .FirstOrDefault();
}

Anonymous delegates:

public Order GetOrder(string orderId)
{
    return GetOrderRepository()
        .GetSatisfying(delegate(Order o)
            { return o.OrderId == orderId; })
        .Query()
        .FirstOrDefault();
}

Do you see the difference? Let me give you a clue. Here's another way to write the anonymous delegate:

public Order GetOrder(string orderId)
{
    return GetOrderRepository()
        .GetSatisfying(o =>
           { return o.OrderId == orderId; })
        .Query()
        .FirstOrDefault();
}

Even though this syntax uses the lambda operator "=>", it still behaves more like an anonymous delegate. The important difference is not the "=>" or the "delegate", its the "return" and the semicolon.

A lambda contains an expression. An anonymous delegate contains statements. Sure, that could be a single return statement, but it doesn't have to be.

It should be stated that only the first block of code compiles in this instance. That's because GetSatisfying takes "Expression<Func<Order, bool>>", and not just "Func<Order, bool>". A function is just a block of code that you can call which happens to return a value. An expression, on the other hand, exists only to produce that value.

Lambdas are expressions. Anonymous delegates are functions.

Linq and regular expressions: a perfect match

Friday, August 21st, 2009

Both Linq and regular expressions are great ways to write declarative code. When you combine the two, the result is magic.

Here's a utility class that returns a human readable name from a camel-case identifier.

public static class NameUtilities
{
    private static Regex WORD = new Regex(
        // Lower-case letters at the beginning of the word.
        "(^[a-z]+)|" +
        // At least two upper-case letters not followed by a lower case letter.
        "([A-Z]{2,}(?![a-z]))|" +
        // An upper-case letter followed by lower-case letters.
        "([A-Z][a-z]+)");

    public static string HumanReadableName(string identifier)
    {
        // Split the identifier at capitals followed by lower case.
        return WORD.Matches(identifier).OfType<Match>()
            .Select(m => InitialCaps(m.Value))
            .Aggregate((name, part) => name + " " + part);
    }

    private static string InitialCaps(string word)
    {
        if (word.Length < 2)
            return word.ToUpper();
        else
            return word.Substring(0, 1).ToUpper() + word.Substring(1);
    }
}

The Matches method returns a MatchCollection. Since this class predates .NET generics, it implements the untyped IEnumerable. OfType<Match>() is required to safely cast each member to a Match.

Each of the Matches is converted into a capitalized word, and the words are concatenated with intervening spaces. The Aggregate() trick is thanks to Deborah Kurata. Sure, it might be less efficient than StringBuilder.Append(), but measure before you assume.

The combination of two great declarative programming techniques creates a concise, readable piece of code. The same algorithm written imperatively would be much more complex.

Avoid mutable variables in linq expressions

Wednesday, April 29th, 2009

Please take a moment to determine whether this unit test (in C#) passes.

var collection = new NamedNumber[]
{
    new NamedNumber() { Name = "One", Number = 1 },
    new NamedNumber() { Name = "Two", Number = 2 },
    new NamedNumber() { Name = "Three", Number = 3 }
};
IEnumerable<string> names = null;
for (int i = 1; i < 3; ++i)
{
    if (i == 1)
        names = from n in collection where n.Number == i select n.Name;
}

string name = names.Single();
Assert.AreEqual("One", name);

We are producing an enumerator over named numbers using linq syntax. Only when i is equal to 1 do we produce this enumerator. You would therefore assume that it would enumerate only the numbers equal to 1.

If that were the case, I wouldn't bother to write this post. Indeed, I wouldn't have even written this unit test. Nope, this test fails:

Assert.AreEqual failed. Expected:<One>. Actual:<Three>.

As you may already know, a linq expression is not evaluated until it is realized. The IEnumerable holds the instructions to perform the query, but does not actually do so until the enumeration is traversed. In this case, we traverse the query with the call to "Single".

By the time we get to "Single", the for loop has terminated. At that point i will be equal to 3. (Notice I didn't use <= 3, which would have terminated with i = 4). Only then was the expression evaluated, finding all of the numbers equal to three.

Closures
Many functional languages have a concept called a closure. A closure is a block of code (a.k.a. a function) that refers to a variable from the outside. This function can be passed around and executed at any time, but it carries with it that variable. Here's a very simple example in F#:

let x = 1
let xplus y = x + y

The function xplus carries with it the variable x. The word "variable" can be misleading. The "variable" cannot actually vary at all. It is immutable. At least in a functional language.

Java
The closest thing to a closure in Java is an anonymous inner class (at least for now). Here's the same test in Java (using my Java Query library to approximate linq):

List<NamedNumber> numbers = new ArrayList<NamedNumber>();
numbers.add(new NamedNumber("One", 1));
numbers.add(new NamedNumber("Two", 2));
numbers.add(new NamedNumber("Three", 3));

Iterable<String> names = null;
for (int i = 1; i < 3; ++i) {
    final int fi = i;
    if (fi == 1) {
        names = Query
            .from(numbers)
            .where(new Predicate<NamedNumber>() {
                public boolean where(NamedNumber row) {
                    return row.getNumber() == fi;
                }
            })
            .select(new Selector<NamedNumber, String>() {
                public String select(NamedNumber row) {
                    return row.getName();
                }
            });
    }
}
String name = Query.from(names).selectOne();
assertEquals("One", name);

Notice the extra step I had to include. I had to create a "final" variable. A final variable must be initialized, and cannot change. It is immutable. Java requires that any variable used inside of an anonymous inner class be declared final.

You may be thinking at this point that the value of fi does change during the course of this method. It is assigned a new value for each iteration of the loop. Not true. In fact, a new fi is created each time the loop is entered. Each instance of fi is initialized to a different value. While there is only one i, there are two fi's (1 and 2). The instance of the anonymous inner class is a closure that carries that instance of fi with it wherever it goes.

Here's my solution
Seeing how Java forces you to solve the problem, let's try the same technique in C#.

var collection = new NamedNumber[]
{
    new NamedNumber() { Name = "One", Number = 1 },
    new NamedNumber() { Name = "Two", Number = 2 },
    new NamedNumber() { Name = "Three", Number = 3 }
};
IEnumerable<string> names = null;
for (int i = 1; i < 3; ++i)
{
    int fi = i;
    if (fi == 1)
        names = from n in collection where n.Number == fi select n.Name;
}

string name = names.Single();
Assert.AreEqual("One", name);

Now if you run this unit test, it passes. The linq query captures the instance of fi that was created during that loop (1). It doesn't matter that a new instance of fi (2) was created before the query had a chance to execute. The query carries around the first instance (1), and uses it to select the name.

While C# has its own equivalent of closures (lambdas and linq), it does not force you to use immutable variables inside of them. It's up to you to be careful never to change a variable referenced inside of a linq expression. Initialize a new variable that never changes, and use it exclusively.

.NET to .NET web services without WSDL

Thursday, April 23rd, 2009

We are using web services internally as an RPC mechanism. Our web tier calls our application tier via service interfaces. We are both exposing these interfaces and consuming them. They are not intended for external consumption.

Since we control both ends of the conversation, WSDL is overkill. WSDL is generated by .NET when you publish a web service. Any compliant consumer can import that WSDL and generate a service proxy. When the consumer is another .NET application, you can use either "Import Service Reference" or "svcutil.exe". Publish the service, generate the proxy, and start calling it.

This publish/import workflow causes problems in a homogeneous .NET/.NET environment. The generated proxy classes are all imported into one namespace, even if the published classes come from different namespaces. While this alone is confusing, it can cause real problems if you pass messages among through one service to another, or from multiple clients. Every client defines types of exactly the same shape, but since they are in different namespaces and assemblies, they are not the same type.

Another problem that this causes is version inconsistencies between clients and servers. We use TFS automated builds for continuous integration. After the build, the server is published to the development environment. If we generated client proxies from that development environment, we would have an old version of the client proxy checked in at the same time as a newer version of the service.

Here's my solution
Instead of going through WSDL and generating a proxy, the client can use exactly the same data types that the server publishes. If you look at the code that is generated for you, you can see how. All of the magic is in System.ServiceModel.ClientBase<T>. This class generates a proxy on the fly based on the interface you specify. Rather than importing that interface from WSDL, you can include it from the source.

Put all of your web service contracts into a single project. This includes all of the ServiceContract interfaces that define the service methods, and the DataContract classes that define their parameters and return values. This project will need to reference System.Runtime.Serialization and System.ServiceModel. None of the implementation goes in this project. In the spirit of the Dependency Inversion Principle, it is completely abstract. I like to call it <MySolution>.Contracts.

Next, add a reference from your web service project to <MySolution>.Contracts. Implement the service contract interface, and do the work of the web service here.

Finally, add a reference from your client project to <MySolution>.Contracts. Don't add a service reference. Don't run svcutil.exe. Instead, add this little interface/class pair to your arsenal:

public interface IServiceClientFactory<TServiceInterface>
{
	void CallService(Action<TServiceInterface> action);
	TResult CallService<TResult>(Func<TServiceInterface, TResult> function);
}

public class ServiceClientFactory<TServiceInterface> :
	IServiceClientFactory<TServiceInterface>
	where TServiceInterface : class
{
	class Client : System.ServiceModel.ClientBase<TServiceInterface>
	{
		public TServiceInterface Service
		{
			get { return base.Channel; }
		}
	}

	public void CallService(Action<TServiceInterface> action)
	{
		Client client = new Client();

		try
		{
			action(client.Service);
			client.Close();
		}
		catch (Exception)
		{
			client.Abort();
			throw;
		}
	}

	public TResult CallService<TResult>(Func<TServiceInterface, TResult> function)
	{
		Client client = new Client();

		try
		{
			TResult result = function(client.Service);
			client.Close();
			return result;
		}
		catch (Exception)
		{
			client.Abort();
			throw;
		}
	}
}

With this, you can call a web service in one of two ways. Either you can invoke a method that returns nothing:

_serviceClientFactory.CallService(client =>
{
	client.DoAction(parameters);
})

Or, you can invoke a method with a return:

var something = _serviceClientFactory.CallService(client => client.GetSomething(parameters));

The client calls the service without ever importing the WSDL. The ServiceClientFactory does the proper Close() or Abort() pattern on the service reference. And the interface makes it suitable for IoC and unit testing.

Returning the autoincrement ID of the last row inserted

Friday, April 17th, 2009

It's a common scenario. You've defined an autoincrement primary key on a table. After you insert a row into this table, you need the ID. Maybe you need to insert related rows into a child table. Maybe you need to redirect the user to a page displaying the new data. It's easy to imagine reasons for needing this ID. In fact, it's hard to imagine not needing it.

Why, then, is it so hard to get it?

Well, now it's easy add this class to your project:

using System;
using System.Data;

namespace AdventuresInSoftware.Data
{
    /// <summary>
    /// Create a ConnectionScope inside of a using to open and close a
    /// database connection. Also offers a convenient LastId method.
    /// </summary>
    public class ConnectionScope : IDisposable
    {
        private IDbConnection _connection;

        /// <summary>
        /// Wrap a database connection inside of a ConnectionScope in
        /// a using statement.
        /// </summary>
        /// <param name="connection">The connection to open and close.</param>
        public ConnectionScope(IDbConnection connection)
        {
            _connection = connection;
            connection.Open();
        }

        /// <summary>
        /// Get the autoincrement key generated by the last insert.
        /// </summary>
        /// <returns>The ID of the last row inserted.</returns>
        public int LastId()
        {
            using (IDbCommand command = _connection.CreateCommand())
            {
                command.CommandText = "SELECT @@IDENTITY";
                command.CommandType = CommandType.Text;
                return (int)(decimal)command.ExecuteScalar();
            }
        }

        /// <summary>
        /// Closes the connection. Intended to be called automatically
        /// by the using statement.
        /// </summary>
        public void Dispose()
        {
            _connection.Close();
        }
    }
}

Then write code like this:

public int SaveVendor(string vendorName)
{
    VendorTableAdapter vendorTableAdapter = new VendorTableAdapter();
    using (var scope = new ConnectionScope(vendorTableAdapter.Connection))
    {
        // Insert the vendor and return the new ID.
        vendorTableAdapter.Insert(vendorName);
        return scope.LastId();
    }
}

As you can see, the above code uses a typed TableAdapter. This is a convenient class generated by ADO .NET to give you strongly typed objects and methods for accessing tables. TableAdapters have been largely obsoleted by ORMs and Entity Framework, but they are still handy for smaller client-side projects. This code is from a smart-client project built on SQL CE.

A typed TableAdapter has a method called Insert which returns an integer. Oh boy! It must be returning the ID of the new row! After all, what else could I possibly want out of an Insert?

No, sorry. Insert returns a row count. That's right. The Insert method, which inserts a row, returns a row count.

Let me say that again. This method who's only reason for existing is to insert one row returns the number of rows it inserted. Did you get that? It always returns the number 1! By design!

Inane, yes, I know. But there it is. Enjoy.

Entity Framework query based on properties of related objects

Thursday, March 5th, 2009

We're using Entity Framework for data access on an enterprise system. It has been painful, but we've gradually learned how to use the product. This last problem was our fault, but we blamed the tool.

The situation is that we have Orders with OrderLines. We want to find all orders that include a particular item. From those orders, we want to get information from a related entity called OrderSummary.

We tried several approaches until we found a syntax that would compile and give us the right set of orders. What we ended up with was a linq query using two "from" clauses:

OrderContainer container = new OrderContainer();
var source = container.Orders.Include("OrderSummary");

var orders =
	from o in source
	from ol in o.OrderLines
	where ol.ItemId == "32180"
	select o;

This query returns the correct set of orders, but the OrderSummary navigation property is not loaded. We called it a bug in EF and worked around it. The workaround involved multiple queries, and was generally a bad idea.

Fortunately, we took the time to revisit the problem. The first thing I did was to use Reflector to find out what that query was actually doing. Here's the equivalent:

var orders = source
	.SelectMany(o => o.OrderLines, (o, ol) => new { order = o, orderLine = ol })
	.Where(result => result.orderLine.ItemId == "32180")
	.Select(result => result.order);

It's a round-about way of saying it, but it looks OK. It creates a tuple of orders and order lines, filters that tuple based on the item ID, then selects just the order part of the tuple. If you think about it relationally, this is pretty close to an old-fashioned WHERE join.

Keeping with the explicit syntax, I wrote the query exactly as I wanted it. This is the result:

var orders = source
	.Where(o => o.OrderLines
		.Any(ol => ol.ItemId == "32180"));

This reads like the specification. It gets all orders where any order line has the desired item ID.

This version of the code not only selects exactly the orders I want, it also includes the OrderSummary. No longer do I have to do additional queries to fill in the missing details.

Here's my solution
My advice after working through this problem is to skip the linq syntax. Get used to the more explicit extension method syntax. Understand .Where(), .Select(), and .Any(). When you are more explicit with your tools (and I'm not talking profanity here), you have a better chance of getting the behavior you want out of them.