SubmitChanges with LINQ to SQL and User Defined Functions

Question

On the SQL server I create a UDF. Let's name it fnCompanyDetails. It selects some information about a company from several joint tables. I then drag-and-drop this function in the new .dbml of my VB project. I want to use it with LINQ. Smth like this:

Dim company = (From c In d.fnCompanyDetails(sap)

                    Select New With {

        .Sap = c.txtSAP,

        .CompanyName1 = c.txtCompanyName1, _

        .CompanyName2 = c.txtCompanyName2, _

        })

The result of this query I display to the user in a form. If some field is changed I want to send the changes to my database, but is this possible if I'm quering like this, from a UDF? Thank y

Anders Abel · Accepted Answer · 2012-03-07 09:31:57Z

1

No, it is unfortunately not possible to do in a simple way. Linq-to-sql supports reading to custom types, but only support updates through the entity types that exactly corresponds to a table.

Normally the best way is to always read pure entity objects if they are read with the intention to update them.

Another solution is to create entity objects out of the data returned from the udf and then attach those entities to the context. If you attach the entities in their original state first and then make changes after attaching, you should get away without any change tracking problems.

answered Mar 7, 2012 at 9:31

Anders Abel

69.6k18 gold badges154 silver badges221 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

kzub Over a year ago

Thank you, Anders! With those entity objects - do you mean that this object should repeat the structure of the database table in order to be possible to upload the changes to the tables? And in the case I want to have all my database logic (all UDF, SP, views etc.) on the SQL server (I mean not to build them with LINQ in the code) won't it be better to work with dataset, than to go into LINQ?

Anders Abel Over a year ago

The entity objects are created automatically for you if you drag a table onto the designer surface. The intention with linq-to-sql is to place (nearly) all of the business logic in the code. If you are using UDFs or SPs, then there might be other options that are better.

kzub Over a year ago

In this case I don't quite understand the entire concept of the LINQ :( if I must select the entire table, which then can be mapped to the generated class and from there be updated and inserted on. I tried to create the entity object "tblCompany" from the result returned by my UDF, but I got the error "Explicit construction of entity type 'tblCompany' in query is not allowed". So this approach doesn't seem to work too :( I think I should better go on with classical ADO.Net

Collectives™ on Stack Overflow

SubmitChanges with LINQ to SQL and User Defined Functions

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related