Linq to SQL Custom Insert/Update/Delete statement

I'm sure it's something simple, but I can't figure out how to override the L2S generated Insert/Update/Delete statements with my own.

First off, to head off this suggestion, I can't modify the SQL database.  It's the backend of a commercial product and it has to stay how it is.  There is a view in that database that simply combines three tables (type1, type2, type3) in a 1 to 1 relationship.  Essentially the developers didn't want to put that many columns in one table so they split the records across three.

Anyway, the view as defined in the database is not updateable.  I would like the L2S class that I've generated to still be able to update the record by updating the three tables individually, but I can't figure out how to get L2S to use my statement instead of its own.  I thought it would be a simple matter of adding the following code (to handle DELETE):

Partial Public Class MyDataContext
    Private Sub DeleteMyView(instance As MyView)
        ExecuteCommand("DELETE FROM type1 WHERE id={0}",instance.id)
        ExecuteCommand("DELETE FROM type2 WHERE id={0}",instance.id)
        ExecuteCommand("DELETE FROM type3 WHERE id={0}",instance.id)
    End Sub
End Class

Open in new window

But every indication is that the code isn't being hit at all when I test it.  Anyone have any thoughts?
LVL 6
mcorrenteAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fernando SotoRetiredCommented:
Hi  mcorrente;

In Linq to SQL the ExecuteCommand( ... ) uses the data context as a pass through to the database using only its connection. There for there is no automatic delete operation. That said if you call the subroutine from your code when you have the value instance.id available and modify the command it should work. Modify your code as follows.
Partial Public Class MyDataContext
    Private Sub DeleteMyView(instance As MyView)
        '' Where dbContext is the instance name of the Linq to SQL data context
        dbContext.ExecuteCommand("DELETE FROM type1 WHERE id={0}",instance.id)
        dbContext.ExecuteCommand("DELETE FROM type2 WHERE id={0}",instance.id)
        dbContext.ExecuteCommand("DELETE FROM type3 WHERE id={0}",instance.id)
    End Sub
End Class

Open in new window

mcorrenteAuthor Commented:
Still getting the same error I get if I don't add any code.  "View is not updatable because the modifications affects multiple base tables"

It's almost like my implementation of DeleteMyView isn't be called.
Fernando SotoRetiredCommented:
Please post the code and how and where you call DeleteMyView.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

mcorrenteAuthor Commented:
Maybe I'm expecting too much.  I added that code thinking that I could then just call
MyDataContext.MyViews.DeleteOnSubmit(instance)
DataContext.SubmitChanges()

Open in new window

and it would use my implementation in DeleteMyView instead of sending the dynamically generated Linq to SQL statement.  Is that wrong?
mcorrenteAuthor Commented:
I'm not directly calling DeleteMyView.  I was assuming that if I implemented it it would be called whenever MyViews.DeleteOnSubmit was used (or, more accurately, when SubmitChanges was called after that).
Fernando SotoRetiredCommented:
As I said in my first post the linq to SQL is just being used as a pass through and therefore your last post will not work. So when you are ready to do this,
''MyDataContext.MyViews.DeleteOnSubmit(instance)
''DataContext.SubmitChanges()

'' Do this.
'' Where instance.id is the ID to be deleted
DeleteMyView(instance.id)

Open in new window

You need to modify your sub something like this.
Partial Public Class MyDataContext
    Private Sub DeleteMyView(instanceid As Integer)
        '' Where dbContext is the instance name of the Linq to SQL data context
        MyDataContext.ExecuteCommand("DELETE FROM type1 WHERE id={0}",instanceid)
        MyDataContext.ExecuteCommand("DELETE FROM type2 WHERE id={0}",instanceid)
        MyDataContext.ExecuteCommand("DELETE FROM type3 WHERE id={0}",instanceid)
    End Sub
End Class

Open in new window

mcorrenteAuthor Commented:
Ok, so I assume there's no way to force the DataContext to use my SQL statement instead of its own?  I know you can set it to use sprocs through the designer but I don't want to modify the existing database in any way.
Fernando SotoRetiredCommented:
I did not state that there is no way of doing it automatically but the way you started to do it is more efficient. To do it automatically you will need to query the three tables through Linq query for the ID's you want to modify/delete which you get when you call the SP. Once you have done that the data will be in the data context and in the variable you used to make the query. Now you can mark the records for deletion and your done. But this will take six extra calls to the database, one for each record to get it and one for each record to delete it that are done by linq itself for you,
mcorrenteAuthor Commented:
Thanks for your help, Fernando.  

I'm not super concerned about performance.  This program will only be accessing a few records at a time.

Assuming I want to do what you describe (i.e. load a record from each of the three tables and mark them each for deletion), where would I put that code if not in the DeleteMyView block?

I know this site is for addressing specific problems and not to instruct newbs like me, so if you know of a resource through which I could dig to answer my own question I'd be happy to do that - I've been googling on this for a few days with no progress.
Fernando SotoRetiredCommented:
Can you post a screen shot of the Linq to SQL designer showing the three table you need to delete records from so that I may be able to put something together with for you. I am leaving to go home right now so it may be a few hours before I can get back to this.
mcorrenteAuthor Commented:
Thanks, Fernando.  I have to head out for the night as well but if I have a chance to get that to you from home I will.
mcorrenteAuthor Commented:
Here's a screenshot of the designer.  I put together a test database to simplify everything - the actual tables have 80-100 columns each.

Linq to SQL Designer
Fernando SotoRetiredCommented:
What I was hoping to see were relationships between the tables if any. So from the screenshot the four tables have no relationships defined between them? Correct

Also, seeming that you have put together a test database can you post that too so I can test my solution.
mcorrenteAuthor Commented:
Correct, none defined in SQL.
mcorrenteAuthor Commented:
The view is defined as:

SELECT     table1.id, table1.field1, table1.field2, table2.field3, table2.field4, table2.field5, table3.field6, table3.field7
FROM         dbo.table1 AS table1 LEFT OUTER JOIN
                      dbo.table2 AS table2 ON table1.id = table2.id LEFT OUTER JOIN
                      dbo.table3 AS table3 ON table1.id = table3.id

Open in new window

Although, in practice, these could just be INNER JOINS because there's always corresponding records in all tables.
Fernando SotoRetiredCommented:
Hi  mcorrente;

Here is some code to do what you need to do.
'' Pass in the instanceID of the records to be deleted
Private Sub DeleteMyView(instanceID As Integer)
    '' Instanciate the Database Context 
    Using MyDataContext As New DataClasses1DataContext
        '' Get the records to be deleted
        Dim t1 As type1s = (From t01 In MyDataContext.type1
                            Where t01.id = instanceID
                            Select t01).FirstOrDefault()
        Dim t2 As type2s = (From t02 In MyDataContext.type2
                            Where t02.id = instanceID
                            Select t02).FirstOrDefault()
        Dim t3 As type3s = (From t03 In MyDataContext.type3
                            Where t03.id = instanceID
                            Select t03).FirstOrDefault()
        '' Mark the records as to be deleted
        MyDataContext.type1.DeleteOnSubmit(t1)
        MyDataContext.type2.DeleteOnSubmit(t2)
        MyDataContext.type3.DeleteOnSubmit(t3)
        '' Delete the records from the database
        MyDataContext.SubmitChanges()
    End Using
End Sub

Open in new window

mcorrenteAuthor Commented:
Wouldn't I have to explicitly call DeleteMyView whenever I want to delete a record?

I'm really looking for a way to have all of this handled by the DataContext just like any other table, like:

Dim db as new MyDataContext
Dim vw as New MyView
vw.id = "UniqueID"
db.MyViews.InsertOnSubmit(wv)
db.SubmitChanges

And then have my code at some point intercept the default behavior of L2S and insert the records manually.  I don't want to have to explain to the next guy that whenever he wants to work with the view he has to call these special methods.

Maybe I'm expecting too much.
Fernando SotoRetiredCommented:
To your question, "Wouldn't I have to explicitly call DeleteMyView whenever I want to delete a record?", yes you would.

To your statement, "I'm really looking for a way to have all of this handled by the DataContext just like any other table, like:", seeming that the View returns a set of ReadOnly columns and rows from multiple tables the DataContext will not execute any code to do so and even if it did the SQL Server would return an exception thrown.

Something like this can't be done, db.MyViews.InsertOnSubmit(wv) because the View is READONLY.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mcorrenteAuthor Commented:
Ah, well.  Sometimes the best advice is "no."

I was able to get the insert to work by overriding the datacontext.SubmitChanges and looping through the ChangeSet.

And I can get the delete and updates to work, but only if I first load them using the datacontext.

For instance, the follow works just fine:

Dim dc as New MyDataContext
Dim vw1 as New view1
vw.id = "UniqueID"
dc.view1s.InsertOnSubmit (vw)
dc.SubmitChanges

dim vw2 = (From v in dc.view1s Where v.id = "UniqueID").FirstorDefault
vw2.field1 = "Field 1 Update"
dc.SubmitChanges

dc.view1s.DeleteOnSubmit(vw2)
dc.SubmitChanges

Open in new window


However, this will not:
Dim dc as New MyDataContext
Dim vw1 as New view1
vw.id = "UniqueID"
dc.view1s.InsertOnSubmit (vw)
dc.SubmitChanges

vw1.field1 = "Field 1 Update"
dc.SubmitChanges

dc.view1s.DeleteOnSubmit(vw1)
dc.SubmitChanges

Open in new window


The disconnect is because I can't find a way to communicate to the original vw1 object that it is now inserted and should act like it.  When I remove it from the ChangeSet it just becomes orphaned and subsequent changes to the same object aren't listed in the ChangeSet
mcorrenteAuthor Commented:
Thanks for your help, Fernando.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.