Preface

Note: if you’re not interested in low level control on your DirectShow graph and don’t need to render DVD content, just stick to MediaElement or VideoDrawing+MediaPlayer. This post is about how to render video yourself in your own filter graph.

I was thinking about porting Power Video Player to WPF long ago and was gathering necessary information that would help cut tough corners for a while. One of the things that I thought was absolutely clear is the way I was going to port the core engine part that relies heavily on PInvoke and native windows management. I didn’t even imagine how much I was wrong.

The basic mechanism that the core’s been using to present the video consists of creating a native window that’s made a child of some managed container (say UserControl) and passing its HWND to one of the renderers. While the core was doing quite a job managing sizing and aspect ration handling it was completely up to the stock renderer (and PVP supported VMR, VMR9, both windowless and not, EVR and even the legacy VideoRenderer) to actually draw the picture on the window (ok, windowed renderers create their own windows on top of yours but never mind, the PVP engine is able to handle all the gory details).

So the initial idea was to retain that very mechanism once WPF provides convenient HwndHost component that can host any native window. HwndHost is actually a FrameworkElement so it naturally fits into WPF elements’ tree (it does however have implications in terms of mouse and keyboard events handling but this is the least important problem).

Edited: On another thought, not too least. What about touch? WPF 4 has first class support for touch but HwndHost is an interop layer to the legacy technology and is restrictive in various ways as you’re going to see soon.

Well, it works. Under certain circumstances, however. Basically, it’s going to work as long as you don’t try to make your main window look different from the standard window. You know, in order to remove the ugly default thick border and make your window skinned (which is a common requirement for a video play application) you have to set WindowStyle to None and Background to Transparent. But what’s essential is that you have to set AllowsTransparency property of your window to True. And once you’ve done that, your HwndHost becomes invisible (not black, green or white, but totally transparent). And anything that’s drawn by its children is invisible too.

This odd behavior made me spend some time investigating and experimenting and in the end it turned out to be a known issue, I like this thread for an explanation. So once you enable WPF’s transparency you start using the new mechanism that is incompatible with Win32 standard one. Among the proposed advises there are:

• Don’t host legacy child HWND controls.  Try to find a WPF equivalent.
• Write a WPF equivalent of the HWND control (HTML content seems to be a popular request).
• Don’t use per-pixel alpha.  Consider applying a HREGION instead.
• Place your HWND control in a regular top-level window that has the app window as its owner.  Position the control window where you want it.
• Use a timer and call Win32’s PrintWindow (or send a WM_PRINT) to capture the HWND control to a bitmap, and display that bitmap in WPF.
• Try to wrap the containing window in a WS_EX_COMPOSITED window and respond to WM_PAINT to capture a bitmap and display the bitmap in WPF.
• Hook the HWND control’s window proc and respond to WM_PAINT by capturing the bitmap and displaying it in WPF.

All suggestions make sense and can applied when appropriate. Last 3 items, for example, are perfectly suitable for people who want to display legacy stuff on some fancy tooltips… They didn’t, however, give a direct or indirect solution to my situation, but… They actually gave me a hint. No, not just a hint. They opened my eyes and made me reconsider my priorities. Do I want to carry the burden supporting legacy platforms in my library? What benefits do I get? What if I leave my legacy stuff as is and set out exploring something new?

 

The new solution

As WPF uses DirectX for rendering and video renderers also use DirectX I had a feeling it should be possible to somehow make the renderers communicate with WPF. I even spent an evening learning how to do 3D drawing in WPF as I thought it might get me closer to what I was looking for.

However, I soon came to realize that WPF manages its own Direct3D surfaces and you can’t easily access them from outside. But, there is one useful component called D3DImage which allows us to provide an arbitrary Direct3D surface to WPF and the latter would copy its content to its own surfaces.

I’ve found walkthrough articles available on MSDN really useful to understand how to properly use D3DImage. I encourage you to take a look at them too: Creating Direct3D9 Content for Hosting in WPF and Hosting Direct3D9 Content in WPF.

The second essential part of the solution is to somehow grab video frames from the renderer and pass them to D3DImage. Both VMR and EVR can be extended with a customer presenter that is actually responsible for managing DirectX stuff and actually presenting the frames. As EVR is the preferred video renderer on Vista and Windows 7 (and it will be on Windows 8 as it is the renderer that Media Foundation uses) I put my attention to it.

By the way I added support for EVR to PVP about 2.5 years ago and I remembered issues I had with its default presenter that didn’t properly update the areas of the media window surrounding the destination rectangle. The solution I came with by then was to resize the media window to fit the destination rectangle. Digging deeper and providing my own custom presenter seemed like an overkill. And it was.

However, Microsoft provided a sample of the custom EVR presenter and a description on How to Write an EVR Presenter. If you’re really interested in the topic I highly recommend you spend some time with this lengthy article. The presenter provided in the sample doesn’t fully implement IMFVideoDisplayControl but overall is a good starting point for your own implementation as its D3DPresentEngine class is designed to be extended (that is, subclassed) and it provides a bunch of essential virtual functions for you to override.

In a nutshell the presenter maintains two queues of video sample objects and each of them has an underlying Direct3D surface. The first queue contains ‘free’ samples that are fed to video mixer to actually draw frames on their surfaces. EVR notifies the presenter when it needs to provide a sample to the mixer.

Once the mixer finishes drawing the frame on the surface, the sample is put into another queue. That second queue contains samples that are ready to be presented at specified timestamps. The queue is managed by the special component of the presenter called scheduler that monitors the queue on a separate thread. Once it’s time to present a sample the scheduler calls D3DPresentEngine passing it the sample. The way the sample is actually presented is the responsibility of D3DPresentEngine and this is where we fit in as we are able to override presentation details by subclassing D3DPresentEngine.

I gave you this short description so that the rest of the post would make sense to you. There are a lot of subtle details like implementing various interfaces that are needed to properly interact with EVR, managing repaints, returning presented samples to the first queue, etc. Once again, if you’re interested in the topic I encourage you to study the article on MSDN and follow its description in the code sample.

Note, there is open source project called Media Foundation .Net that ported the sample to managed code. I didn’t try it though as I was concerned about performance of too frequent interactions between EVR and the managed presenter. However, you might be interested in trying it out. Still, I stuck to the native C++ Microsoft sample.

Another note: there is well known WPF MediaKit project that uses the same approach but in a special way. I’m going to get to it later in this post but I have to say looking at the sample provided on the project’s page kind of assured me that the whole idea was worth investing my time into it.

 

Initial approach

Looking at D3DPresentEngine you would notice it stores a pointer to Direct3D surface of the most recent sample in a private variable called m_pSurfaceRepaint. Moreover, it increments a reference count on it effectively preventing the sample from returning to the first queue (thanks to IMFTrackedSample interface, yes, please check out the MSDN article).

It needs m_pSurfaceRepaint to be able to repaint the last frame in response to IMFVideoDisplayControl->Repaint() which can be called (and is recommended to be called) when a video window receives WM_PAINT message.

And this is actually the frame that we want to pass to D3DImage. However, here’s a gotcha. The surface pointed to by m_pSurfaceRepaint belongs to the sample that can be fed to the mixer any time so that it draws another frame on it. So it can’t be safely used as a back buffer for D3DImage. Instead, we want to maintain our own surface, called ‘return’ surface and copy the content of the most recent frame to it when the most recent frame changes.

So in our derived class (let’s call it PvpPresentEngine) we override OnCreateVideoSamples function that is called by D3DPresentEngine whenever it recreates video samples which can happen due to variety of reasons. Note that before recreating samples it calls OnReleaseResources() which we should also override to delete previous Direct3D resource that we might have allocated.

void PvpPresentEngine::OnReleaseResources()
{
    SafeRelease(&m_pReturnSurface);
    SafeRelease(&m_pRecentSurface);
}

HRESULT PvpPresentEngine::OnCreateVideoSamples(
                            D3DPRESENT_PARAMETERS& pp)
{
    int hr = this->m_pDevice->CreateRenderTarget(
                                 pp.BackBufferWidth, 
                                 pp.BackBufferHeight, 
                                 pp.BackBufferFormat, 
                                 pp.MultiSampleType, 
                                 pp.MultiSampleQuality, 
                                 true, 
                                 &m_pReturnSurface, 
                                 NULL);

    return hr;
}

We are passed a reference to D3DPRESENT_PARAMETERS structure so we know the configuration of D3D surface to create (we want our ‘return’ surface to be fully compatible with the surfaces of video samples that the presenter maintains in its two queues). Whenever it recreates its sample we’re going to recreate our return surface.

You might have noticed a pointer to some recent surface (m_pRecentSurface). What is it? It’s actually a pointer to the same most recent frame/surface (m_pSurfaceRepaint) but we maintain our own copy of the pointer. We receive it in the overridden PresentSwapChain function:

HRESULT PvpPresentEngine::PresentSwapChain(
                        IDirect3DSwapChain9* pSwapChain, 
                        IDirect3DSurface9* pSurface)
{
    EnterCriticalSection(&m_ObjectLock);

    if (m_pRecentSurface != pSurface)//'borrow' the latest surface
    {
        CopyComPointer(m_pRecentSurface, pSurface);
        m_bNewSurfaceArrived = TRUE;
    }
    
    LeaveCriticalSection(&m_ObjectLock);
    return S_OK;
}

CopyComPointer increments a reference count on the new pointer and decrements it on the old one. This is a pretty convenient function that helps us keep the surface from being returned to the queue of available ones:

template <class T>
void CopyComPointer(T* &dest, T *src)
{
    if (dest)
    {
        dest->Release();
    }
    dest = src;
    if (dest)
    {
        dest->AddRef();
    }
}

We also use a critical section as PresentSwapChain is called on the scheduler’s thread while we consume the surface on WPF’s composition thread.

On WPF’s end we subscribe to CompositionTarget.Rendering event and try to get the latest surface there:

private void CompositionTarget_Rendering(object sender, 
                                         EventArgs e)
{
    RenderingEventArgs args = (RenderingEventArgs)e;

    // It's possible for Rendering to call back twice 
    // in the same frame 
    // so only render when we haven't already rendered 
    // in this frame.

    if (_d3dImage.IsFrontBufferAvailable && 
        _lastRender != args.RenderingTime)
    {
        bool newSurfaceArrived;
       _pvpPresenter.HasNewSurfaceArrived(out newSurfaceArrived);
        if (newSurfaceArrived)
        {
            _d3dImage.Lock();

            IntPtr pSurface;
            _pvpPresenter.GetBackBufferNoRef(out pSurface);

            if (pSurface != null)
            {
                // Repeatedly calling SetBackBuffer with the 
                // same IntPtr is a no-op. 
                // There is no performance penalty.
                _d3dImage.SetBackBuffer(
                      D3DResourceType.IDirect3DSurface9, 
                      pSurface);

                _d3dImage.AddDirtyRect(
                      new Int32Rect(0, 
                                    0, 
                                    _d3dImage.PixelWidth, 
                                    _d3dImage.PixelHeight));
            }

            _d3dImage.Unlock();
        }

        _lastRender = args.RenderingTime;
    }
}

This C# part pretty much mimics the sample available at MSDN. _pvpPresenter is a reference to RCW over my native presenter.

It’s important to note that we first check if a new frame is available and only if it is we lock D3DImage in order give it new content. We do the rendering right in GetBackBufferNoRef() and we confirm to D3DImage recommendation to render on the back buffer only when it’s locked.

As to the native implementation it is as follows:

HRESULT PvpPresentEngine::HasNewSurfaceArrived(
                                     BOOL *newSurfaceArrived)
{
    EnterCriticalSection(&m_ObjectLock);

    *newSurfaceArrived = m_bNewSurfaceArrived;

    LeaveCriticalSection(&m_ObjectLock);
    return S_OK;
}

HRESULT PvpPresentEngine::GetBackBufferNoRef(
                               IDirect3DSurface9 **ppSurface)
{
    EnterCriticalSection(&m_ObjectLock);

    HRESULT hr = S_OK;
    *ppSurface = NULL;

    if (m_bNewSurfaceArrived && m_pRecentSurface != NULL)
    {
        hr = D3DXLoadSurfaceFromSurface(m_pReturnSurface,
                                        NULL,
                                        NULL,
                                        m_pRecentSurface,
                                        NULL,
                                        NULL,
                                        D3DX_FILTER_NONE,
                                        0);

        m_bNewSurfaceArrived = FALSE;

        if (SUCCEEDED(hr))
        {
            *ppSurface = m_pReturnSurface;
        }
    }

    LeaveCriticalSection(&m_ObjectLock);
    return hr;
}

As you can see we again have to lock before we copy the latest fame to m_pReturnSurface and return the latter to WPF because the latest surface may be swapped any time by the presenter’s scheduler thread.

Although we try not to waste resources and don’t copy the buffers if no new recent frame has arrived (this is achieved with a simple m_bNewSurfaceArrived flag and an extra HasNewSurfaceArrived call), there is still a lot thread locking going on which results in dropped frames. For instance, the presenter thread can’t swap the latest frame while we are still copying and returning the previous one to D3DImage. And at the same time the presenter keeps adding new ready sample to the scheduler’s queue. However, when the scheduler’s thread is unlocked again it sees that queued frames have missed their presentation time and it needs to go on and drop them.

 

Second attempt

To minimize the influence of locking let’s introduce two additional queues and a pool of return surfaces. The pools will consist of 3 or 4 surfaces that will be stored in either queue. The first queue (called m_AvailableSurfaces) will contain surfaces available for rendering on by the scheduler’s thread. The second queue (called m_RenderedSurfaces) will contain surfaces that are ready to be sent to D3DImage.

Here’s how the initialization part has transformed:

void PvpPresentEngineQueued::OnReleaseResources()
{
    m_RenderedSurfaces.Clear();
    m_AvailableSurfaces.Clear();

    SafeRelease(&m_pReturnSurface);
}

HRESULT PvpPresentEngineQueued::OnCreateVideoSamples(
                                  D3DPRESENT_PARAMETERS& pp)
{
    HRESULT hr = S_OK;

    for(int i = 0; i < 4; i++)
    {
        IDirect3DSurface9 *pSurface = NULL;
        int hr = this->m_pDevice->
                CreateRenderTarget(pp.BackBufferWidth, 
                                   pp.BackBufferHeight, 
                                   pp.BackBufferFormat, 
                                   pp.MultiSampleType, 
                                   pp.MultiSampleQuality, 
                                   true, 
                                   &pSurface, 
                                   NULL);
        if(FAILED(hr))
        {
            break;
        }

        hr = m_AvailableSurfaces.Queue(pSurface);
        pSurface->Release();

        if(FAILED(hr))
        {
            break;
        }
    }

    hr = this->m_pDevice->CreateRenderTarget(pp.BackBufferWidth, 
                                          pp.BackBufferHeight, 
                                          pp.BackBufferFormat, 
                                          pp.MultiSampleType, 
                                          pp.MultiSampleQuality, 
                                          true, 
                                          &m_pReturnSurface, 
                                          NULL);

    return hr;
}

The queues will be stored in ThreadSafeQueue objects. This handy class is already provided in the sample and is used by the scheduler as well. The class maintains its own critical section so all Queue() and Dequeue() operations are synchronized. Moreover, when we store a pointer in the queue it increments its reference count and it decrements it when we remove an item from the queue. So we have to call Release() on an item when we store it in the queue and are done with it.

You should probably have noticed that we still maintain the m_pReturnSurface. This is still the only surface we return to D3DImage. If we returned surfaces from the pool it would explode D3DImage literally. Its memory consumption would grow to gigabytes in seconds and the application would crash. We escape this problem by making sure that the back buffer stays the same. However, it means we will have to do additional copying from m_RenderedSurfaces to m_pReturnSurface:

HRESULT PvpPresentEngineQueued::PresentSwapChain(
                             IDirect3DSwapChain9* pSwapChain, 
                             IDirect3DSurface9* pSurface)
{
    HRESULT hr = S_OK;

    IDirect3DSurface9 *pRenderSurface = NULL;
    if (m_AvailableSurfaces.Dequeue(&pRenderSurface) == S_OK)
    {
        hr = D3DXLoadSurfaceFromSurface(pRenderSurface,
                                        NULL,
                                        NULL,
                                        pSurface,
                                        NULL,
                                        NULL,
                                        D3DX_FILTER_NONE,
                                        0);

        m_RenderedSurfaces.Queue(pRenderSurface);
        pRenderSurface->Release();
    }

    return hr;
}

HRESULT PvpPresentEngineQueued::HasNewSurfaceArrived(
                                     BOOL *newSurfaceArrived)
{
    IDirect3DSurface9 *pSurface = NULL;
    if (m_RenderedSurfaces.Dequeue(&pSurface) == S_OK)
    {
        m_RenderedSurfaces.PutBack(pSurface);
        pSurface->Release();
        *newSurfaceArrived = TRUE;
    }
    else
    {
        *newSurfaceArrived = FALSE;
    }

    return S_OK;
}

HRESULT PvpPresentEngineQueued::GetBackBufferNoRef(
                                IDirect3DSurface9 **ppSurface)
{
    HRESULT hr = S_OK;
    *ppSurface = NULL;

    EnterCriticalSection(&m_ObjectLock); // to safely release 
                                         // and possibly 
                                         // re-create resources

    IDirect3DSurface9 *pSurface = NULL;
    if (m_RenderedSurfaces.Dequeue(&pSurface) == S_OK)
    {
        hr = D3DXLoadSurfaceFromSurface(m_pReturnSurface,
                                        NULL,
                                        NULL,
                                        pSurface,
                                        NULL,
                                        NULL,
                                        D3DX_FILTER_NONE,
                                        0);

        m_AvailableSurfaces.Queue(pSurface);
        pSurface->Release();

        if (SUCCEEDED(hr))
        {
            *ppSurface = m_pReturnSurface;
        }
    }
    
    LeaveCriticalSection(&m_ObjectLock);

    return hr;
}

As you can see PresentSwapChain, HasNewSurfaceArrived and GetBackBufferNoRef only lock each other on accessing either m_AvailableSurfaces or m_RenderedSurfaces queues. But this locking is significantly shorter as threads don’t have to wait for long operations like surface copying.

The lock on m_ObjectLock doesn’t have anything to do with the scheduler’s thread. It’s there to synchronize with the parent’s class when it decides to recreate D3D resources and thus makes us recreate ours.

This implementation noticeably improved performance of the presenter. However, when rendering full HD video or making D3DImage resize relatively small video frames to full HD resolutions you still can notice discrete “jumps” instead of smooth playback. However this time the “jumps” are less harsh as they were with dropped frames. Instead, you see another anomaly caused by the fact that the scheduler manages to add too many frames to m_RenderedSurfaces and we are not feeding them fast enough to D3DImage so the video starts lagging behind and you can see, for example, that a person’s lips are often unsynchronized with what the person says. It’s not a huge gap (I’m talking in terms of milliseconds here) but still noticeable.

We could add a check for frames’ presentation time before sending them to D3DImage but it would make us consciously start dropping frames again.

 

Third (much better) solution

This solution was inspired by WPF MediaKit project. The major difference is that instead of polling the renderer for new frames in CompositionTarget.Rendering event handler, we make the renderer callback into the managed code when a new frame is available. When I was running the MediaKit’s sample application I noticed that it produced better result than my previous solutions.

MediaKit’s version of the implementation renders a frame and sends a D3D surface to the managed code right in the callback before D3DImage is locked. This somewhat goes against official recommendation not to render on D3DImage’s back buffer when it’s unlocked.

I decided to make the callback a simple notification about a new surface availability. When we receive this notification we make our normal GetBackBufferNoRef() call to fetch the surface.

HRESULT PvpPresentEngine2::RegisterCallback(
             IPvpPresenterCallback *pCallback)
{
    m_pCallback = pCallback;
    return S_OK;
}

HRESULT PvpPresentEngine2::PresentSwapChain(
   IDirect3DSwapChain9* pSwapChain, IDirect3DSurface9* pSurface)
{
    HRESULT hr = S_OK;

    if (m_pCallback != NULL && m_pRecentSurface != pSurface) 
    {
        CopyComPointer(m_pRecentSurface, pSurface);

        hr = m_pCallback->OnNewSurfaceArrived();
    }

    return hr;
}

HRESULT PvpPresentEngine2::GetBackBufferNoRef(
                    IDirect3DSurface9 **ppSurface)
{
    EnterCriticalSection(&m_ObjectLock);

    HRESULT hr = S_OK;
    *ppSurface = NULL;

    if (m_pRecentSurface != NULL)
    {
        hr = D3DXLoadSurfaceFromSurface(m_pReturnSurface,
                                        NULL,
                                        NULL,
                                        m_pRecentSurface,
                                        NULL,
                                        NULL,
                                        D3DX_FILTER_NONE,
                                        0);

        if (SUCCEEDED(hr))
        {
            *ppSurface = m_pReturnSurface;
        }
    }

    LeaveCriticalSection(&m_ObjectLock);
    return hr;
}

As you can see I reverted to using just a single return surface to avoid synchronization issues. I also introduced new IPvpPresenterCallback interface and allows the presenter to send notifications to the managed code. My managed class implements this interface and here is the handler:

public int OnNewSurfaceArrived()
{
    _d3dImage.Dispatcher.Invoke(new Action(() =>
    {
        if (_d3dImage.IsFrontBufferAvailable)
        {
            _d3dImage.Lock();

            IntPtr pSurface;
            _pvpPresenter.GetBackBufferNoRef(out pSurface);

            if (pSurface != null)
            {
                // Repeatedly calling SetBackBuffer with 
                // the same IntPtr is a no-op. 
                // There is no performance penalty.
                _d3dImage.SetBackBuffer(
                   D3DResourceType.IDirect3DSurface9, pSurface);

                _d3dImage.AddDirtyRect(new Int32Rect(0, 0, 
                    _d3dImage.PixelWidth, _d3dImage.PixelHeight));
            }

            _d3dImage.Unlock();
        }
    }), System.Windows.Threading.DispatcherPriority.Send);
    
    return 0;
}

As the callback is invoked on the presenter’s scheduler thread we have to dispatch to a thread that D3DImage was created on in order to access it. I do it synchronously with the highest priority which effectively transforms into a direct call (well, a ‘send’ operation to be correct, but it’s supposed to be done immediately, not scheduled). The presenter thread is blocked until we return from the Dispatcher.Invoke handler.

I also tried asynchronous handling and it required additional locking on a critical section in the native code as we release the scheduler' thread and it go and try to substitute the recent surface anytime. However, this approach resulted in a less smooth experience compared to the synchronous handling.

One downside that you need to be aware of is that if D3DImage is created on your main UI thread, this thread becomes over occupied with rendering activities which negatively affects the responsiveness of your UI. Working with D3DImage is somewhat beyond the scope of the post but I recommend you have a look at this post explaining how you can build multithread UI in WPF.

Imagine you have created a custom template for your window and disabled standard chrome. If you want to maximize your window you can’t just set its WindowState to Maximized because it will move over the taskbar. Instead, you will have to position it so its top left corner is at 0,0 and its bottom right corner is at SystemParameters.WorkArea’s Height and Width respectively. When you want to restore your window back to its normal state you will reset the coordinates and sizes according to Window.RestoreBounds.

The problem is that in WPF you can’t define your window’s position and size in one call. You will have to set its Left, Top, Width and Height properties separately which results in a rather unattractive effect when you can actually see each step as it happens. The more the difference in size is (maximize/restore or full screen/restore scenarios) and the heavier your UI is the more ugly it gets to look.

More...

The Skinner library that I introduced some time ago didn’t put much care into security issues you may run into when loading and executing 3rd party components (skins). I had touched on these issues in one of my previous posts where I also gave my view on the better solution. The two major points that represent that solution are:

1. Don’t use MEF for scanning as opposed to initial version. Not because MEF is bad, but because we need better control over the scanning process.
2. Require strong name signing so that you could verify that skin assemblies had been signed by an authority you trust (which is you).

Both of these points did affect the way you pack your skins and the way you use the Skinner library.

More...

I have just completely reinstalled my laptop and was going to work on a project when suddenly I found out it woudn't build. For every assembly I was getting the following error:

Cryptographic failure while signing assembly 'path\to\your.dll' -- 'Error signing assembly -- Access is denied.

It didn’t’ take long to discover the problem is well-known on at least Windows 7 environment (with VS 2010, I didn’t bother to check the older versions). I must have run into it some time (say, years) ago when I was re-installing but the issue completely evaporated from my memory so it kind of caught me off guard.

So, basically the user account you’re running under can’t access crypto keys located at c:\Users\All Users\Microsoft\Crypto\RSA\MachineKeys\. There are a bunch of them and it would be nice to identify the necessary ones (which is doable with Process Monitor) however a quick advice is to give your user read permissions on the whole MachineKeys folder. Rough, but well, it’s amazing it hasn’t been officially addressed yet!

It was fun to discover that SourceGear Vault that I use as a private source control system might probably have run into some issues and had to give their server’s pool identity explicit permissions:

They might have their own reasons though. Still, IUSR and NETWORK SERVICE accounts were given full control too however my local Administrators group wasn’t:

Note that my user is a member of above mentioned Administrators group and this group is the owner of this folder. This, however, isn’t enough for a member of the group to be able to give the group necessary permissions. It might have something to do with UAC, I wouldn’t say and frankly too lazy to check (as 2 system restarts are required, one when disabling UAC and another one when enabling it back again).

So, the proper procedure will be:

1. Take ownership (click that Advanced button and you’ll see). Assign the ownership to your user.
2. Then you can give your user read (or full control if you don’t mind) permissions on that folder.

I just wrote it as a reminder for myself. Hope next time I reinstall there will be one WTF less .

In my initial scratch of the Skinner I unwillingly ignored the security issues and the resulting code is only going to work in a full trust environment. There are few things that raise a security concern:

• we need to scan for available skins in arbitrary directories (initial version of Skinner used MEF to do the job);
• as we require a skin to implement ISkin interface we will have to call ISkin.GetSkinDescription() that gives the 3rd party code a chance to run any logic it has (if we don’t care to build up sufficient restrictions);
• when it comes to actually loading a skin (which is a compiled resource dictionary) we have to do it in the main application domain (I had already mentioned there is no way you can load BAML in one domain and pass the resulting object graph to another);

The first 2 bullets can be tackled: we can created a sandboxed domain and do the scanning there. It can get a little tricky though because if a plugin has to implement an interface you provide you have to make sure your assembly that contains this interface can be loadable from the sandboxed domain. The first thing you can do is to give FileIOPermission to the assembly file when creating the domain. However, if the new domain’s ApplicationBase is not set to point to the directory containing your assembly, the resolution is going to fail. And there is no way to subscribe to AppDomain.AssemblyResolve event from within the sandboxed domain because it requires the code to be executed in full trust.

We still can set the sandboxed domain’s ApplicationBase to point to  the directory containing your assembly but we may well run into trouble loading our supposed-to-be full trust assembly in the sandboxed environment. To work around this we can isolate the public interfaces that are necessary for a plugin’s (a skin’s) implementation into a separate assembly and mark it as SecurityTransparent (assuming .NET 4 model is in use). Giving FileIOPermission to that ‘secure’ assembly only will help, however I had subsequent problems when trying to go that path with my Skinner library.

 

The third bullet

I could continue to push further with scanning however the 3rd bullet kept sticking before my eyes. If you have to load a 3rd party skin in your main domain you’re looking for trouble. This is XAML, it can instantiate any object and  that object can contain malicious code directly executable from its constructor.

In this situation you have to know what you’re loading upfront. ‘Knowing’ means you trust the plugin and there are ways you can validate they deserve your trust:

• Use regular strong name signing
• Use authority-based signing (Authenticode)

With Authenticode signing you can say that the publisher is the one who he’s trying to pass for. That is, John Smith is confirmed to be John Smith by a party you trust (your mom) and in you have a note in your records that you trust John Smith. When these two facts come true you can relax and say ‘This is my old buddy John, I can let him in’. It has two implications: 1 (for you) you have to know the parties being signed; 2 (for plugin creators) they have to pay some respected authority to give them recognition (certificate) you can verify.

With strong naming you take the responsibility of that ‘well respected authority’. That means that you actually sign plugins with your private key. No one else can sign them. You only load and execute plugins signed with your key. In this scenario plugin creators will have to submit their built work to you to sign.

The one you choose is really dependent on your situation but in my situation I prefer the strong name signing. It would be completely great if I could load skins from unknown authors but it just can’t be done in a sober mind under full trust.

 

Strong name verification

It’s really easy when you sign your application (or plugin consuming library) with the same key you sign plugins.

You can choose a strategy you like (one key for all apps, a separate key for an app. a separate key for a lib). It just depends on the level you’re ready to make your life harder .

Then you can come up with an extension method like this:

public static bool IsSignedWithSameKeyAs(this Assembly assembly, 
    Assembly anotherAssembly)
{
    bool ret = false;

    var thisAssemblyKey = assembly.GetName().GetPublicKey();
    var anotherAssemblyKey = assembly.GetName().GetPublicKey();

    if (thisAssemblyKey != null && 
        thisAssemblyKey.Any() && 
        anotherAssemblyKey != null && 
        anotherAssemblyKey.Any() &&
        thisAssemblyKey.Length == anotherAssemblyKey.Length)
    {
        ret = true;
        
        for (int i = 0; i < thisAssemblyKey.Length; i++)
        {
            if (thisAssemblyKey[i] != anotherAssemblyKey[i])
            {
                ret = false;
                break;
            }
        }
    }

    return ret;
}

It is obvious that you should perform this check before running any code from a plugin assembly. However, if you loaded an assembly and it failed the check there is no way to unload it. To solve this issue you have to have some kind of a pre-loading mechanism. For instance, in Skinner there is a scanning operation that is done in a dedicated sandboxed domain which is unloaded once the scanning is finished. The actual check for a strong name signature should happen there.

However, how can we guarantee that the assembly hasn’t been substituted between scanning and the subsequent loading for consumption? A possible solution could be generation of the unique hash over the assembly file during the scanning pass after the strong name validation has been successful and keeping that hash value in memory in, for example, a dictionary that would map an assembly’s filename to the generated hash value.

When it comes to loading the plugin for execution (in full trust) don’t just load the assembly. First, load the file as a byte array and generate the hash. Then verify if it matches the one you generated for that particular file during the scanning pass.

There is one more thing to take care about. When loading a plugin it can dynamically load other assemblies. For instance, a resource dictionary can define merge directionaries from other assemblies. To be on the safe side you want to perform the same check on other assemblies as well. But how would you determine which will get loaded? AppDomain has a property called IsFullyTrusted that makes it possible for a plugin to determine if it's running within restriced permissions and adjust its behavior appropriately. On the other hand, if you signed the plugin you probably went though a certain review procedure. See, there is room for discussion but make sure the review is thorough. Once the 3rd party code becomes fully trusted you have no means to restrict it. Yes you can put the plugins in a separate location so they won't be able to automatically load assemblies from your probing directories. You could then perform described above verification in AppDomain.AssemblyResolve event handler, but what if the plugin tries and loads another assembly through reflection? And it can be smart enough to guess the file location.

Again, this may not be relevant to most plugins that need to perform some logic. You will run them in the sandboxed domain and be certain they won't do what you didn't allow them to do. But there are special kinds of plugins (like WPF skins) that you will have to execute in full trust. And before you give them your trust you will have to carefully varify them before they can even start being consumable by your application.

 

What about MEF?

Use it when you know what you’re loading. But watch out! When an assembly is loaded with reflection (which is what MEF does, of course) it will get loaded regardless of whether it was signed or not. And even if you load your imports as Lazy<> the moment you access their .Value property their constructors get executed. And, ironically, you have to instantiate them in order to access the Assembly object to inspect if it was signed but it happens to be too late.

This is a follow-up post to the previous oneabout the Skinner library. In this post I want to focus on a few technical peculiarities of the library and explain why certain things were done the way they were done.

Loading a skin

I already mentioned that Skinner does scanning for available skins on a separate thread and in a dedicated domain that it shuts down when scanning is over. It insures we don’t keep potentially large number of assemblies in the main application’s domain and it also helps us implement a sort of a hot pluggability, when you can add/remove skin assemblies at runtime and refresh the list of available skins by calling ISkinManager.Scan() again.

My initial desire was also to load a chosen skin through a dedicated domain. Here’s how you might have implemented it:

internal class Loader : MarshalByRefObject
{
    public static LoadResult Load(Uri packUri)
    {
        LoadResult result = new LoadResult();
        AppDomain domain = null;
        Stream stream = null;

        try
        {
            domain = 
              AppDomain.CreateDomain("Skinner.Loader.Domain");
            Loader loader = 
              (Loader)domain.CreateInstanceAndUnwrap(
                     Assembly.GetExecutingAssembly().FullName,
                     typeof(Loader).FullName);
            
            stream =  GetStream(packUri);
            result.SkinResourceDictionary = Load(stream);
        }
        catch (Exception e)
        {
            result.Error = e;
        }
        finally
        {
            if (stream != null)
                stream.Dispose();
            if (domain != null)
                AppDomain.Unload(domain);
        }

        return result;
    }

    private static ResourceDictionary Load(Stream stream)
    {
        // details are omitted
    }

    private Stream GetStream(Uri packUri)
    {
        var streamInfo = Application.GetResourceStream(packUri);
        return streamInfo.Stream;
    }
}

So we instantiate an instance of the loader in another domain and call GetStream(Uri packUri) through remoting. Getting a resource dictionary from a stream can be done with Baml2006Reader:

private static ResourceDictionary Load(Stream stream)
{
    var reader = new Baml2006Reader(stream);
    return (ResourceDictionary)XamlReader.Load(reader);
}

However, Baml2006Reader is available in .NET 4 so if we needed to target earlier versions we could resort to the known undocumented way that boils down to calling an internal static LoadBaml method on XamlReader through reflection:

private static ResourceDictionary Load(Stream stream)
{
    var pc = new ParserContext();
    var readerType = typeof(System.Windows.Markup.XamlReader);
    var method = readerType.GetMethod("LoadBaml", 
       BindingFlags.NonPublic | BindingFlags.Static);
    return (ResourceDictionary)method.Invoke(null, 
       new object[] { stream, pc, null, false });
}

The abstract Stream class derives from MarshalByRefObject and we might have expected that WPF’s implementation supported remote calls. However, it doesn’t. Although the proxy is successfully created it can’t be used to read BAML.

Running that code in the current domain without remoting works great. However, in this case it’s preferable to use a convenient Application.LoadComponent method that takes a relative pack Uri and returns a ResourceDictionary instance to you. This is how Skinner ultimate loads resource dictionaries.

Note that for this to work, XAML that is references by the pack Uri is expected to have the ResourceDictionary element as it root.

 

Resolving resource location

Application.LoadComponent method expects a relative Uri. As a skin’s resource is located in a different assembly than Skinner, that relative Uri must contain the skin’s assembly name.

However, when you specify a Uri in the SkinDescription you can omit the assembly name. Skinner will kindly reformat your Uri and actually send a normalized version in SkinFound event.

One more important thing is physical skins’ assemblies location. Skinner uses MEF for scanning and configures the composition container to scan in the main application’s directory (AppDomain.CurrentDomain.SetupInformation.ApplicationBase) as well as in the special ‘skins’ directory that is located under the main application’s directory. I think it’s sufficient for most purpose but if for any reason you don’t like this convention you can always update Skinner to meet your requirements.

Still, when you place skins’ assemblies under ‘skins’ directory the default assembly resolution will fail. Skinner addresses this by reacting to AppDomain.AssemblyResolve event and trying to load a requested assembly from the ‘skins’ subdirectory:

internal class SkinManager : ISkinManager
{
    private readonly Regex _regexAssemblyName = 
       new Regex(@"^(?<name>.+),.+$");
    private readonly Regex _regexAssemblyResources = 
       new Regex(@"^[^\.]+\.resources");

    public SkinManager()
    {
        AppDomain.CurrentDomain.AssemblyResolve += 
            (sender, args) =>
            {
                return ResolveAssembly(args.Name);
            };
    }

    private Assembly ResolveAssembly(string name)
    {
        Assembly assembly = null;
        
        try
        {
            string assemblyName = GetAssemblyFileName(name);
            if (!string.IsNullOrEmpty(assemblyName))
                assembly = 
                   Assembly.LoadFrom(GetAssemblyFileName(name));
        }
        catch { }

        return assembly;
    }

    private string GetAssemblyFileName(string name)
    {
        if (_regexAssemblyResources.IsMatch(name))
            return null; // ignore additional requests for 
                         // AssemblyName.resources
        
        string assmeblyName = name;
        Match m = _regexAssemblyName.Match(name);
        if (m.Success)
            assmeblyName = m.Groups["name"].Value;

        // try to find an assembly in the 'skins' subdirectory
        string dir = 
           Path.Combine(AppDomain.CurrentDomain.
              SetupInformation.ApplicationBase, "skins");

        string assumedAssembly = 
            Path.Combine(dir, 
               string.Format("{0}.dll", assmeblyName));

        if (!File.Exists(assumedAssembly))
        {
            try
            {
                var matchedFiles = 
                    Directory.GetFiles(dir, 
                        string.Format("{0}.*", assmeblyName));
                if (matchedFiles.Length > 0)
                    assumedAssembly = matchedFiles[0];
            }
            catch { }
        }

        return assumedAssembly;
    }
}

If the missing assembly is TestSkins2 (the actual file can be TestSkin2.dll but it can be an .exe just as well) its name will come in the form of ‘TestSkins2, Culture=neutral’. However, we need to pass a concrete file name to Assembly.LoadFrom so we do our best to find one.

NOTE: The information presented in this post is outdated. It does describes the problem the library tries to solve but the approach in which this is done has changed. For the current library description please visit WPF skin engine - revisited.

In the upcoming couple of posts I’m going to a cover a simple yet useful helper library I wrote recently. Its purpose is to provide a convenient way to manage skinned interface in an application that wants to support various skins and a possibility to change them at runtime. WPF has all that’s needed to apply a different look to everything from a thumb on a track bar to the whole window but it’s still a programmer’s task to implement the logic needed to look for available skins, load, apply and unload them. This library called Skinner solves this task and allows you to jump right to actually creating art work (XAML templates) and/or great functionality of your application without bothering about the tedious details of managing skins.

When I started working on it I outlined a few of the core requirements:

• It should be simple, that is dead-simple to use;
• Scanning for available skins should be efficient, that is fast and not hurting the application;
• It should be able to enable a chosen skin, disabling the previous one if there was any;
• Loading a skin should be fast, it’s extremely important for the start-up time.

What is a skin? By the way, I’d love to use the term ‘theme’ instead but in WPF world it’s already used to refer to OS themes support (Luna, Aero). So not to cause a conclusion I stopped on ‘skin’.

So what is a skin? Effectively a resource dictionary containing a bunch of templates to define the look of various UI elements.

But keeping your skins in XAML is not efficient in terms of performance as parsing them will severely affect loading time. If you struggle to keep your application’s start-up time as little as possible, parsing XAML will hurt too bad. So we want to compile our skins into BAML and put it in an assembly as a resource.

Now that we have decided on what a skin physically represents, we can decide how to find and load it.

Skinner uses MEF for scanning purposes. It appears to be extremely fast. Moreover, as potentially there can be a lot of third party skins provided for your application (and these skins are .NET assemblies containing BAML resources) you wouldn’t want to load them all in your process during the scan as these assemblies can’t be unloaded unless you load them in a separate domain that you can unload. Thus, Skinner will create a domain for scanning purposes and shut it down when finished scanning.

And of course, scanning for new skins is not something your application should be doing in the foreground. It needs to start, load the currently selected skin and start a background search for new skins. Skinner takes care of that too.

 

Developing a skin

First, you need XAML. I’m going to use one of the freely availablethemes and just reference it in my skin’s resource dictionary:

<ResourceDictionary 
  xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
  xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml">
    <ResourceDictionary.MergedDictionaries>
        <ResourceDictionary 
Source="pack://application:,,,/WpfThemes;component/BureauBlue.xaml" />
    </ResourceDictionary.MergedDictionaries>
</ResourceDictionary>

I also need a bit of metadata for the Skinner library which is basically the name of my skin (something you want to display to the user) and a pack Uri that Skinner will use to load this skin.

The metadata is defined for each skin in a class called SkinDescription. Skinner expects a a MEF-exportable class that implements ISkin interface to be defined for each skin. ISkin interface defines just one method:

/// <summary>
/// A contract that represents a skin.
/// </summary>
public interface ISkin
{
    /// <summary>
    /// Get the description of the skin.
    /// </summary>
    /// <returns>Description of the skin.</returns>
    SkinDescription GetDescription();
}

Thus, this is all the code needed to define your skin:

[Export(typeof(ISkin))]
public class BureauBlue : ISkin
{
    public SkinDescription GetDescription()
    {
        return new SkinDescription("Bureau Blue", 
            new Uri("/Skins/BureauBlue.xaml", UriKind.Relative));
    }
}

Here’s an interesting thing – the URI is defined relatively to the skin’s assembly. This is because Uri class’ constructor won’t happily accept an absolute pack Uri (sighs…). But how are we going to load a resource with a relative Uri like this? It doesn’t even mention the assembly name… Don’t worry, Skinner will take care of this too. In fact, it supports all these forms:

pack://application:,,,/MyAssembly;component/Skins/BureauBlue.xaml
/MyAssembly;component/Skins/BureauBlue.xaml
MyAssembly;component/Skins/BureauBlue.xaml
/Skins/BureauBlue.xaml
Skins/BureauBlue.xaml

However, as I mentioned, you probably won’t be able to create a Uri object with an absolute pack Uri.

 

Consuming skins with Skinner

You control Skinner with a skin manager. Skin manager is a class that implements (guess what?) ISkinManager interface. Skinner provides a default implementation for you, which a singleton and should be requested through SkinManagerFactory:

ISkinManager manager = SkinManagerFactory.GetSkinManager();

It insures there is only one instance of skin manager in your application as it keeps track of what skin is currently loaded.

Let me post the ISkinManager interface in its entirety:

/// <summary>
/// A contract for a Skin Manager.
/// Skin Manager is an entry point for a WPF application 
/// to the skinner library.
/// Through this interface it is possible to discover available 
/// skins, load and unload them.
/// 
/// The manager is expected to be called from UI thread 
/// and is not guaranteed to be thread-safe.
/// </summary>
public interface ISkinManager
{
    /// <summary>
    /// Fires when a new skin has been discovered. 
    /// Applications should subscribe to this event 
    /// to receive skin descriptions that they can use 
    /// to load skins.
    /// 
    /// The event is fired on the UI thread if Application 
    /// is available.
    /// </summary>
    event EventHandler<SkinFoundEventArgs> SkinFound;
    
    /// <summary>
    /// Notifies a subscriber of any errors that occured while 
    /// the scan was in progress.
    /// The error event is not fatal and doesn't abort scanning.
    /// It's primarily for notification purpose only.
    /// </summary>
    event EventHandler<ScanErrorEventArgs> ScanError;

    /// <summary>
    /// Scan for skins asynchronously using a separate domain.
    /// The domain will be unloaded once the scanning is 
    /// finished.
    /// </summary>
    void Scan();

    /// <summary>
    /// Load a skin.
    /// If there is a previously loaded skin it will be unloaded
    /// first.
    /// </summary>
    /// <param name="skinDescription">A description of the skin 
    /// to load.</param>
    void Load(SkinDescription skinDescription);

    /// <summary>
    /// Unload a skin that has been loaded with 
    /// Load(SkinDescription) before.
    /// If no skin has been loaded, the method does nothing.
    /// </summary>
    void UnloadCurrentSkin();
}

I’ve tried to document it as well as I could. I believe it’s simple enough to meet my first requirement, isn’t it? At the end of this post you will find a link to a solution containing test projects as well as the Skinner library itself (licensed under MS-PL) so you could try it in action.

 

Scanning performance

One thing that’s not yet been covered is performance. The attached solution contains ConsoleTestApp project that tests scanning performance. It runs Skinner against 202 skin assemblies and Skinner finishes scanning in about 1.6-2 seconds.

As to the loading performance you can check the provided WPF test project (there must be a running WPF application so that the skin can be merged into its resource dictionary).

Loading a skin from another assembly and applying it takes around 80 ms on my machine. Loading it for the 2nd time takes around 20-30 ms. Why? Because when you load a skin, the assembly is loaded into your main application’s domain. That’s the only way I could properly manage to load BAML. For details, have a look of part 2 of this post that’s coming soon.

I wanted to create a WPF resource dictionary and put it in a separate assembly for a certain purpose. I started up by creating  a regular class library and was about to add a new item to it when I was puzzled – I couldn���t find a proper template anywhere in the UI the Visual Studio provided to me. It was absent on the ‘New File’ dialog and it wasn’t there on the context menu either.

However, I knew it should be there, I’ve tried another project that was specifically created using one of the WPF templates and the items were there:

I tried adding WPF assemblies to my class library references (PresentationCore, PresentationFramework, System.Xaml, WindowsBase) but it didn’t make Visual Studio realize I need WPF specific items. I didn’t find a way to set it within a standard project’s settings dialog.

Then I compared the two project files (.csproj) and the only line that was interesting was:

<PropertyGroup>
  ...
  <ProjectTypeGuids>{60dc8134-eba5-43b8-bcc9-bb4bc16c2548};
         {FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}</ProjectTypeGuids>
  ...
</PropertyGroup>

On a regular class library project the ‘ProjectTypeGuids’ node was missing at all. It didn’t take long to find what those mysterious GUID’s are about: List of known project type Guids. So the first one says it’s a WPF project and the second one says it’s a C# project.

Adding the line to the project file did the trick and I was just left wondering why it’s such a bad idea to provide appropriate UI to control project types. Not a big deal though…

One day I got really annoyed by my laptop performing sluggishly. It was strange and annoying because its spec tops many of the regular development workstations we use at work by a mile (sic! hope they don’t read it ). Still, that wasn’t fun and I had no idea what was causing it. I have a lot of stuff installed for my development needs but no shitware and other pop-up-tray-assistance-wizards. And I have SQL Server Express that I use as a repository for Vault and my primary database system for my development.

So I just shrugged each time I had to wait a little more than usual, the laptop was performing well for most of the time and it does today. Although the rest of the article will be focused on SQL Server I do not make a statement that it’s a resource hog. Well it can be but it’s just not the point of this post.

So one day I opened up an Event Viewer in order to find details about a crashed app and guess what? I couldn’t find it! The Application log was full of messages reported by SQL Server and all of those messages were alike:

• Starting up database 'AdventureWorks2008R2'.
• Starting up database 'AdventureWorks2008R2'.
• Starting up database 'AdventureWorks2008R2'.
• ...

It went on and on and on… and what was striking is that it was reporting this message EVERY SECOND! Well, I had installed those sample databases some time ago to check something I don’t event remember what but I certainly didn’t ask for this intensive activity while I’m not watching.

My first reaction was ‘well, shit happens’ and I dropped all the AdventureWorks databases and thought I was done with that.

I wasn’t. In a few hours I went into the Event Viewer again and was red-bull-eyes’ed with:

• Starting up database 'ReportServerTempDB'.
• Starting up database 'ReportServerTempDB'.
• Starting up database 'ReportServerTempDB'.
• …

It was reporting it every 10 minutes which might have been felt like a relief but for me it was a trigger – time to do some investigation. I wasn’t going to uninstall the Reporting Services so I had to do something anyway.

Hit the Bing and you find out the problem is known and it directly relates to SQL Server ‘Auto Close’ property. Saying ‘problem’ is probably wrong. There may be no problem at all!

See, ‘Auto Close’ means that the database is ‘closed’ when a connection with the last client is closed, that is SQL Server release all the resource needed to keep the database in question running. Next time a connection is requested to this database SQL Server has to go for a heavy lifting to open it up again. This is the annoying event we see in the Event Viewer!

Why is that a problem? Or why is it not? It depends. If you got a database server you want it to be as responsive as possible. If the database is frequently accessed you want to keep it ‘hot’. Even the official recommendationfrom Microsoft is to keep ‘Auto Close’ OFF in this case:

If a database is accessed frequently, set the AUTO_CLOSE option to OFF for the database.

So if it’s accessed randomly the ‘Auto Close’ is the way to go. Why waste resources for nothing?

But what would you do with that activity happening each second on your laptop. YOU don’t need that. It’s understood. But what if it happens each 10 minutes? I don’t know what the Reporting Services are doing and I can live with a 10 minutes interval.

But if THAT happens on a database server – you got a problem and you’re better off switching the ‘Auto Close’ off.

‘Auto Shrink’

I mentioned this option too as it can also be a blessing in some situations and it can be evil in others.

It’s a trade-off between disk space and resources needed to do the shrink on one hand and, on the other hand, it’s not really a trade-off as you can disable it but still do the shrinks manually.

Official recommendationis as follows:

Set the AUTO_SHRINK database option to OFF. If you know that the space that you are reclaiming will not be needed in the future, you can reclaim the space by manually shrinking the database.

Conclusion

There is no ultimate rule. Recommendations and factors that can influence your decision are given above. Analyze, try. For example, I got a Vault database with both ‘Auto Close’ and ‘Auto Shrink’ options ON. But it’s not bothering me with those annoying messages. It gets open when needed and closed after that. It’s not hogging my resources when I don’t work with it and I’m fine with it .

We all know what ‘IN’ clause is about – we want records where a field value matches one of the specified variants. Let’s imagine the following model although it may seem artificial at first but it actually reflects a situation you can find in a goods tracking software:

So we have income and outcome documents. Each document has a header and a number of items. An outcome item also has a link to a corresponding income document, or technically a header of an income document. That link is important because for one of our business reports we want to show what items have been sold and who were their suppliers. Well, there can be lots of other purposes but that’s not the focus of the post.

So I was debugging a problem in a typical LOB application one of these days and there was a similar model (a bit more complicated, of course) and at the data layer I hit upon the following method:

public OutcomeItem[] GetItemsByIncomeHeaderIds(
    IEnumerable<int> incomeHeaderIds)
{
    using (TestDbEntities context = new TestDbEntities())
    {
        var items = from item in context.OutcomeItems
                    join id in incomeHeaderIds on 
                               item.IncomeHeaderId equals id
                    select item;

        return items.ToArray();
    }
}

So this is a resource layer implementation of an operation that needs to return outcomes for a specified number of income documents. What strikes you at first is the the choice a developer made to accomplish the goal – a join of database items and the provided list of income document ID’s. It’s not going to result in a SQL query that uses ‘IN’ clause, instead it results in a query like this if we pass it 3 ID’s (1, 2, 3):

SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[OutcomeHeaderId] AS [OutcomeHeaderId], 
[Extent1].[IncomeHeaderId] AS [IncomeHeaderId], 
[Extent1].[Name] AS [Name], 
[Extent1].[Price] AS [Price]
FROM  [dbo].[OutcomeItems] AS [Extent1]
INNER JOIN  (SELECT 
    [UnionAll1].[C1] AS [C1]
    FROM  (SELECT 
        1 AS [C1]
        FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]
    UNION ALL
        SELECT 
        2 AS [C1]
        FROM  ( SELECT 1 AS X ) AS [SingleRowTable2]) 
                                                    AS [UnionAll1]
UNION ALL
    SELECT 
    3 AS [C1]
    FROM  ( SELECT 1 AS X ) AS [SingleRowTable3]) AS [UnionAll2]
                   ON [Extent1].[IncomeHeaderId] = [UnionAll2].[C1]

See how ugly it is? It’s transforming our provided list of ID’s into SQL constructs but, wait, that’s not all. Check out what’s happening when you pass it 4 ID’s (1, 2, 3, 4):

SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[OutcomeHeaderId] AS [OutcomeHeaderId], 
[Extent1].[IncomeHeaderId] AS [IncomeHeaderId], 
[Extent1].[Name] AS [Name], 
[Extent1].[Price] AS [Price]
FROM  [dbo].[OutcomeItems] AS [Extent1]
INNER JOIN  (SELECT 
    [UnionAll2].[C1] AS [C1]
    FROM  (SELECT 
        [UnionAll1].[C1] AS [C1]
        FROM  (SELECT 
            1 AS [C1]
            FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]
        UNION ALL
            SELECT 
            2 AS [C1]
            FROM  ( SELECT 1 AS X ) AS [SingleRowTable2]) 
                                                     AS [UnionAll1]
    UNION ALL
        SELECT 
        3 AS [C1]
        FROM  ( SELECT 1 AS X ) AS [SingleRowTable3]) 
                                                     AS [UnionAll2]
UNION ALL
    SELECT 
    4 AS [C1]
    FROM  ( SELECT 1 AS X ) AS [SingleRowTable4]) AS [UnionAll3]
                   ON [Extent1].[IncomeHeaderId] = [UnionAll3].[C1]

Do YOU see where it’s getting at? See the tendency? If you pass it a lot of ID’s (say 2000 or more) it’s going to fail like this:

"Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries."

Although that join works pretty damn fast and it’s probably acceptable when you know the number of request parameters is limited at a reasonable level, it’s not at all acceptable when you need this piece of code to be scalable up to no limits.

The ‘IN’ clause might solve our problem here but we also know that it can be REALLY heavy when the number of arguments you put in it is big. How much big? Not too big really, you’re going to start straining the database with 1000 already.

The second problem with ‘IN’ clause is that different databases have different limits on the number of arguments you can put into it. It’s primarily dictated by resource/performance reasons. SQL Server 2008 allows you to put a lot more than other well-knows databases out there but it still has limits and one day it’s going to strike you with this:

"Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them."

Still, let’s see how we should form our LINQ query to trigger the ‘IN’ clause to be generated:

public OutcomeItem[] GetItemsByIncomeHeaderIds(
    IEnumerable<int> incomeHeaderIds)
{
    using (TestDbEntities context = new TestDbEntities())
    {
        context.CommandTimeout = 600;
        
        var items = from item in context.OutcomeItems
                    where incomeHeaderIds.Contains(
                                             item.IncomeHeaderId)
                    select item;

        return items.ToArray();
    }
}

The ‘Contains’ method on the collection does the trick. Now given the problem described above let’s rewrite our method so it becomes robust and scalable against any number of provided arguments:

public OutcomeItem[] GetItemsByIncomeHeaderIds(
    IEnumerable<int> incomeHeaderIds)
{
    using (TestDbEntities context = new TestDbEntities())
    {
        IList<IEnumerable<int>> parts = 
           new List<IEnumerable<int>>();
        List<int> allIds = new List<int>(incomeHeaderIds);

        while (allIds.Count() > 0)
        {
            int take = allIds.Count() > 1000 ? 1000 : 
                allIds.Count();
            IEnumerable<int> part = allIds.Take(take);
            parts.Add(part.ToList());

            allIds.RemoveRange(0, take);
        }

        List<OutcomeItem> list = new List<OutcomeItem>();
        foreach (var part in parts)
        {
            list.AddRange((from item in context.OutcomeItems
                           where part.Contains(item.IncomeHeaderId)
                           select item).ToList());
        }

        return list.ToArray();
    }
}

We don’t allow more than 100 arguments to be included in the query. It’s probably not the ideal number and it’s worth more testing to identify one but to prove my point let’s run the method against 20000 ID’s. When we put them all in 1 ‘IN’ clause the query executes in 2.22 minutes but when we run the last version that actually yields 20 SQL queries it finishes in 8 seconds!

‘IN’ clause is expensive. Don’t let too many arguments to be put into it.

One crazy idea: what if we take this approach of splitting parameters in parts but instead of using the ‘IN’ construct we will use the original join solution? Well, the truth is we won’t be able to feed 1000 arguments at a time. It’s limit is about 40 and in addition to ugly SQL it gives us about 19 seconds in total. More than 2 times worse compared to ‘IN’. But on the other hand, 40 at a time means it had to execute 500 queries! That proves that these queries are a lot faster than those using 'IN'. Still, when it comes to scalability this solution loses.

Bottom line: it’s ok to use ‘IN’, it works reasonably fast with a reasonable number of arguments, don’t let this number exceed 1000 (hm, who wants to run a few tests and come up with the ideal number?) and you’re going to be fine in terms of performance and scalability.