Trying to return data from a .SqlQuery just returns nulls

Question

I am trying to run a SQL Query against a database:

public partial class Report2
{
    public string Col1 { get; set; }
    public string Col2 { get; set; }

}
var query = db.Database.SqlQuery<Report2>("Select Substring([English],1,1), Count(1) From Phrase Group by Substring([English], 1, 1)");

Running the select manually gives me 36 rows with values in two columns just like I would expect.

However when I run the EF version it gives me 36 objects but col1 and col2 are null in all of them.

Does anyone have any ideas why the results in Report2 would just be all nulls?

can you please try to modify your query to Select Substring([English],1,1) Col1, Count(1) Col2....? — Adil Mammadov
– Adil Mammadov, Commented Oct 5, 2016 at 6:54
you select a column named Substring and Count (most likely) from your database and map it to an object of col1 and col2. since no entries are in col1 and col2 for each row, null is returned. try SELECT Substring([English],1,1) as Col1, Count(1) as Col2 From Phrase... and set col2 to an Integer. — DevilSuichiro
– DevilSuichiro, Commented Oct 5, 2016 at 6:56
I think that helped but now I get a message saying: exceptionMessage=The specified cast from a materialized 'System.Int32' type to the 'System.String' type is not valid. — Alan2
– Alan2, Commented Oct 5, 2016 at 7:55

Benjamin Soulier · Accepted Answer · 2016-10-05 09:16:46Z

1

2 issues in your code:

first, you need to name your columns to map them to your model names:

Select Substring([English],1,1) AS Col1, Count(1) AS Col2 From Phrase Group by Substring([English], 1, 1)

second, your second field needs to be an int and not a string (as count returns an integer), that's why you get an error message like 'System.Int32' type to the 'System.String' type is not valid

public partial class Report2
{
    public string Col1 { get; set; }
    public int Col2 { get; set; }
}

answered Oct 5, 2016 at 9:16

Benjamin Soulier

2,2931 gold badge20 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Trying to return data from a .SqlQuery just returns nulls

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related