LINQ to SQL is a programming model that introduces queries as a first-class concept into any Microsoft .NET language. However, complete support for LINQ requires some extensions in the language used. These extensions boost productivity, thereby providing a shorter, more meaningful and expressive syntax to manipulate data.
I’ve just finished building a small site for a client of ours entirely using LINQ to SQL and I was pleasantly surprised at the ease and simplicity of hooking things up. I was a bit sceptical at first, but I think it worked out fine in the end.
I just had to remember that the fundamentals don't change :
- Try to stay as stateless as possible
- Keep clean lines between your services and data access
- Don't fight LINQ, use it. If it isn't helping you, you are probably doing something wrong
It’s important to mention that my project consisted of 5 or 6 main objects each with associated child relationships so we really are talking small and simple, hence why I wanted to play around with LINQ to SQL. I also knew that my target user base would be fewer than 20-30 internal users and scalability wouldn’t be an issue in the immediate future.
From reading a few blog posts I noticed that many other developers had expressed concerns about LINQ to SQL not being a true ORM and how it lacks support for the ability to do many-to-many relationships. However I still wanted to push forward and implement my solution using this method.
The syntax is very straight forward once you have mapped out all of your keys/foreign keys and added your Tables to the inbuilt DBML designer (shown below).
I like the fact that all of my business logic is embedded in my source code, and everything written is simple enough for any other developer to come along, open the source and modify as they see fit. If the database schema changes, just update the DBML and start building against the strongly typed objects created.
One thing I noticed when researching LINQ to SQL was the notion of a DataContext and how it’s responsible for managing access to the database as well as tracking changes made to entities retrieved from the database.
I decided to create a new Context foreach atomic operation For example, each time I want to retrieve a single Asset (my object) I would call the following code:
public Asset FindById(int assetID){ return _dataContext.Assets.SingleOrDefault(a => a.AssetID == assetID);}
Whereby dataContext is setup:
public AssetService() { _dataContext = BaseDataContext.GetContext(); _dataContext.Log = new LinqDebugger(); } public static DatabaseDataContext GetContext() { DatabaseDataContext db = new DatabaseDataContext(ConfigurationManager.AppSettings["ConnString"]); return db; }
Saving and updating objects is a breeze:
public int SaveOrUpdate(Asset asset){ if (asset.AssetID == 0) { asset.DateCreated = DateTime.Now; _dataContext.Assets.InsertOnSubmit(asset); } _dataContext.SubmitChanges(); return asset.AssetID;}
The above samples show how we can write clean, typesafe and composable queries that follow the DRY (Don’t repeat yourself) principle.
Other useful extensions include:
- Where
- Sum / Min / Max / Average / Aggregate
- Join / GroupJoin
- Take / TakeWhile
- Skip / SkipWhile
- Concat
- OrderBy / ThenBy
- Reverse
- GroupBy
- Distinct
- First / FirstOrDefault / Last / LastOrDefault
- Any / All / Contains
- Count
Despite its power, LINQ doesn't deprecate SQL. It takes more than 95% of the querying brunt, but you still sometimes need SQL for:
- Hand-tweaked queries (especially with optimisation or locking hints)
- Queries that involve selecting into temporary tables, then querying those tables
- Predicated updates and bulk inserts
All in all it was a fun and worthwhile experience using LINQ to SQL and I think it can certainly evolve with future versions of .Net. I’ll definitely be utilising some if not all of the techniques for small to medium sized projects in the future.
Two thumbs up!