1

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

kzub
  • 232
  • 3
  • 13

1 Answers1

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.

Anders Abel
  • 67,989
  • 17
  • 150
  • 217
  • 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? – kzub Mar 07 '12 at 13:14
  • 1
    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. – Anders Abel Mar 07 '12 at 13:21
  • 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 – kzub Mar 11 '12 at 13:20