All posts in Linq to SQL

I briefly got the chance to talk about MetaCMS, the content management system and e-shop platform I’ve designed and built, a while back. Since then a lot of sites have been built using it. Some of which (the biggest ones) include:

 

And as if building these sites wasn’t enough we’ve decided to completely re-write the platform and built MetaCMS V2. The fact that it had been “tied” to LinqToSql was bothering me all along but due to lack of time didn’t have the time to do something about it. But eventually and although I had put it on “steroids”, the Context has been disposed caching bugs I’ve talked about previously, drove us to completely re-write that part.

login

So what’s new in this new version, well basically we’ve done two things

  • First we’ve build View Models of serializable POCO (Plain Old CLR Objects) objects, taking advantage of the T4 Visual Studio templating engine and the Linq models we already had. In addition these View Model objects were designed to have lambda expressions that will lazy load related entities once those are requested.
  • Secondly I’ve designed a simple IoC system that instantiates Repositories that are responsible to return data and “Hydrate” the view model objects.

Repository

Making these changes we were able to solve the caching problems and the dreadful Context has been disposed exception we were getting when caching Linq objects and at the same time build a more robust, scalable, solution one that can target multiple client technologies (Sliverlight, WPF, etc) and use different Data access methods.

But that’s not all, except from the bug fixes a lot of new features and modules have been added as well. Some of which are:

  • Extensibility support
    Hook into various MetaCMS actions and build your own custom business logic.
  • Messaging platform
    Capable of sending templatized newsletters and Emails.
  • Import / Export
    Import and Export engine that allows communication with arbitrary data sources.
  • Stock Market platform
    Utilizes a provider model to allow communication with any Stock Market data provider (Rueters, XAA etc.)
  • Competition Module
    Supports multiple and recurring competitions with custom winner picking rule engine.
  • Elections
    Build to support Elections with maximum detail and data coming from any provider.

And last but not least the e-commerce part of MetaCMS that has been upgraded to an Enterprise Level solution with all the features even the most demanding shop would need. Speaking of which there is a large e-shop project coming up soon that unfortunately though I still can’t disclose.

Time to stop mumbling… if you have more questions or need a demo get in touch and I’ll arrange it.


There are quite a few developers that believe that Linq to SQL is dead in favor of EF. That’s not the case though and I have the facts to prove it. Here’s the list of changes that are coming with the next .net framework, .Net 4.0.

Performance
  • Query plans are reused more often by specifically defining text parameter lengths (when connecting to SQL 2005 or later)
  • Identity cache lookups for primary key with single result now includes query.Where(predicate).Single/SingleOrDefault/First/FirstOrDefault
  • Reduced query execution overhead when DataLoadOptions specified (cache lookup considers DataLoadOptions value equivalency)
Usability
  • ITable<T> interface for additional mocking possibilities
  • Contains with enums automatically casts to int or string depending on column type
  • Associations can now specify non-primary-key columns on the other end of the association for updates
  • Support list initialization syntax for queries
  • LinqDataSource now supports inherited entities
  • LinqDataSource support for ASP.NET query extenders added
Query stability
  • Contains now detects self-referencing IQueryable and doesn’t cause a stack overflow
  • Skip(0) no longer prevents eager loading
  • GetCommand operates within SQL Compact transactions
  • Exposing Link<T> on a property/field is detected and reported correctly
  • Compiled queries now correctly detect a change in mapping source and throw
  • String.StartsWith, EndsWith and Contains now correctly handles ~ in the search string (regular & compiled queries)
  • Now detects multiple active result sets (MARS) better
  • Associations are properly created between entities when using eager loading with Table-Valued Functions (TVFs)
  • Queries that contain sub-queries with scalar projections now work better
Update stability
  • SubmitChanges no longer silently consumes transaction rollback exceptions
  • SubmitChanges deals with timestamps in a change conflict scenario properly
  • IsDbGenerated now honors renamed properties that don’t match underlying column name
  • Server-generated columns and SQL replication/triggers now work instead of throwing SQL exception
  • Improved binding support with the MVC model binder
General stability
  • Binary types equate correctly after deserialization
  • EntitySet.ListChanged fired when adding items to an unloaded entity set
  • Dispose our connections upon context disposal (ones passed in are untouched)
Database  control
  • DeleteDatabase no longer fails with case-sensitive database servers
SQL Metal
  • Foreign key property setter now checks all affected associations not just the first
  • Improved error handling when primary key type not supported
  • Now skips stored procedures containing table-valued parameters instead of aborting process
  • Can now be used against connections that use AttachDbFilename syntax
  • No longer crashes when unexpected data types are encountered
LINQ to SQL class designer
  • Now handles a single anonymously named column in SQL result set
  • Improved error message for associations to nullable unique columns
  • No longer fails when using clauses are added to the partial user class
  • VarChar(1) now correctly maps to string and not char
  • Decimal precision and scale are now emitted correctly in the DbType attributes for stored procedures & computed columns
  • Foreign key changes will be picked up when bringing tables back into the designer without a restart
  • Can edit the return value type of unidentified stored procedure types
  • Stored procedure generated classes do not localize the word “Result” in the class name
  • Opening a DBML file no longer causes it to be checked out of source control
  • Changing a FK for a table and re-dragging it to the designer surface will show new FK’s
Code generation (SQL Metal + LINQ to SQL class designer)
  • Stored procedures using original values now compiles when the entity and context namespaces differ
  • Virtual internal now generates correct syntax
  • Mapping attributes are now fully qualified to prevent conflicts with user types
  • KnownTypeAttributes are now emitted for DataContractSerializer with inheritance
  • Delay-loaded foreign keys now have the correct, compilable, code generated
  • Using stored procedures with concurrency no longer gets confused if entities in different namespace to context
  • ForeignKeyReferenceAlreadyHasValueException is now thrown if any association is loaded not just the first
Potentially breaking changes

We worked very hard to avoid breaking changes but of course any potential bug fix is a breaking change if your application was depending on the wrong behavior. The ones I specifically want to call out are:

Skip(0) is no longer a no-op

The special-casing of 0 for Skip to be a no-op was causing some subtle issues such as eager loading to fail and we took the decision to stop special casing this. This means if you had syntax that was invalid for a Skip greater than 0 it will now also be invalid for skip with a 0. This makes more sense and means your app would break on the first page now instead of subtlety breaking on the second page. Fail fast 🙂

ForeignKeyReferenceAlreadyHasValue exception

If you are getting this exception where you weren’t previously it means you have an underlying foreign key with multiple associations based on it and you are trying to change the underlying foreign key even though we have associations loaded.Best thing to do here is to set the associations themselves and if you can’t do that make sure they aren’t loaded when you want to set the foreign key to avoid inconsistencies.


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.


Before picking up where I left off yesterday, I have to first make a small disclaimer. I received a couple of comments stating that the caching solution I presented wasn’t that sophisticated or complete or that the cache keys that I’ve used were not the right and the answer is of course “yes”. The solution I presented was far from perfect but it served well as a simplified demonstration of the problems one might face when caching Linq to SQL entities.

Ok now that we’re done with the typicalities let’s see what was wrong with the solution shown earlier. To demonstrate the problem I’m going to add a single button to my web form and attach an event handler to it. In the event handler I’m going to ask the Person from the Cache and Lazy load his phones.

public partial class _Default : System.Web.UI.Page
{
	protected void Page_Load(object sender, EventArgs e)
	{
		if (!IsPostBack)
		{
			DataBind();
		}
	}

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

		employeesRepeater.DataSource = new List<Person>() { GetPerson("M") };
		employeesRepeater.DataBind();

	}

	protected void btnGetPhone_Click(object sender, EventArgs e)
	{
		Person person = GetPerson("M"); //This person comes from the cache
		var phones = person.PersonPhones.ToList(); //Attempt to lazy load throws exception as Context has been disposed
	}

	protected Person GetPerson(string lastNameStartsWith)
	{
		using (AdventureworksDataContext context = new AdventureworksDataContext())
		{
			return CacheGet<Person>(
					() => context.Persons.Where(p => p.LastName.StartsWith(lastNameStartsWith)).FirstOrDefault(),
					string.Format("Person_LastName_StartsWith:{0}", lastNameStartsWith));
		}
	}

	protected T CacheGet<&lt;T>(Func<T> loader, string cacheKey) where T : class
	{
		var cachedObject = this.Cache[cacheKey] as T;
		if (cachedObject == null)
		{
			cachedObject = loader.Invoke();
		}
		return cachedObject;
	}
}

CaptureIf you ran this code and click the button you’ll end up with the exception
Cannot access a disposed object.
Object name: ‘DataContext accessed after Dispose.’.

That’s the real problem when caching Linq to SQL entities, the fact that they have a dependency (reference) on the data context that created them and as such they can not be cached. There are ways you can work-around this problem by detaching and re-attaching the entities to the currently loaded data context. But since there is no way you can actually detach an entity from the context you can only rely on hacks to do that. A way to detach an entity is serialize it (of course entities must be declared serializable) cache it and then before retrieving it from the cache deserialize it and attach it to the context. Another is to manually detach an entity (set certain properties to null) and another to manually clone it. Each of these methods has advantages and disadvantages, like the fact that you’ll loose all object’s graph if you serialize it or the complexity if you follow the manual detaching path. One thing is for sure though, there is no easy way around it.

What’s my preffered way you ask? I guess the most painful way (;-)) but at the same time the most scalable and robust. Build my own custom POCO entities model, populate it using whatever data access technology and cache those entities.


A caching solution is always necessary if you want to build scalable applications which will handle lots of users and heavy data access without requiring more hardware resources. I’ve already talked about caching when using Entity Framework, but is there a way to use caching in Linq to SQL and what are the problems one might face.

Capture To explore caching in Linq to SQL I’ve built a small web site that had a single page. This page used Linq To SQL to access the AdventureWorks sample Database which you can download from Codeplex.

The very simple Linq To SQL model that I’ve created had three entities, a Peson and its Phones and Email addresses.

 

The site default page contained a repeater that was databound to the first Person that his last name begun with the letter M, fetched by a call to the GetPerson method.

public partial class _Default : System.Web.UI.Page
{
  protected void Page_Load(object sender, EventArgs e)
  {
    if (!IsPostBack)
    {
      DataBind();
    }
  }

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

    employeesRepeater.DataSource = new List<Person>() { GetPerson("M") };
    employeesRepeater.DataBind();

  }

  protected Person GetPerson(string lastNameStartsWith)
  {
    using (AdventureworksDataContext context = new AdventureworksDataContext())
    {
      return context.Persons.Where(p => p.LastName.StartsWith(lastNameStartsWith)).FirstOrDefault();
    }
  }
}

This works, but imagine tens of thousand of users accessing this page at the same time asking for the same thing. This would result in thousand of SQL commands reaching SQL server. And in this case the SQL command is trivial but what would happen if the query was more complex? The answer is that this page would become significantly slower as more users would try to access it.

To overcome this, I’ve introduced a simple Caching mechanism to the page utilizing the System.Web.Caching.Cache Object of the Page and changed the GetPerson method to use this caching mechanism.

protected Person GetPerson(string lastNameStartsWith)
{
  using (AdventureworksDataContext context = new AdventureworksDataContext())
  {
    return CacheGet<Person>(
      () => context.Persons.Where(p => p.LastName.StartsWith(lastNameStartsWith)).FirstOrDefault(),
      string.Format("Person_LastName_StartsWith:{0}", lastNameStartsWith));
  }
}

protected T CacheGet<T>(Func<T> loader, string cacheKey) where T : class
{
  var cachedObject = this.Cache[cacheKey] as T;
  if (cachedObject == null)
  {
    cachedObject = loader.Invoke();
  }
  return cachedObject;
}

	

 

Now only the first call to the GetPerson method results into a query being made to the SQL Server, all resulting calls will be served by the Cache (that is of course if the cache key aka last name character matches) and its going to be equally fast for either one user or million of users. So what’s the problem? Anyone care to guess?

To be continued…


Protected: MSLinqToSQLGenerator fails after extending a model

Categories: Linq to SQL
Enter your password to view comments.

This content is password protected. To view it please enter your password below: