CODEWITHSUNDEEP
c#.netlinqlinq-to-sql
Lee Tickett
Lee Tickett

Reputation: 6037

Can I update LINQ-to-SQL records retrieved with db.ExecuteQuery()?

My query is dynamically retrieved from the app.config. I know that certain columns will be included. I want to update a column in each record returned after processing it. Can I do this using .SubmitChanges() or similar? Or will I have to write another db.ExecuteQuery() with the update sql?

DataClasses1DataContext db = new DataClasses1DataContext();
IEnumerable<invoice> invoices = db.ExecuteQuery<invoice>(ConfigurationManager.AppSettings["query_sql"].ToString());
foreach (invoice i in invoices)
{
    // do something with the invoice
    // flag the invoice as processed
    i.doc_processed = "Y";
    db.SubmitChanges();
}

UPDATE

Thanks to madd0's answer I reworked the way my application allows dynamic SQL. Instead of entering a query in the app.config, the application looks for a SQL view with a specific name. This way the view can be added and LINQ will automatically generate the type(s)/class(es) and allow tracking/updating using .SubmiteChanges()

Upvotes: 0

Views: 1037

Answers (2)

madd0
madd0

Reputation: 9323

In order to use SubmitChanges to update your entities, Linq to Sql must be able to track them. The easiest way to do this is to allow Linq to generate your types (alternatively, you can also decorate POCOs with a bunch of attributes.)

If your invoice type was not generated so that Linq to Sql can track it, it will be impossible for the DataContext to determine which objects have changed and to generate the proper UPDATE statements when SubmitChanges is called.

If you are using tracked entities and ObjectTrackingEnabled is set to true, but will want to use ExecuteQuery<T> instead of letting Linq to Sql generate your query, you still have to make sure that your SELECT command returns all tracked fields and that your table has a primary key (this is explained in the documentation of ExecuteQuery<T>.)

Finally, as I explained in my original answer, you can always manually execute an UPDATE query with ExecuteCommand. This is how I do most of my updates because it is much more effective in most cases, especially when updating large a large number of entities, since Linq to SQL will execute individual UPDATE commands on the server for each of the updated records when using SubmitChanges, whereas if you do it by hand you will most likely be able to write it as a single command.

If you do decide to create your own UPDATE statements and execute them with ExecuteCommand, please remember to use parameters and not use concatenation to do so in order to limit SQL injection attacks. Examples of parameterized queries are available in the documentation for ExecuteCommand.

Upvotes: 1

Pleun
Pleun

Reputation: 8920

As allways, it depends.

In your case it depends on the // Do something with the invoice. If you anyway touch a lot of fields of each invoice in that parts, this code is fine. Except for the fact that you can move the submitchanges outside the loop:

  foreach (invoice i in invoices)
    {
        // do something with the invoice
        // flag the invoice as processed
        i.doc_processed = "Y";
    }
 db.SubmitChanges();

so you will have the benefit of a single transaction (still multiple updates though, so with large amounts of invoices performancewise it will not be the best).

If the // do something is relatively plain and simple, I would code the entire // do something logic inside a query and execute that inside the sql, effectively reducing your code to

db.ExecuteQuery<invoice>(ConfigurationManager.AppSettings["do_something_update_query_sql"].ToString());

This way it will be most performant, but you do not use any of the ORM parts of Linq2sql.

Upvotes: 0

Related Questions