locked
How to form dynamic queries in LINQ? RRS feed

  • Question

  • Hello all.

    I'm trying to develop an application which requires, among other things, the retrieval of all records from either one of two tables.   I would like to reuse the same code for both tables; it's really more of a learning exercise for me than something I would want to do in practice.    But since I want to  re-use the code, I need to know how to specify the table name at runtime.   If I were using connection and SQL strings in the XSD world, it wouldn't be an issue, but I want to use LINQ if possible.

    What I want to do is separate the actual interfacing with the database, something like what follows below.   Am I approaching this the wrong way?   It seems a reasonable assumption that isolating the actual database activity in the code makes good architectural sense.

    //C#-Pseudoese

    //DBInterFace.cs
    namespace DBInterface
    {
        static class DBInterfaceClass
        {

           public class object ReturnTableContents (string TableName)
           {
                  ArrayList ar = new ArrayList();
                  List<Object> ObjectList           =new List<object>();

                 //LINQ magic to somehow get the string TableName into an actual LINQ query
                 //    Select all records from table, corresponding to "Select *" in Oracle
                 foreach (object o in queryresult)
                 {
                    foreach (field f in o) ar.Add(o);


                 }
             
           }
        }
    }
    //UseDBInterface.cs
    namespace UseDBInterface.cs;

    //

                List<object> O;
                O = (List<object>)DBInterface.ReturnTableContents
                    ("DBName", "processsuccessordefinition");

    // then use LINQ syntax to work with the collections of objects returned

    }



    Wednesday, June 11, 2008 7:53 PM

Answers

  • An addition comment on the OP:
    "If I were using connection and SQL strings in the XSD world, it wouldn't be an issue, but I want to use LINQ if possible."

    The trick is, we're talking about a scenario with DataTable or IDataReader - i.e. where *all* you can get back is proper, fully formed objects; that isn't easy to do, even in the SQL strings world.

    Re the later post:

    If the caller wants the data, then yes; you'll need to return something. The question is: what something? Normally I would say something involving T (using generics), but that won't help the caller talk to different tables with the same code (since a single variable can't be both IQueryable<Customer> and IQueryable<Order> for example). So either I would move the real code *inside* the generic method (and talk just to T, perhaps using lambdas from the caller to access key fields), or you would need to get the data as (for example) cast as IEnumerable<object>.

    For the second approach (cast) you don't even need the method - LINQ has some suitable helpers, such as:

    IEnumerable<object> data;
    switch(tableName) {
      case "Customer": data = ctx.Customers.Cast<object>(); break;
      case "Order": data = ctx.Orders.Cast<object>(); break;
      // etc
    }
    Although I must admit it isn't very pretty!

    Re the first approach (lambdas for key fields), something like:

    void WriteSummary<T>(IQueryable<T> source, Func<T, string> selector)
    {
      foreach(T item in source) {
        Console.WriteLine(selector(item));
      }
    }
    ...
    WriteSummary(ctx.Customers, cust => string.Format("{0}: {1} ({2})",
        cust.CustomerID, cust.Name, cust.Region));

    But the correct approach really depends on what exactly you want to do.


    Marc
    • Marked as answer by jack 321 Wednesday, June 18, 2008 2:12 AM
    Thursday, June 12, 2008 6:45 AM
  • OK; try explicitly typing "prop" as PropertyDescriptor - i.e.

     

    foreach(PropertyDescriptor prop in props) {...}


    Marc
    • Marked as answer by jack 321 Wednesday, June 18, 2008 2:12 AM
    Tuesday, June 17, 2008 8:31 AM

All replies

  • Well, LINQ doesn't really work with literals; you'd end up writing lots of expression/reflection code, which isn't fun.

    Perhaps more pragmatic, perhaps use generics mixed with component-model:

    void Test<T>(IEnumerable<T> source) where T : class

    {
      var props = TypeDescriptor.GetProperties(typeof(T));

      foreach(T obj in source) {
        foreach(var prop in props) {
          object val = prop.GetValue(obj);
          Console.WriteLine("{0}={1}", prop.Name, val);
        }

      }

    }

    then you can pass any data-source (including IQueryable<T>) to it, perhaps as a switch:

    switch(table) {
      case "Foo":
        Test(ctx.Foos); break;
      // etc
    }


    Marc
    Wednesday, June 11, 2008 8:34 PM
  • Thanks Marc,  I'll try it.

    (Editing after review)

    Doesn't the Test function have to have some sort of nonvoid return type?  

    If the switch block passes one of several tablenames, how does the caller access the data when Test returns void?
    • Edited by Pithecanthropus Thursday, June 12, 2008 5:18 AM Changed content after review
    Wednesday, June 11, 2008 8:43 PM
  • An addition comment on the OP:
    "If I were using connection and SQL strings in the XSD world, it wouldn't be an issue, but I want to use LINQ if possible."

    The trick is, we're talking about a scenario with DataTable or IDataReader - i.e. where *all* you can get back is proper, fully formed objects; that isn't easy to do, even in the SQL strings world.

    Re the later post:

    If the caller wants the data, then yes; you'll need to return something. The question is: what something? Normally I would say something involving T (using generics), but that won't help the caller talk to different tables with the same code (since a single variable can't be both IQueryable<Customer> and IQueryable<Order> for example). So either I would move the real code *inside* the generic method (and talk just to T, perhaps using lambdas from the caller to access key fields), or you would need to get the data as (for example) cast as IEnumerable<object>.

    For the second approach (cast) you don't even need the method - LINQ has some suitable helpers, such as:

    IEnumerable<object> data;
    switch(tableName) {
      case "Customer": data = ctx.Customers.Cast<object>(); break;
      case "Order": data = ctx.Orders.Cast<object>(); break;
      // etc
    }
    Although I must admit it isn't very pretty!

    Re the first approach (lambdas for key fields), something like:

    void WriteSummary<T>(IQueryable<T> source, Func<T, string> selector)
    {
      foreach(T item in source) {
        Console.WriteLine(selector(item));
      }
    }
    ...
    WriteSummary(ctx.Customers, cust => string.Format("{0}: {1} ({2})",
        cust.CustomerID, cust.Name, cust.Region));

    But the correct approach really depends on what exactly you want to do.


    Marc
    • Marked as answer by jack 321 Wednesday, June 18, 2008 2:12 AM
    Thursday, June 12, 2008 6:45 AM
  • Thanks again for your suggestions.

    I was just barely learning C# when vs2008  came out .  I went to a  couple of presentations and got really excited about LINQ; it seemed potentially useful for a University project.    You know what they say about the person who gets a new hammer and thinks everything is a nail.

    I've had two classes at UCLA Extension, which doesn't sound like "just barely", but we still have not covered generics.
    Thursday, June 12, 2008 9:14 PM
  • BTW, is the "ctx" object in your example a DataContext, which you get with the LINQ-TO-SQL class?
    Thursday, June 12, 2008 9:18 PM
  • Yup; although it could equally be a LINQ-to-Entities model, or any other LINQ provider.
    Marc
    Thursday, June 12, 2008 9:21 PM
  • I can't seem to resolve the GetProperties(), GetValue(), or Name attributes.  I've got these namespace references, do I need more?

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Collections;
    using System.Data.Common;
    using System.Data.Linq;
    using System.Linq.Expressions;
    Friday, June 13, 2008 12:54 AM
  • using System.ComponentModel;

    You might also notice that if you right-click on one of the things that isn't working the IDE can find and add the missing "using" statements for you. In VS2008 you get a red "squiggle" that tells you this option is available.
    Marc
    Friday, June 13, 2008 2:22 PM
  • No, actually I'm getting blue squiggles and the IDE isn't telling me that anything's available to fix it.   I think there must be a reference I need to add in the solution explorer, but I don't know what it is.

    I already do have "using System.ComponentModel;"

    The exact error I get is:

    Error    1    'object' does not contain a definition for 'GetValue' and no extension method 'GetValue' accepting a first argument of type 'object' could be found (are you missing a using directive or an assembly reference?)    C:\Projects\ProcMaintMapping\ProcMaintMapping\Generics.cs    22    39    ProcMaintMapping

    ...apparently GetValue can't accept an object as a parameter.

    The error for prop.Name is exactly parallel.

    Monday, June 16, 2008 10:16 PM
  • OK; try explicitly typing "prop" as PropertyDescriptor - i.e.

     

    foreach(PropertyDescriptor prop in props) {...}


    Marc
    • Marked as answer by jack 321 Wednesday, June 18, 2008 2:12 AM
    Tuesday, June 17, 2008 8:31 AM
  • yep, now it compiles!

    Thank you.
    Tuesday, June 17, 2008 6:01 PM
  • And yes, the logic you gave me upstream works perfectly.  

    With your help I have accomplished what I was setting out to do in this thread.
    Tuesday, June 17, 2008 6:06 PM
  • Ah, well, it turns out that I am not yet entirely out of the woods yet.

    I'm getting the data back to the caller, by defining the return type of the function as List<object>.

    In debug, I can roll the mouse over the list, after the method call, and see that all the data is populated.  
    But when I try to write a LINQ query over the list, I can't figure out how to write the where clause or how to reference what were originally columns in a database table.
    Wednesday, June 18, 2008 6:51 PM
  • I'll post my question about list translation in a new thread.
    Thursday, June 19, 2008 2:29 AM
  • New thread:

    http://forums.msdn.microsoft.com/en-US/csharpgeneral/thread/fca22eb4-f748-41ad-a6e4-0411504d9431
    Friday, June 20, 2008 12:39 AM