Bundle the DataContext with the results? Or is there a better way?
-
Here's a little architectural dilemma regarding "round-trip" data access I've encountered using LINQ in my ASP.NET applications... would like to hear some perspectives. Due to the nature of the projects I've been working on, its not really an option to build proper classes to hold much of the data retrieved from the database because the schema changes on a weekly basis and there is the need to be able to reflect these changes in the front-end without having to go into the App_Code every time. A typical example: an ASP.NET page displays information about a user. Each time the page is viewed, the users table in the database must be updated to reflect the number of times that particular user as been viewed. As far as I can tell, there are 3 general approaches for doing this with LINQ, all of which are unsatisfactory. 1) Do your query in the code-behind (BAD) ... MyDataContext dc = new MyDataContext(); user u = (from u in dc.users where u.screenName = Request.QueryString["screenName"] select u).First(); u.views++; dc.SubmitChanges(); SomeFrontEndElement.DataSource = u; SomeFrontEndElement.DataBind(); ... 2) Use LINQ for selection, and SQL for inserts / updates (UGLY) [in some static class in the App_Code] public static user GetUser(string screenName) { MyDataContext dc = new MyDataContext(); return (from u in dc.users where u.screenName = Request.QueryString["screenName"] select u).First(); } public static user UpdateViews(user u) { u.views++; //No way to submit this - the DataContext is long gone ExecuteNonQuery("update users set views = "+u.views+" where screenName = "+u.screenName); } [in the code-behind] user u = SomeClass.GetUser(Request.QueryString["screenName"]); SomeFrontEndElement.DataSource = u; SomeFrontEndElement.DataBind(); SomeClass.UpdateViews(u); 3) Bundle the DataContext and the LINQ results together in a class (BETTER) public class User { private MyDataContext dc; public user Obj{get; set;} public User(string screenName) { dc = new MyDataContext(); U = (from u in dc.users where u.screenName = Request.QueryString["screenName"] select u).First(); } public SubmitChanges(); { dc.SubmitChanges(); } } then in the code-behind: ... User u = new User(Request.QueryString["screenName"]); u.Obj.views++; u.SubmitChanges(); SomeFrontEndElement.DataSource = u; SomeFrontEndElement.DataBind(); ... Certainly, the pattern that I've ju
-
Here's a little architectural dilemma regarding "round-trip" data access I've encountered using LINQ in my ASP.NET applications... would like to hear some perspectives. Due to the nature of the projects I've been working on, its not really an option to build proper classes to hold much of the data retrieved from the database because the schema changes on a weekly basis and there is the need to be able to reflect these changes in the front-end without having to go into the App_Code every time. A typical example: an ASP.NET page displays information about a user. Each time the page is viewed, the users table in the database must be updated to reflect the number of times that particular user as been viewed. As far as I can tell, there are 3 general approaches for doing this with LINQ, all of which are unsatisfactory. 1) Do your query in the code-behind (BAD) ... MyDataContext dc = new MyDataContext(); user u = (from u in dc.users where u.screenName = Request.QueryString["screenName"] select u).First(); u.views++; dc.SubmitChanges(); SomeFrontEndElement.DataSource = u; SomeFrontEndElement.DataBind(); ... 2) Use LINQ for selection, and SQL for inserts / updates (UGLY) [in some static class in the App_Code] public static user GetUser(string screenName) { MyDataContext dc = new MyDataContext(); return (from u in dc.users where u.screenName = Request.QueryString["screenName"] select u).First(); } public static user UpdateViews(user u) { u.views++; //No way to submit this - the DataContext is long gone ExecuteNonQuery("update users set views = "+u.views+" where screenName = "+u.screenName); } [in the code-behind] user u = SomeClass.GetUser(Request.QueryString["screenName"]); SomeFrontEndElement.DataSource = u; SomeFrontEndElement.DataBind(); SomeClass.UpdateViews(u); 3) Bundle the DataContext and the LINQ results together in a class (BETTER) public class User { private MyDataContext dc; public user Obj{get; set;} public User(string screenName) { dc = new MyDataContext(); U = (from u in dc.users where u.screenName = Request.QueryString["screenName"] select u).First(); } public SubmitChanges(); { dc.SubmitChanges(); } } then in the code-behind: ... User u = new User(Request.QueryString["screenName"]); u.Obj.views++; u.SubmitChanges(); SomeFrontEndElement.DataSource = u; SomeFrontEndElement.DataBind(); ... Certainly, the pattern that I've ju
I think the main problem here is multiple app domains in ASP.NET. Setting u.Views += 1 is nice but with ASP.NET you could have several instances of the application - they would both update Views independently so the value is wrong. The way to solve that would be to store the number of views as a field on the user in the database. Then the issue becomes how do you update it? There isn't in LINQ to SQL a hook for selection e.g. OnUserSelect() as there is for updates/inserts etc. Instead I would suggest a static method on the User class for doing the selection that updates the count and returns the user in one go. The first thing this method does is call a stored procedure, lets say UpdateUserCount(username) which updates the count value of the user you're about to access. Then the routine does the LINQ select and returns the user. for example:
public static User SelectUser(string username)
{
var dx = new MyDataContext();
dx.UpdateUserCount(username);
return dx.Users.Single( u => u.Username == username);
}'Howard