So, you've read my previous tutorial, learned how to set up a DataContext and retrieve
data from it and read about the common pitfalls of learning LINQ... or you've decided
you're too good to have to read my drivel on the basics and just skipped ahead to
actually start learning how to use LINQ in a way that's helpful.
In this tutorial I'm going to be covering returning more complex annoymous types,
using joins and updating and inserting data back in the database.
Returning data
In the last tutorial I gave a really simple example of a select statement: var results = from t in testDb.TableName select t.Field.
That little ditty gets you back just one field. Since there's only one field to
deal with the .NET compiler can assign the variant a basic type and you can happily
program as if you've just pulled a string or int out of your proverable hat. Most of the time though you'll be wanting to get at least
a couple of fields back at a time. Here you've got two options;
you can either return a set of records matching the format of one of the tables
of the database or you can return any set of fields you like using an anonymous
type. There are advantages and disadvantages to both - if you want to directly update
the records you get back you'll need to use a record type from one of your datacontext
tables, if you want to return information from different tables as part of one record
then you'll need to use an anonymous type.
So, first example: Returning a record matching the type of one of the tables. Instead of typing "select t.Field" at the end, just type "select t" at the end. This should return an enumerable set of classes strongly typed to match the format of the record in the table.
Returning an anonymous type is also pretty simple. Just replace the "select t.Field" with "select new { AnonField1 = t.Field1, AnonField2 = t.Field2, t.Field3 };". Note that you don't need to assign names to each field of an anonymous type, you can simply give one of the original records fields and the name of that field will be preserved.
Don't forget that you can't pass anonymous types between functions - the compiler isn't that clever (yet). If you want to pass a result set between functional scopes you'll need to define a type to hold your results in and use the select statement to call a constructor for that type. Also, remember that binding doesn't occur until a function actually uses the data so if you do want to pass the result set around you'd be best off calling the .ToList() function on the results in order to bind the result set to a variable.
Using joins