Raw SQL on DbContext with computed column

Question

I need to run a raw SQL query and return a calculated column. The query matches the properties of a POCO class. The query runs fine when running against the DB but the calculated column is returning null

public class PocoClass
{
    public int ID { get; set; }

    [NotMapped]
    public DateTime? CalculatedDate { get; set; }
}

Query fake code (actual query more complex):

string sql = @"SELECT ID, max(date) as CalculatedDate 
               from randomtable
               where ID = 1
               group by ID
               order by CalculatedDate";

var result = db.Database.SqlQuery<PocoClass>(sql).ToList();

I thought using the "as CalcluatedDate " would allow the result to include that field but its not working.

What am I missing?

@Rafa, ID comes back accurate; CalulatedDate comes back null for every row. When I run the query against the DB itself, the CalculatedDate field is accurate. — apierceSO, Mar 04 '14 at 23:47
What about the "NotMapped" attribute? Have you tried to remove it? — Rafa Paez, Mar 05 '14 at 00:00
The [NotMapped] attribute is needed because I don't want a column added to the database. Perhaps I'm using this wrong or perhaps there is another solution. I don't even want to attempt to try this solution because then I'll have to undo it get the column out of the db. — apierceSO, Mar 05 '14 at 13:56

CJBS · Answer 1 · 2014-03-05T18:14:54.907

0

If using Code First, try adding the DatabaseGenerated attribute to the CalculatedDate field:

[DatabaseGenerated(DatabaseGeneratedOption.Computed)]
public DateTime? CalculatedDate { get; set; }

If using database first, and you've generated your classes in the designer, open up the model (.edmx file), and navigate to the column on the entity in question. Then, in properties, set the StoreGeneratedPattern to Computed, and then regenerate the model.

edited Mar 05 '14 at 18:14

answered Mar 05 '14 at 00:20

CJBS

15,147
6
86
135

strike my comment...this works but the column is added to database which is what I'm trying to avoid (in other words, not sure what this accomplishes differently than if I simply removed "NotMapped" – apierceSO Mar 05 '14 at 14:59
1

This approach will only work for code first (as opposed to DB first). If you're using DB first, and you've generated your classes in the designer, open up the model (.edmx file), and navigate to the column on the entity in question. Then, in properties, set the StoreGeneratedPattern to Computed, and then regenerate the model. – CJBS Mar 05 '14 at 18:10
I'm using Code First -- I've used your suggestion, but as I said in my prior comment, the field is now added as a column in the database, which is what I was trying to avoid. – apierceSO Mar 05 '14 at 20:44
These links might be helpful: http://stackoverflow.com/questions/6944904/how-should-i-access-a-computed-column-in-entity-framework-code-first/6995622#6995622 and http://stackoverflow.com/questions/14264750/how-to-call-stored-procedures-with-entityframework – CJBS Mar 05 '14 at 22:18

Raw SQL on DbContext with computed column

1 Answers1