Basics of Linq to SQL Plumbing

A few notes and getting started with Linq to SQL using the O/R Mapper – including a peak at the generated code. We’ll assume VS2008 is used for all this.

So, to start using Linq to SQL I’d recommend adding a new class library to your project and naming it something like BLL or DAL – busines or data access layer, as Linq effectively rolls these to jobs into one you can take your pick on this.

Next  up, to get started add a “Linq to SQL” class to your project using the Add New Item and then look under the Data templates. This will give you a datacontext you can use to update a set of tables, so depending on your needs name this either after your entire database, or give it a name representing a subset of database tables (like UserContext or OrderContext).

Once you have done this, you will see a new .dbml file in your solution, and once this opens you will be looking at the Visual Studio Object Relational Mapper – O/R Mapper for short.

Next step is to test this on some table. Bear in mind that updates and retrieval by default is on a 1-to-1 table basis with the code generated from the O/R Mapper – however, as you will see there are fairly easy ways to extend your classes to be able to load relational data effectively.
In many ways having the 1-to-1 limitation can be seen as a good thing. There are ORM tools out there like EntitySpaces, IronSpeed and many more who will build the whole database in code for you, many-to-many relations and all – however, while it’s nice to auto-generate a complete datalayer with two clicks and a cup of coffee, you can also very easily begin to feel that you don’t really know 100% of what is actually really going on in your application.
And let me just assure you that for a developer – that is not a good thing! If you don’t know how it works, no-one knows how it works.

In contrast to this, Linq to SQL strikes a healthy balance between the convenience of generating classes and a data context (which is easy to query and understand) and leaving the implementation of more advanced features (like retrieving and updating related data) and the core business logic for the developer to implement.

Anyways – moving on. Once you have your O/R Designer window open, get the Server Explorer window up and drill into you database and pick two or three good tables to play around with. Preferably these should be related in the database, and you might also want to add a TimeStamp column to each table (I call mine RowVersion).

First thing to do here is to look at the visual tables in the O/R mapper – check your properties window and see that you have a nice set of data for each one. Note that you have description of the SQL datatype, the matching CLR type, you have null indication, primary key, update checks (check your Timestamp columns! (if you are unfamiliar with timestamps google this, it’s an important SQL feature. Each table can have one Timestamp column (with whatever name you give it) and this is changed every time that row is updated – so it’s a concurrency tool) .
Also note that your relations form the database are displayed with the correct parent/child (PK/FK)  relation.

Next thing you might want to do is save and build, and then grab the full path from the .dbml file tab in Visual Studio and open this file in your favourite text editor (which should of course be the most excellent EditPlus). So here you can see how your SQL tables are described – and note that your relations  are also included in both the FK and PK table.

Next, jump back to VS2008 and have a look at the generated designer.cs class. Here you can see a load of properties, and you should be able to link these back to the information in the .dbml file pretty easily. Don’t worry too much about the details for now.

Next go back to the visual view of the .dbml file in VS2008, right-click one of your tables and choose “View Code” – note that this gives you access to a partial class, that extends the autogenerated class. For now just make a note of this, and realise that this has quite a lot of potential for extending your autogenerated class.


ScottGu’s Linq to SQL Debug Visualizer

From ScottGu’s most excellent blog ( you can download a free Visual Studio debugger add-in: the SQL Server Query Visualizer. Follow this thread to install it within your Visual Studio 2008:

This add-in is really useful because it allows you to check the query syntax built by LINQ before it’s sent to the database. Moreover, it allows you to execute the query from the debugger to discover if the result is what you were expecting. You can also modify the query.

Database Project vs. SQL Server Project in Visual Studio

Just a quick overview of the difference between these two, and a few initial recommendations for their use.

SQL Server Projects are used to create Common Language Runtime (CLR ) User Defined Types (UDT) in SQL Server. Please bear in mind that unless you have a really, really good reason, the almost unanimous advice from SQL Server experts is to leave these well alone.
MS SQL  Server is a pretty advanced product, and you should not have any needs to start extending the set of datatypes available.

Database Projects on the other hand are essential tools for versioning your SQL Server Development. Note that these integrate with your VS IDE – you can for example right click a SQL table in the server explorer and choose to generate create scripts to your Database Project.

Main purpose and benifit of DB Projects is to log all your create and subsequent update scripts in here. Use folders to version these – and to keep the order of execution clear, should you ever need to rebuild your database.

On a wider more general note, this all ties in with a certain style of development, where you might use IDE’s to create, update or drop SQL Server Objects (tables, views, sprocs – whatever) – but you never do anything without creating and saving a script that executes this change using pure SQL.
This is true for both creating, altering and dropping – also make sure all your script have the proper if exists drop etc etc.

For some resources on how to do all this in SQL – we’d recommend reading: Pro T-SQL 2005 Programmer’s Guide from Apress (in general just read stuff from Apress and you’ll be ok!).

Umbraco Basics: Templates

Right, so this is simple – thou perhaps not as simple in version 4 as in version 3. Main problem is simply just that the main vidoe tutorial on this only covers v3 – where we are dealing with raw HTML templates, whereas v4 now by default comes with support for .NET MasterPages switched on.

So where as the tutorial tells you that Umbraco templates are completely empty and that you can (must) start from scratch – the new v4 in stead comes with something like the below for default:

<%@ Master Language="C#" MasterPageFile="~/umbraco/masterpages/default.master" AutoEventWireup="true" %>

<asp:Content ContentPlaceHolderID="ContentPlaceHolderDefault" runat="server">


So, I thought, well great that makes sense, and proceeded to try and nest another MasterPage below this. Full and nice support for this, but I kept getting this error message:

Cannot find ContentPlaceHolder 'masterContentPlaceHolder' in the master page '/masterpages/Master.master'

So what the heck? Well, of course what I realised (fairly quicly, kinda :0) is that of course these MasterPages are placed inside the Umbraco page engine – which no doubt uses it’s own master page – so you have to realise that you are already in a MasterPage inheritance chain when you define your first base MasterPage.
And what does that mean for us? Well it means that everything has to be wrapped in a Content tag – even from the very first start – to illustrate let me show you my new Base Master and a Home Page template that is nested in this:

<%@ Master Language="C#" MasterPageFile="/umbraco/masterpages/default.master" AutoEventWireup="true" %>

<asp:Content  id="MasterContentPlaceHolder" ContentPlaceHolderID="ContentPlaceHolderDefault" runat="server">

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "">




<asp:ContentPlaceHolder Id="masterContentPlaceHolder" runat="server"></asp:ContentPlaceHolder>




So note that the above is already nested into the Umbraco engine – hence the need for a <asp:content tag at the base level before you can add any HTML.
And so nested within the above I have my child template for the home page:

<%@ Master Language="C#" MasterPageFile="/masterpages/Master.master" AutoEventWireup="true" %>

<asp:Content ContentPlaceHolderId="masterContentPlaceHolder" runat="server">

Home page Template Rules!


Hope that makes some sense.

Some other useful and worthwhile features include the ability to insert data from your document types and generic Umbraco fields as well. Us the top buttons to do this.
We will return to this in more detail later.

Umbraco Basics: Document Types (Tabs, Properties, Structure and Data Types)

Right – so we’re playing with Umbraco, and watching some facinating videos online, and we thought we’d share our pain with you here and tell you something about Document Types (don’t get me wrong, as CMS’s go Umbraco is pretty sweet).

So what are document types? Well together with Templates they form the core building blocks of Umbraco (Templates are covered later) – and whereas Templates defines the look and feel of a page, then the Document Type defines the data available to the page. Really you can think of it as a database table that you define for a certain type of pages.
The data you define is what users can edit when working with pages of that type through the CMS. So think of them as blueprints or specifications for pages – defining the data that the Editor users will be asked for when creating new pages of that type.
Some examples of document types could be “HomePage”, “StandardContentPage” or  “ArticlePage”,  “you name it” – I think is the general idea.

So, how is this done?
Well, it’s done through Generic Properties which each have a data type. Properties are in turn grouped into Tabs which provide a logical grouping for properties in the edit interface.
Note that Properties all come with a set of meta data which really constitute the information you would normally glean from looking at a data table definition. This meta data includes Name, Alias (unique to the tab), Type (the data type) and of course the Tab which the property belongs to (required).

So, Tabs we have already touched on. Each document type can have one or more of these – effectively grouping the properties. Often you will find that these will correspond to the functional areas on your page layout (so for example MainContent, LatestNews, FooterInfo – etc).

Next we have Structure information. This defines the hierachy of the Document Types (that again constitute the building blocks of your site). Here you can define which Document Types can be nested below others – this is termed “Allowed child nodetypes”.

And finally – thou first in the Document Type editor interface, we have the Info meta data.
This is just plain old descriptive information for your Document Type, like it’s name, icon, description – and the allowed Templates. Again, we will cover Templates in a tick – but for now just realise that Document Types are data definitions, this data can of course be showed in more than one way – and for this reason it is also possible to specify one or more “Allowed Templates” – i.e. one or more ways that the data (document type) can be displayed on the frontend.

One final aspect to touch on is the Data Types of the Generic Properties. There are a long list of built in datatypes like number, textstring, Rich Text etc. Most of which funnily enough are listed not by type, but by the control type they are represented by in the editor interface (so we have for example a Rich Text Editor data type).
Interestingly (and no doubt important) you can create your own custom data type by choosing a name and a base type (from Date, integer, Ntext and NVarchar). So, just to clarify – what this does is it allows you to present the editor with a control that allows them to select from a specific data type you defined.

No doubt we will have more fun with Data Types later on. For now, let’s move onto Templates

OOA/D with UML and OO Patterns – Conceptual Domain Model

This post is based on the 2nd Ed. of Craig Larman’s most excellent “Applying UML and Patterns”.

Welcome back for yet another adventure into the bright and colourful world of Craig Larman (CL) and UML. I got a slightly tricky domain that I have done some Use Cases for, and now I want to single out one of the high risk areas and start an iteration focused on this – and to gain a good OO basis to start from, I’ll be doing a conceptual Domain Model using UML – and in particular the community edition of Visual Paradigm for UML 7.0. Nice free tool to get you started with UML.

Can you think of anything better to do with an afternoon? Me neither.

So how did we get into this mess in the first place? Well, we decided we wanted to do OO analysis, and Domain Models is one of the core building blocks in Larman’s approach to this. And we like and trust Larman. The essence of OO analysis is division by conceptual classes (not functionality) and the subsequent allocation of responsibilities to these.

So, what is a Domain Model (DM)? Well, it’s a conceptual visualisation of the objects within (a part of) our business domain. So this is still nothing to do with actual Java or C# classes, we are thinking in terms of real world or virtual world actors here. Note that UML does not actually supply a specific syntax for this, so what you do is simply use Class Diagrams, but leave out any code specific details.

ScreenShot016Also, note that we are still in a agile and iterative process (Unified Process) – so don’t try to create a DM that covers every single possible object, association and attribute of your domain. In stead, focus on a high risk area, and within that in your DM only focus on details that are of interest for your current iteration and focus.
If you leave something out, you can revisit this and refine your DM in later iterations. Be cool! Cool as a cucumber. However, don’t try to over-simplify the area you are focusing on. As a general rule more conceptual classes are better than few. Don’t be a pansy. Also note that some objects might only have a behavioural role, which is fine.

So how do we find these blessed conceptual objects?

Well CL recommends using a) a Conceptual Class Category list and b) Identifying Noun Phrases.

I will skip over the first one a bit lightly as I find it a bit rigid and unintuitive – basically it’s a load of categories that you can use as triggers for your poor brain. I found the list online in a word document here if you’re interested:
Our thanks to Mary Margaret.

Much more fun is going through your Use Cases looking for Noun Phrases!
Now, of couse this is just natural written language with all it’s nuances and ambiguities so you cannot just do a one-to-one mapping, but you’ll work it out.

Either way – all you do now is plunk these into a Class Diagram – and then we’re onto Attributes and Associations. Happy days!

But no! Hang on a mo – first we must head a few words of caution from our wise friend CL:

  • If something is of a simple datatype (string, number) it’s most likely an Attribute.
  • Beware that you will quite often find a need for Definition object and an Instance object. See below here:
    ScreenShot017So the key here is Normalization really – we do not want to duplicate the description and price for every item, and also we need to retain these even if there is currently zero items in the system. (Larman cooks up this elaborate example about flights and some airline disaster where all flights are cancelled, but we have to retain the flight information. Think he might be suffering from post 9/11 trauma).
  • Finally CL recommends “lowering the representational gap” by choosing real world names. I don’t know what else you would do really, but nonetheless it makes sense.
  • Finally, finally – don’t spend days on this, CL says that with some experience you should be able to do a DM from some good Use Cases in a few hours. It’s important, but it’s not rocket science – so don’t worry if you finish it quickly. That’s ok, have a Doughnut.

Adding Associations

Right, probably main sticking point here will be the UML syntax, so let’s cover that. From CL’s book:


Note that the direction arrow is just an aid to the text.

Big question is of course, how do we identify the correct associations? Well, two ways: 1) Consider relationships where knowledge of the association need to be recorded or preserved for some duration, and 2) use the Common Associations List below to check through the conceptual objects you have identified.

Category Examples
A is a transaction related to another transaction B CashPayment—SaleCancellation—Reservation
A is a line item of a transaction B SalesLineItem—Sale
A is a product or service for a transaction (or line item) B Item—SalesLineItem (or Sale)Flight—Reservation
A is a role related to a transaction B Customer—PaymentPassenger—Ticket
A is a physical or logical part of B Drawer—RegisterSquare—BoardSeat—Airplane
A is physically or logically contained in/on B Register—Store, Item—ShelfSquare—BoardPassenger—Airplane
A is a description for B ProductDescription—ItemFlightDescription—Flight
A is known/logged/recorded/reported/captured in B Sale—RegisterPiece—SquareReservation—FlightManifest
A is a member of B Cashier—StorePlayer—MonopolyGamePilot—Airline
A is an organizational subunit of B Department—StoreMaintenance—Airline
A uses or manages or owns B Cashier—RegisterPlayer—PiecePilot—Airplane
A is next to B SalesLineItem—SalesLineItemSquare—SquareCity—City

Of the above note the underlined High Priority Associations. Also don’t at this point go overboard with every conceivable association, stick to those that matter in your current iteration and focus. This whole exercise is about conveying meaning, don’t clutter you UML with noise.

Each end of an association is called a Role – and these can have names, multiplicity and navigability – all optional. Below some details on Multiplicity:


The standard naming convention for associations is TypeName-VerPhrase-TypeName – see the example above and stick to this format.

Finally CL dispenses a little gem of wisdom. Question is should DM be kept strictly up to date in future iterations as you discover new and interesting complexities or perhaps your requirements change? CL’s answer is – only if there is a practical real value to be gained from this. The purpose of DM is to convey knowledge and inspire later steps (namely our real Class Diagrams) – they are not a strict documentation tool!

Adding Attributes

Again CL tells us to focus on what we care about and what is important when adding these. Also focus on simple datatypes, anything beyond this is likely to be a conceptual object candidate in it’s own right. An example from the post traumatic world of CL:


If in doubt: define it as a separate conceptual class and avoid using FKs or overly trivial properties at this point, stick to what describes the Conceptual Class, things that describe the real world object we are working with. However – note also these  examples of seemingly simple data types which should really be conceptual classes are:

  • It is composed of separate sections: phone number, name of person
  • There are operations associated with it, such as parsing or validation: social security number
  • It has other attributes: promotional price could have a start (effective) date and end date
  • It is a quantity with a unit: payment amount has a unit of currency
  • It is an abstraction of one or more types with some of these qualities: item identifier in the sales domain is a generalization of types such as Universal Product Code (UPC) and European Article Number (EAN)

Now, a note on quantities. Most numeric quantities should not be represented as plain numbers – because it is a common requirement to know the unit and support conversions. So the attribute could perhaps be “quantity” and the unit “money” or the attribute is “width” and the unit is “centimeters” or “pixels” – what have you (you might want to convert pixels into print units). However, mapping each of these out into seperate classes called Quantity and Width is overkill, so what you do is collapse this information into the attribute, and you annotate these using “amount: money” or “width: pixels” – etc.
Basically, don’t just write a numerical attribute on it’s own, and be more specific than “number” or “int” when you define the unit. That’s all.

And that’s that!

OOA/D with UML and OO Patterns – Unified Process and Use Cases

This post is based on the 2nd Ed. of Craig Larman’s most excellent “Applying UML and Patterns”.

So this is going to be a fairly quick run-down of some of the core things to take away from the initial part of this book, dealing in particular with a reusable approach to identifying system requirements, risks and scopes – and more interestingly using use cases to build a foundation for our subsequent Object Oriented analysis of a business domain.

As I noted elsewhere I am reading the 2004 2nd edition of this book, so this book focuses on the “Unified Process” (UP) and only briefly mentions the newer SCRUM and Agile methodologies, however looking over the 3rd edition Larman tells us that the UP is a very open and flexible approach that is indeed very compatible with these newer working methodologies.

Let’sa kick off:

The Unified Process and Iterative Development

So you have probably heard this one before in some shape or form. What is the only constant in software development?
Change, is the answer. So, if  you’re going to plan, plan for change.

So what we should not do is come up with a huge rigid development specification and plan that sets out exacly what will be done when, but in stead apply an adaptive iterative process, where complexities are broken down, high risks are tackled one step at a time, and feedback from stakeholders is incorporated – all in a continuous upwards and organic cycle (our iterative process).
Really what you have to think of is the feeling that you don’t really understand something until you have done it, you don’t fully understand a problem until you have solved it, etc.

Some benefits of iterative development:

  • Early mitigation of high risks
  • Early visible progress
  • Early feedback from stakeholders
  • Better management of complexity (let’s just admit that we only have so much working memory, and tackle one set of problems at a time)
  • Lessons from each iteration can be applied to the next, so we have a self improving process (cue the violins around here)

How long is an iteration? Really depends on project, team size and what have you, but “Short is good” – find logical chunks and if in doubt, go for the shorter option.

Right – so how does this fit in with the UP? Well, let’s look at some tenants of UP:

  • Tackle high risk/value issues early.
  • Continuously engage stakeholders for feedback, evaluation and refinement of requirements.
  • Build a cohesive core architecture in early iterations.
  • Test early, often and realistically
  • Apply use cases
  • Model software visually (UML)
  • Carefully manage requirements
  • Carefully manage change requests

So reading through that you would probably agree when Larman says that this is compatible with Agile and SCRUM methodologies.

So what are the phases of the UP?

  1. Inception – this is a brief initial probing of the scope, vision and business case. Basically this is deciding whether to do an initial investigation – so we’re just testing the water here.
  2. Elaboration – thunderbirds are go! The project is a reality, and we now refine our vision, we identify most of the requirements and scoper and start an iterative implementation of the core architecture.
  3. Construction – iterative implementation of lower risks and more straightforward elements (thou the devil is of course always in the detail).
  4. Transition – beta test and deployment.

Despite this nicely laid out 1,2,3,4 list, Larman is keen to underline that this is not a conventional waterfall approach to development – each step (well at least 2 and 3) are iterative, and so contains x number of iterations where all the elements are repeated.

Also Larman briefly mentions an Agile UP – but this is just a kind of lightweight extra adaptive spin on the above, so we’re fly fucking about how you plan between iterations and such like. Key ideas is still to keep things light, plan for change, get feedback, test and adapt your planning and specification on a continuous basis.
Sounds great right? Sounds like a lot of meetings and a lot of emails if you ask me, but do realise that all projects have the potential for turning into an utter disaster, and being proactive and engaging your users and actually engaging yourself in the development process through systems like the UP or Agile/SCRUM is much less painful in the long run.
Can even be fun, but just don’t get too excited. This is still work, and there is a reason you’re paid to do it.

Now, let’s look at the old UP in a bit more detail:

FURPS+ Requirements Pattern

“FØRPS” – you can tell this is going to be good right. However, despite the name this or any other pattern you might find to your liking, is useful just to bear in mind.
Getting your requirements is of course important, and having a little check list like this to keep in mind is not a bad idea.

So – here they are (straight from he book (almost)):

  • Functional— features, capabilities, security.

  • Usability— human factors, help, documentation.

  • Reliability— frequency of failure, recoverability, predictability.

  • Performance— response times, throughput, accuracy, availability, resource usage.

  • Supportability— adaptability, maintainability, internationalization, configurability.

The “+” in FØRPS+ indicates ancillary and sub-factors, such as:

  • Implementation— resource limitations, languages and tools, hardware, …

  • Interface— constraints imposed by interfacing with external systems.

Great stuff – but do use it, will pay of.

Use Case Models

This one is important – and it’s actually a great tool if you use it the right way.

So first of all, use cases are just text that you write – so, in a sense this is not much of a “tool” – however, it’s the way that you do it that does it, so to speak.

So what you do is you write about important or key scenarios (sequence of actions or interactions) also known as a use case instance – and details what actors (person/user/computer system/organisation/3rd party interface, whatever) do to achieve a certain goal.

That last bit is important when trying to decide where to start with your use cases. Start by asking what are the main objectives of the actors in the domain, and don’t be afraid to start out quite general, i.e. “Customers want to buy products” – and then analyse this goal and break it down into more specific scenarios that you can write about in some meaningful level of detail. I.e. “Customers browse products online” and/or “Customer purchases products online”.

Another important aspect, especially for all you geeks out there, is to keep this simple and in a format that can be understood by all stakeholders, so don’t start worrying about technical details and implementation at this point. Keep it all at a high level of abstraction (aka Black-box style), with phrases like “System records the sale” but nothing about a databases being updated or similar.
Bear in mind the UP and the continuous feedback from your stakeholders (clients/users) in each iteration – Use Cases is an important tool that you can share with these, so make sure they are readable and also try not to make them overly convoluted or unnecessarily long.

Use Cases come in different formats:  brief, casual and fully dressed.
During Inception you might do a load of brief Use Cases and maybe 10-20% in fully dressed for high risk areas, and then during the elaboration iterations you will expand this to have fully dressed use cases for all important scenarios.

Within the fully dressed format, we have different formats as well, most noteworthy we have one that simply lists the flow of actions and another two-columns approach that separates user (human) actions on the left and system actions on the right. Larman prefers the first, I think I like the second.

Regardless of this, you should always use a format that includes:

  • Specify the primary actor.
  • List the stakeholders and their interests (goals) for the scenario.
  • Preconditions (what must happen before our scenario can start)’
  • Main Success scenario (in one or two column format)’
  • Extensions (alternative flows) – so for example some possible problems and how they should be handled.
  • Special requirements (any details that don’t fall into the above)’
  • Technology and Data Variations list (specific technical details (place here and not in main scenario description)).
  • Frequency of occurrence for the scenario (any time, monthly).
  • Open issues (things to be clarified and/or confirmed).

You can see an example template here: which contains most (and some additional) of the above.

As a little aside I’ve (in my own personal capacity) started using AbstractSpoon’s wonderous ToDoList program for my Use Cases, using the Rich Text Editor in here you can start with general Use Cases and then sub these with more specific “Child Use Cases” as you go through the elaboration iterations – very useful – and also an absolute excellent time management programme! 5 out of 5!

Finally – let’s just touch on what is suitable for use cases. We’ll take this straight from the Larman’s mouth:

Use cases are defined to satisfy the goals of the primary actors. Hence, the basic procedure is:

  1. Choose the system boundary. Is it just a software application, the hardware and application as a unit, that plus a person using it, or an entire organization?
  2. Identify the primary actors—those that have goals fulfilled through using services of the system.
  3. Identify the goals for each primary actor.
  4. Define use cases that satisfy user goals; name them according to their goal.

Of course, in iterative and evolutionary development, not all goals or use cases will be fully or correctly identified near the start. It’s an evolving discovery.

Finally, Larman tells us to name use cases stating with a verb. So for example “Manage Users” or “Sell Product” etc.

Some other requirements for the Inception and Elaboration phase

A couple of good recommendations from Larman are:

Write a vision for the project – so record what the overall aim and purpose is (and keep your eyes on the price using this as you go meandering down the winding road of development).

Start a Glossary and keep this up to date (this is just an excellent idea, and especially for larger systems this becomes extremely valuable as people jump to and from the process).

Next up let’s have a look at System Sequence Diagrams (quick) and the Domain Model (in detail!).