Linq to Sql and Entity Framework cache

Linq to Sql has a caching system that rely on the primary key of each object. Inside the data context, an identity map keep the value of the retrieved data. Every thing is handled by the data context and you have to worry about nothing. Keep in mind that if someone else change the value that the cache won’t be refreshed.

If you change the data on your side, the cache won’t also be updated. This can be problematic in some situation. If this behavior is not what you want, you can disable the caching feature of Linq to Sql with a property of the data context.

myDataContext.ObjectTrackingEnabled = false;

Or, you can refresh specific object.

myDataContext.Refresh(RefreshMode.OverwriteCurrentValues, myObjectToRefresh);

The reason behind this technical choice is that initially it has been developed for performance on consecutive call of the same object. The goal is not the have a real caching system.

To conclude, the title mentions Entity Framework and I haven’t say anything about it yet. The reason is that Entity Framework react the same way as Linq to Sql for the cache.

Linq To Sql .dbml file

To create a new Linq To Sql classes to communicate with your database you need to add a new item of Linq To Sql classes types in Visual Studio.

Create new Linq to Sql classes

Once created, the next step is to drag and drop table into the Visual Designer. This will generate Linq To Sql data class. You can also drag and drop stored procedure to be used.

When done, you need to save and you can after that instantiate the Linq to Sql’s data context.

 
var dataContext = new MapperDataContext();
dataContext.UpdateRegion(1, "Estern");

The process of creating a Linq to Sql data context will create a .dbml file. This file contains all tables dropped into the visual designer.

This file is a XML file that contain all definitions of the table and the stored procedure you added into the Visual Designer.

Here is a snippet of the .dbml of Northwind database with a custom stored procedure UpdateRegion.

   <Table Name="dbo.Region" Member="Regions">
    <Type Name="Region">
      <Column Name="RegionID" Type="System.Int32" DbType="Int NOT NULL" IsPrimaryKey="true" CanBeNull="false" />
      <Column Name="RegionDescription" Type="System.String" DbType="NChar(50) NOT NULL" CanBeNull="false" />
      <Association Name="Region_Territory" Member="Territories" ThisKey="RegionID" OtherKey="RegionID" Type="Territory" />
    </Type>
  </Table>
 ...
  <Function Name="dbo.UpdateRegion" Method="UpdateRegion">
    <Parameter Name="id" Type="System.Int32" DbType="Int" />
    <Parameter Name="txt" Type="System.String" DbType="VarChar(50)" />
    <Return Type="System.Int32" />
  </Function>

But that’s not all, the .dbml come with a .dbml.layout file. This file is also a XML but a little bit more complicate to understand. Here is a short section of the file.

 <classShape Id="40fcd11c-862f-4d4c-9d0f-b7b810cf15f4" absoluteBounds="6.75, 7.5, 2, 1.1939925130208344">
      <DataClassMoniker Name="/MapperDataContext/Region" />
      <nestedChildShapes>
        <elementListCompartment Id="59d75784-613d-41c6-b53c-10f870c17964" absoluteBounds="6.765, 7.96, 1.9700000000000002, 0.63399251302083326" name="DataPropertiesCompartment" titleTextColor="Black" itemTextColor="Black" />
      </nestedChildShapes>
    </classShape>

This esoteric is in fact just the information of the visual representation of all data classes of the visual designer. You should never have to look it.

The last file that you have under the .dbml is *.dbml.designer.cs that contain the class that you will create an instance from. This file inherit of System.Data.Linq.DataContext and contains all methods generated to correspond to the classed generated from the tables dropped into the Visual Designer. This file contains multiple classes. You will have the DataContext class but also a single class for each of the tables. Here is an example of the code generated for the small table “Regions” of Northwind database.

[global::System.Data.Linq.Mapping.TableAttribute(Name="dbo.Region")]
	public partial class Region : INotifyPropertyChanging, INotifyPropertyChanged
	{
		
		private static PropertyChangingEventArgs emptyChangingEventArgs = new PropertyChangingEventArgs(String.Empty);
		
		private int _RegionID;
		
		private string _RegionDescription;
		
		private EntitySet<Territory> _Territories;
		
    #region Extensibility Method Definitions
    partial void OnLoaded();
    partial void OnValidate(System.Data.Linq.ChangeAction action);
    partial void OnCreated();
    partial void OnRegionIDChanging(int value);
    partial void OnRegionIDChanged();
    partial void OnRegionDescriptionChanging(string value);
    partial void OnRegionDescriptionChanged();
    #endregion
		
		public Region()
		{
			this._Territories = new EntitySet<Territory>(new Action<Territory>(this.attach_Territories), new Action<Territory>(this.detach_Territories));
			OnCreated();
		}
		
		[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_RegionID", DbType="Int NOT NULL", IsPrimaryKey=true)]
		public int RegionID
		{
			get
			{
				return this._RegionID;
			}
			set
			{
				if ((this._RegionID != value))
				{
					this.OnRegionIDChanging(value);
					this.SendPropertyChanging();
					this._RegionID = value;
					this.SendPropertyChanged("RegionID");
					this.OnRegionIDChanged();
				}
			}
		}
		
		[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_RegionDescription", DbType="NChar(50) NOT NULL", CanBeNull=false)]
		public string RegionDescription
		{
			get
			{
				return this._RegionDescription;
			}
			set
			{
				if ((this._RegionDescription != value))
				{
					this.OnRegionDescriptionChanging(value);
					this.SendPropertyChanging();
					this._RegionDescription = value;
					this.SendPropertyChanged("RegionDescription");
					this.OnRegionDescriptionChanged();
				}
			}
		}
		
		[global::System.Data.Linq.Mapping.AssociationAttribute(Name="Region_Territory", Storage="_Territories", ThisKey="RegionID", OtherKey="RegionID")]
		public EntitySet<Territory> Territories
		{
			get
			{
				return this._Territories;
			}
			set
			{
				this._Territories.Assign(value);
			}
		}
		
		public event PropertyChangingEventHandler PropertyChanging;
		
		public event PropertyChangedEventHandler PropertyChanged;
		
		protected virtual void SendPropertyChanging()
		{
			if ((this.PropertyChanging != null))
			{
				this.PropertyChanging(this, emptyChangingEventArgs);
			}
		}
		
		protected virtual void SendPropertyChanged(String propertyName)
		{
			if ((this.PropertyChanged != null))
			{
				this.PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
			}
		}
		
		private void attach_Territories(Territory entity)
		{
			this.SendPropertyChanging();
			entity.Region = this;
		}
		
		private void detach_Territories(Territory entity)
		{
			this.SendPropertyChanging();
			entity.Region = null;
		}
	}

The most interesting part is to notice the inheritance of two interfaces. The INotifyPropertyChanging and INotifyPropertyChanged interface that notify when a property is changing.

To conclude, Linq to Sql dbml file is an auto-generated file that you should never really need to look the code. The Visual Designer is powerful enough to handle all modifications possible with Linq to Sql.

How to log with Linq to Sql?

It’s possible to log all SQL Query generated by Linq to Sql with C# code. This can be useful when debugging or to optimize how the Linq is done.

DataClasses1DataContext db = new DataClasses1DataContext();
StringWriter sw = new StringWriter();
db.Log = sw;

this.gridLinqToSql.DataSource = db.Customers.Where(c => c.CustomerID.StartsWith("A"));
this.gridLinqToSql.DataBind(); //Here is when the Linq query will be executed.

sqlLinqToSql.Text = sw.GetStringBuilder().ToString();

Linq to Sql use a DataContext object that is generated with the .dbml file. For example, in this test project, the .dbml file name is DataClasses1.dbml. From there, this object contain a Log property that takes a TextWriter. The TextWriter class come from the System.IO namespace and it’s an abstract class. The namespace contain two concrete classes that inherit from this class. The first one is StringWriter that has been used in the example above. This class has a StringBuilder and when using the property GetStringBuilder() of the StringWriter let you get a reference on it. From there, using the ToString() method give a String with all the SQL used to get the information.

The result will look like this:

SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle]
FROM [dbo].[Customers] AS [t0]
WHERE [t0].[CustomerID] LIKE @p0

-- @p0: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [A%]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 4.0.30319.1

The Log return the SQL statement (or many SQL statement if many calls has been done) with a parameter way (@p…). After the SQL Statement, each parameter details are written like the type, and the size of the SQL parameter.

To conclude, using the Log property is interesting to get the SQL without using a SQL Profiler. Of course, a good practice would be to not log all the queries in production environment.