Linq To Sql using store proc is calling update instead of delete

  • Thread starter Thread starter Kalman Skulski
  • Start date Start date
K

Kalman Skulski

I have a schema that has a parent child relationship. I have decided to
implelment operations using stored procs for the child table(view). I wrote
my custom insert update and delete and then mapped them to my view using the
configure behavior dialog.

At first I got an "An attempt was made to remove a relationship between a
Parent and a Child. ... OwnerParentId) cannot be set to null" when I
attempted to delete a child record. After a bit of research I changed my
schema association and added a DeleteOnNull. This made no change. I still
got the error. I then made by child FK field nullable. It won't ever be
null but OK.
This made the error disapear. Instead it just doesn't work.

I check the dbcontext log and found instead of calling my delete stored proc
it was calling my update stored proc.

I did try a manual delete by calling DeleteOnSubmit but when I delete it
using the UI (winform grid) it calls update instead of delete.

Any Ideas?
 
Kalman Skulski said:
I have a schema that has a parent child relationship. I have decided to
implelment operations using stored procs for the child table(view). I
wrote
my custom insert update and delete and then mapped them to my view using
the
configure behavior dialog.

At first I got an "An attempt was made to remove a relationship between a
Parent and a Child. ... OwnerParentId) cannot be set to null" when I
attempted to delete a child record. After a bit of research I changed my
schema association and added a DeleteOnNull. This made no change. I
still
got the error. I then made by child FK field nullable. It won't ever be
null but OK.
This made the error disapear. Instead it just doesn't work.

I check the dbcontext log and found instead of calling my delete stored
proc
it was calling my update stored proc.

I did try a manual delete by calling DeleteOnSubmit but when I delete it
using the UI (winform grid) it calls update instead of delete.

Any Ideas?
Click to expand...

Although you can you sprocs, the best practice is not to use them as you
defeat the purpose of the virtual database in an ORM solution, which is an
object oriented solution dealing objects or entities.

You'll be better off just using ADO.NET SQL Command object if all you're
doing is using sprocs.



__________ Information from ESET NOD32 Antivirus, version of virus signature database 4035 (20090425) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com
 
That is my point. I am trying to use procs and am getting strange behavior.
It is treating deletes as updates. There are many ways to solve problems.
I have alot of that comes from other records that I want to display as read
only info on the record that is displayed. I thought I might try this
method. They (MS) make it easy to map to stored procs. You think it might
actually work. I read the docs, they make mention of updating idenity cols
and timestamps. But non of that is at issue here.
 
I found the problem and DeleteOnNull is the solution the error I had made was
I didn't notice I was editing the parent side of the relationship when I
added delete on nuil. You must add it to the child. I has nothing to do
with using stored procs. This happens with directly bound tables as well.
Why MS made this the defualt behavior is beyond me.
 
That does't work real well in alot of case.
e.g.
Lets say I have a POLine that contain references to a ID in another table
that contains say Description, and Mfr#

On the PO line I wasn the user to be able to enter a part # and Qty and Price.

When they enter the part # I want it to pull in the Description and Mfr#.
This is not stored in the PO Line table. Is is stored on a related table.

stored procs let me do both. I can handle all the concurrency issues I care
to.
I have access to the original values and writing CRUD procedures is trivial.

While on a PO I would probably want to have a copy of this data there are
many places when I want a look up code and description to be displayed on
the line often many more related field as well. But not want that to be part
of my data table design) If you have any suggestions on a better way I am
open to it. I have many years of experience doing db work but less
experience in .net and am quite new to using linq to sql.
 
I looked at entity frameworks and while it does look promising it has many
drawbacks.
1) Performance. generaly about 40% performance overhead.
2) The designer doesn't support 50% of the language feature.
3) I am not trying to be database agnostic. I don't need to run againt any
datastore. Using stored procedure to post complex transaction is a good idea
SQL Server is very good at it. So are Oracle and other db's.

I am quite happy that I can my to my own crud routines it saves alot of work.
You just have to be sure to pass values and origianlvalues back to do your
concurrency checking.

CREATE UpdateProc (@pkid int, @value decimal, @oldvalue decimal) as
begin
update table
set value = @value
where pkid = @pkid and ((value = @orgvalue) or (value is null and
@orgvalue is null))

if @@rowcount <> 1
RAISERROR('Data has changed',16,1)
end
You get the added flexibility of doing careful concurrency checking where
required and lazy where not required. My original issue turn out to have
nothing to do with stored procs.
 
Back
Top