Using enums in LINQ to SQL

A common practice when working with databases is to use enumerations for any static lookup (ID/description) data. These enums are represented both in code and also in the database for referential integrity. The idea being this static data rarely changes so we don't need to pull it back continually from the database.

Taking the following data model as an example;

All of the tables that end with Enum are static tables that consist of reference data only and we don't want this to be pulled back from the database. Instead these enum tables are represented in code as enums and as such we need to tell LINQ to map the fields that refer them to enum values rather than the database tables.

First things first, I mapped out my basic entities as follows (notice the lack of enum tables as objects in this model).

In order to force the entities to use an enum value instead of loading a child entity object, I simply set the Type property for the field to a type in my project, as per the image below; (Also I renamed the fields from FieldXYZID to just FieldXYZ).

Finally, notice that because my enum definition is within the same assembly as my DBML, the type namespace is relative to the DBML. If it was in a different assembly, then you'd specify a full type name here instead.

Fixing the ClickOnce Error - "can't download files"

If after creating a click once deployment, you put it on your web server but find that when you run the .application URL, you get a "can't download files" error instead of your application running, check the details. If you're getting 404 errors for the .manifest or .deploy files, chances are IIS isn't configured correctly to support these file types.

By default IIS doesn't know what .manifest or .deploy files are, so it doesn't serve them out. You need to add them as MIME types by going into IIS manager, right clicking on the server name and selecting properties. Click MIME types and add the following;

.deploy      application/octet-stream

.manifest   application/manifest

Database diagram support objects cannot be installed...

Twice is two weeks SQL 2005 has reported the following error when I've clicked on the database diagrams node on certain databases;

"Database diagram support objects cannot be installed because this database does not have a valid owner".

The databases in question were restored onto my local SQL from backups on other machines and servers. I'd restore the database, set the owner using ALTER AUTHORIZATION, and then try to create a database diagram, only to be presented with the above error.

The symptoms actually mask the underlying problem - the database's compatibility mode. On both occasions the compatibility level was set to SQL 2000 (80) for the restored databases and it should be set to SQL 2005 (90). To resolve;

• Open the properties for the affected database
• Go to the options section
• Change the compatibility level to SQL 2005 (90)

Introducing LINQ (to objects) in 10 seconds

The database uses of LINQ (to SQL/to Entities) get all of the glory, but LINQ can be used against your run of the mill objects in .NET. Take for example sorting strings;

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

static void Main(string[] args) {   string[] friends = new string[]{     "Tony Johnson",     "Joe Mangel",     "Helen Daniels",     "Sky Mangel",     "Stingray",     "Dylan Timmins",     "Gerard Rebbeci",     "Pippa Styger"};   IEnumerable<string> sorted = from friend in friends orderby friend select friend;   foreach (string person in sorted)     Console.WriteLine(person); }

The interesting part is on lines 13/14 - we get a list of sorted strings by executing SQL like syntax on our string array. Lets try something else, lets sort the list based on who's got the longest name, sorting from longest to shortest: Notice I'm also using anonymous types in the following example( var ).

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

static void Main(string[] args) {   var friends = new string[]{     "Tony Johnson",     "Joe Mangel",     "Helen Daniels",     "Sky Mangel",     "Stingray",     "Dylan Timmins",     "Gerard Rebbeci",     "Pippa Styger"};   var sorted = from friend in friends          orderby friend.Length descending          select friend;   foreach (var person in sorted)     System.Console.WriteLine(person); }

Easy, but very useful. You can use LINQ to sort collections of objects and do all sorts of powerful and useful things.

App Domains and Dynamic Loading

Eric Gunnerson has posted an interesting and useful article on dynamic assembly loading and unloading using appdomains.

http://blogs.msdn.com/ericgu/archive/2007/06/05/app-domains-and-dynamic-loading-the-lost-columns.aspx

SQL Server - Changing collation order for an entire cluster

An annoying situation arose today with a project - development machines were using legacy SQL collation orders whilst the production cluster was using a windows collation. As all servers and databases should really use the same collation order, cross database joins and tempdb actions will cause you problems if not , it was decided that we'd change the production cluster to use the SQL_ collation orders!

What should have been a straight forward process actually turned out to be more difficult than expected and I found the following information useful, and largely undocumented;

The process for changing the collation order on a SQL server cluster is as follows;

1. Backup all of your databases and then detach them all
2. Take the SQL services offline in cluster management
3. Slap your SQL install disk into a drive and run the following command from it:

   start /wait setup.exe 
      /qn VS=<cluster name> 
      INSTANCENAME=<sql instance name or MSSQLSERVER for default> 
      REINSTALL=SQL_Engine 
      REBUILDDATABASE=1 
      SAPWD=<new strong SA password> 
      SQLCollation=<new collation order> 
      AdminPassword=<strong password> 
      SQLAccount=<domain\account> 
      SQLPassword=<strong password> 
      AGTAccount=<domain\account> 
      AGTPassword=<domain\account>

4. Bring SQL online in cluster management
5. Restore / re-attach your databases

Your master database etc will have been rebuilt and everything should be fine.

The problem I ran into however was when I used a different location to run setup from than was used in the original installation. Setup kept failing saying that it couldn't find valid setup package for "SQL Standard Edition (64 bit)". After trying lots of different combinations of the above, I discovered that despite running setup.exe from a new location, it actually looks at the registry to discover where it was installed from originally and wants to get the MSI files from there!!

The solution? You could change the registry to point to your new location, but easier is this extra parameter on your command line:

REINSTALLMODE=vomus

This tells the setup not to bother looking at the old location, and use the location where the setup is residing. This 60 minute job ended up taking almost 3 hours in the end - but at least I'll know for next time!

How fast is reflection?

Most .NET developers have an affinity to using reflection for things like configuration, inversion of control, dependency injection etc, but I was wondering today just how fast IS reflection - what overhead does it bring? So I wrote a (far from exhaustive) test;

The test consisted of code that would simulate 500,000 cycles of loading 9 different objects from an external assembly, create an instance of each of those objects and then invoke a method on it.

The method itself then resolves and creates 5 instances of 5 different objects before returning. In total this activity equates to 3 million reflection resolutions by type and 3 million object constructions followed by 500,000 method calls.

The results? The total time from start to finish on my laptop was 19.7 seconds. That seems pretty quick to me.

For refernce my laptop is an Intel Centrino duo core, 2Ghz with 2Gb of memory.

Losing intellisense in your content pages?

When using master pages in ASP.NET, visual studio 2005 often gets confused and loses its ability to display intellisense in your content pages. IE: Within your asp:Content tags, if you type asp: you won't get any intellisense for the standard asp controls.

There's a simple work around for this and that's to ensure you have your master page file open in a tab in the editor. If it's open, you'll get the intellisense back in your content page.

I'm sure this will be fixed in a later release, but until then, the above should suffice.

System.Net.WebRequest problems

This problem has been killing me. The overall problem is within a complex app, site and service installation, but i've scaled it right back to a simple web application that invokes a web request on another unsecured resource. My web app is very simple with just the following in Page_Load:

1   2 3

System.Net.WebRequest tx = System.Net.WebRequest.Create ("http://othermachine/listener.aspx"); System.Net.WebResponse rx = tx.GetResponse(); rx.Close();

This script is hosted on localhost and othermachine is within the intranet zone and no proxies etc are involved. On my machine things work fine, but on the other machines in the office (who also have the above installed on localhost) we the error message below during execution of the web request to the other machine (line 2 of the source);

A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond

Now, here's the kicker. the above code works when it's run through the VS.NET ASP.NET Development web server! It only fails when it's served through IIS!!! I've been trying to work this out for 2 days now to no avail. If anyone has any ideas, please get in touch....there's a beer in it for the solver!

UPDATE - HERE'S THE SOLUTION
Despite it working when hosted outside of IIS, and despite checking in several places to see if proxy / firewalls were to blame it turned out that when the above code was hosted within IIS it WAS using a proxy server on the failing machines. The solution was to simply disable the proxy for the app through web.config:

<system.net>
<defaultProxy enabled="false"/>
</system.net>

For some unknown reason, when hosted under IIS, the WebRequest would initialise and then ask the proxy server (configured in IE) for access to othermachine, but it would obviously ask the proxy server for this access as LOCALMACHINE\ASPNET or NETWORK SERVICE - which of course would probably be refused access to the proxy.

Really don't know why this was a problem under IIS only, but the above setting should work regardless of hosting environment.

The old double hop issue

Recently I had a WCF service calling into a simple ASMX service, but I got a 401 error each time it did so, even though my WCF service was setup to impersonate correctly.

The problem boils down to something called "double-hopping", which is where by default you're not allowed to pass network credentials beyond a single network hop.

Assuming the client application, the WCF and the ASMX are on separate servers, the client connects to the WCF, impersonating the client caller identity. When the WCF then connects on to the ASMX however it isn't allowed to pass up the impersonated credentials and instead uses the process identity for the worker thread (commonly ASPNET or NETWORK SERVICE).

Establishing trust for delegation is the answer - setting up the various servers to allow them to pass on impersonated credentials. The following support article at Microsoft covers how to configure everything: http://support.microsoft.com/default.aspx?scid=kb;en-us;810572

For reference here's a good posting about it.

Once you understand double hop issues, the problem is undeniably straight forward, however, if you're not "in-the-know" as I wasn't at the time I was struggling, double hopping in service oriented environments can be a royal pain in the ass.

WCF Impersonation

Impersonation (running code as the invoking user) under WCF is configured slightly differently to ASMX services. You need to first of all tell your binding to use windows authentication to identify the caller as follows;

    <bindings>
      <wsHttpBinding>
        <binding name="AuthBinding">
          <security>
            <transport clientCredentialType="Windows"/>
          </security>
        </binding>
      </wsHttpBinding>
    </bindings>

However, impersonation wise this won't do a thing - when your service code attempts to do something like write to the file system, it will do this as the user your worker processes are configured to use (normally ASPNET or NETWORK SERVICE).

Normally in ASMX you'd configure impersonation using web.config's <identity impersonate="true"/>, but WCF appears to ignore this. Instead you're individual methods in WCF need to be marked up by adding the following attribute onto the method;

[OperationBehavior(Impersonation:=ImpersonationOption.Required)]

When you consume the service on the client just add this code;

ClientCredentials.Windows.AllowedImpersonationLevel =
  System.Security.Principal.TokenImpersonationLevel.Impersonation;

The service will now impersonate the calling client user.

Identifying Styles in Sharepoint Services 3.0

Sharepoint has moved on leaps and bounds with the release of SPS 3.0, but it's still a pig when it comes to customisation. If you're changing the look and feel of a theme in SPS, you've got a tough time ahead! If it's available to you, using SPS designer aids with this greatly, but regardless, one of the hardest jobs in customising a theme is knowing which of the hundreds of styles apply to an element you want to change.

And so, in rolls the following code that you can re-use. By adding a content editor web part and using the source editor you can paste the following code in and have a web part that shows you the full CSS hierarchy of any element you move your mouse over:

<table border="1" width="100%">
<td valign="top" width="100%">
    <div id="spsClassTitle">TITLE</div>
    <div id="spsClassTree" style="height:300px;">CSS</div>
  </td>
</tr>
</table>

<script language="JavaScript">
function GetSPSClasses()
{
  var currElement = window.event.srcElement;
  var classTree = "";
  var Style1 = "color:green;";
  var Style2 = "color:red;";
  var CurrentStyle = Style1;
  
  if( currElement != null )
  {
    spsClassTitle.innerText = "Styles under the cursor";
    if( currElement.id != null && currElement.id != "" )
      spsClassTitle.innerText += "(" + currElement.id + ")";
      

    while( currElement != null )
    {
      if(currElement.className != null && currElement.className != "")
      {
        var newEntry = "<div style=\"" + CurrentStyle + "\"><B>" +
                       currElement.tagName + ":</B><BR/>" +
                       currElement.className + "</div>"
        if( classTree != "" )
          classTree = newEntry + classTree;
        else
          classTree = newEntry;

        if( CurrentStyle == Style1 ) CurrentStyle = Style2;
        else CurrentStyle = Style1;
      }

      currElement = currElement.parentElement;
      
    }
    
    spsClassTree.innerHTML = classTree;
  }
  else
  {
    spsClassTitle.innerText = "No styles under cursor";
    spsClassTree.innerText = "";
  }
}

window.document.body.onmouseover = GetSPSClasses;
</script>

SQL and Remote Connections

Excuse the simplicity of this post but we've all been tripped up by SQL Server / SQL Express not accepting remote connections or not allowing SQL Logins and have had to hunt through our scratty notes to remember what we did last time we installed it. Well here's the definitive guide to getting SQL to accept remote connections and getting it to accept the sa (or other SQL user) login.

1. Ensure TCP/IP is enabled in surface area configuration
2. Ensure "enabled" for TCP/IP in Network Configuration -> protocols in SQL Server Configuration manager.
3. Ensure sqlservr.exe exempt in firewall (if necessary)

That'll get it accepting remote connections. If you've installed with windows auth mode only though and you want to login with SQL users, you'll need to turn mixed mode on too:

1. Open Enterprise Manager and open the server properties
2. Set security to SQL Server and Windows auth mode
3. If necessary enable the sa user:
   • Open security -> logins and open properties for the sa user
   • Select the status tab from the left hand side
   • Set Login: Enabled
4. Restart SQL Server if you changed security at step 2

I know this was all very simple, but if nothing else I'll find it useful to refer back to when I try to connect and get a "connection refused" error and scratch my head for a bit.... [8-|]

WCF - Handling large data sizes

WCF is a bit fussy when it comes to protecting you against denial of service attacks by sending large data packets. This is great when you don't need to send large amounts of data, but when you do, it can cause you a few problems.

I've come across a situation recently sending multi megabyte attachments through a WCF service. In its default state WCF won't accept large data transmissions, you have to specifically configure it to accept large data by changing the binding as follows:

<binding name="AuthBinding" maxReceivedMessageSize="100000">

As the name implies maxReceivedMessageSize tells WCF how big a message it will be allowed to receive. If you're sending a large byte array across the wire - maybe an attachment file or something - you need to also configure the XmlReader that WCF uses to tell it how big an array to accept. You do this through the use of the readerquotas declaration within your binding:

<readerquotas maxArrayLength="10485760" />

This would allow the XmlReader to accept a byte array equivalent to a 10mb file.

FLUX.NET CMS v2.0 Released!

I released version 2.0 of the flux.net content management system and application framework yesterday. Go get it from http://www.fluxcms.co.uk/ if you haven't got it already!

FLUX.net is an open source content management platform and web application framework. It offers very advanced content and asset management facilities out of the box, coupled with an exceptional developers API and framework for building even the most complex web applications quickly and efficiently. Flux is presently in stable release 2.0 and 3.0 is in development.