Fun with LINQ: Reactions and tips from a n00b
My latest app, being built with Blend, has left me toiling in Visual Studio for quite a while. Incidentally, I highly recommend making the jump to VS08 if you have the means. One of the reasons for that is the integration with LINQ, which has been occupying most of my time lately. I had mixed feelings coming in to write my application, which is essentially a nice user interface for working with a very specific type of database (I won't be putting all the details up for a while yet because this application is big, IMHO). To begin with... I hate writing DB code. Interacting with a relational database is fine; all the logical hoops you can jump through, properly designing your DB for maximum flexibility, picking out just the right query for the occasion; I find all that stuff quite fun. What I absolutely, positively loathe is the simplest part of making things work with DB apps. How do I connect? There are wizards in VS that do it (and those helped me out immensely), but still even opening the connection in code is not remotely obvious, and there is a huge hump on the learning curve for someone wanting to build an app from scratch. It took me about two weeks of sifting through code to figure out how to get access to my connection string:
string connectionstring = MyProjectName.Properties.Settings.Default.DBNameConnectionString;
-- for the curious. Then you can do everything you need with just a little connection = new OleDbConnection(connectionstring); and connection.Open(), etc. After that the curve lightens up and you are left to the mercy of the logic of your queries and the quality of your DB design. And yes, I used Access to create my DB. I know you can go more nuts and be a powerhouse with a SQL DB, but... I just wanted to drag and drop... and the DB doesn't have to be terribly big. This isn't a banking application or something that serves millions of customers; it is meant to be run by one person on one computer for that person's benefit.
The facts (as I'm willing to give them out at this time) are these: this is a DB app for a user who doesn't need to know that she's doing DB interactions. It just keeps track of a specific kind of data, and allows her great flexibility with the different types of data she stores relating to a central theme.
I used LINQ to handle my DB interactions at some risk -- nobody on my team has ever used it and I don't have a lot of sources to go to if I run into trouble. I have a book (Introducing Microsoft LINQ by Paolo Pilorsi and Marco Russo, which covers the relevant topics pretty well), and people who know a bit about DB interaction to give me pointers when I need them. The good news is that I have LINQ doing a lot of nontrivial things pretty well right now, and haven't been tripping over myself to get them done.
Figuring out your DB connection stuff is no different with LINQ than it is with older ADO stuff. You still need to open connections, make connection strings etc., and there is no reason not to do that from the Data -> Add New Data Source menu commands in VS. The first few pages of any ADO book will explain what is going on and how to do it. Be ware that if you are using VS 2005 and your database is created in Access 2007, you will need to save your DB in Access 2003 format, as the old VS doesn't recognize the new format.
The neat thing about LINQ is that it allows you to treat your tables like objects and get a bunch of autocomplete help when writing queries, along with design-time compiling that can tell you when you've written a query that has a major flaw just like VS tells you when you've forgotten the semicolon at the end of the line. To do this, though, you need to define "Entity Classes" for each of your DB tables.
[
Table(Name = "Attributes")]
public class Attribute {
[
Column(Name = "ID", IsPrimaryKey = true, IsDbGenerated = true)] public int ID;
[
Column] public string AttributeName;
[
Column] public int AttributeType;
}
I believe there is a way to autogenerate these tables, though I didn't use that for my own program. You can do quite a bit with these classes, including making different tables act as subclasses of a master class for manipulation of the data in a more general fashion, or add a lot of the DB constraints in with these. You can also put functions into the class definitions if you so choose. I wrote my application in a pretty basic fashion and eschewed the fancy stuff, relying on the fact that I would be limiting the user's ability to manipulate the database via the UI and relying on my own ability not to do anything horrible. Anyhow, if you create these tables manually that can be a bit of busywork, but I've had to do worse things in code (back when I was learning Java we had to actually write out all our GUI code... heaven forbid).
Having done all that, you need to establish and open your connection, and then the querying is quite simple to get a hold of. You need to get a DataContext element
public
DataContext GetDataContext()
{
return new DataContext(connection);}
Then get the tables from the DataContext and make your queries. There is a little bit of a twist here though. A LINQ query typically runs FROM, WHERE, SELECT, a permutation of the way we are used to thinking about DB queries. It helps with intellisense and keeping with the class structure of the tables you've defined, and you get used to it quickly. I've turned off the pretty VS coloring for the next bit b/c pasting it into my blog causes ugly line jumps.... I really should get LiveWriter for this. Anyhow, a sample query.
try
{
DataContext db = this.handler.GetDataContext();
Table<AttributeTypeDisplayed> attributeTypesDisplayed = db.GetTable<AttributeTypeDisplayed>();
Table<ActorStringAttribute> stringData = db.GetTable<ActorStringAttribute>();
Table<Actor> actors = db.GetTable<Actor>();
var actorStringData=
from myActor in actors
join stringDatum in stringData
on myActor.ID equals stringDatum.ActorId
select new { stringDatum, myActor.ActorType };
}
First you get the tables, all of which you have Entity classes for. Then you simply write your query as the definition of a var. This var is an IQueryable, and you can put the results in a list like so: answerList.AddRange(actorStringData); I think the most interesting part of this query is the way that the results are output. Note that I SELECT a "new { stringDatum, myActor.ActorType};". I can now iterate through the responses and get to the results as follows:
foreach(actorStringDatum in actorStringData) {
this.DoSomething(actorStringDatum.stringDatum);
this.DoSomethingElse(actorStringDatum.ActorType);
}
The neat part is you can treat everything the way the part of your brain that thinks in C# (or any OO language) really wants to: like an object. I want to iterate through the results of my query. If I want to pull a handfull of different columns from my query, I want to just access them like I access fields of an object. I want the results of my query to function more or less the same as any other data type, like a List<> or Table<>.
You even have set operations:
var
currentUniverseActorTypes =
(
from actorType in actorTypes
join displayedType in displayedActorTypes
on actorType.ID equals displayedType.ActorTypeId
where displayedType.UniverseId == universeId
select actorType).Except(universalActorTypes);
It's all quite nice. Thus far, no bumps, other than making sure my queries get what I think they are getting. My results always act the way I want them to; like nice, iterable lists. I frequenly put them in Lists simply by using the List<>.AddRange() function so I have a specific type that can be passed back and forth among objects, making it easy for me to put all my queries in one class (and therefore not have the nightmare of making sure my DB connections don't proliferate) and pass the results nicely back to the class that actually needs them.
More to come on this topic later: specifically, the joys of databinding.
McDuffee out.