Modern .NET Development and The Joy of Simple LINQ to SQL
Introduction – Modern .NET Development
It took me a while to realize it, but Microsoft .NET application development has significantly evolved in the past year and a half. This is not your dad’s .NET. The following releases have added many considerable advances in .NET application development technologies:
Microsoft Product |
Release Date |
Major Features |
November 11th, 2006 |
||
September 5th, 2007 |
XAML rendering for web applications, JavaScript API |
|
November 19th, 2007 |
||
November 19th, 2007 |
IDE for all things .NET |
|
Beta 1 on March 5, 2008, RTM late summer 2008 |
Managed CLR, C#, .NET Framework, greater parity with WPF XAML |
|
v1 May 2007, v2 beta March 2008 |
XAML user experience (UX) IDE |
If you fancy yourself an adept .NET developer, you had better be ramped up on these new technologies.
Today, if you are considering user interface/experience development, you should consider leveraging the rich UX elements and platform independence offered by Silverlight, and the power of authoring with the Expression Studio. If you are designing some middleware, you should consider developing software services using Windows Communication Foundation (WCF) technology. For coding backend data access it is now wise to evaluate LINQ to Entities or LINQ to SQL techniques. And before you write any C# foreach loop on an array or other collection, you might be able to write it better using LINQ to Objects techniques instead.
The bottom line here is that .NET developers have many new tools available to leverage for developing just about any application. Using these new techniques should eventually accelerate the delivery of applications that look better and are better-built. I say “eventually” because these new tricks will take some ramp up time to learn and master. And first, the developer needs to be aware of them and know how to judiciously leverage them.
However, we are talking about a pretty big load of new stuff to digest here. So let us discuss just one example of one these new tricks: LINQ to SQL. The primary purpose of rest of this article is to illustrate one simple example application of LINQ to SQL.
Simple LINQ to SQL
A couple of months ago, I was tasked with developing an internal application that would periodically transfer some data from a SQL database into a monitoring system. The SQL database was a Hewlett Packard Systems Insight Manager installation. The monitoring system is a Microsoft Systems Center Operations Manager (SCOM) 2007 installation, accessed via the System Center Operations Manager 2007 SDK, and the Microsoft.EnterpriseManagement Namespace.
LINQ to SQL is well-suited for this situation as it helps avoid changing either the source or destination installations, and with a minimal “footprint” of moving parts. With a single stand-alone console application, I was able to get this job done, and with a not-too-kludgey implementation. I developed a single, modest-length class file to do the whole thing, with no hard-coded transact SQL code!
I won’t drag you through my entire solution. I pared down the code to illustrate just the LINQ to SQL mechanics, as run against that Hewlett Packard Systems Insight Manager installation:
using System;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Data.Linq;
namespace LinqLite
{
class Program
{
static void Main(string[] args)
{
DataContext db = new DataContext("YOUR HPSIM SQL SERVER CONNECTION STRING GOES HERE");
Table<CIM_Chassis> TableCIM_Chassis = db.GetTable<CIM_Chassis>();
Table<Devices> TableDevices = db.GetTable<Devices>();
Table<Notices> TableNotices = db.GetTable<Notices>();
var LinqQuery = from c in TableCIM_Chassis
join d in TableDevices on c.NodeID equals d.DeviceKey
join n in TableNotices on d.DeviceKey equals n.DeviceKey
where n.NoticeId > 35000 //TODO: make this a LastIdProcessed variable
orderby n.NoticeId
select new { n.NoticeId, n.NoticeSeverity, n.Generated, n.Comments, d.DeviceKey, d.ProductName, d.Name };
foreach (var qdata in LinqQuery)
Console.WriteLine(string.Format("{0}\t{1}\t{2}\t{3}\t{4}\t{5}\t{6}"
, qdata.NoticeId
, qdata.NoticeSeverity
, qdata.Generated
, qdata.Comments
, qdata.DeviceKey
, qdata.ProductName
, qdata.Name
));
Console.ReadLine();
}
}
[Table]
class CIM_Chassis
{
[Column] public long NodeID = 0;
}
[Table]
class Devices
{
[Column] public int DeviceKey = 0;
[Column] public string ProductName = string.Empty;
[Column] public string Name = string.Empty;
}
[Table]
class Notices
{
[Column] public int NoticeId = 0;
[Column] public int NoticeSeverity = 0;
[Column] public long Generated = 0;
[Column] public string Comments = string.Empty;
[Column] public int DeviceKey = 0;
}
}
I call this solution simple LINQ to SQL primarily because it is all done in one code file, and does not involve using the fancy Visual Studio 2008 Object Relational Designer (O/R Designer). When you use the O/R Designer, you get a handy GUI for assembling some classes that map to the SQL data. You can easily end up with several auto-generated class files with many code stubs, just in case you might need them. You use these classes in your LINQ queries to access the data. But if you are doing simple read-only selections, you can hand-code the classes, make them lean, and place them in-line in the code, like I did:
[Table]
class CIM_Chassis
{
[Column] public long NodeID = 0;
}
[Table]
class Devices
{
[Column] public int DeviceKey = 0;
[Column] public string ProductName = string.Empty;
[Column] public string Name = string.Empty;
}
[Table]
class Notices
{
[Column] public int NoticeId = 0;
[Column] public int NoticeSeverity = 0;
[Column] public long Generated = 0;
[Column] public string Comments = string.Empty;
[Column] public int DeviceKey = 0;
}
These class definitions only list the fields that I am interested in, and not the entire tables. Another simple part of my example is the brief “using” list:
using System;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Data.Linq;
Hopefully my core LINQ selection code is not too hard to decipher – seven fields selected from two joined tables, with a third table joined in to filter the data to only notices for “chassis” devices. First, a DataContext is created to set up a connection the SQL Server database. Next, references are made to the table classes using DataContext.GetTable(TEntity) Generic Method:
Table<CIM_Chassis> TableCIM_Chassis = db.GetTable<CIM_Chassis>();
Table<Devices> TableDevices = db.GetTable<Devices>();
Table<Notices> TableNotices = db.GetTable<Notices>();
Then the LINQ query is defined, complete with joins, a where clause, and orderby:
var LinqQuery = from c in TableCIM_Chassis
join d in TableDevices on c.NodeID equals d.DeviceKey
join n in TableNotices on d.DeviceKey equals n.DeviceKey
where n.NoticeId > 35000 //TODO: make this a LastIdProcessed variable
orderby n.NoticeId
select new { n.NoticeId, n.NoticeSeverity, n.Generated, n.Comments, d.DeviceKey, d.ProductName, d.Name };
I put the “TODO” comment on the where clause to indicate that a LastIdProcessed variable could be persisted and recalled to use for filtering the notices data. Thus, you might fetch only new notices that have occurred after the last time this process was executed.
Finally, a foreach construct is employed to “harvest” and display the selected data:
foreach (var qdata in LinqQuery)
Console.WriteLine(string.Format("{0}\t{1}\t{2}\t{3}\t{4}\t{5}\t{6}"
, qdata.NoticeId
, qdata.NoticeSeverity
, qdata.Generated
, qdata.Comments
, qdata.DeviceKey
, qdata.ProductName
, qdata.Name
));
The Console.ReadLine() at the very end merely serves as a way to pause program execution, so the displayed data can be viewed before the console window disappears.
MSDN’s “LINQ to SQL: .NET Language-Integrated Query for Relational Data” article does well to explain more about how this code works.
Conclusion
I hope this article has helped demystify LINQ for you, and has maybe inspired you to consider using this powerful .NET feature. I have a lot yet to learn about LINQ and the rest of those modern .NET technologies previously listed in that first table. But, I feel it is ripe time to dig into these things to advance the craft.
Comments
Anonymous
January 01, 2003
Introduction – Modern .NET Development It took me a while to realize it, but Microsoft .NET applicationAnonymous
May 21, 2008
Good blog. But I'd have to say linq syntax is not intuitive to me and nothing in msdn has helped. I think the problem is you can't do much just from seeing a few examples. You also need to understand the full syntax as well.Anonymous
May 21, 2008
Hi, Mark. Thanks for the comment. I totally agree with you -- LINQ is not very intuitive to apply. And the examples don't help that much, because it is a deep subject about a sophisticated, powerful .NET feature. First you have the many different varieties of LINQ: SQL, DataSet, XML, Objects, Entities. Each one of these seems to have its own nuances and is applicable in different scenarios. You can read about these but actually experimenting seems to be key. The first time I tried to apply LINQ, I did not have much time to explore it, and ended up not using it. In my second run at it, I had more time. But, it took me quite a while to distill what I needed from numerous examples and make enough sense out of it to get the job done. An important factor for me was that I was only reading data, and not writing it. I hear that if you need to read and write, it makes sense to employ the Visual Studio 2008 Object Relational Designer and use LINQ to Entities. The entities end up being logical representations of business objects in your data store. MSDN’s “LINQ to SQL: .NET Language-Integrated Query for Relational Data” article could help you. Also, www.linqpad.net is a useful tool for exploring the syntax and the examples are pretty good in there. Good luck, Eric