locked
Execute sql query in EF RRS feed

  • Question

  •  i have requirement like execute SqlQuery in entityFramework 
    The sqlQuery like "Select employeename,id,number,state from tablename1,tablename2,tablename3"
     but sometimes in sql query columns count coming different means sometimes two columns will come,three columns,four columns will come
    right now i am doing like this "this.Database.SqlQuery<Entity>("sql query").ToList()"

    but it only applicable if column counts are same in sql query




    lokesh


    • Edited by Lokesh536 Wednesday, May 21, 2014 4:19 PM
    Wednesday, May 21, 2014 4:17 PM

Answers

  • If you don't have a .NET type to load the data into, then run the query using ADO.NET and load the results into a DataTable. 

    eg, add a method like this to your DbContext:

                public DataTable RunAdHocQuery(string sql)
                {
                    var wasOpen = Database.Connection.State == ConnectionState.Open;
                    if (!wasOpen)
                    {
                        Database.Connection.Open();
                    }
    
                    var cmd = Database.Connection.CreateCommand();
                    var dt = new DataTable();
                    using (var dr = cmd.ExecuteReader())
                    {
                        dt.Load(dr);
                    }
    
                    if (!wasOpen)
                    {
                        Database.Connection.Close();
                    }
                    return dt;
                }

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by Lokesh536 Friday, May 23, 2014 5:35 PM
    Thursday, May 22, 2014 7:55 PM

All replies

  • Hello,

    It sounds like you are looking for a dynamic object which will automatically change with the return result.

    Unfortunately, it is impossible, we need to first define the entity class implicitly with the this.Database.SqlQuery<T>() method.

    My suggestion is to create an entity class which contains all the fields from table1, table2 and table3, and use this type as the return type as:

    this.Database.SqlQuery<TheEntireEntityClass>()

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Lokesh536 Thursday, May 22, 2014 4:29 PM
    • Unmarked as answer by Lokesh536 Thursday, May 22, 2014 4:30 PM
    Thursday, May 22, 2014 6:19 AM
  • table names are coming dynamically,some times two tables will come,three ,four tables will come and so on 

    lokesh

    is anyway to execute without creating EntireEntityclass
    • Edited by Lokesh536 Thursday, May 22, 2014 5:19 PM
    Thursday, May 22, 2014 4:34 PM
  • If you don't have a .NET type to load the data into, then run the query using ADO.NET and load the results into a DataTable. 

    eg, add a method like this to your DbContext:

                public DataTable RunAdHocQuery(string sql)
                {
                    var wasOpen = Database.Connection.State == ConnectionState.Open;
                    if (!wasOpen)
                    {
                        Database.Connection.Open();
                    }
    
                    var cmd = Database.Connection.CreateCommand();
                    var dt = new DataTable();
                    using (var dr = cmd.ExecuteReader())
                    {
                        dt.Load(dr);
                    }
    
                    if (!wasOpen)
                    {
                        Database.Connection.Close();
                    }
                    return dt;
                }

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by Lokesh536 Friday, May 23, 2014 5:35 PM
    Thursday, May 22, 2014 7:55 PM
  • is anyway to execute without creating EntireEntityclass

    Why don't you just being back the data as an XML document? You can base the XML tags and data within the tags based on your dynamic columns returned and the data the columns hold..

    You can also use the EF backdoor and use Dynamic T-SQL to make the query, or you can use Dynamic  Entity-SQL to make the query dynamically,  return the resultset and build the XML Document to be returned. Either way T-SQL or E-SQL, you can use a datareder to read the results and dymancally build the results to be retuned.

    You don't have to use Database.SqlQuery to achive this.

    Thursday, May 22, 2014 9:05 PM