none
SubmitChanges when datacontext is from a user-defined function RRS feed

  • Question

  • My datacontext is the result of a UDF, which I drag-and-drop from my database in the method pane. It seems that SubmitChanges doesn't work in this case. What should I do if I want to use the functions from my existing database? Thanks in advance.
    Monday, December 19, 2011 11:40 PM

Answers

  • To use L2S for updating L2S needs to know what to update.  A UDF returns nothing that can be updated.  

    To update the database you must (in the case of updates not inserts) retrieve the items from the database which you want to update, update the fields of those items and then submit your changes.

    If the CompanyDetailsBase is a table in your database and you wish to insert the new items then you will attach the items created in your query to the datacontext and then submit your changes.

    LS


    Lloyd Sheen
    Saturday, December 24, 2011 3:46 PM

All replies

  • Hi kzub,

    Welcome to MSDN Forum!

    The function only can returns a scaler value or a table, so I don't think it can return a datacontext, more information about it, please refer to User Defined Function.

    Here's a vedio about User Defined Function in LINQ to SQL, please refer to it, I think you can get help from it.

    User Defined Function in LINQ to SQL

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us
    Wednesday, December 21, 2011 9:45 AM
    Moderator
  • You are mixing things.

    The UDF operates within the database server and will pass back a scalar or table result.

    The term datacontext is used more on the client side with the term being used to describe a group of tables/functions/stored procedures that exist on your database server.

    SubmitChanges is used when you have a L2S object which is mapped to a table in the database.

    When you say you want to use the functions from your database what do you mean?  UDF's cannot modify the database so I'm confused as to what you are attempting.

    LS


    Lloyd Sheen
    Thursday, December 22, 2011 9:59 PM
  • First thank u all for your replies and suggestions.

    I obviously didn't properly expressed my idea. I'll better show u.

    Here is the code:

     

    Dim sapList = (From c In d.fnCompanyDetails(sap)
    
                            Select New CompanyDetailsBase With {
    
                .Sap = c.txtSAP,
    
                .CompanyName1 = c.txtCompanyName1, _
    
                .CompanyName2 = c.txtCompanyName2, _
    
                }).ToList()
    

    fnCompanyDetails is a UDF in my database. I drag-and-droped it in my dbml.

    Now if I change some values in this list how can I submit my changes back to the database?

     

    Saturday, December 24, 2011 3:32 PM
  • To use L2S for updating L2S needs to know what to update.  A UDF returns nothing that can be updated.  

    To update the database you must (in the case of updates not inserts) retrieve the items from the database which you want to update, update the fields of those items and then submit your changes.

    If the CompanyDetailsBase is a table in your database and you wish to insert the new items then you will attach the items created in your query to the datacontext and then submit your changes.

    LS


    Lloyd Sheen
    Saturday, December 24, 2011 3:46 PM
  • So, if I want to update a value I should know the table where this field resides, select it, change it and then submit changes?

    I'm new to LINQ. Using it for the first time, so I'm a little out :(...

     

    CompanyDetailsBase is not a table. It's an object.

    Saturday, December 24, 2011 4:13 PM
  • Without more details it will be difficult to help.  I would assume that the UDF will provide some information that will provide access to the rows/tables that you need to update.  If not then this approach will not work.

    LS


    Lloyd Sheen
    Saturday, December 24, 2011 9:49 PM