LINQ to SQL – bits and pieces

In a project I’m currently working on we’re using LINQ to SQL. While most of it is straight forward, there are some quirks that are not that obvious (at least to me).

This article is mostly a FAQ but I will explain some of the not-so-obvious features in more detail.

Note: I’m not going to explain how to setup the connection to the database in this article. I’m assuming that this already works.

Preface ∞

Before digging into the details, lets lay some foundation for the remainder of this article.

Abbreviations & Terminology ∞

Throughout the article I’ll use some abbreviations:

L2S: LINQ to SQL
field(s): For brevity, I’ll always only talk about “fields” but will actually mean “fields or properties” (of .NET classes).
relationships: Database relationships (foreign key stuff); in .NET they’re expressed via [Association] attribute

Data Model ∞

The data model is very simple. We simply have companies that have multiple employees.

So, the table definitions look like this (MSSQL syntax):

CREATE TABLE [dbo].[Companies] (
    [Id]   INT            NOT NULL,
    [Name] NVARCHAR (100) NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

CREATE TABLE [dbo].[Employees]
(
    [CompanyId]  INT            NOT NULL,
    [EmployeeId] INT            NOT NULL,
    [Name]       NVARCHAR (100) NOT NULL,
    PRIMARY KEY CLUSTERED ([CompanyId] ASC, [EmployeeId] ASC),
    CONSTRAINT [FK_Employees_Companies] FOREIGN KEY ([CompanyId]) 
                                        REFERENCES [dbo].[Companies] ([Id])
);

The C# pendants look like this (relationships not modeled yet):

[Table(Name = "Companies")]
public class CompanyEntity {
    [Column(Name = "Id", IsPrimaryKey = true)]
    public int CompanyId { get; set; }

    [Column]
    public string Name { get; set; }
}

[Table(Name = "Employees")]
public class EmployeeEntity {
    [Column(IsPrimaryKey = true)]
    public int CompanyId { get; set; }

    [Column(IsPrimaryKey = true)]
    public int EmployeeId { get; set; }

    [Column]
    public string Name { get; set; }
}

The attributes [Table] and [Column] are defined in the System.Data.Linq.Mapping namespace.

Also, let’s insert some example data:

INSERT INTO Companies (Id, Name) VALUES (1, 'MyCompany');
INSERT INTO Companies (Id, Name) VALUES (2, 'MyCompany2');
INSERT INTO Companies (Id, Name) VALUES (3, 'MyCompany3');
INSERT INTO Companies (Id, Name) VALUES (4, 'MyCompany4');

INSERT INTO Employees(CompanyId, EmployeeId, Name) VALUES (1, 1, 'Smith');
INSERT INTO Employees(CompanyId, EmployeeId, Name) VALUES (1, 2, 'Jones');
INSERT INTO Employees(CompanyId, EmployeeId, Name) VALUES (1, 3, 'Miller');
INSERT INTO Employees(CompanyId, EmployeeId, Name) VALUES (1, 4, 'Lee');
INSERT INTO Employees(CompanyId, EmployeeId, Name) VALUES (1, 5, 'Williams');

Loading entities from the database ∞

Furthermore, we have a child class of DataContext that will give us access to the database tables:

public class MyDataContext : DataContext {
    // These "Table" fields will be set by the LINQ to SQL
    // runtime automatically.
    public Table<CompanyEntity> Companies;
    public Table<EmployeeEntity> Employees;

    public MyDataContext() : base("myConnectionString") { }
}

Now, to load some entities from the database, we can do this:

using (var ctx = new MyDataContext()) {
    // via query syntax (see http://msdn.microsoft.com/en-US/library/vstudio/bb397947.aspx )
    IQueryable<CompanyEntity> companies = from company in ctx.Companies
                                          where company.Name == "MyCompany"
                                          select company;

    // or via method syntax
    CompanyEntity oneCompany = ctx.Companies.SingleOrDefault(company => company.Name == "MyCompany");  
}

Let’s start digging into some details.

Column attribute ∞

Regarding columns ([Column], MSDN) in a database table, there are a couple of questions:

Q: Can [Column] be defined on fields (instead of properties)?
A: Yes.
Q: Can the column property/field be private?
A: Yes.
Q: Can properties with [Column] have a backing field?
A: Yes, LINQ to SQL simply uses the property accessors).
Q: What effect does ColumnAttribute.Storage have?
A: LINQ to SQL stores the value in the specified field/property instead of the one with the [Column] attribute. This also means that property accessors for the property with the [Column] attribute are not called.
1. Q: Can a property be used for Storage?
  A: Yes, and the property accessors are also being used by LINQ to SQL.
2. Q: Which visibility can the Storage field/property have? Can it be private? Can it be public?
  A: It must not be public (otherwise you’ll get a “Bad Storage property” exception). It can be anything else (including private, which I’d recommended).
3. Q: Can a storage field be readonly?
  A: To be done
Q: What’s with field initializations? (like int m_myField = 5;)
A: The value will be overwritten (after the class’ constructor has been called).
Q: Can there be columns in the database for which no .NET property/field exist?
A: Yes, it’s possible. There are some limitations though: If the database column is NOT NULL and doesn’t have a default value, you won’t be able to insert new rows into the database table.
Q: Can there be .NET properties/fields for which no column in the database exist?
A: None that I’m aware of.
Q: How does versioning with IsVersion = true work?
A: In theory, L2S should update fields with this attribute automatically on DataContext.SubmitChanges(). Unfortunately, I couldn’t get it to work on my setup – not even with the example code provided by Microsoft.

The Storage property ∞

You can define an alternative storage location for [Column] and [Association].

[Column(Storage = "m_desc")]
public string Description
{
    get { return this.m_desc; }
    set { this.m_desc = value; }
}
private string m_desc; // must NOT be public

In this case the setter of Name won’t be called. Instead, the LINQ to SQL runtime will store the value of the Name column directly into m_name.

Note: The storage field must not be public. Otherwise you’ll get a “Bad Storage property” exception. (Just making the setter private isn’t enough either.)

So, the code above is identical to:

public string Description
{
    get { return this.m_desc; }
    set { this.m_desc = value; }
}

[Column(Name = "Description")]
private string m_desc;

IQueryable ∞

When loading entities from a database with IQueryable (see above), there are a couple of questions that come to mind:

Q: When will the objects returned by the query be created?
A: On first use, not when IQueryable is created.
Q: Will objects be recreated when the same query is executed twice?
A: Internally, instances will be created again (maybe for comparison reasons?) by the LINQ to SQL runtime, but the objects returned by the query will be exactly the same as in the first query (verify via object ids).
Q: Is the parameter-less constructor of the entity class invoked?
A: Yes. For example, when querying for CompanyEntitys, the constructor CompanyEntity() will be called.

Object creation and constructors ∞

To check this behavior, add a parameter-less constructor to CompanyEntity and add a breakpoint there.

Then load companies from the database:

using (var ctx = new MyDataContext()) {
    // CompanyEntity objects won't be created by this call...
    IQueryable<CompanyEntity> companies1 = from company in ctx.Companies select company;

    // ... but by this (i.e. when actually iterating 
    // the query for the first time).
    List<CompanyEntity> companies2 = companies1.ToList(); // objects created: 8

    // This call will return the exact same objects as the previous call.
    List<CompanyEntity> companies3 = companies1.ToList(); // objects created: 4
}

When the IQueryable object (companies1) is created, no instances of CompanyEntity will be created.

When companies1 is iterated for the first time, each object will be created twice (one object will be returned, the other one will be kept for update tracking). However, only the first instances (i.e. #1, #3, #5, #7) are returned. (You can test this by adding object ids via Debugger to the created objects.) Setters of column properties will be called every time (i.e. 8 times in total).

When companies1 is iterated for the second time, new instances of Company will be created (#9 to #12), but the original instances (i.e. #1, #3, #5, #7) will be returned. Setters of column properties won’t be called this time.

Association attribute ∞

From an entity’s point of view, a relationship (property/field marked with [Association]) can either be 1-to-1 (EntityRef) or 1-to-many (EntitySet). Most questions in this section will apply to both scenarios and will be explained with EntitySet.

Q: Does the [Association] attribute behave like [Column] regarding to fields and properties?
A: Yes. Property accessors are being used; fields can be used; properties/fields can be private; AssociationAttribute.Storage works like for [Column].
Q: Do [Association]‘s properties ThisKey and OtherKey use the .NET name (property or field) or the database name?
A: They use the .NET name. (For example, for CompanyEntity.CompanyId it’d CompanyId, not Id.)
Effects of properties of [Association]:
1. Q: What effect does IsForeignKey have?
  A: Must only be set to true on 1-to-1 relationships (EntityRef). If set for an EntitySet, you may get a NullReferenceException when you call DataContext.SubmitChanges(). Also, it’s required for when you specify DeleteOnNull = true.
2. Q: What effect does IsUnique have?
  A: None, it seems.
3. Q: What effect does DeleteOnNull have?
  A: Only possible for 1-to-1 relationsips (EntityRef) where the foreign key is NOT NULL. In this case, when you set the relationship property to null, the foreign key can’t be set to NULL. Instead LINQ to SQL will delete the current object (in SubmitChanges()). Note: Requires IsForeignKey = true.
4. Q: What effect does DeleteRule have?
  A: To be done
Q: How do I load relationships together with their parent object?
A: Use EntitySet<T> and DeferredLoadingEnabled = true. For other possibilities, see below.
Q: How do I update relationships together with their parent object?
A: To be done
Q: Which collection types (lists, sets, dictionaries) are supported for 1-to-many relationships?
A: EntitySet<T>, List<T>, and T[]. (Note: They differ in loading behavior; see below).
1. Q: What’s so special about EntitySet then?
  A: EntitySet supports deferred loading (see HasLoadedOrAssignedValues).
Q: What’s with DataContext.LoadOptions?
A: On one hand, they’re used to reduce to number of trips to the database (more info). On the other hand, they’re necessary for loading relationships into List<T> or T[] (see below).
1. Q: What’s the difference between LoadOptions and DeferredLoadingEnabled?
  A: Depends on the data type of the relationship property/field. See below) for the exact details.
Q: How do I define a 1-to-1 relationship?
A: You need to use a storage field with EntityRef<T>. See below for more details.
Q: What happens if I specify a 1-to-1 relationship but actually have a 1-to-many relationship?
A: If the property/field is “mentioned” in the DataContext.LoadOptions, an exception will be thrown (“Sequence contains more than one element”). Otherwise the property/field will be null.
Q: Should EntitySet fields be initialized? Or should they remain null?
A: In my opinion, they should be initialized. The L2S runtime will use existing EntitySet instances where possible (see question about how L2S updates EntitySets below). It’ll only create new ones when the field is null. So, no harm is done when initializing EntitySet fields. On the contrary: This way, you don’t need to worry about uninitialized fields when you create entity objects yourself (e.g. for the purpose of adding them to the database). Also, you can specify onAdd and onRemove callbacks in EntitySet‘s constructor.
Q: Should EntityRef properties/fields be initialized? Or should they remain null?
A: EntityRef is a struct and thus is always initialized/can’t be null.
Q: How does LINQ to SQL update EntitySets after DataContext.SubmitChanges() has been called?
A: It seems that when the LINQ to SQL runtime starts tracking an entity (either when you attach it or the LINQ to SQL runtime creates one), it obtains the references of all EntitySets in the entity class. If an EntitySet is null, the LINQ to SQL runtime will create a new one and store it in the relationship property/field. This way the runtime always knows all EntitySet instances. That’s also why you should probably use EntitySet.Assign() instead of the assignment operator (=) when you assign a new EntitySets to an entity.

Defining a 1-to-many relationship ∞

To define a 1-to-many relationship, use something like this:

public class CompanyEntity {
    ...

    [Association(OtherKey = "CompanyId")]
    public EntitySet<EmployeeEntity> Employees { get; set; }
}

Notes:

You don’t need to specify Association.ThisKey as this is automatically determined through Column.IsPrimaryKey on CompanyEntity.CompanyId.
Specifying Association.ForeignKey doesn’t seem to have any effect. The same is true for Association.ForeignKey

Defining a 1-to-1 relationship ∞

Defining a 1-to-1 relationship is a little bit more involved than a 1-to-n relationship.

It’s usually done with EntityRef<T> like this:

public class EmployeeEntity {
    ...

    [Association(Storage = "m_company", ThisKey = "CompanyId")]
    public CompanyEntity Company
    {
        get { return this.m_company.Entity; }
        set { this.m_company.Entity = value; }
    }
    private EntityRef<CompanyEntity> m_company;
}

But you can also do it like this (not recommended):

public class EmployeeEntity {
    ...

    [Association(Storage = "m_company", ThisKey = "CompanyId")]
    public CompanyEntity Company { get; set; }
}

For the user both alternatives look the same. There differences, however, in how EmployeeEntity.Company is being set by the LINQ to SQL runtime. For more details, see the section DeferredLoadingEnabled and LoadOptions.

Notes:

You cannot use EntityRef<T> as data type for 1-to-1 relationships (e.g. EntityRef<CompanyEntity>). The LINQ to SQL runtime won’t be able to resolve the database keys involved in this relationship (unlike with EntitySet<T>).
Unlike EntitySet<T>, EntityRef<T> is a struct.
m_company must not be public.
If you want to specify DeleteOnNull = true you must also specify IsForeignKey = true.

ObservableEntitySet ∞

EntitySet allows you to specify “event handlers” for when an entity has been added to or removed from an EntitySet. Unfortunately, they need to be specified in the constructor and can’t be altered later.

To solve this problem, you can use ObservableEntitySet instead:

ObservableEntitySet.cs

Use it like this:

public class Company
{
    [Association(OtherKey = "CompanyId",  DeleteRule = "CASCADE")]
    public EntitySet<EmployeeEntity> Employees
    {
        get { return this.m_employees.EntitySet; }
        set { this.m_employees.EntitySet = value; }
    }
    private readonly ObservableEntitySet<EmployeeEntity> m_employees = new ObservableEntitySet<EmployeeEntity>();
}

Note: Don’t use AssociationAttribute.Storage here.

EntityDict ∞

Often you may want to quickly search for certain entries in an EntitySet – like you would in a database. Unfortunately, EntitySet is just a list, meaning searching has the complexity of O(n).

That’s why I implemented a class called EntityDict:

EntityDict.cs (you also need ObservableEntitySet from above)

You can use it like this:

public class Company
{
    [Association(OtherKey = "CompanyId",  DeleteRule = "CASCADE")]
    public EntitySet<EmployeeEntity> Employees
    {
        get { return this.m_employees.EntitySet; }
        set { this.m_employees.EntitySet = value; }
    }
    private readonly ObservableEntitySet<EmployeeEntity> m_employees = new ObservableEntitySet<EmployeeEntity>();

    public EntityDict<int, EmployeeEntity> EmployeesDict { get; private set; }

    public CompanyEntity()
    {
       this.EmployeesDict = this.m_employees.ToDict(e => e.EmployeeId);
    }
}

Note: Any changes you make to the EntityDict will be reflected into the underlying EntitySet.

DeferredLoadingEnabled and LoadOptions ∞

There are two properties of DataContext that influence how relationships are loaded: DeferredLoadingEnabled and LoadOptions. Their effects depend on the data type of the relationship property/field.

Note: Both properties must be set before executing the first query.

For 1-to-n relationships ∞

The following table shows whether a relationship property/field will be set and how when loading the parent entity from the database:

LO specified	DLE	`EntitySet<T>`	`List<T>`, `T[]`
no	false	no, null	no, null
no	true	yes, deferred	no, null
yes	false	yes, loaded	yes
yes	true	yes, loaded	yes

For 1-to-1 relationships ∞

The following table shows whether a relationship property/field will be set and how when loading the parent entity from the database:

Note: This table is identical as the one for 1-to-n relationships. It’s just here for completeness.

LO specified	DLE	`EntityRef<T>`	`T`
no	false	no, null¹	no, null
no	true	yes, deferred	no, null
yes	false	yes, loaded	yes
yes	true	yes, loaded	yes

¹ Since EntityRef<T> is a struct it can’t be null. However, in this case its value (Entity) will always be null and HasLoadedOrAssignedValue will always be false.

Specifying LoadOptions ∞

For LoadOptions, each relationship property/field needs to be “mentioned” in the load options. For example, to “mention” the property Employees (class CompanyEntity), do the following (CompanyEntity.Employees is “mentioned” in line 3):

using (var ctx = new MyDataContext()) {
    var dlo = new DataLoadOptions();
    dlo.LoadWith<CompanyEntity>(c => c.Employees);    ctx.LoadOptions = dlo;
 
    CompanyEntity company = ctx.Companies.Single(c => c.Name == "MyCompany");
    ...
}

Modifying the database (INSERT, UPDATE, DELETE) ∞

LINQ to SQL uses object/change tracking to determine what to do on DataContext.SubmitChanges().

Updating: Updates are tracked automatically (either by implementing INotifyPropertyChanging or automatically via object comparison).
Inserting: Use Table<T>.InsertOnSubmit() or Table<T>.InsertAllOnSubmit()
Deleting: Use Table<T>.DeleteOnSubmit() or Table<T>.DeleteAllOnSubmit()

For EntitySets:

Inserting: will be tracked, i.e. objects added to an EntitySet will be inserted automatically
Deleting: won’t be tracked, i.e. removing an object from an EntitySet will have no effect – unless the removed object is deleted manually. You can, however, use this EntitySet constructor to add callbacks for when entities are added or removed – like in the example below.

For EntityRefs:

Severing the connection between two objects (by setting the foreign object to null) will delete the current object, if AssociationAttribute.DeleteOnNull is set to true.
Related EntitySets will be automatically updated (i.e. the deleted instance will be removed) after DataContext.SubmitChanges() has been called.
Setting the foreign object to not null (i.e. specifying an object), the related foreign key column field/property will be updated automatically after DataContext.SubmitChanges() has been called.

Order of Operations, Constraints ∞

When you call SubmitChanges() on a DataContext it’ll do its operations in the following order:

Inserts
Updates
Deletes

You can get the entities for each operation type via GetChangeSet().

Note however, that the order inside of each operation type is undefined (at least as far as the call is concerned). For example, if you’re updating two entities A and B they can be updated either (A, B) or (B, A).

Usually this is not a problem. It may become a problem, however, when you have database constraints defined (e.g. unique constraint, check constraint).

For example, consider a database table called CarColors with columns Id, ColorName, and IsDefault. The last column defines which color is the default color. There can only be one default color. So the column’s type is bool and there’s a constraint defined on it that enforces that there is only one default color.

Now, consider you want to change the default color. What you would do is something like this:

CarColor oldDefaultColor = colorsTable.Single(entity => entity.IsDefault);
CarColor newDefaultColor = colorsTable.Single(entity => entity.Id == newDefaultColorId);

oldDefaultColor.IsDefault = false;
newDefaultColor.IsDefault = true;
dataContext.SubmitChanges();

However, this may fail if L2Q first tries to execute the SQL UPDATE statement for newDefaultColor. (This UPDATE statement would violate the constraint defined on column IsDefault because there’s already a default color, namely oldDefaultColor.)

In this example, the order of UPDATE statements matters. We first need to update oldDefaultColor, then newDefaultColor.

Note: The best solution (IMHO) for this problem would be “deferred constraints“. With deferred constraints, the consistency of each constraint would be checked at the end of a transaction, not after every statement. Unfortunately, MSSQL doesn’t support deferred constraints.

Unfortunately, there doesn’t seem to be a way to specify the order of operations within the same operation type in L2Q.

To force a specific order, you need to add SubmitChanges() calls – preferably wrapped in a transaction.

using (var transaction = new System.Transactions.TransactionScope())
{
  CarColor oldDefaultColor = colorsTable.Single(entity => entity.IsDefault);
  CarColor newDefaultColor = colorsTable.Single(entity => entity.Id == newDefaultColorId);

  oldDefaultColor.IsDefault = false;
  dataContext.SubmitChanges();
  newDefaultColor.IsDefault = true;
  dataContext.SubmitChanges();
}

Note: Transactions currently don’t work with await statements within the transaction (because transactions need to be disposed on the thread that created them).

Tracking additions and removals in EntitySet ∞

The following is an example of how you could implement an EntitySet that automatically keeps track of foreign key relationships.

TrackingEntitySet.cs