Most efficient way to auto-hookup a foreign key in LINQ

Question

I have two tables for tracking user sessions on my site. This is a gross oversimplification btw :

 Campaign:
     campaignId    [int]
     campaignKey   [varchar(20)]
     description   [varchar(50)]

 Session:
     sessionDate   [datetime]
     sessionGUID   [uniqueidentifier]
     campaignId    [int]
     campaignKey   [varchar(20)]

I want to insert a new record into Session, using LINQ :

     var s = new Session();
     dbContext.Session.InsertOnSubmit(s);

     s.sessionDate = DateTime.Now;
     s.sessionGUID = Guid.NewGuid();
     s.campaignKey = Request.Params["c"];
     // dont set s.campaignId here...

     dbContext.SubmitChanges();

Notice that I am not currently setting campaignId in this code.

What I want is for something to automaticaly hookup the foreign key to the 'Campaign' table, and whatever does it must first add a new row to the 'Campaign' table if the campaign passed in has never been used before.

I have a few decisions to make and would really appreciate insight on any of them :

• I don't know if I should use a trigger, a stored proc or do it in LINQ manually :

  • Trigger: slightly icky, never really liked using them, but would guarantee the 'campaignId' was updated by the time I need it

  • Stored proc: again slightly icky, my SQL is not great and I value the consistency of being able to do everything in LINQ as much as possible.

  • Linq manually: i'd have to keep a copy of the 'Campaign' table in memory and use a C# hashtable to do the lookup. i'd then have to worry about keeping that table up to date if another client added a new campaign.

• My main two reasons for wanting this foreign key table is for a more efficient index on 'Session' for 'campaignId' so that I can do grouping faster. it just seems like it ought to be a lot faster if its just an integer column being grouped. The second reason is to give partners permissions to see only their campaigns through joins with other tables.

Before anyone asks I do NOT know the campaigns in advance, as some will be created by partners.

Most importantly: I am primarily looking for the most 'LINQ friendly' solution.

Answer 1

I would definitely recommend adding a nullable foreign key constraint on the Session table. Once you have that setup, it should be as simple as tacking on a method to the Session class:

public partial class Session
{
    public void SetCampaignKey(string key)
    {
        // Use an existing campaign if one exists
            Campaign campaign =
            from c in dbContext.Campaigns
            where c.campaignKey == key
            select c;

        // Create a new campaign if necessary
        if (campaign == null)
        {
            campaign = new Campaign();
            campaign.campaignKey = key;
            campaign.description = string.Empty; // Not sure where this comes in
            dbContext.Campaign.InsertOnSubmit(campaign);
        }

        // We can now set the reference directly
        this.Campaign = campaign;
    }
}

My LINQ may be a bit off, but something like this should work.

You can call SetCampaignKey() instead of manually setting the campaignKey property. When you call dbContext.SubmitChanges, the campaign will be added if necessary and the Session entry will be updated accordingly.

In this case, only the campaignId property would be set automatically. You could rely on a simple trigger to set campaignKey or do away with it. You could always retrieve the value by joining on the Campaign table.

Am I oversimplifying the problem?