Blog ala Vidar

SQL, AppFrame and other cool technologies


LINQ stands for Language INtegrated Query and is a part of .NET Framework 3.5. You can use LINQ to any object you want as long as it implements the IQueryable interface, including XML and SQL which makes it very interesting. When I first heard about LINQ I thought that it sounded cool, but I was worries about performance. I still am worried, but it’s even cooler than I imagined! To test it out I started creating TeamDoc in WPF, using LINQ. I connected to one of our SQL servers, drag and dropped a couple of tables and sim salabim my LINQ to SQL was ready to be used. I then started creating a user control for “recent updates”. The SQL query behind this looks something like this:

		FROM stbl_TeamDoc_Websites WITH (NOLOCK)
		WHERE TeamDocRef = Table1.PrimKey) AS Url
		  C.PrimKey, C.Title, C.Status, C.Updated,
		  CASE WHEN C.Updated > ISNULL(US.LastRead,'1999-01-01')
			AND C.HideUntil  ISNULL(US.LastRead,'1999-01-01')
			     AND C.HideUntil <= GETUTCDATE()
			   THEN 1 ELSE 0 END DESC, C.Updated DESC) AS Table1

This, in LINQ looks something like this:

Dim vUpdatedDocuments = _
	(From d In vTeamDoc.Documents _
	Join us In vTeamDoc.UserSettings _
	On d.PrimKey Equals us.TeamDocRef _
	Where us.Login = Globals.Username _
	  AndAlso us.Notify = "Inbox" _
	  AndAlso d.Status = DocumentTypes.ToString() _
	  AndAlso d.Deleted Is Nothing _
	  AndAlso (From p In vTeamDoc.Permissions _
		   Join gm In vTeamDoc.GroupsMembers _
		   On p.GroupRef Equals gm.GroupRef
		   Where gm.Login = Globals.Username
		   Select p.TeamDocRef).Contains(d.PrimKey)
	Order By If(d.Updated > _
			If(us.LastRead.HasValue, us.LastRead, d.Updated.AddDays(-1)), 1, 0) Descending, _
	  d.Updated Descending _
	Take 30 _
	Distinct _
	Select New UpdatedDocument With _
	  {.Title = d.Title, .Updated = d.Updated, _
	   .LastRead = us.LastRead, .PrimKey = d.PrimKey})

I’m terrible sorry about the indenting and newlines, but I had to cut it to fit in here. Anyways, if you take a look at the LINQ query you’ll probably understand most of it. The only big difference is that I’m doing a Select New UpdatedDocument (on line 20). What this is doing is creating one UpdatedDocument (a class I’ve created) for each row returned by this query, and sets the properties .Title, .Updated etc. So, how do I loop this? VERY easy!

For Each vDocument As UpdatedDocument In vUpdatedDocuments
	Dim vTextBlox As New TextBlock With { _
		.Height = 15, _
		.VerticalAlignment = Windows.VerticalAlignment.Top, _
		.Text = vDocument.Title, _
		.Margin = New Thickness(0, vFromTop, 0, 0), _
		.Tag = vDocument _

	If vDocument.Unread Then
		vTextBlox.FontWeight = Windows.FontWeights.Bold
	End If


Here I’m creating a new textblock (some kind of mix between textbox and label in WPF), where I’m setting the .Tag property to my UpdatedDocument, so that I can use this object when for instance clicking on the textblock. And, if the document is unread, I’m setting it to Bold and then adding it to my RecentUpdates user-control.

I’ve struggled with LINQ a couple of days now, and I’m starting to get a hold of it. It’s still weird since I’m used to T-SQL, but I like the possibilities it gives me when developing. One thing I wasn’t too impressed by was the query it generated on the SQL Server though. I used profiler to check it out. It’s actually so ugly that I don’t dare to put it here 🙂 So, if you want to see it, test it yourself! Btw, to get started, Microsoft’s “LINQ To SQL Samples” is a good place to start.

Comments are closed.

%d bloggers like this: