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!

Elconin

@BFree - editing a question simply to change someone else's coding style is just plain rude IMHO. Please don't. – Elconin

My bad. I didn't think you actually meant to do that, I thought it was an accident. – Mccorkle

@Daniel - posting code that forces people to scroll right to read it is a great way to get them to ignore your question. Bitching at people trying to help is an even better way..... – Brigand

Well I guess I'll let the people that can't be bothered to scroll a little bit answer other folks' questions :) – Elconin

Answer 1

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.

Elconin

I witnessed this fix via SharedView, and I'm baffled by it as well. – Encroachment

You don't even need the Name if you're doing it this way. You can just say [Table]. – Mccorkle

Just noticed it also works for more complex query's I just did a recursion query and then selected from it and it works. – Deflation

Answer 2

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).

Mccorkle

I've never had an issue with mapping as long as I specify the correct column name in the mapping attribute... though this would've made about as much sense as what the problem actually was, ha (see my answer) – Elconin

Well typically when you drag the tables / stored procedures on to the Linq to Sql desinger, it does all that for you, so you don't pay attention to the table attributes. You focus more on the column stuff. Learn something new every day... – Mccorkle

I stopped using the designer when I started getting random build errors using it. I find it just as easy to write up the properties like I've done above, and it's easier to manage in the project (IMHO), cleaner code, etc. – Elconin