How to retrieve multiple columns from non-entity type sql query?

Question

I have a view in database with several columns. I am trying this query

public class TestEntity
{
    public string ref { get; set; }
    public string Name { get; set; }
    public string Batch { get; set; }
}
var res = dbContext.Database.SqlQuery<TestEntity>("Select * from dbo.MyView").ToList();

but this returns list of objects with only null values and no data. However, when I try to retrieve single column like this it works

var res = dbContext.Database.SqlQuery<string>("Select Name from dbo.MyView").ToList();

I have noticed that the problem is with the TestEntity because when I use string instead of TestEntity it works. Any suggestion what am I doing wrong here?

Filter the NULL records -> WHERE Col1 IS NOT NULL AND Col2 IS NOT NULL.. — sagi
– sagi, Commented May 24, 2016 at 12:39
I'm sorry. I wanted to say that everything is null. All the result — fredzyadi
– fredzyadi, Commented May 24, 2016 at 12:40
are the column names in the database the same as in your class? otherwise you'll need a modelBuilder in your context or change your SQL query to "SELECT ... as ref, ... as Name [...]" — DevilSuichiro
– DevilSuichiro, Commented May 24, 2016 at 13:36

Mayur Siddhapura · Accepted Answer · 2016-05-24 14:00:29Z

11

just replace the below code

var res = dbContext.Database.SqlQuery<TestEntity>("Select * from dbo.MyView").ToList();

with this and try again...

var res = dbContext.Database.SqlQuery("Select * from dbo.MyView").ToList<TestEntity>();

and if still that is not working then you need to check for your

TestEntity

and your

dbo.MyView

for same columns. because if there is different columns in MyView and TestEntity then it will not work...

If you change the column name in query, then it will throw an exception or it'll not work properly...

if this'll help you then don't forget to mark...

Thanks...

edited May 24, 2016 at 14:00

answered May 24, 2016 at 12:56

Mayur Siddhapura

2962 silver badges6 bronze badges

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

3 Comments

DevilSuichiro Over a year ago

I do think both of these lines are equal, since object materialization is done on .ToList() anyways.

Mayur Siddhapura Over a year ago

Did you try it or not?

fredzyadi Over a year ago

Yes the problem was mismatched names. I had dashes ("-") in my sql view where as c# doesn't support this syntax so entities were without dashed. That was the reason. Thank you for your help.

Collectives™ on Stack Overflow

How to retrieve multiple columns from non-entity type sql query?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related