Down the rabbit hole with LINQ-to-SQL.
Man, just when I thought I was starting to get the hang of LINQ-to-SQL and knowing what kind of cool features it has, I ran into some interesting problems. For one the DataContext needs to have an ‘appropriate lifetime’ which means you have to play around with it, there aren’t real standards. The issue of context lifetime manifests itself when you try to use LINQ-to-SQL as a middle tier application brick of sorts to toss objects into back and from the db — you get duplicate key conflicts when you try to .Attach() and object to update, so you basically have to kill the context to avoid that. How crazy is that? For example, on a web page, (aspx) I use a single datacontext, and I tried caching it in the HttpContext.Application[] scope but I kept getting errors basically pointing to staleness of the primary keys (saying duplicate key exists, etc…)
I’m going to go over two things on this context lifetime issue: A) What the problem is, exactly, and B) what I did to fix it in my Webpage example.
Part I: The problem
The idea of the DataContext in LINQ is that it tracks changes for you. Like a fancy living Dataset. You put objects into it, and holds state that represents whether or not you’ve changed something since it was read from the db, and when you call .SubmitChanges() you get them all committed. The problem with this is that the context object HOLDS A REFERNCE to all of the objects it’s ever touched since it’s instantiation. That’s an issue when you’re trying to say… change and object, or attach an object that you’ve constructed from serialization, but has the same primary key as another that’s already in there… So what to do? Squash the whole context after a quick call to SubmitChanges();
Part II: The solution (That I used.)
The last sentence of the last paragraph of ‘the problem’ alludes to the fact taht I squash the context; however, it doesn’t say how I hook on that in an aspx app. I read around and found a suggestion that I try out a Request-Scoped context. How might one do this?
Global.asax! You add two handlers:
void Application_BeginRequest(object sender, EventArgs args)
{
//create the context
MyDataContext ctx = new MyDataContext();
// gotcha now, for the whole duration of the request (Context.Items lives for One (1) request!)
HttpContext.Current.Items["DataContextStorage"] = ctx;
}
void Application_EndRequest(object sender, EventArgs args)
{
MyDataContext o = HttpContext.Current.Items["DataContextStorage"] as MyDataContext;
// say goodbye! Properly
if(o != null)
{
o.Dispose();
}
}
So How do I Access this easily? Wrap it up!
public static class Config
{
public static MyDataContext GetCurrentDataContext()
{
return HttpContext.Current.Items["DataContextStorage"] as MyDataContext;
}
}
// DO NOT USE THE IDISPOSABLE PATTERN HERE OR ELSE!
var MyContext = Config.GetCurrentDataContext();
var Foozles = MyContext.Foobars.Where(a => a.FoobarId < 1000);
Ok, so that was a minor ‘problem’ but really just a poor understanding of the technology on my part. I know better now, I think.
There’s something better that LINQ-to-SQL does, or rather, it’s a lot SEXIER in programming terms. Since you can ‘where’ expression your way through life with a lambda, it sure as heck makes it simple to Where your way to dynamic SQL queries generated by something simple in the UI (like a query builder interface.) I have a perfectly great example, but rather than try and write and claim it myself, I’ll give you the source that I got, and perhaps develop a more familiar and complete example later on in another post: C# 3.0 In a Nutshell (book website ‘excerpt’)
You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.
Hi Dave,
Thanks for the helful info.
I am working on a web application with a single DBML file (Linq to sql) for holding all objects placed in a data access layer (windows class lib project). I am using 3 tier project arch. My web application showing strange behavior, sometimes it becomes too slow & thorws exception & sometimes work faster with the same code. I wanted to get ride of this behavior, can you pls help me?
Your help will be very much appreciated.
Thanks
RZ