All posts in Linq

I’m starting a new line of blog posts in which I’m going to give out a few tips and tricks I’ve picked up during the past years. I’m going to start with one of the most common mistakes I often face when reading code.

CaptureTo demonstrate that, I’m going to use LinqToSQL as my data access method (although the problem can be found in any kind of Data access technology) and use the the same model I used in my earlier Caching series.

As you can see the model is very simple and contains just three entities, a Peson, its Phones and its Email addresses.

Next I’m going to create a web form in which I want to display a list of Persons along with their email address. To do that I’m going to add a Repeater control which I’m going to Bind to the Person retrieved from the store.

<asp:Repeater ID="personGrid" runat="server" OnItemDataBound="personGrid_ItemDataBound">   <ItemTemplate>     <asp:Label ID="lblName" runat="server" Text='<%# string.Format("{0} {1}", Eval("FirstName"), Eval("LastName")) %>'></asp:Label>  |      <asp:Label ID="lblEmail" runat="server" Text='Email' Font-Bold="True" Font-Italic="True"></asp:Label>   </ItemTemplate>   <SeparatorTemplate><br /></SeparatorTemplate>
</asp:Repeater>

Since the Email Address is not in the same entity as the Person I’m going to take advantage of the repeater’s OnItemDataBound event to fetch the Email address of these persons. So typically would find something like that in the code behind.

protected void Page_Load(object sender, EventArgs e)
{
	if (!Page.IsPostBack)
	{
		DataBind();
	}
}

public override void DataBind()
{
	base.DataBind();

	using (AdventureworksDataContext context = new AdventureworksDataContext())
	{
		var query = from p in context.Persons
		select p;

		personGrid.DataSource = query.Take(10);
		personGrid.DataBind();
	}
}

protected void personGrid_ItemDataBound(object sender, RepeaterItemEventArgs e)
{
	Label lblEmail = e.Item.FindControl("lblEmail") as Label;
	Person currentPerson = e.Item.DataItem as Person;

	if (lblEmail != null && currentPerson != null)
	{
		using (AdventureworksDataContext context = new AdventureworksDataContext())
		{
			var personEmail = context.EmailAddresses.Where(ea => ea.BusinessEntityID == currentPerson.BusinessEntityID).FirstOrDefault();
			lblEmail.Text = (personEmail != null) ? personEmail.EmailAddress1 : "";
		}
	}
}

This will work just fine but what some developers don’t realize is that that this will make as many queries to the database as the records on the Persons Table, cause it’s time a Person record is Data bound I’m querying for its Email Address on the ItemDataBound Event Handler.

What we could do instead, is take advantage of all the cool new features in .Net 3.5 and Linq, like data projection, data shaping and anonymous types to prepare a read-only view to bind to the repeater. So a more optimized and much cleaner version of this code would look something like that:

protected void Page_Load(object sender, EventArgs e)
{
	if (!Page.IsPostBack)
	{
		DataBind();
	}
}

public override void DataBind()
{
	base.DataBind();

	using (AdventureworksDataContext context = new AdventureworksDataContext())
	{
		var query = from p in context.Persons
			    select new { 
				FirstName = p.FirstName, 
				LastName = p.LastName, 
				Email = p.EmailAddresses.Select(e => e.EmailAddress1).FirstOrDefault() };

		personGrid.DataSource = query.Take(10);
		personGrid.DataBind();
	}
}

//protected void personGrid_ItemDataBound(object sender, RepeaterItemEventArgs e)
//{
//  Label lblEmail = e.Item.FindControl("lblEmail") as Label;
//  Person currentPerson = e.Item.DataItem as Person;

//  if (lblEmail != null && currentPerson != null)
//  {
//    using (AdventureworksDataContext context = new AdventureworksDataContext())
//    {
//      var personEmail = context.EmailAddresses.Where(ea => ea.BusinessEntityID == currentPerson.BusinessEntityID).FirstOrDefault();
//      lblEmail.Text = (personEmail != null) ? personEmail.EmailAddress1 : "";
//    }
//  }
//}

This way I can also remove the OnItemDataBound event handler completely (or use it just for visual stuff that’s my preference) from the repeater and bind the Email Label to the new Field of the anonymous type that I’ve just created. So the page code is going to look something like that:

<asp:Repeater ID="personGrid" runat="server" OnItemDataBound="personGrid_ItemDataBound">   <ItemTemplate>     <asp:Label ID="lblName" runat="server" Text='<%# string.Format("{0} {1}", Eval("FirstName"), Eval("LastName")) %>'></asp:Label>  |      <asp:Label ID="lblEmail" runat="server" Text='<%# Eval("Email") %>' Font-Bold="True" Font-Italic="True"></asp:Label>   </ItemTemplate>   <SeparatorTemplate><br /></SeparatorTemplate>
</asp:Repeater>

Although this kind of behavior is pretty obvious and I’m pretty sure that most of you are already aware of it, there are times when this behavior is disguised and rather difficult to spot. For example the following code has exactly the same problem since the GetEmailAddress method is going to be called as many times as the person records (although there is no ItemDataBOund handler).

<asp:Repeater ID="personGrid" runat="server" OnItemDataBound="personGrid_ItemDataBound">   <ItemTemplate>     <asp:Label ID="lblName" runat="server" Text='<%# string.Format("{0} {1}", Eval("FirstName"), Eval("LastName")) %>'></asp:Label>  |      <asp:Label ID="lblEmail" runat="server" Text='<%# GetEmailAddress((int)Eval("ID")) %>' Font-Bold="True" Font-Italic="True"></asp:Label>   </ItemTemplate>   <SeparatorTemplate><br /></SeparatorTemplate>
</asp:Repeater>

And of course this behavior doesn’t only apply to repeaters but in every list control (drop down list, GridView, Checkbox list etc) that is going to be bound to fields belonging to more than a single entity.


Bart de Smet has released a very usefully query expression translation cheat sheet this is an excellent resource if you’re into Linq and want to be able to write a Linq expression in code.

image


One of the things that troubled me for quite a long time now was the fact that my RSS feeds were out of sync between my home and work PCs. That’s because I kept forgetting to add the feeds I was subscribing from one PC to the other. And of course I didn’t want to delete all feeds from either one of them cause that could result in loosing some.

I had to build a small utility that, using XML DOM and XPath, would read my aggregators’ OPML files and synchronize them. The problem was that I didn’t have time for such a program.

With Linq to XML though things got a lot easier, so much easier that I can now write a single line of code that will find out which feed subscriptions are not contained from an OPML file to another like so:

var feedQuery = 
    from f in XElement.Load("subset.opml").Descendants("outline")
    where f.Attribute("xmlUrl") != null &&
          (from XElement o in XElement.Load("Full.opml").Descendants("outline")
           where o.Attribute("xmlUrl") != null
           select (string)o.Attribute("xmlUrl")).ToList().Contains(((string)f.Attribute("xmlUrl")))
    select f;

So I’m finally going to have all my feeds synced ….


IMGP0002 If you had only had tine for just one Session in this year’s TechEd then I would definitely recommend Mike Taulty’s one on Linq to Entities (is going to be repeated on Friday). If you didn’t make it this year then I would suggest watching its replay at The virtual side as soon as it comes available.

It’s not just the subject (which is interesting as it is), it’s also the fact that he’s a great speaker, one that speaks with a lot of examples and demos and avoids lengthy power point presentations. I was lucky to see him on two sessions today, Linq to XML and Linq to Entities, although the second one was packed full and had to wait till some people left to get in.

IMGP0003 I also had the chance to attend Pablo Castro’s project Astoria presentation which was also great. His enthusiastic presentation on project Astoria renewed my interest on the project and plan to have another look once back home.

 

Having lunch today at the top floor of the exhibition center revealed the great view the exhibition center has to the ocean and got me thinking how great it would be if we could get the chance of hosting TechEd at Greece. After all the 2004 Olympic games left us with an infrastructure that can be utilized for such events (like the Taekwon-Do center which is planned to be converted into a convention center) and we certainly could use all the Tourism and advertisement this event would bring to Greece.

IMGP0006 Stitch

Today is also the Greek delegates party, we’re going to eat and have drinks at Red Lounge somewhere in Barcelonita. Let’s hope that food there’s going to be better from what I’ve eaten lately ;-). Barcelona’s cuisine constitutes mainly of fish which is not my greatest food.


It’s only been a couple of weeks since Linq’s final release with Visual Studio 2008 beta 2 and great tools have already started coming to life.

A great one I discovered from SoCalDevGal is the LinqPad – a Linq query expression tool which allows you to run queries against databases using Linq.

If you’re exploring Linq this is a great tool.


I’ve been following ADO.NET Entity Framework for over a year now and the truth is I was bit surprised when DLinq was released. From the initial look at things – it seemed as though the ADO.NET EF folks and the LINQ to SQL folks were operating in completely isolated environments – each producing their own copy of a wheel but with very different attributes, benefits, and even target audiences.

From what I’ve been reading lately though I came to understand that that isn’t the case. The ADO.NET Entity Framework has a different scope than LINQ to SQL. The ADOEF is responsible for three things:

  • mapping a database schema to a model,
  • creating a model
  • allowing eSQL queries against that model.

Combined with Linq, which allows you to run LINQ queries against in-memory structures, you are free to issue LINQ queries against the generated ADOEF classes.

Furthermore with ADOEF you also have features like

  • Entity inheritance – DLinq derived classes have a 1:1 table mapping structure. You get one class per table. EF on the other hand lets you create entities like Customer and EliteCustomer and SuperEliteCustomer that allow you to maintain progressively more information in the object model while still using the same normalized back-end relational store.
  • Entity composition – You can have a single EF entity that is composed of data retrieved from multiple locations within the relational store. For example, you can have a single object that has properties that originated in 3 different tables.
  • Composed Entity Updates Using EF, you can actually insert or update a single entity that originated from 3 different tables. If you modify three properties that originated from 3 different tables, then when the EF does an update for that data, it will issue 3 different queries. This is impossible to do in DLinq alone. The best you can do is manually create a transaction, manually add rows to 3 tables, and then call update. In short, EF preserves the object-oriented view of the data whereas the DLinq object-oriented view does half of that.
  • Change schema – if you make a change to the schema in the underlying database in simple DLinq, you need to re-generate the classes, potentially screwing up any customizations you made to those classes. With EF, all you need to do is change the mapping specification and you can leave the Entity Data Model (EDM) completely untouched.

From the looks of it ADOEF combined with Linq promises to be the foundation of an extremely powerful persistence and business object framework.