none
Entity Framework Execute Raw SQL Query RRS feed

  • Question

  • I am using C# and Entity Framework to connect to my SQL Server, and I want to run a query directly in the C# syntax.  I found this example online, but with the firstordefault added, will it ONLY return the first record?  

    What I am after is running this sql query = Select id, schoolname, schoolgrade, Count(students) As COStu From is where already counted IS NULL

    How would I do that using EntityFramework?

    using (var ctx = new SchoolDBEntities())
    {
        var student = ctx.Students
                        .SqlQuery("Select * from Students where StudentId=@id", new SqlParameter("@id", 1))
                        .FirstOrDefault();
    }

    Wednesday, November 28, 2018 10:37 PM

All replies

  • I am using C# and Entity Framework to connect to my SQL Server, and I want to run a query directly in the C# syntax.  I found this example online, but with the firstordefault added, will it ONLY return the first record?  

    What I am after is running this sql query = Select id, schoolname, schoolgrade, Count(students) As COStu From is where already counted IS NULL

    How would I do that using EntityFramework?

    using (var ctx = new SchoolDBEntities())
    {
        var student = ctx.Students
                        .SqlQuery("Select * from Students where StudentId=@id", new SqlParameter("@id", 1))
                        .FirstOrDefault();
    }

    Wednesday, November 28, 2018 10:26 PM
  • it is where you can post.

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=adodotnetentityframework

    Wednesday, November 28, 2018 10:36 PM
  • Hi IndigoMontoya,

    >>I am using C# and Entity Framework to connect to my SQL Server, and I want to run a query directly in the C# syntax.  I found this example online, but with the firstordefault added, will it ONLY return the first record? 

    Please check the document about firstordefault, which mentioned that it returns the first element of a sequence, or a default value if no element is found. for EF entities, it will return the first record if the query has records, it will return null if no records.

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 29, 2018 1:48 AM
    Moderator
  • just dont't use the FirstOrDefault then, you can use ToList for getting the multiple results :

    var students = ctx.Students
                        .SqlQuery("Select * from Students where StudentId=@id", new SqlParameter("@id", 1))
                        .ToList();


    [If a post helps to resolve your issue, please click the "Mark as Answer" of that post or click Answered "Vote as helpful" button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


    Blog | LinkedIn | Stack Overflow | Facebook
    profile for Ehsan Sajjad on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, November 29, 2018 5:58 AM
  • You can get multiple records too by using the ToList method after the SqlQuery call like:

    var students = ctx.Students
                        .SqlQuery("Select id, schoolname, schoolgrade, Count(students) As Count From is where already counted IS NULL")
                        .ToList();



    Hope it helps.


    [If a post helps to resolve your issue, please click the "Mark as Answer" of that post or click Answered"Vote as helpful" button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


    Blog | LinkedIn | Stack Overflow | Facebook
    profile for Ehsan Sajjad on Stack Exchange, a network of free, community-driven Q&A sites


    • Edited by Ehsan Sajjad Thursday, November 29, 2018 6:03 AM
    Thursday, November 29, 2018 6:02 AM