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
– Ashiquzzaman

2017-05-01 07:18:00 +00:00
Commented May 1, 2017 at 7:18 — Ashiquzzaman
– Ashiquzzaman, Commented May 1, 2017 at 7:18
Don't know but VS 2013. May be 4.5

Nur Uddin
– Nur Uddin

2017-05-01 07:34:06 +00:00
Commented May 1, 2017 at 7:34 — Nur Uddin
– Nur Uddin, Commented May 1, 2017 at 7:34
did it although i solved another way!

Nur Uddin
– Nur Uddin

2017-08-27 18:25:58 +00:00
Commented Aug 27, 2017 at 18:25 — Nur Uddin
– Nur Uddin, Commented Aug 27, 2017 at 18:25

Reyan Chougle · Accepted Answer · 2017-05-01 05:53:06Z

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 1, 2017 at 5:53

answered May 1, 2017 at 5:26

Reyan Chougle

5,3813 gold badges37 silver badges65 bronze badges

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

13 Comments

Nur Uddin Over a year ago

How can i return a list in json? @Reyan Chougle

Reyan Chougle Over a year ago

You will need to use Newtonsoft.Json. You can check this stackoverflow.com/questions/6366118/…

Reyan Chougle Over a year ago

This is also a good post. stackoverflow.com/questions/9110724/serializing-a-list-to-json

Nur Uddin Over a year ago

It is not working yet! See my web api code. i edited my post. How can return the result?

Nur Uddin Over a year ago

`return JsonConvert.SerializeObject(studentName);' internal server error

|

Ashokkumar M. Prajapati · Accepted Answer · 2017-05-01 05:21:31Z

0

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.

answered May 1, 2017 at 5:21

Ashokkumar M. Prajapati

4934 silver badges12 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

13 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

13 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related