LINQ-to-SQL in Azure

Object-Relation Mappers

When code talks to a database, the basic method is to use plain ol' SQL, which works everywhere. But SQL has disadvantages: It is interpreted, so any errors are catched at run time, instead of compile time. Conversion of data to and from objects or other data structures has to be done manually.
Therefore many programmers use an Object-Relation Mapper (ORM). In the Microsoft world of SQL Server and .NET, there are several ones available. Here are a few that I have experimented with:

Microsoft itself heavily backs its Entity Framework, launched in 2008. This is flexible in that it can generate a database schema from an self defined entity model or the other way around. Disadvantages are that it is complex and that gives a lot of overhead, which is a pain in situations where performance is important.
Somewhat lighter is NHibernate, an open source ORM, based on Hibernate from the Java world.
Dapper is much lighter than the previous two, providing both minimal functionality and overhead over SQL.

There are many more ORMs availble, like Genome, LinqConnect, LLBLGen, PLINQ, the Signum Framework and others. Some are open source, some require payment. Some are available as NuGet packages, some not. You should pick one that suits your business the best.

LINQ-to-SQL

This article focuses on an ORM that has not been listed above: LINQ-to-SQL. As the name suggests it integraties tightly with LINQ and in my opinion it occupies a sweet spot between lightness and usability. Sadly Microsoft no longer actively developes it, but it is still very usable. LINQ-to-SQL works together with a tool called SQLMetal.exe that can generate entity classes from a database schema, either in .dbml format or code, or both. Unfortunately, SQLMetal does not work with Azure databases. Fortunately, it is possible to write your own SQLMetal program! Below I will outline how this can be done, without providing full code as that is proprietary.

First use plain SQL to query SQL Server's metadata about the structure of its databases. Query sys.tables, sys.columns, sys.foreign_keys, sys.foreign_key_columns, sys.indexes and such.
For example to find the names of all tables use:

        
select [name] from sys.tables
where [type_desc] = 'USER_TABLE'
and [temporal_type_desc] = 'NON_TEMPORAL_TABLE'
order by [name] asc;

And to find details of a table's columns use:

            
declare @TableName nvarchar(255)
set @TableName = 'myTableName'
select c.[name] as ColumnName, t.[Name] as TypeName, c.[max_length], c.[is_identity], c.[is_nullable],
isnull(
   (select top 1 i.[is_primary_key]
    from sys.index_columns as ic
    inner join sys.indexes as i
        on ic.[object_id] = i.[object_id]
        and ic.[index_id] = i.[index_id]
    where ic.[object_id] = c.[object_id]
    and ic.[column_id] = c.[column_id]
    order by i.[is_primary_key] desc
   )
,0) as IsPrimaryKey
from sys.columns as c
inner join sys.types as t
    on t.[user_type_id] = c.[user_type_id]
where c.[object_id] = object_id(@TableName);

Next use the output of these queries to build up a DBML and save that to a file. This is an XML file, so you may want to use the System.Xml.Linq.XDocument class to construct it. Loop through all your tables and within each, through all its columns and foreign keys. Generate XML-elements for each. You can find the DBML XML schema here. If you have access to a local SQL Server that you can still access with SQLMetal, you can also run that and spy on the output.
Below is a small example of a DBML file:

            
<?xml version="1.0" encoding="utf-8"?>
<Database Name="myDatabaseName" EntityNamespace="myNamespace" ContextNamespace="myNamespace" Class="MyDataContext" xmlns="http://schemas.microsoft.com/linqtosql/dbml/2007">
  <Table Name="dbo.State" Member="State">
    <Type Name="State">
      <Column Name="ID" Type="System.Int32" DbType="Int NOT NULL" IsPrimaryKey="true" CanBeNull="false" />
      <Column Name="Name" Type="System.String" DbType="NVarChar(255)" CanBeNull="true" />
      <Association Name="FK_Thing_State" Member="Thing" ThisKey="ID" OtherKey="StateID" Type="Thing" DeleteRule="NO ACTION" />
    </Type>
  </Table>
  <Table Name="dbo.Thing" Member="Thing">
    <Type Name="Thing">
      <Column Name="ID" Type="System.Int32" DbType="Int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="true" CanBeNull="false" />
      <Column Name="Description" Type="System.String" DbType="NVarChar(1024)" CanBeNull="true" />
      <Column Name="StateID" Type="System.Int32" DbType="Int NOT NULL" CanBeNull="false" />
      <Column Name="CreateTime" Type="System.DateTime" DbType="DateTime NOT NULL" CanBeNull="false" />
      <Column Name="ModifyTime" Type="System.DateTime" DbType="DateTime" CanBeNull="true" />
      <Association Name="FK_Thing_State" Member="State" ThisKey="StateID" OtherKey="ID" Type="State" IsForeignKey="true" />
    </Type>
  </Table>
</Database>

DBML-files can be used with LINQ-to-SQL, but if you want even more padding you can generate entity classes in code too. The easiest way to do that is not to generate .NET code like the DBML was constructed above and to use SQLMetal as a shortcut for the second part. Though SQLMetal cannot access an Azure database, it does know how to handle DBML files. Now one simple command suffices to create a class file from the DBML:

sqlmetal /code:"MyEntityClass.cs" /language:C# /namespace:myNamespace /context:MyDataContext MyFile.dbml

That is the gist of the process. There are some peculiarities to tackle, like the demand to define a foreign key in two places, both on the 'sending' and the 'receiving' end. Also you need to consider if you need to support things like default column values, unique indices and such, each of which requires extra DBML-elements and extra code to generate that. If you work out a complete solution, a few hundred lines of code will suffice.