Venturing into the world of LINQ I'm determined to give
it a go because writing 3 -10 sps per table is just a pain in the bottom.
If you come from a database background one of the things that will concern
you is the SQL that LINQ generates. The good news is that you can capture all
the SQL that LINQ generates by using the Log property of you data context. The
following sets the PubsDataContext to log to the file c:\log.txt
PubsDataContext
dc = new PubsDataContext();
TextWriter tw = new StreamWriter("c:\\log.txt");
dc.Log = tw;
What I noticed straight away was updates, by
default when an update is performed, irrespective of what columns are changed,
all the columns are specified in the WHERE clause. This is used to enforce
optimistice locking. This is to ensure that when you update data it hasn't
already been updated by someone else. The downside however is that every column
has to be checked.
You can change this behaviour on each column in the LINQ to SQL model that is
generated by adding the UpdateCheck attribute i.e.
[
Column(Storage="_col2",
DbType="Int",
UpdateCheck=UpdateCheck.Never)]
public
System.Nullable<int> col2
{
get...
However its a bit of a pain to do that on each column in each
table. However there is light at the end of the tunnel. If you have a timestamp
on the table only this column will be check to verify the record has't
changed.
You might be still asking why I want this at all. Imagine your bank account
(£100 balance), you've cash a cheque £50 and you use the cash machine to
withdraw £100, what if it happens that the bank pays the cheque into your
account at the same time you use the cash point, both read the balance at
£100, the chequed is cashed updating it to £150, but then the cash point
withdrawl updates the £100 balance with the £100 withdrawl leaving you with a
balance of £0. What happened to the cheque?
If the each transaction checked that the record had not been updated then it
would have realised that the data had changed and the update would have failed.
The other option is pessimistic locking, which puts a lock on a record for
the duration that the record is being read until it is updated. This is not very
good for concurrency as locks cause blocking and so the number of users you
application will be able to support will not be very high.