none
Processing dynamic query returns RRS feed

  • Question

  • I've used Scott Gu's Dynamic Linq library to process arbitrary queries built at runtime, and after a load of research, found the way to extract the actual data from the query (all examples just used it to generate a query which was bound to a Web Forms control, and there's no ToList() method available from his extension). The bit of code that yielded the data from the query was:

    // 'sql' is a string that represents the query to run.
    // A generic list can be created with the usual ToList() method.
    IQueryable<s_Project> projects =
        context.ExecuteQuery<s_Project>(sql)
        .AsQueryable();
    

    In the UI, the user can drag/drop columns around until they're happy with the order, and then check which ones to actually return. All of this works just fine. Now, I have to generate the rendered html with the returned objects. Of course, I can just test for values in the View (I'm working with MVC) in order to show or hide columns, but this doesn't do anything for the dynamic ordering. I figured I'll have to generate a series of custom objects and then create the table string to send out to the View. That leads to my question here...

    If I was dealing with a regular SQL query, the return includes columns in the order they appeared in the SQL query, and only those. When returning LinqToSql objects reflected in the dbml file, they're not in specific order anymore and I get the entire object (of course). Here's my entire method for returning a list of project objects:

    public IQueryable<s_Project> GetFilteredProjects(List<string> columns, string orderBy)
    {
      string sql = string.Empty;
    
      // First option is that columns were specified and an orderBy was included.
      if(columns.Count > 0 && !string.IsNullOrWhiteSpace(orderBy))
        sql = "select " + columns.SerializeToCsv() + " from s_Projects order by " + orderBy;
      // Second option is that columns were specified by no orderBy was included.
      else if (columns.Count > 0 && string.IsNullOrWhiteSpace(orderBy))
        sql = "select " + columns.SerializeToCsv() + " from s_Projects order by Name";
      // Third option is that no columns were specified but an orderBy was included.
      else if (columns.Count == 0 && !string.IsNullOrWhiteSpace(orderBy))
        sql = "select * from s_Projects order by " + orderBy;
      // Fourth option is that no columns or orderBy was included.
      else
        sql = "select * from s_Projects order by Name";
    
      IQueryable<s_Project> projects =
        context.ExecuteQuery<s_Project>(sql)
        .AsQueryable();
    
      return projects;
    }
    

    Feel free to use and improve this code wherever it proves useful. The "SerializeToCsv" method is an extension method for List<string> I created that does just what it sounds like - generates a comma-delimined string from a generic list of strings. What I would like to do at this point is to create a list of custom objects that include only the columns I specify, and in the order I want them. I created a class for this:

    public class CustomProject
    {
      public Dictionary<Type, object> PropertyList;
    
      public CustomProject()
      {
        PropertyList = new Dictionary<Type, object>();
      }
    }

    My plan was to add each column returned as a dictionary entry, with its type and value included. Unfortunately, I came up blank with a means to extract this. I have the column names and the order inside of my GetFilteredProjects method, but I can't do something like this:

    foreach (s_Project project in projects)
    {
      var property = project["ID"];
    }
    
    Accessing a property of the object like this isn't permitted. I get a compiler error. So I'm wondering if there's some way to access the properties I want from the Linq object by string index or some other manner I haven't thought of, so I can generate my list of custom objects to render to the View. 

    Wednesday, November 10, 2010 10:49 PM

Answers

  • , and there's no ToList() method available from his extension). The bit of code that yielded the data from the query was:


    Here is a .ToList extension that goes hand-in-hand with System.Linq.Dynamic:

    public static IList ToList(this IQueryable query)
    {
      return (IList)Activator.CreateInstance(typeof(List<>).MakeGenericType(query.ElementType), query);
    }

    Copied from: http://social.msdn.microsoft.com/forums/en-US/linqprojectgeneral/thread/0ae94c64-d674-4df0-bd60-4a7c5adf407a/#608130b2-5b0a-4f50-82ac-bd8c0a12b750

     

    Accessing a property of the object like this isn't permitted. I get a compiler error. So I'm wondering if there's some way to access the properties I want from the Linq object by string index or some other manner I haven't thought of, so I can generate my list of custom objects to render to the View.

    Use reflection to access individual properties in dynamically generated types:

    public object GetPropertyValue(object obj, string propName)
    {
        System.Reflection.PropertyInfo propInfo = obj.GetType().GetProperty(propName);
        return propInfo.GetValue(obj, null);
    }

     
       Cool tools for Linq-to-SQL and Entity Framework 4:
     huagati.com/dbmltools - Rule based class and property naming, Compare and Sync model <=> DB, Sync SSDL <=> CSDL (EF4)
     huagati.com/L2SProfiler - Query profiler for Linq-to-SQL and Entity Framework v4
    Thursday, November 11, 2010 8:05 AM
    Answerer

All replies

  • , and there's no ToList() method available from his extension). The bit of code that yielded the data from the query was:


    Here is a .ToList extension that goes hand-in-hand with System.Linq.Dynamic:

    public static IList ToList(this IQueryable query)
    {
      return (IList)Activator.CreateInstance(typeof(List<>).MakeGenericType(query.ElementType), query);
    }

    Copied from: http://social.msdn.microsoft.com/forums/en-US/linqprojectgeneral/thread/0ae94c64-d674-4df0-bd60-4a7c5adf407a/#608130b2-5b0a-4f50-82ac-bd8c0a12b750

     

    Accessing a property of the object like this isn't permitted. I get a compiler error. So I'm wondering if there's some way to access the properties I want from the Linq object by string index or some other manner I haven't thought of, so I can generate my list of custom objects to render to the View.

    Use reflection to access individual properties in dynamically generated types:

    public object GetPropertyValue(object obj, string propName)
    {
        System.Reflection.PropertyInfo propInfo = obj.GetType().GetProperty(propName);
        return propInfo.GetValue(obj, null);
    }

     
       Cool tools for Linq-to-SQL and Entity Framework 4:
     huagati.com/dbmltools - Rule based class and property naming, Compare and Sync model <=> DB, Sync SSDL <=> CSDL (EF4)
     huagati.com/L2SProfiler - Query profiler for Linq-to-SQL and Entity Framework v4
    Thursday, November 11, 2010 8:05 AM
    Answerer
  • I've tried using ToList() from the query returned when using the dynamic library Scott Gu wrote. In the exact same code in the same place, if I use a regular Linq query, then I get an IQueryable result set that I can call ToList() on. If I use any arbitrary strings that causes the extensions Scott Gu created to kick in, I do not have access to this method. It does not exist, and if I try to use it I get a compiler error. This is possibly due to the fact that after using a dynamic query I only get the query, and not the actual data, unlike what I get when I use Linq without the dynamic library. For instance, 

    var projects = context.s_Projects.OrderBy(sortBy).Select("new(" + columns.SerializeToCsv() + ")");

    I cannot tack on "ToList()" to the end of this statement. Intellisense doesn't show it, and if I manually type it in, I get an error: 'System.Linq.IQueryable' does not contain a definition for 'ToList' and no extension method 'ToList' accepting a first argument of type 'System.Linq.IQueryable' could be found (are you missing a using directive or an assembly reference?)

    When the above statement runs, the result I get stored in the 'projects' variable is:

     

    {SELECT [t0].[CourseNumber], [t0].[Program]

    FROM [dbo].[s_Projects] AS [t0]

    ORDER BY [t0].[CourseNumber]

    }

    I can't call ToList() on the result variable. I tried, just to be sure. In the exact same method, if I use this:

    var projects = context.s_Projects.OrderBy(p => p.Name).Select(p => p.Name).ToList();

    Note that this is exactly the same statement, except that now it uses property names identified from the lambda expression instead of arbitrary strings. I only grabbed one property name for simplicity, and I'm not sure I could specifiy a series like this. Now, I have access to ToList(), and even if I don't call ToList(), I still get an IQueryable set of data, rather than just a query. None of this makes a lot of sense to me, as I'm getting an IQueryable list, and it clearly has access to ToList(), in contrast to the error message above. I can place these statements right next to each other (so I know it's not a missing assembly reference or other odd error), and these are the results I get. Except by calling an ExecuteQuery() method on the DataContext and also calling AsQueryable(), I have yet to see one single method shown anywhere online of how to use the dynamic linq library to get the actual data, instead of only getting a query to bind to a Web Forms control.

    If I try to directly assign the result of the query to an IQueryable list of this specific type of object as in this:

    IQueryable<s_Project> projects2 = 
      context.s_Projects.OrderBy(orderBy)
      .Select("new(" + columns.SerializeToCsv() + ")"); 

    I get this compiler error:

    Cannot implicitly convert type 'System.Linq.IQueryable' to 'System.Linq.IQueryable<DSC.Data.DataContext.s_Project>'. An explicit conversion exists (are you missing a cast?)

    So I'm assuming that, in trade for gaining dynamic query capability, I'm losing the strong typing for the IQueryable result, which in turn loses the ability to call ToList(), since I can't specify the type at this point. 

    Thursday, November 11, 2010 4:52 PM
  • I cannot tack on "ToList()" to the end of this statement. Intellisense doesn't show it, and if I manually type it in, I get an error: 'System.Linq.IQueryable' does not contain a definition for 'ToList' and no extension method 'ToList' accepting a first argument of type 'System.Linq.IQueryable' could be found (are you missing a using directive or an assembly reference?)


    The ToList extension method in my previous reply works fine on IQueryable here. Did you add dd it to a static class? If not, do that to make it available to your compiler.

     


     
       Cool tools for Linq-to-SQL and Entity Framework 4:
     huagati.com/dbmltools - Rule based class and property naming, Compare and Sync model <=> DB, Sync SSDL <=> CSDL (EF4)
     huagati.com/L2SProfiler - Query profiler for Linq-to-SQL and Entity Framework v4
    Friday, November 12, 2010 2:24 AM
    Answerer