By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,950 Members | 1,005 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,950 IT Pros & Developers. It's quick & easy.

Best way to delete a record using LINQ?

P: n/a
I am trying to do all my DB access through LINQ. For example, I am
trying to delete a record from the JobQueue table. Thereís a couple
ways I could do this:

1. Method 1 Ė The disadvantage is that it makes 2 calls to the DB: one
to get the record and then one to delete it:

using (LogicDataContext ctx = new LogicDataContext(m_strConnect))
{
JobQueue jq = ctx.JobQueues.Single(queue =queue.JobQueueNo ==
queueNo);
ctx.JobQueues.DeleteOnSubmit(jq);
ctx.SubmitChanges();
}

2. Method 2 Ė The disadvantage is that Iím writing SQL directly, which
I donít want to do. In fact, itís the whole reason Iím using LINQ.

using (LogicDataContext ctx = new LogicDataContext(m_strConnect))
{
string str = "Delete JobQueue WHERE JobQueueNo = " + queueNo;
ctx.ExecuteCommand(str);
}

3. Method 3 Ė This doesn't work because it throws an exception: "Row
not found or changed." It doesn't make two trips to the DB like
method 1, but instead tries to attach a JobQueue to the data context.
But will only work if all the column values are exactly the same as
the values in the DB:

using (LogicDataContext ctx = new LogicDataContext(m_strConnect))
{
JobQueue jq = new JobQueue() { JobQueueNo = requestQueueNo };
ctx.JobQueues.Attach(jq, false);
ctx.JobQueues.DeleteOnSubmit(jq);
ctx.SubmitChanges();
}

Is there a better way? I think LINQ should have a built in function
to delete a record using the primary key.

I guess if I was going to use the second method, I could write a
helper function that would use reflection to create the DELETE
statement using the table name and the fact that the primary key
column property has an attribute on it like this:

[Column(Storage="_JobQueueNo", AutoSync=AutoSync.OnInsert,
DbType="Int NOT NULL IDENTITY", IsPrimaryKey=true,
IsDbGenerated=true)]

Thanks in advance,
John
Nov 19 '08 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.