What is the quickest way to query a database with LINQ?

Question

I'm reading a WROX book on LINQ and the author is performing LINQ on a database. Essentially he is accessing the database as an object as shown in the code below.

But I don't see how he expects to "access the database as an object", even the downloaded code gets an error on "db.DirectoryInformation" saying "DirectoryInformation" is unknown.

What am I missing? I would think I first need to create LINQ-to-SQL classes or an ADO.NET EDM or is there even a more direct way to hook LINQ up to a database, i.e. just by creating a database class and that inherits from DataContext?

AdventureWorks db = new AdventureWorks("Integrated Security=sspi");

...

    [Database(Name = "AdventureWorks")]
    public class AdventureWorks : DataContext
    {
        //public Table<DirInfo> DirectoryInformation;
        public AdventureWorks(string connection) : base(connection) { }
        public Table<DirectoryInformation> DirectoryInformation;
    }

You can download the whole code here, chapter 1, LINQ.sln.

Answer 1

Oh, absolutely you can use vanilla objects with LINQ-to-SQL; you don't even need to subclass DataContext - but you do need to tell it about your model. This is often done with attributes on members (for columns) and types (for tables), but can also be done with an external mapping file (xml). I wonder if they are just over-abbreviating for simplicity... for example, I suspect that the table should be a property:

public Table<DirectoryInformation> DirectoryInformation {
    get { return GetTable<DirectoryInformation>(); }
}

The whole "dbml" thing is just there as a designer tool to help you generate the classes; the important code is just decoracted classes (with some conventions on things like navigation properties to make life simper to use). That said, for "quickest": use the designer.

Answer 2

Look at the end of the Form1.cs source file, the LINQ to SQL database is declared using attributes:

[Database(Name = "AdventureWorks")]
public class AdventureWorks : DataContext
{
    //public Table<DirInfo> DirectoryInformation;
    public AdventureWorks(string connection) : base(connection) { }
    public Table<DirectoryInformation> DirectoryInformation;
}

[Table(Name = "DirectoryInformation")]
public class DirectoryInformation
{
    [Column(DbType="varchar(50)")]
    public string DirectoryName;

    [Column(DbType = "varchar(255)")]
    public string DirectoryDescription;
}

Providing the settings with the project define a connection string, this is all you need for a simple mapping of the DirectoryInformation type to the DirectoryInformation table in the AdventureWorks database.