none
How to do T-SQL WHERE IN using EF DbSet.SqlQuery

    Question

  • I'm developing an ASP.Net MVC3 app using EF and I have a question about making an sql call similiar to how you would in T-SQL with the IN keyword used in the WHERE clause

    The sample I have been using to help me is Contoso example foune here.

    In that example they use the EF DbSet.SqlQuery method like so...

     


    I'm developing an ASP.Net MVC3 app using EF and I have a question about making an sql call similiar to how you would in T-SQL with the IN keyword used in the WHERE clause

    The sample I have been using to help me is Contoso example found here....

    http://www.asp.net/entity-framework/tutorials/advanced-entity-framework-scenarios-for-an-mvc-web-application


    In that example they use the EF DbSet.SqlQuery method like so...

     

    'GET: /Course/Details/5
     Function Details(id As Integer) As ViewResult
     Dim query = "SELECT * FROM Course WHERE CourseID = @p0"
     Return View(unitOfWork.CourseRepository.GetWithRawSql(query, id).Single())
     End Function

     


    my question is what if you wanted multiple Courses from a list of ID's?  something like...

     

    ' GET: /Course/IndexRawSql/1,3,5
     Function IndexRawSql(ByVal ParamArray Ids() As Integer) As ViewResult
     Dim sWhere As String = "1, 3, 5" 'ParamArrayToWhere(Ids)
     Dim query = "SELECT * FROM Course WHERE CourseID In (" + sWhere + ")"
    Return View(unitOfWork.CourseRepository.GetWithRawSql(query, Ids).ToList())
    End Function

     


    • Edited by John Marsing Tuesday, August 09, 2011 9:58 PM clean up
    Tuesday, August 09, 2011 9:56 PM

Answers

  • Hi John,

    Thanks for your feedback.

    There are two overrides for DbSet.SqlQuery. we can just run the select Command text:

     var test = context.T_Employee.SqlQuery("select * from T_Employee where FAge in(23,28)").ToList();
    


    or

    var test = context.T_Employee.SqlQuery("select * from T_Employee where FAge in(@p0,@p1)",23,28).ToList();
    


    You can try to write like this:

     Function IndexRawSql(ByVal ParamArray Ids() As Integer) As ViewResult
     'Dim sWhere As String = "1, 3, 5" 'ParamArrayToWhere(Ids)
     Dim query = "SELECT * FROM Course WHERE CourseID In (@p0,@p1,@p2)"
    Return View(unitOfWork.CourseRepository.GetWithRawSql(query, Ids).ToList())' 1,3,5
    End Function
    


    BTW, I'm not familiar with VB.net, thanks for understanding.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by John Marsing Thursday, August 11, 2011 1:54 PM
    Thursday, August 11, 2011 11:37 AM

All replies

  • Hi John,

    Welcome!

    The SqlQuery on DbSet allows a raw SQL query to be written that will return entity instances. So it works.

    You can refer this blog: http://blogs.msdn.com/b/adonet/archive/2011/02/04/using-dbcontext-in-ef-feature-ctp5-part-10-raw-sql-queries.aspx

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, August 10, 2011 8:54 AM
  • Thank you Alan for your response and the link, however I don't think you addressed my question with specificity.  I acknowledge that it does work that it in some instances like for example the one I showed from the contoso example that returns one entity.  My question rather was can it work trying to return multiple entities with the T-SQL reserved word IN.

     

    If it can't return mutplie instances that's fine I will work out another solution.

     

    Thanks again for the link.  I added this question to in the blog comments and referenced this thread.

    John


    John Marsing http://MyHebrewBible.com/
    Wednesday, August 10, 2011 1:52 PM
  • Hi John,

    Thanks for your feedback.

    There are two overrides for DbSet.SqlQuery. we can just run the select Command text:

     var test = context.T_Employee.SqlQuery("select * from T_Employee where FAge in(23,28)").ToList();
    


    or

    var test = context.T_Employee.SqlQuery("select * from T_Employee where FAge in(@p0,@p1)",23,28).ToList();
    


    You can try to write like this:

     Function IndexRawSql(ByVal ParamArray Ids() As Integer) As ViewResult
     'Dim sWhere As String = "1, 3, 5" 'ParamArrayToWhere(Ids)
     Dim query = "SELECT * FROM Course WHERE CourseID In (@p0,@p1,@p2)"
    Return View(unitOfWork.CourseRepository.GetWithRawSql(query, Ids).ToList())' 1,3,5
    End Function
    


    BTW, I'm not familiar with VB.net, thanks for understanding.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by John Marsing Thursday, August 11, 2011 1:54 PM
    Thursday, August 11, 2011 11:37 AM
  •  

    Thanks, that worked!

     

    I should say it works if I hard code the values in the expression, like so....

    Dim v = unitOfWork.ScriptureRepository.GetWithRawSql("SELECT * FROM Scripture WHERE ID in(@p0,@p1)", 1, 3).ToList()
    
    Now I need to figure out how to set up a ParamArray that I can load and pass to DBSet.SqlQuery.  Here's what I tried...

    Dim verses() As Int32 = {1, 3}
    Dim v = unitOfWork.ScriptureRepository.GetWithRawSql(query, verses).ToList()
    


    John Marsing http://MyHebrewBible.com/
    Thursday, August 11, 2011 2:02 PM
  • Hi John,

    Thanks for your feedback.

    You are right!

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, August 12, 2011 2:55 AM