Entity framework raw SQL Query

Question

I have to select multiple columns from a database and I don't have a matching entity. so my query looks like this:

var result = _dbContext.Database.SqlQuery<List<string>>(
             "select ID, NAME, DB_FIELD from eis_hierarchy");

I am getting the result set, each row contains list of strings but count is 0.

So how do I select multiple columns using Database.SqlQuery?

Gert Arnold · Accepted Answer · 2015-01-20 13:50:06Z

50

You have to capture the results into a class with matching property names, and (at least) a parameterless constructor:

class DbResult
{
    public int ID { get; set; }
    public string NAME { get; set; }
    public string DB_FIELD { get; set; }
}

var result = _dbContext.Database.SqlQuery<DbResult>(
                 "select ID, NAME, DB_FIELD from eis_hierarchy");

edited Jan 20, 2015 at 13:50

answered Aug 7, 2013 at 7:06

Gert Arnold

110k36 gold badges218 silver badges319 bronze badges

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

2 Comments

BCA Over a year ago

I found this to work well--except when the SP result field has a name like "unallocated space", which obviously can't be used as a .NET property name. Anyone have an idea what to do in that case?

Gert Arnold Over a year ago

Probably use an alias: select [unallocated space] AS unallocated_space from Table.

Mehdi Qorbanpur · Accepted Answer · 2023-04-19 09:45:30Z

0

Using C# version higher than 6, it can be written in this way as well:

var result = _dbContext.Database.
                SqlQuery<(int ID, string NAME, string DB_FIELD)>("select ID, NAME, DB_FIELD from eis_hierarchy");

edited Apr 19, 2023 at 9:45

answered Apr 19, 2023 at 9:38

Mehdi Qorbanpur

215 bronze badges

Collectives™ on Stack Overflow

Entity framework raw SQL Query

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related