How to read values with different datatype in Entity Framework?

Question

I have a query (simple SELECT statement) which returns Id and Name where Id is int and Name is string. The query returns result in SSMS. I am trying to get that record using EF in my MVC application. I can get that value if I create custom class but not of I use dictionary. Second option doesn't throw any error but it comes with zero record.

How can I read the value without creating custom class?

C# Logic:

// Option-1 = Works
var sql = @"SELECT UnitNumber, 
                   UnitName
            FROM dbo.MyTable 
            WHERE Id = @p0
            AND ScriptId = @p1";

var result = context.Database.SqlQuery<MyClass>(sql, callCenterId, id);

public class MyClass
{
    public int UnitNumber { get; set; }
    public string UnitName { get; set; }
}


// Option-2 = Doesn't Work
var sql = @"SELECT UnitNumber, 
                   UnitName
            FROM dbo.MyTable 
            WHERE Id = @p0
            AND ScriptId = @p1";

var result = context.Database.SqlQuery<Dictionary<int, string>>(sql, callCenterId, id);

SQL Query:

SELECT UnitNumber, 
       UnitName
FROM dbo.MyTable 
WHERE Id = 1
AND ScriptId = 10

SQL Output:

UnitNumber  UnitName
----------- -----------
9           Universal

I wouldn't expect option #2 to work. The query is not returning columns that can be converted to a dictionary. Just use a class. — Dan Wilson
– Dan Wilson, Commented May 8, 2018 at 20:23

Harsh · Accepted Answer · 2018-05-08 20:26:25Z

2

You need to project your resultset into a dictionary, like this :

var result = context.Database.SqlQuery<MyClass>(sql, callCenterId, id)
             .ToDictionary(o => o.UnitNumber, o => o.UnitName);

or

var result = context.Database.SqlQuery<MyClass>(sql, callCenterId, id)
         .Select(x=> new KeyValuePair<int, string>(x.UnitNumber, x.UnitName))
         .ToDictionary(x=>x.Key, x=>x.Value);

answered May 8, 2018 at 20:26

Harsh

3,7713 gold badges30 silver badges42 bronze badges

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

1 Comment

GThree Over a year ago

This make sense. Thank you.

Collectives™ on Stack Overflow

How to read values with different datatype in Entity Framework?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related