locked
How to dynamically build a table fieldname name{0} and use it in LINQ RRS feed

  • Question

  • In SQL i have this:

    queryString = String.Format("SELECT FP{0}, FP{1} from User WHERE idUser = @userID;", fingersinuse[0], fingersinuse[1]);

    How do I achieve:

    select new { x.FP{0} }

     

     

     

     

     

    Monday, September 12, 2011 9:27 AM

Answers

  • Hi

    I think the answer to your question is in the same answer that JA Reyes gave to one of my recent questions. "Could dynamic LINQ library help you?".

    You would need to download a small extension (DynamicLibrary.cs) to include in your project and then you can build a query something like this...

     

    var result = datacontext.Users
                            .Where ( usr => usr.idUser = "JoeSoap" )
                            // The following .Select(...) is the Dynamic Linq extension
                            .Select ( "FP{0}, FP{1}",  fingersinuse[0], fingersinuse[1]);
    
    
    

    However I believe that 'result' is not strongly typed so I don't know if your can then use 'result.FPLeftRingFinger'. Instead you might have to use reflection to get the values of FP{0} and FP{1} as something like ...

     

    PropertyInfo[] properties = result.GetProperties();
    
    var fp0property = properties.Where(prop => prop.PropertyName == "FPLeftRingFinger").SingleOrDefault();
    
    object val = null;
    if (fp0property != null)
    {
    	// sorry I can't remember the exact syntax of GetValue off the top of my head
    	val = fp0property.GetValue(...);
    }
    



    I am novice with Dynamic LINQ library, so please forgive me if I have been wrong.

     

    • Marked as answer by Jackie-Sun Monday, September 26, 2011 6:29 AM
    Saturday, September 17, 2011 7:23 PM
  • Hmmm, nice link John.

    You can also achieve this by using Expression Trees

    http://msdn.microsoft.com/en-us/library/bb882637.aspx

    Expression trees are a very handy tool, very good for some cases, you should read the whole article, not only the part about dynamic queries.

    http://msdn.microsoft.com/en-us/library/bb397951.aspx

    However, this requires at least VS2008 to work

    Regards

    • Marked as answer by Jackie-Sun Monday, September 26, 2011 6:29 AM
    Tuesday, September 20, 2011 1:44 PM

All replies

  • VS does the mapping for you automatically, so you in most cases don't have to do anything.  If you are using SQL Metal then you can use that to autogenerate the code

    Regards

    Friday, September 16, 2011 1:03 PM
  • Sorry Serguey123. I cant learn from that.

     

    Friday, September 16, 2011 2:02 PM
  • Then, please explain better what is that you need.

    If for whatever reason you want to see how the code for the mapping looks like, you can look at the autogenerated cs file on your project.

    Regards

    Friday, September 16, 2011 2:26 PM
  • I have a table in the database that has ten finger templates labelled FP0 to FP9. 2 of those are selected by the user and inserted into fingersinuse[0] and fingersinuse[1], meaning that I must extract the selected 2 finger templates for comparison with the live items, hence the original

    queryString = String.Format("SELECT FP{0}, FP{1} from User WHERE idUser = 'JoeSoap';", fingersinuse[0], fingersinuse[1]);

    SQL Metal disects the database as it stands and has no dynamic capability as such.

    HOW DO i express this in LINQ

    Your interset is highly appreciated.

     

    Saturday, September 17, 2011 1:43 PM
  • Hi

    I think the answer to your question is in the same answer that JA Reyes gave to one of my recent questions. "Could dynamic LINQ library help you?".

    You would need to download a small extension (DynamicLibrary.cs) to include in your project and then you can build a query something like this...

     

    var result = datacontext.Users
                            .Where ( usr => usr.idUser = "JoeSoap" )
                            // The following .Select(...) is the Dynamic Linq extension
                            .Select ( "FP{0}, FP{1}",  fingersinuse[0], fingersinuse[1]);
    
    
    

    However I believe that 'result' is not strongly typed so I don't know if your can then use 'result.FPLeftRingFinger'. Instead you might have to use reflection to get the values of FP{0} and FP{1} as something like ...

     

    PropertyInfo[] properties = result.GetProperties();
    
    var fp0property = properties.Where(prop => prop.PropertyName == "FPLeftRingFinger").SingleOrDefault();
    
    object val = null;
    if (fp0property != null)
    {
    	// sorry I can't remember the exact syntax of GetValue off the top of my head
    	val = fp0property.GetValue(...);
    }
    



    I am novice with Dynamic LINQ library, so please forgive me if I have been wrong.

     

    • Marked as answer by Jackie-Sun Monday, September 26, 2011 6:29 AM
    Saturday, September 17, 2011 7:23 PM
  • I've just re-read the link and think that the select statement should be "Select ( "new (FP{0}, FP{1})",  fingersinuse[0], fingersinuse[1])".
    Saturday, September 17, 2011 7:28 PM
  • Hmmm, nice link John.

    You can also achieve this by using Expression Trees

    http://msdn.microsoft.com/en-us/library/bb882637.aspx

    Expression trees are a very handy tool, very good for some cases, you should read the whole article, not only the part about dynamic queries.

    http://msdn.microsoft.com/en-us/library/bb397951.aspx

    However, this requires at least VS2008 to work

    Regards

    • Marked as answer by Jackie-Sun Monday, September 26, 2011 6:29 AM
    Tuesday, September 20, 2011 1:44 PM