locked
EF with Raw SQL RRS feed

  • Question

  • I have been searching the internet for awhile now and I cant not find a solution.
    I am trying to take execute a raw sql statement.

    Here is a simple example

    strSQL = "SELECT visit_id, start_time " & _
            "FROM  " & _
            "VISIT_SCHEDULE V with (nolock)   "
    
    Dim Visits = context.Database.SqlQuery(Of VisitDetail)(strSQL)
    For Each Visit In Visits
        Console.WriteLine(Visit.visit_id)
        Console.WriteLine(Visit.start_time)
    Next

    Public Class VisitDetail
       Public visit_id As String
       Public start_time As String
    End Class

    The result in Visits has the correct number of records but everything is null.

    I realize I can just do something like the below, but this is just a simple example. I am doing something more complex. Like joining tables across databases. Which I have researched and EF can not do that.

    Dim Visits = From v In context.VisitSchedules
                    Select New With {
                    .visit_id = v.visit_id,
                    .start_time = v.start_time
                    }
    

    Thank you for your help.

    Friday, May 16, 2014 2:11 PM

Answers

  • Hi GuyInTexas;

    I think that if you make the following changes to the VisitDetail class as shown below it will work for you.

    Public Class VisitDetail
       Public Property visit_id As String
       Public Property start_time As String
    End Class


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    • Marked as answer by GuyInTexas Monday, May 19, 2014 7:28 PM
    Saturday, May 17, 2014 1:52 AM

All replies

  • I believe you can easily do this with EF by using Entity SQL and using a ESQL datareader.

    http://msdn.microsoft.com/en-us/library/vstudio/bb738684(v=vs.100).aspx

    http://msdn.microsoft.com/en-us/library/vstudio/bb387118(v=vs.100).aspx

    http://msdn.microsoft.com/en-us/library/vstudio/bb387145(v=vs.100).aspx

    You can also implement the (nolock) too by using System.Transactions.

    http://www.hanselman.com/blog/GettingLINQToSQLAndLINQToEntitiesToUseNOLOCK.aspx

    You can also use the EF backdoor to run a sproc or T-SQL too.

    http://blogs.msdn.com/b/alexj/archive/2009/11/07/tip-41-how-to-execute-t-sql-directly-against-the-database.aspx

    You can use objects on the model and populate them from a datareader or you can make DTO(s)  and use them.  

    Friday, May 16, 2014 3:30 PM
  • Thank you for your response.

    The last link (You can also use the EF backdoor to run a sproc or T-SQL too.)

    uses ExecuteStoreQuery which is from ObjectContext

    So I changed it to this and get the same result.

            Dim Visits = DirectCast(context, IObjectContextAdapter).ObjectContext.ExecuteStoreQuery(Of VisitDetail)(strSQL)
    
            For Each Visit As VisitDetail In Visits
                Console.WriteLine(Visit.visit_id)
                Console.WriteLine(Visit.start_time)
            Next
    

    Friday, May 16, 2014 4:45 PM
  • So I changed it to this and get the same result.

    Then I suggest that you don't use it, and you do the first example in the link where you got the example from,  the backdoor link,  and use a datareader to read the returned result.

    Friday, May 16, 2014 11:02 PM
  • Hi GuyInTexas;

    I think that if you make the following changes to the VisitDetail class as shown below it will work for you.

    Public Class VisitDetail
       Public Property visit_id As String
       Public Property start_time As String
    End Class


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    • Marked as answer by GuyInTexas Monday, May 19, 2014 7:28 PM
    Saturday, May 17, 2014 1:52 AM
  • That worked Fernando, thank you very much! I spent a lot of hours on this and I was just missing the word Property.
    Monday, May 19, 2014 7:28 PM
  • Not a problem GuylnTexas, glad I was able to help. I know sometimes looking at an issue like this can keep me going for a while because I don't see it until someone pointed out to me, so glad to be your eyes this time.

    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Monday, May 19, 2014 9:02 PM