Extending The Partial Classes of Stored Procedures' Results in LINQ to SQL

Original: Where do we go next? by Anke L, CC-BY-NC-SA One of the nice things about LINQ to SQL is the ability to extend the types of the generated entities. Another nice thing is being able to get typed results from stored procedures. Let's try and combine the two together.

We'll take the following generated code for instance:

public partial class GetStuffResult
{

private int _Id;

private string _Name;

public GetStuffResult()
{
}

[Column(Storage="_Id", DbType="INT NOT NULL")]
public int Id
{
get
{
return this._Id;
}
set
{
if ((this._Id != value))
{
this._Id = value;
}
}
}

[Column(Storage="_Name", DbType="NVarChar(100)")]
public string Name
{
get
{
return this._Name;
}
set
{
if ((this._Name != value))
{
this._Name = value;
}
}
}
}

I would like to extend it by adding a new property to it, named TranslatedId, which translates the returned Id to and from a Guid:

partial class GetStuffResult
{
public Guid TranslatedId
{
get
{
return Translator.GetGuid(this.Id);
}
set
{
this.ProjectId = Translator.GetInt(value);
}
}
}

Translator's two methods translate between the integer id and the Guid id. Note that 0 translates to and from an empty Guid.

I'll try to run it and will find out that all of my Ids are 0. Why is that?

Apparently, when running a stored procedure in LINQ to SQL, it requires all of the properties defined on the class to have both getters and setters (which means you can not write read-only properties in the partial class) and sets all non-result (i.e. non-generated) properties to their default values. This means that although the real Id gets selected from the database, TranslatedId gets set to an empty Guid immediately afterwards, which in turn overrides Id to 0.

To work around this, we have to apply a dirty little hack:

partial class GetStuffResult
{
private Guid dupe;

[Column(Storage = "dupe")]
public Guid TranslatedId
{
get
{
return Translator.GetGuid(this.Id);
}
set
{
this.ProjectId = Translator.GetInt(value);
}
}
}

This is pretty horrible, but what it does is direct LINQ to SQL to set the default value into the property's storage field, which is some dupe field, instead of into the property itself. Now the field's value will be reset and not the property's. Note that the dupe field's type should be the same as the property's type.

Yes, it's ugly, but this is the only workaround I found. If anyone knows of any other workaround or solution to this problem, I'd love to hear it.

[Original image used: Where do we go next?]

No Comments