473,394 Members | 1,944 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,394 software developers and data experts.

Best way to delete a record using LINQ?

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
0 3663

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

13
by: G | last post by:
Hello, Looking for opinions on a fairly simple task, new to ASP.net (C#) and want to make sure I do this as efficiently as possible. I have a web based form, and I need to run some SQL before...
2
by: shapper | last post by:
Hello, I have two tables: TagId, PostId TagId, PostId How can I delete, given a TagId, the record from Tags and all records associated with it in PostTags.
3
by: shapper | last post by:
Hello, I have a table named Tags with the following columns: TagId and TagValue. How can I delete a record, using LINQ, given the TagId value. Thanks, Miguel
1
by: news.microsoft.com | last post by:
Hello people: First of all forgiveness for my English. I am working with Linq to SQL, Visual Studio 2008 and SQL Server 2000. When you delete a record and then go back to Insert (ie, insert a...
2
by: news.microsoft.com | last post by:
Hello people: First of all forgiveness for my English. I am working with Linq to SQL, Visual Studio 2008 and SQL Server 2000. When you delete a record and then go back to Insert (ie, insert a...
3
by: shapper | last post by:
Hello, I have 3 tables, Tags, ArticlesTags and FilesTags, with the following columns: Tags TagID, Text ArticlesTags TagID, ArticleID FilesTags TagID, FileID
2
by: Arjen | last post by:
Hi, How can I delete multiple records with Linq? On the internet I found the DeleteAll function, but this one does not exist in my library. Please help me out. Thanks!
4
by: trullock | last post by:
Hi, Can anyone suggest the best way to go about the following... I'm tracking clicks (mouse down x,y coordinates) on a web page by using some javascript to create an XHR which sends the...
0
by: mesut | last post by:
Hi, I'm using LINQ.. and I would like to understand if it's a bug or not in LINQ... I've a situation like: I'm reading all records via "Select" Statement and a specific select statment #1...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.