Linq to SQL class lifespan

Question

As far as I understand, when I am new to the Linq to SQL class, this is the equivalent of the new'ing of the SqlConnection object.

Suppose I have an object with two methods: Delete() and SubmitChanges() . Would it be wise for me to upgrade the Linq class to SQL in each of the methods, or will the private variable containing the Linq to SQL class, new to the constructor, be the way to go?

I am trying to avoid a timeout.

UPDATE:

 namespace Madtastic { public class Comment { private Boolean _isDirty = false; private Int32 _id = 0; private Int32 _recipeID = 0; private String _value = ""; private Madtastic.User _user = null; public Int32 ID { get { return this._id; } } public String Value { get { return this._value; } set { this._isDirty = true; this._value = value; } } public Madtastic.User Owner { get { return this._user; } } public Comment() { } public Comment(Int32 commentID) { Madtastic.DataContext mdc = new Madtastic.DataContext(); var comment = (from c in mdc.Comments where c.CommentsID == commentID select c).FirstOrDefault(); if (comment != null) { this._id = comment.CommentsID; this._recipeID = comment.RecipesID; this._value = comment.CommentsValue; this._user = new User(comment.UsersID); } mdc.Dispose(); } public void SubmitChanges() { Madtastic.DataContext mdc = new Madtastic.DataContext(); var comment = (from c in mdc.Comments where c.CommentsID == this._id select c).FirstOrDefault(); if (comment != null && this._isDirty) { comment.CommentsValue = this._value; } else { Madtastic.Entities.Comment c = new Madtastic.Entities.Comment(); c.RecipesID = this._recipeID; c.UsersID = this._user.ID; c.CommentsValue = this._value; mdc.Comments.InsertOnSubmit(c); } mdc.SubmitChanges(); mdc.Dispose(); } public void Delete() { Madtastic.DataContext mdc = new Madtastic.DataContext(); var comment = (from c in mdc.Comments where c.CommentsID == this._id select c).FirstOrDefault(); if (comment != null) { mdc.Comments.DeleteOnSubmit(comment); mdc.SubmitChanges(); this._isDirty = false; this._id = 0; this._recipeID = 0; this._value = ""; this._user = null; } mdc.Dispose(); } } } 

DISABLED CODE (according to Grank specification):

 namespace Madtastic { public sealed class CommentNew : IDisposable { private Madtastic.DataContext _mdc; private Madtastic.Entities.Comment _comment; private Madtastic.User _user; public Int32 ID { get { return this._comment.CommentsID; } } public String Value { get { return this._comment.CommentsValue; } set { this._comment.CommentsValue = value; } } public Madtastic.User Owner { get { return this._user; } } public void Comment(Int32 commentID) { this._mdc = new Madtastic.DataContext(); this._comment = (from c in _mdc.Comments where c.CommentsID == commentID select c).FirstOrDefault(); if (this._comment == null) { this._comment = new Madtastic.Entities.Comment(); this._mdc.Comments.InsertOnSubmit(this._comment); } else { this._user = new Madtastic.User(this._comment.User.UsersID); } } public void SubmitChanges() { this._mdc.SubmitChanges(); } public void Delete() { this._mdc.Comments.DeleteOnSubmit(this._comment); this.SubmitChanges(); } void IDisposable.Dispose() { this._mdc.Dispose(); } } } 

Answer 1

Now that I’ve reviewed the sample code that you edited for publication, I would definitely reorganize your class to take advantage of LINQ-to-SQL built-in functionality. (I will not edit my previous comment because this is the best answer to the general question)
The fields of your class seem to be a fairly direct mapping of the columns in the Comments table in the database. Therefore, you do not need to do most of what you do manually in this class. Most functions could be handled simply by having a private member of type Madtastic.Entities.Comment (and simply matching properties with its properties if you need to keep this class interacting with the rest of the project). Then your constructor can simply initialize the private member Madtastic.DataContext and set your private member Madtastic.Entities.Comment to the result of the LINQ query on it. If the comment is null, create a new one and call InsertOnSubmit in the DataContext. (but it doesn’t make sense to post changes since you didn’t set any values ​​for this new object anyway)
In your SubmitChanges, all you have to do is call SubmitChanges in the DataContext. It saves its own track about whether to update the data, it will not get into the database if this does not happen, so you do not need _isDirty.
In your Delete (), all you have to do is call DeleteOnSubmit in the DataContext.
In fact, you may find with a small overview that you do not need the Madtastic.Comment class at all, and the Madtastic.Entities.Comment LINQ to SQL class can act directly as your data access level. It seems that the only practical differences are the constructor that accepts commentID, and the fact that Entities.Comment has a UserID property, where your Madtastic.Comment class has a whole user. (However, if the User is also a table in the database, and the UserID is the foreign key to his primary key, you will find that LINQ-to-SQL created the User object on the Entities.Comment object, which you can access directly with comment. User)
If you find that you can completely exclude this class, it may mean that you can further optimize the DataContext life cycle by creating it in accordance with the methods of your project that use a comment.

Edited to publish the following example of a reorganized code (apologies for any errors, since I typed it in a notebook in a couple of seconds, and did not open a visual studio, and I would not intellisense for your project):

 namespace Madtastic { public class Comment { private Madtastic.DataContext mdc; private Madtastic.Entities.Comment comment; public Int32 ID { get { return comment.CommentsID; } } public Madtastic.User Owner { get { return comment.User; } } public Comment(Int32 commentID) { mdc = new Madtastic.DataContext(); comment = (from c in mdc.Comments where c.CommentsID == commentID select c).FirstOrDefault(); if (comment == null) { comment = new Madtastic.Entities.Comment(); mdc.Comments.InsertOnSubmit(comment); } } public void SubmitChanges() { mdc.SubmitChanges(); } public void Delete() { mdc.Comments.DeleteOnSubmit(comment); SubmitChanges(); } } } 

You might also want to implement IDisposable / using, as suggested by a number of people.

Answer 2

Depending on what you call the "LINQ-to-SQL class", and what this code looks like.
If you are talking about a DataContext object, and your code is a class with a long service life or the program itself, I think it would be better to initialize it in the constructor. This is not very similar to creating and / or opening a new SqlConnection, in fact, very reasonable management of the database connection pool and concurrency and integrity so that you do not need to think about it, that part of the joy in my experience, far from LINQ-to -SQL. I have never seen a timeout problem.
One thing you should know is that it is very difficult to exchange table objects in the DataContext area, and this is really not recommended if you can avoid this. Detach () and Attach () can be harsh. Therefore, if you need to pass a LINQ-to-SQL object that represents a row in a table in your SQL database, you should try to design the life cycle of the DataContext object to cover all the work that you need to do on any object that comes out of this.

In addition, there is a lot of overhead associated with creating the DataContext object and the large amount of overhead that it manages ... If you use the same tables again and again, it would be better to use the same instance of the DataContext as it will manage with your connection pool, and in some cases, cache some things to improve efficiency. However, it is recommended that each table in your database is not loaded into your DataContext, but only the ones you need, and if access to the tables is very separate in very different circumstances, you can consider splitting them into several DataContexts, which gives you some options upon initialization of each of them, if the circumstances associated with them are different.

Answer 3

I assume that you mean holding a value for the DataContext class? Personally, my preference is that the "using" clause is used by default for anything that is IDisposable (which belongs to the DataContext classes). Creating an instance in the constructor and storing the DataContext in a private variable would make this impossible. Therefore, for me, I put the instance in the methods, but more specifically, I would enter the instance in the usage section to ensure proper disposal.

Answer 4

One of my current projects uses Linq to SQL, where we hold the datacontext as a private field inside the object. Doing this was not unpleasant for the most part, it makes fun in the future if I pass in a datacontext in the constructor, and just seems cleaner than opening multiple datacontexts. Like Jacob mentioned, we implement IDisposable to make sure that the datacontext can be reliably closed when the object is no longer needed.

Answer 5

SqlConnections are merged by default. You must upgrade them to justify this pool cost!

DataContexts are cheap to make (after the first). You must upgrade them to justify this first new value!