Setting Foreign keys in Linq to SQL

Question

It's well known that you cannot set foreign key IDs directly in Linq to SQL if the entities have already been loaded. You can however look up the entity by it's foreign key and then set the entity as the foreign entity using the entity relationship. (I've taken out the enum here and used integer values for simplicity). i.e. If I have a loaded Appointment entity and an associated AppoinmentStatus Entity I can't do this:-

ExistingAppointment.AppointmentStatusID = 7

But I can do this:-

ExistingAppointment.AppointmentStatus = (From appstat In db.AppointmentStatus _
                                        Where appstat.StatusID = 7 _
                                        Select appstat).Single

I have this kind of thing littering my code and I'd like to refactor. So...

I could obviously use a helper method in a module like this:-

Module Helper
    Public Shared Function GetAppointmentStatus(ByVal AppStatusID As Integer) As AppointmentStatus
        GetAppointmentStatus = (From appstat In db.AppointmentStatus _
                                       Where appstat.AppointmentStatusID = AppStatus _
                                       Select appstat).Single
    End Function
End Module

I could even make this into an extension method, like this.

Imports System.Runtime.CompilerServices
Module Helper
Extension()> _
    Public Shared Function GetAppointmentStatus(ByVal db as DataClassesDataContext, ByVal AppStatusID As Integer) As AppointmentStatus
        GetAppointmentStatus = (From appstat In db.AppointmentStatus _
                                       Where appstat.AppointmentStatusID = AppStatusID _
                                       Select appstat).Single
    End Function
End Module

I could also put this in the Linq to SQL partial class, like this.

Partial Public Class DataClassesDataContext    
    Public Function GetAppointmentStatus(ByVal AppStatusID As Integer) As AppointmentStatus
        GetAppointmentStatus = (From appstat In Me.AppointmentStatus _
                                       Where appstat.AppointmentStatusID = AppStatusID _
                                       Select appstat).Single
    End Function
End Class

Further I could put the code in the Linq to SQL Appointment Entity partial class like this:-

Partial Public Class Appointment    
    Public Function GetAppointmentStatus(ByVal db as DataClassesDataContext, ByVal AppStatusID As Integer) As AppointmentStatus
            GetAppointmentStatus = (From appstat In db.AppointmentStatus _
                                       Where appstat.AppointmentStatusID = AppStatusID _
                                       Select appstat).Single
    End Function
End Class

Which should I do and why, or is there a better alternative?

Answer 1

Normally the setter of AppointmentStatusId looks like: (Code in C# but it should be easy to get it)

[Column(Storage="_AppointmentStatusId", DbType="Int")]
public System.Nullable<int> AppointmentStatusId
{
    get
    {
        return this._AppointmentStatusId;
    }
    set
    {
        if ((this._AppointmentStatusId != value))
        {
            if (this._AppointmentStatus.HasLoadedOrAssignedValue)
            {
                throw new System.Data.Linq.ForeignKeyReferenceAlreadyHasValueException();
            }
            this.OnCapabilityIdChanging(value);
            this.SendPropertyChanging();
            this._AppointmentStatusId = value;
            this.SendPropertyChanged("AppointmentStatusId");
            this.OnCapabilityIdChanged();
        }
    }
}

So, if I were you, I'd add a method SetAppointmentStatusId(int statusId) to the partial file that comes with the designer generated one, and add more whenever needed for other properties. You may even choose to make the setter of the property private (from the designer select the property and press F4 to for the VS properties window to change accessibility).
In this method I'd write code identical to the setter except that it does NOT include this part:

            if (this._AppointmentStatus.HasLoadedOrAssignedValue)
            {
                throw new System.Data.Linq.ForeignKeyReferenceAlreadyHasValueException();
            }

Answer 2

I typically put these methods in the partial class for the DataContext, on the theory that these methods are similar to stored procedures in some sense, and stored procedures are manifested as methods on the DataContext.

Whichever way you decide to proceed, it's definitely worth refactoring so that you have this query in only one place and don't repeat yourself. This also leaves you the option of replacing the simple method you've described with a more sophisticated one that caches a compiled version of the query and reuses it, without having to update all the callers of the method.

Answer 3

There are two main schools of thought on this:

1. Put the logic in the DataContext (partial class, or actual class if you code your DataContext by hand). The rationale behind this is that your DataContext already knows about all of your different entities, so this isn't creating any additional coupling and isn't leading to class bloat.

   The disadvantage, of course, is that if you have a few hundred of these API methods (and you probably will, eventually) then your DataContext will quickly start turning into a ball of mud, filled with every random query API any programmer decides to throw in. You can try to clean this up by separating related functions into different instances of the same partial DataContext class, but that's really only a cosmetic improvement.

2. Put the logic in a repository class, i.e. an AppointmentRepository. Two advantages to this approach are (a) the ability to use dependency injection on the repository and an IoC framework, in case you decide to change your data model, and (b) the fact that you're sticking to the Single Responsibility Principle - it actually makes sense for the method to be where it is.

   The main disadvantages to putting these in repositories are: (a) They may be duplicating very similar logic that's already in your DataContext as Stored Procedures; (b) they have a way of creating headaches when it comes to transaction management (if you also use them to save); and (c) when you start to have a lot of custom queries that return specially-tailored DTOs for specific operations or reports, you're left with the two crummy choices of either creating one repository for each and every DTO, or creating one master "utility" repository for all the DTOs or some loosely-related group of them. Both end up being a rather poor design.

Those are the tradeoffs; only you can decide which is better for your own purposes.

I would definitely advise against the extension-method approach, as extension methods are difficult to discover (you can't just type the method and have Intellisense pick up the relevant reference), and they're also simply not necessary when you have the ability to directly modify or extend (via partials) the original class.

I'd also advise against extending the Appointment class; one of the reasons we use tools like Linq To SQL is so we can deal with POCO entities that don't need to know anything about where they came from. For this reason I personally am very much against the coupling of entity classes to their DataContext - the dependency should be only one-way.