LINQ-to-SQL: ExecuteQuery(Type, String) populates one field, but not the other

Question

I've written an app that I use as an agent to query data from a database and automatically load it into my distributed web cache.

I do this by specifying an sql query and a type in a configuration. The code that actually does the querying looks like this:

List<Object> result = null;
try { result = dc.ExecuteQuery(elementType, entry.Command).OfType<Object>().ToList(); }
catch (Exception ex) { HandleException(ex, WebCacheAgentLogEvent.DatabaseExecutionError); continue; }

elementType is a System.Type created from the type specified in the configuration (using Type.GetType()), and entry.Command is the SQL query.

The specific entity type I'm having an issue with looks like this:

public class FooCount
{
    [Column(Name = "foo_id")]
    public Int32 FooId { get; set; }

    [Column(Name = "count")]
    public Int32 Count { get; set; }
}

The SQL query looks like this:

select foo_id as foo_id, sum(count) as [count]
from foo_aggregates
group by foo_id
order by foo_id

For some reason, when the query is executed, the "Count" property ends up populated, but not the "FooId" property. I tried running the query myself, and the correct column names are returned, and the column names match up with what I've specified in my mapping attributes. Help!

Answer 1

Linq To Sql has a hard time mapping stuff when the names of the properties are different than the names of the columns. Try changing your property name to foo_id with the underscore. That should to the trick.

Either that, or you can change your select statement to foo_id as FooId to match your property. Either way, they should be the same (don't need to be the same case though).

Answer 2

This is insane...

What fixed my problem was decorating my entity class with TableAttribute:

[Table(Name = "foo_aggregates")]
public class FooCount
{
    [Column(Name = "foo_id")]
    public Int32 FooId { get; set; }

    [Column(Name = "count")]
    public Int32 Count { get; set; }
}

I had assumed (wrongly, apparently) that since I wasn't using the GetTable<T>() method, I didn't need the corresponding mapping attribute.

Update: A year and a half later, it finally dawned on me it seems like the ColumnAttribute decorations on the properties are ignored unless there's a corresponding TableAttribute decoration on the class. This explains why the "Count" property was getting populated, since its naming would match the column in the SQL statement, whereas FooId/foo_id of course do not match.