How to use SQL Raw query in ASP .Net MVC web api

Question

My Connection String:

 <add name="addConnection" connectionString="Data Source=(LocalDb)\v11.0;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\FLWorkDB.mdf" providerName="System.Data.SqlClient" />
 <add name="FLWorkDBEntities" connectionString="metadata=res://*/MyEdmx.csdl|res://*/MyEdmx.ssdl|res://*/MyEdmx.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=(LocalDB)\v11.0;attachdbfilename=|DataDirectory|\FLWorkDB.mdf;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

My Web API:

 [HttpGet]
    [Route("api/JobApi/BrowseJobs/")]
    public object BrowseJobs()
    {
        using (var ctx = new FLWorkDBEntities())
        {
            //Get student name of string type
            object studentName = ctx.Database.SqlQuery<string>(@"Select j.JobID,j.JobDetails,j.JobTitle, j.Deadline ,j.Budget,j.Category, emp.FirstName,emp.LastName,Skills = Stuff((Select ',' +SkillName From  Skill Where charindex(concat(',',SkillID,','),','+j.ReqSkill+',')>0 For XML Path ('')),1,1,'') 
                        From  Job j
                        join Employeer emp on j.EmployeerID = emp.EmployeerID 
                        WHERE NOT EXISTS 
                            (SELECT * 
                             FROM ClosedJob c 
                             WHERE c.JobID = j.JobID) AND

                         NOT EXISTS 
                            (SELECT * 
                             FROM AppliedJob apj 
                             join JobOffer jo on apj.AppliedJobID =jo.AppliedJobID
                             join Contract con on jo.OfferID =con.OfferID
                             WHERE con.CompletedDate != Null)").ToList();
        }

        return stu;
    }

Now i am doing all with LINQ. But i want to avoid LINQ and add direct sql query.

RAW Sql: "Select * from A where A.ID =2"(with join)

I want to add the RAW SQL Query in my web api that returns list in json. Can i add sql query without entity framework, i mean .edmx file?

Edited============== Tried this:

 var results = db.Database.SqlQuery<FLWorkDBEntities>(@"Select j.JobID,j.JobDetails,j.JobTitle, j.Deadline ,j.Budget,j.Category, emp.FirstName,emp.LastName,Skills = Stuff((Select ',' +SkillName 
                                From  Skill
                                Where charindex(concat(',',SkillID,','),','+j.ReqSkill+',')>0
                                For XML Path ('')),1,1,'')
                     From  Job j
                    join Employeer emp on j.EmployeerID = emp.EmployeerID
                    WHERE NOT EXISTS 
                        (SELECT * 
                         FROM ClosedJob c 
                         WHERE c.JobID = j.JobID) AND

                     NOT EXISTS 
                        (SELECT * 
                         FROM AppliedJob apj 
                         join JobOffer jo on apj.AppliedJobID =jo.AppliedJobID
                         join Contract con on jo.OfferID =con.OfferID
                         WHERE con.CompletedDate != Null)").ToList<FLWorkDBEntities>();

        return  JsonConvert.SerializeObject(results);

But returns:

Self referencing loop detected with type 'System.Data.Entity.DynamicProxie

================Solved==================== I made a stored procedure (BrowseJobs) then attached it with the following code

 SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["addConnection"].ConnectionString);
        SqlCommand com = new SqlCommand("BrowseJobs", con);
        com.CommandType = CommandType.StoredProcedure;
        SqlDataAdapter da = new SqlDataAdapter(com);
        DataSet ds = new DataSet();
        da.Fill(ds);
        return  ds.Tables[0];

what 's version of EF you using? – Ashiquzzaman May 01 '17 at 07:18 — Ashiquzzaman, May 01 '17 at 07:18
Don't know but VS 2013. May be 4.5 – Nur Uddin May 01 '17 at 07:34 — Nur Uddin, May 01 '17 at 07:34
did it although i solved another way! – Nur Uddin Aug 27 '17 at 18:25 — Nur Uddin, Aug 27 '17 at 18:25

Reyan Chougle · Accepted Answer · 2017-05-01T05:53:06.007

2

Yes. You can go through this link http://www.entityframeworktutorial.net/EntityFramework4.3/raw-sql-query-in-entity-framework.aspx

E.g.;

using (var context = new SqlDbContext())
{
    //Get command for non entity types
    string studentName = context.Database.SqlQuery<string>(@"SELECT StudentName FROM Student WHERE StudentID = 1").FirstOrDefault<string>();

    //Get command for entity types. SQL columns returned by SQL query should match the property of the entity type
    var students = context.Students.SqlQuery(@"SELECT * FROM Student").ToList<Student>();

    //Update command
    int noOfRowsUpdated = context.Database.ExecuteSqlCommand(@"UPDATE Student SET StudentName = 'New Name' WHERE StudentID = 1");

    //Insert command
    int noOfRowsInserted = context.Database.ExecuteSqlCommand(@"INSERT INTO Student(StudentName) VALUES('New Student')");

    //Delete command
    int noOfRowsDeleted = context.Database.ExecuteSqlCommand(@"DELETE FROM Student WHERE StudentID = 1");
}

You can return the list in JSON format using Newtonsoft.Json. Just follow these steps:

PM> Install-Package Newtonsoft.Json
using Newtonsoft.Json;
var jsonResult = JsonConvert.SerializeObject(students);

edited May 01 '17 at 05:53

answered May 01 '17 at 05:26

Reyan Chougle

4,917
2
30
57

How can i return a list in json? @Reyan Chougle – Nur Uddin May 01 '17 at 05:38
You will need to use Newtonsoft.Json. You can check this http://stackoverflow.com/questions/6366118/converting-list-to-json-format-quick-and-easy-way – Reyan Chougle May 01 '17 at 05:39
This is also a good post. http://stackoverflow.com/questions/9110724/serializing-a-list-to-json – Reyan Chougle May 01 '17 at 05:40
It is not working yet! See my web api code. i edited my post. How can return the result? – Nur Uddin May 01 '17 at 05:46
`return JsonConvert.SerializeObject(studentName);' internal server error – Nur Uddin May 01 '17 at 06:05
See Edited Part @ Reyan Chougle – Nur Uddin May 01 '17 at 13:59
Ok. But the answer I have posted should have worked for you. I don't know what went wrong in your case. – Reyan Chougle May 04 '17 at 14:15
What is the meaning of " non entity types" ? I want to return data based on join. Then what will i use in 'string' in context.Database.SqlQuery? @Reyan Chougle – Nur Uddin May 04 '17 at 16:03
Non entity types means string, int. In your case use entity types. i.e; your model or view model in case of join – Reyan Chougle May 04 '17 at 16:16
If i use Employees model , it returns ok (without join-like select * fromEmployees ). But when i use join ,it returns error. I tried 'object' ,but failed @ Reyan Chougle – Nur Uddin May 04 '17 at 16:28
Use the view model instead object – Reyan Chougle May 04 '17 at 16:47
What do you mean by view model? Can you give an example? – Nur Uddin May 04 '17 at 16:51
Please refer this http://stackoverflow.com/questions/11064316/what-is-viewmodel-in-mvc – Reyan Chougle May 04 '17 at 17:07

score 0 · Answer 2 · answered May 01 '17 at 05:21

You can do this in entity framework.

The documentation link : https://msdn.microsoft.com/en-us/library/jj592907(v=vs.113).aspx

example link : http://www.entityframeworktutorial.net/EntityFramework4.3/raw-sql-query-in-entity-framework.aspx

To, do it without edmx then use old ado.net.

How to use SQL Raw query in ASP .Net MVC web api

2 Answers2