c# - Return Anonymous Type using SqlQuery RAW Query in Entity Framework

Question

How can I make Entity Framework SqlQuery to return an Anonymous type.

Right now I run a context.TheObject.SqlQuery() RAW query. the query joins two tables and I want to return the results of the joined tables.

If I use it with a type context.TheObject.SqlQuery() I only get to see the results of the table of that same type.

I tried db.Database.SqlQuery<DbResults>("the sql query here"); With a pre-defined class that matches the result's objects, but all the fields are null.

Using Entity Framework 6 with MySQL.

Answer 1

I'm going out on a limb here, and will try to address your underlying problem instead of directly answering your question.

Your scenario with the pre-defined class should work. A likely pitfall is that the column names and the properties of your class did not match up.

Sample code (LinqPad)

    var results = Database.SqlQuery<TestResult>("select r.Name, b.BankName from relation r inner join BankAccount b on b.RelationId = r.Id where r.Id = 2");
    results.Dump();
}

public class TestResult {
    public string Name { get; set; }
    public string BankName { get; set; }

I'd strongly advise you to revisit your problematic code using explicit types.

---

In direct response to your question: no, you can't return anonymous types from SqlQuery. The best you can do is build dynamic objects, but that unfortunately requires a fair bit of manual work using TypeBuilder. See http://www.codeproject.com/Articles/206416/Use-dynamic-type-in-Entity-Framework-SqlQuery for a sample.