I'm still very much just feeling my way tentatively forward with LINQ to SQL, but my first reaction is one of relief that it really is very similar to the LINQ to Objects approach and so the learning curve is minimal. Of course, there is the small matter of connecting to database that isn't a factor in LINQ to Objects, but so far the use of DataContext has been painless. (Hope I'm not celebrating too soon!)
A new Hands On Labs that is now available from the Visual Basic Team is very good and I recommend you download it and give it a try. You can get it via the VBFeeds web site here. As I don't think I can improve on that resource, I won't run through a whole list of examples as I did with LINQ To Objects. May be better if I just highlight some points that caught my attention. (This latest Hands on Lab, by the way, has been updated to run under Orcas Beta 1.)
Distinct
I don't know about you, but I find it hard to think of "Distinct" without the prefix of "Select" as in the standard SQL Query syntax of "SELECT DISTINCT". For whatever reason, in LINQ to SQL you do have to pull them apart though as the compiler won't recognise the phrase "Select Distinct" in a LINQ to SQL Query.
If I can claim to have done enough LINQ work to develop habits, I guess I'd say that I'm in the habit of using the query format of: (Pseudocode)
Dim x = From element in collection _
Where typedobject = SomeValue _
Order By somecolumn _ Select expression
i.e. writing the guts of the query before finishing it off with the Select line. It seems though that with a LINQ to SQL Query that uses the Distinct keyword, the format has to be:
Dim ids = From Customer In db.Customers _
From emp In db.Employees _
Where Customer.City = emp.City _
Select emp.EmployeeID Distinct
or:
Dim ids =
From Customer
In db.Customers _
From emp
In db.Employees _
Where Customer.City = emp.City _
Select emp.EmployeeID _
Distinct
of course.
Just as a minor note of warning, if you do use this query format:
Dim ids = From Customer In db.Customers _
From emp In db.Employees _
Where Customer.City = emp.City _
Distinct _
Select emp.EmployeeID
where the Distinct keyword is inserted prior to the Select keyword, you won't get a syntax error flagged up and the query will compile. However you won't get a distinct list as the result; you'll get all EmployeeIDs (repeated) that match the criteria.
Syntax Checking
Something else you have to get used to while writing out LINQ queries is the syntax editor barking at you halfway through some queries. This might just be because I'm still working with a Beta version and may not happen once we get to RTM. However it can be off-putting if you think you've written the code correctly (but haven't finished it) and you get various error messages that subsequently prove not to be errors.
One self-inflicted error (that actually is an error!) is to forget to insert the space and underscore at the end of a line in an incomplete LINQ Query. Sometimes when you're concentrating so hard on getting the syntax just right this is a really easy thing to forget. The error messages vary, but never refer to the missing underscore, but - understandably - refer to the next line of code which the compiler won't be able to understand because of the missing punctuation mark. So the first thing to check before trying to figure out what the supposed error might be is that you haven't forgotten that space and underscore. It'll soon become second nature - trust me!
Going back to the Hands On Labs, I quite like the way it begins by demonstrating how easy it is to use the built-in LINQ To SQL File Template to create a working query quickly and easily. It then digs deep down into the code to show how this is done under the hood and how you can hand build equivalents yourself if you want to. Personally, having worked through their examples, I was very happy to accept their advice which is:
"Generating the database table relationships and object model can be tedious and prone to error. We do NOT advise you take that approach but it's included in this lab for education.....
The easiest and best option is to use the new designer with its seamless Visual Studio integration."
Yay to that!