How to retrieve data with LINQ to SQL into a weakly-typed object that can be moved around

Question

In classic ASP we had the record set object. With ADO.Net we had the datatable.

Both were simple .Net objects that could be moved around easily.

What is the equivalent for using LINQ To SQL?

Most examples show "var" being used, however, this seems completely non-oo as you can't move a var around (without a cast hack). My understanding is that, in "proper" OO, you would never retrieve data and use it in the same place, so I can't see any value in using var with LINQ to SQL.

I understand that I can make a custom object for every single dataset I retrieve. However, I don't really want to do this as it seems like extra work and complexity. If I looped any of this data 1000 times w boxing/unboxing, I would do that. But in this case, I'm not looping and want to keep things simple, I'm not afraid to have a little boxing/unboxing, I can't imagine it affecting performance in a noticeable way.

Here is the code I'm working with:

using (ormDataContext context = new ormDataContext(connStr))
{
    var electionInfo = from t1 in context.elections
               join t2 in context.election_status
               on t1.statusID equals t2.statusID
               select new { t1, t2 };
}

Answer 1

This is how I would do it.

I would create a foreign key relationship (and index if appropriate) between the two tables. I would create a new Linq to Sql Classes object in my project, which creates a DBML file. I'd open the DBML file to get the designer surface for my new data context and drag both tables from the Server Explorer -- after creating the connection to the DB, of course.

This would create the entities matching the two tables in my data context and the association between them. This would give me an EntityRef in the Election entity corresponding to the Election_Status -- or an EntitySet depending whether it is a 1-1 or 1-Many relation. I'd use the association instead of doing a join to get the data for each election.

using (var context = new ormDataContext(connStr))
{
    foreach (var election in context.Elections)
    {
        Console.WriteLine( "{0}: {1}",
                           election.Name,
                           election.Election_Status.Status );
    }
}

If I wanted to filter by a particular status, I could do that as well.

using (var context = new ormDataContext(connStr))
{
    var query = context.Elections
                       .Where( e => e.Election_Status.Status == someStatus );

    foreach (var election in query)
    {
        Console.WriteLine( "{0}: {1}",
                           election.Name,
                           election.Election_Status.Status );
    }
}

Answer 2

If you are dead set on LINQ, you'll have to extract what you need from the variant ("electionInfo") return value and pass that around, instead of passing around the electionInfo variant itself. In some cases, this might mean making a custom object. (though, I don't know why this is such an issue, as you probably would have made a custom object anyway if you weren't using LINQ or if you were using classic ASP's record set).

You could also simply ignore LINQ and use the 'SqlCommand..ExecuteReader' method which returns a SqlDataReader. This is how most Sql queries were read prior to LINQ.

My advice? without going into too much detail, I think the advantages of LINQ outweigh the extra boilerplate code that is needed to use SqlDataReader.

Answer 3

You should qualify what you mean by "move around". Much of what you might have done in Classic ASP is now considered to be a bad practice, be careful not to repeat well known mistakes which have had solutions for some years now.

Also, "var" is strongly-typed, just anonymous: "no name", not "no type".

Please say what you mean about "proper OO" not permitting data to be both fetched and used in the same place.

Also, remember that examples are meant to show off individual features. No single example is going to show you everything. In particular, the answer to "how do I return an anonymous type" is "you don't return anonymous types". For instance:

using (ormDataContext context = new ormDataContext(connStr))
{
    var electionInfo = from t1 in context.elections
               join t2 in context.election_status
               on t1.statusID equals t2.statusID
               select new ElectionWithStatus { Election=t1, Status=t2 };
}

Where:

public class ElectionWithStatus {
    public Election Election {get;set;}
    public ElectionStatus Status {get;set;}
}

So, bottom line, if you have to move it around, then move a strongly-typed object, not a weakly-typed object.