none
LINQ to SQL with variable column name RRS feed

  • Question

  • I have no idea what I am doing.

    I wrote a webservice that will query a SQL database, get a CSV string, parse it into an ArrayOfSrings and return that array of strings as XML.

    The code is working properly.  I am stuck though in needing to specify the name of the column that i want to pull data from in the table.  I would rather pass the column name to the function and have the function return the values for any column in the table.  It took me several days to get this far, and I've been trying to variablize the column name for the last few days without any success.  I've tried reading postings on dynamic linq, but I don't really get it.

    Here is my Code.  You will see that the function takes a "SettingName" argument, however the SQL query specifies (hard coded) the column "ADComputerGroups"  I would like to remove the reference to ADComputerGroups and be able to return any of the dozens of columns in the "Settings" table.  You can assume that the query will return exactly one row. that row has several dozen columns and I want to be able to call the function specifying the column to work with rhather than it being hard coded to a specific column.  It seems so simple, but it has already eaten two days.

     Public Function GetListSetting(ByVal ID As String, ByVal Type As String, ByVal SettingName As String) As List(Of String)
            Dim MDTDB As New MDT2012Entities
            Dim strSetting As String
    
            Dim query = From sets In MDTDB.Settings _
                        Where sets.ID = ID _
                        And sets.Type = Type _
                        Select sets.ADComputerGroups
    
            If query.Count() = 1 Then
                strSetting = Query.Single
            Else
                strSetting = ""
            End If
            Dim strArray = Split(strSetting, ",")
            Dim strList As New List(Of String)
            If strArray.Count > 0 Then
                strList = strArray.ToList
            End If
    
            Return strList
    
        End Function

     
    Friday, January 3, 2014 8:09 PM

Answers

  • Hi,

    We can think about why it needs a string parameter. Because since it is a string type, we can make it be what we want.

    >> I want to send in to function "Steve" "USA"  "Zipcode" and get returned "02132"

    So the “Steve” and “USA” should be where clause and the “Zipcode” is column that you want, then we just need to write codes as below:

    string returnColumns = "new(Zipcode)";
    
    
                    string whereClause = "Name==\"Steve\"&&Country==\"USA\"";
    
    
                    var result = db.Tables.Where(whereClause).Select(returnColumns);
    

    >> I want to send the same function "Steve" "USA" "Phone Number" and have it return "315 211 4456" For this, just change the returnColumns to be:

    string returnColumns = "new(Phone Number)";

    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.

    Tuesday, January 7, 2014 10:16 AM
    Moderator

All replies

  • Hello,

    According to your description, what you want is a dynamic select method. For this, what you need is the Dynamic LINQ.dll. We can specify the columns we want like below:

    For details, please refer to link below:

    http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

    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.

    Monday, January 6, 2014 9:50 AM
    Moderator
  • Thanks for your help...

    Believe me, I have read that post many times and it is all greek to me.  I can't translate that into the information I need to do what I want.  Every google search about variable columns points to that article and everyone responds with "thanks" and "great post" which makes me feel extra stupid.

    I should be able to figure out how to link the DLL so don't worry about that part. but in the code samples there, I don't see them specifying a varable column in the select statement. 

    As far as I can tell they are still hard coding in the column names that they are pulling in the select statement - those still look hard coded to me.

    I also have trouble finding examples in VB, and my C -> VB translation skills are poor at best.

    Monday, January 6, 2014 5:06 PM
  • I think maybe I am misunderstanding.  Aren't I already using LINQ to read from SQL?  I think I am.  I am already specifying variables for the "Where" clause.  What I am having trouble with is selecting the single string from the query where the column description matches a variable.

    If the query returns a single row, how do I get the data from a single field from that row where the field description = some string variable.

    Like say I have a SQL Table with

    Name     Country    City      Zipcode   Phone number

    Mike       Spain       Madrid  NA           312 956 3020

    Steve    USA         Boston  02132        315 211 4456

    I want to send in to function "Steve" "USA"  "Zipcode" and get returned "02132"

    I want to send the same function "Steve" "USA" "Phone Number" and have it return "315 211 4456"

    There are lots of colums in the table.  I can get to the row I want, but I can't figure out how to pull data from the column specified by a variable.

    Monday, January 6, 2014 5:56 PM
  • Hi,

    We can think about why it needs a string parameter. Because since it is a string type, we can make it be what we want.

    >> I want to send in to function "Steve" "USA"  "Zipcode" and get returned "02132"

    So the “Steve” and “USA” should be where clause and the “Zipcode” is column that you want, then we just need to write codes as below:

    string returnColumns = "new(Zipcode)";
    
    
                    string whereClause = "Name==\"Steve\"&&Country==\"USA\"";
    
    
                    var result = db.Tables.Where(whereClause).Select(returnColumns);
    

    >> I want to send the same function "Steve" "USA" "Phone Number" and have it return "315 211 4456" For this, just change the returnColumns to be:

    string returnColumns = "new(Phone Number)";

    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.

    Tuesday, January 7, 2014 10:16 AM
    Moderator
  • This is quite an old thread now but is still the best and simplest solution to be found in a Google search on how to pass variable column name(s) to a linq query. As Todd Miller says, the ScottGu blog that many seem to praise is actually of little use on this subject.

    In case it is of help to others here is my vb solution:

    Dim returnColumn As String = tblSubjectOrderColumn            
    Dim whereClause As String = "ReportID=" & ReportID            
    
    Dim result = db.SubjectOrders.Where(whereClause).Select(returnColumn)            
    
    If result.Count = 1 Then               
    For Each subj In result                    
    Dim x As String = subj               
    Next           
    End If

    Wednesday, June 19, 2019 4:15 PM