LINQ to SQL - 5 minute introduction.

Whilst perhaps not a fully fledged OR/M solution like NHibernate, LINQ to SQL has a place in this world as it's an excellent tool if your domain entities map 1:1 to your database schema as it provides a visual tool (not always useful, depends on scale) to design your model along and it uses the excellent LINQ query syntax to get data out of the database.

I started this example by creating a separate project for the data access layer. This project is a standard class library, from which I've exposed the entities in our database along with repository objects that will be responsible for managing these entities.

After creating a new class library for our DAL project, I've then added a couple of enum files that correspond to the different types of static data that I'll be needing and then added a LINQ to SQL Classes item.

This creates a DBML file and presents us with a designer surface to start dropping our tables and views from our database onto. A connection to the database needs to be defined in server explorer first, but once that is in place, and with the designer surface and server explorer open, you can now start to drag and drop the database elements onto the LINQ to SQL surface, thereby building up your entity model.

Notice how the designer automatically walks foreign keys and constraints and this in turn then automatically creates properties in your elements. For example, the Category entity in this example has a property named CategoryBudgets automatically created that will load the budgets associated with a category on demand (lazy load).

With your DBML file in place, and after following the post on how to use enums with LINQ from my earlier post, you should have a full object model of entities that can be queried using LINQ in your code;

For example, in your application you would be able to get a list of all categories by querying thus;

   1: DC.Finances.Database.FinanceDataContext dc = 

   2:     new DC.Finances.Database.FinanceDataContext();

   3:  

   4: var cats = from c in dc.Categories select c;

   5: MessageBox.Show(cats.Count().ToString());

However, we wouldn't want to put this LINQ code directly into our UI code as, sticking to the tenets of good design, specifically separation of concerns, we're going to keep that sort of thing in the DAL, so we would wrap this up with a repository class to go ahead and give us data we require, whilst implementing any rules and logic for us. eg: CategoryManager.GetAllCategories()