none
How to select more than one field using SqlQuery? RRS feed

  • Question

  • I am having a hard time figuring out how to extract two fields from a Table.

    Here is what I am tried to do:

         string query1 = "select K, [HbyD] from " + _CurveSelected + " where HbyD <= " + Input.ToString() + " order by HbyD desc";
         var var1 = ctx.Database.SqlQuery<IEnumerable<decimal>>(query1).FirstOrDefault();

    Error: 

    The result type 'System.Collections.Generic.IEnumerable`1[System.Decimal]' may not be abstract and must include a default constructor.

    Obviously, I have tried a lot more different codes, but nothing works. 

    It'll only works if I'll use the name of the Class instead of IEnumerable, but I won't known it during the runtime. Or if I only select one field. 

    Monday, January 5, 2015 11:22 AM

Answers

All replies

  • The only way I got it working was by creating a separate Class (not the entity class) that would represent the field in the select clause. 

    Here is the Class:

        class DNVforQuery
        {
            public decimal K { get; set; }
            public decimal HbyD { get; set; }
        }


    Here is the query:

                        string query1 = "select [K], HbyD from " + _CurveSelected + " where HbyD <= " + Input.ToString() + " order by HbyD desc";
    
                        var var2 = ctx.Database.SqlQuery<DNVforQuery>(query1).FirstOrDefault();
                        decimal aabb =var2.K;
                        decimal aaahh = var2.HbyD;

    Note:

    Keep in mind that if the property names won't match then it'll not give any error but instead setting its values to 0. 

    Monday, January 5, 2015 5:03 PM
  • http://blogs.msdn.com/b/alexj/archive/2009/11/07/tip-41-how-to-execute-t-sql-directly-against-the-database.aspx

    You could just use the backboof, execute t-sql or sproc, use datareader and populate the object. You can return a List<T> of the objects.

    Monday, January 5, 2015 7:02 PM
  • Thanks darnold,

    Is your answer any different than the one I provided. I mean is there any way that I could get the two fields without creating another class. Taking into consideration that you don't know which entity the data is retrieved from. However, by some extent knowing the property names.

    Monday, January 5, 2015 9:16 PM
  • Hello Bopy,

    >>I mean is there any way that I could get the two fields without creating another class.

    This is by designed about the SqlQuery<T> method, we always have to pass a type which contains matched properties since it would do a match operation in memory. Although we could use the dynamic type to make the query worked even at runtime, however, it would only return a list of objects which is not helpful.

    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.

    Thursday, January 8, 2015 7:58 AM
    Moderator
  • dynamic is an interesting type. 

    I tried this:

                        //List<dynamic> var1 = ctx.Database.SqlQuery<List<dynamic>>(query1).FirstOrDefault();
                        //double k1sss = (double)var1[0];

    but var1.count = 0, though, there has to be two values. 

    Thursday, January 8, 2015 3:10 PM
  • Is your answer any different than the one I provided. I mean is there any way that I could get the two fields without creating another class. Taking into consideration that you don't know which entity the data is retrieved from. However, by some extent knowing the property names.

    If you dump the results into a datatable and return a datatable, then it doesn't matter what columns/properties are retuned, becuase the table is built dynamically based on the result returned from the T-SQL query. In this situation, a datatable wins over using EF entity or custom object, becuase they can't be built dynamically. This would apply to using the EF backdoor.

    Thursday, January 8, 2015 3:44 PM
  • Thanks darnold,

    I tried this:

                            var adapter = (IObjectContextAdapter)ctx;
                            var objectContext = adapter.ObjectContext;
                            var entityConn = objectContext.Connection as EntityConnection; // here I get entityConn as null, why?
    var dbConn = entityConn.StoreConnection as SqlConnection;
    dbConn.Open();
    string query1 = "select [K], HbyD from " + _CurveSelected + " where HbyD <= " + Input.ToString() + " order by HbyD desc";
    var cmd = new SqlCommand(query1, dbConn ); 
    using (var reader = cmd.ExecuteReader()) 
    { 
        while (reader.Read()) 
        {
            double kaaa = (double)reader[0];
        } 
    } 

      var entityConn = objectContext.Connection as EntityConnection; // here I get entityConn as null, why?

    Thursday, January 8, 2015 5:16 PM
  • The EntityConnection would be defined along the lines you see in the link.

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

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

    You should follow what's in the links and not make things up.

    Thursday, January 8, 2015 7:07 PM
  • Thanks darnold,

    It worked but requires the table name for every column in the query. And I can't auto cast it to double, first I need to convert it into string and then cast it to double. 

    The important thing is that it works. It might not be very well suited for this particular example but it's definitely useful. 

    Friday, January 9, 2015 9:17 AM