Execute complex raw SQL query in EF6

Question

Execute complex raw SQL query in EF6

dbcontext entity-framework entity-framework-6 sql-server

Question

I am developing a web api using Entity Framework 6. I have to execute a complex SQL query which is getting data from multiple tables as shown in the code. I have tried but get the following error:

The data reader has more than one field. Multiple fields are not valid for EDM primitive or enumeration types.

The query successfully return data in SSMS Query Analyzer.

[HttpGet]
public IHttpActionResult getJobNo(string cmpCode, string branchCode)
{       
    string query = string.Format(
        @"select 
            M.S1, M.S2, C.S2 As S30, M.S3, SC.S2 As S31, M.D1, M.S5,
            JT.S2 As S32, M.S6, TM.S2 As S33, M.S10 
         from 
            MERTRM M, CMF C, CMFS SC, PMCD JT, PMCD TM 
         where 
             M.S100 = 'JOB' and M.S102 = '{0}' and 
             M.S108 = '{1}' and 
             M.S101 not in('U', 'C', 'F') and 
             M.S2 = C.S1 and C.S102 = '{0}' and 
             C.S100 = 'CC' and M.S3 = SC.S1 and SC.S102 = '{0}' and 
             C.S1 = SC.S109 and M.S5 = JT.S1 and JT.S102 = '{0}' and
             JT.S100 = 'JTP' and M.S6 = TM.S1 and TM.S102 = '{0}' and
             TM.S100 = 'TPM'",
        cmpCode,branchCode);
    var result = db.Database.SqlQuery<string>(query);               
    return Json(result.ToArray());               
}

Since the query returns a list of records so when I tried as follows:

var result = db.Database.SqlQuery<IEnumerable<string>>(query).ToList();

It gave me the following error:

'System.Collections.Generic.IEnumerable`1[System.String]' may not be abstract and must include a default constructor.

How can I execute this query?

Regards!

Awais Mahmood

Popular Answer

Probably the easiest way is to define your own class that has the same fields as returned SQL and use this class as output.

public class MyReport {
    public string S1 { get; set; }
    public string S2 { get; set; }
    public string S30 { get; set; }
    public string S3 { get; set; }
    public string S2 { get; set; }
    public string S31 { get; set; }
    public string D1 { get; set; }
    public string S5 { get; set; }
    public string S32 { get; set; }
    public string S6 { get; set; }
    public string S33 { get; set; }
    public string S10 { get; set; }
}

var result = db.Database.SqlQuery<MyReport>(query).ToList();

Lukas Kabrt

View more on Stack Overflow

tschmit007 · Accepted Answer · 11/19/2015 1:21:58 PM

Accepted Answer

you must use a DAO/DTO type:

class MyDAOType {
    public String S1 {get; set;}
    public String S2 {get; set;}
    //...
    public String S10 {get; set;}
}

and the query

var result = db.Database.SqlQuery<MyDAOType>(query);

tschmit007

Execute complex raw SQL query in EF6

Question

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator

Related