At first i thought that linq2sql may be a problem because using same language to communicate with List object, xml document and database seems like mission impossible. What makes me wonder is that all these smart-ass guys at microsoft thought that's an excellent idea. Well, you can communicate with people from different countries without using their native language but that's kinda hard and you just can't express everything. That's the problem that linq suffers from. There are some more analogies. Some people may learn common language like english and then they can communicate pretty well with each other. However, that demands other people to learn language other than their native. Same thing with linq providers. If every datasource had well written linq provider maybe it would be really nice to query everything with linq. Well, i highly doubt that there are good linq providers for even most important datasources. What is more, even if you can speak english because you've learnt to do so, in most cases you won't be able communicate as fluently as in your native language and expressing more complicated/specific thoughts may still be a problem. And again, i'm experiencing same thing with linq. You just can't create common interface for every datasource without creating some methods that will be ignored in most of the datasources because they exist only for the sake of one particular medium.
Enough analogies, i will give some examples.
In this part i want to show one problem that i lately ran into. This problem may seem to be a bit rare but i think it opens whole range of problems that you may encounter using linq2sql. In second part i will provide examples of problems that you may encounter in even simple projects.
I would like to thanks stackoverflow and especially sambo99 for helping me to solve this problem in general.
The details of the problem are here: http://stackoverflow.com/questions/959501/problem-with-deadlock-on-select-update.
To sum up i need get a record from database and then update it. However i was running into a problem when more than one thread tried to this at the same time. Some more explanation. Suppose there are two threads - thread A and thread B. The sequence of events may be as follows:
A: select record
B: select record
B: update record
A: update record
As you can see thread A was updating data that thread was changed by thread B - obvious conflict.
OK, I thought. Transaction at Serializable Isolation Level will solve the issue. So i created a transaction and what i've got?
Exception: Transaction (Process ID 59) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Wtf? After reading some articles about deadlock, changing indexes in table, rewritting the code for datasets and then even for simple sqlcommands, creating simple test table i went to stackoverflow and fortunately got an answer. Sql server needs a little hint. I need to select the row with (updlock). I tried using some hints before but they didn't work, finally updlock worked. Hurray.
Wait... Hurray? But what about linq? Awww....
Whispering: You just can't do it with linq.
You just can't do it with linq.
But smart guys from microsoft came up with some interesting ideas.
Solution 1: create a stored procedure (you must be kidding me... i'm using your wonderful orm just to hear - don't use it - write a stored procedure instead)
Solution 2: you may still write a query by hand and execute on database, sth like IEnumerable
Some more details:
More examples in part 2, coming soon ;)