none
LINQ query fields to String RRS feed

  • Question

  • Hi, I'm new at LINQ.

    I need some help... I have a code that brings to a datagridview the information from a database and it shows perfect. The problem is that I need to save each field of the database table, into a string. For example

    Name       Age

     May          22

    String1 = May

    String2 = 22

    And I've read a lot of articles, forums and blogs and I can't find anything similar.

    Please help me figure this out.

    • Moved by Vicky SongMicrosoft employee Thursday, May 24, 2012 6:02 AM (From:Visual Studio Database Development Tools (Formerly "Database Edition Forum"))
    Wednesday, May 23, 2012 9:33 PM

Answers

  • Hi nacho110987;

    This query is in the wrong syntax.

    Dim fields= From proof In db.Customers _
                Select New With 
                {
                    ' field in the below line is revieving 
                    ' the results of the query as can be seen from the first line
                    ' it should be the local variable of the query "proof"
                    .City = fields.City.ToString()
                }

    The query should be as follows:

    Dim fields= From proof In db.Customers _
                Select New With _
                { _
                    .City = proof.City.ToString() _
                }

    To your question, "But when I try to use the ".City" I become confused. e.g. msgbox(City)", the above query and all Linq queries return a collection of objects and in this case a collection of IEnumerable(Of Anonymous) types unless the query is acted upon by one of the methods that causes only one element to be returned such as the method First or Single. So to show all the cities you need to enumerable the collection such as the following:

    For Each c in fields
        ' Note that c represents a single Anonymous type that contains
        ' one property called City of type string.
        MessageBox.Show(c.City)
    Next

    To find out how many cities are in the collection you can do the following:

    fields.Count()

    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by nacho110987 Monday, May 28, 2012 7:43 PM
    Monday, May 28, 2012 4:28 PM

All replies

  • Hi nacho,

    I am moving your case to the LINQ to SQL forum so that you can get better support there.

    Thanks.


    Vicky Song [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, May 24, 2012 6:02 AM
  •  

    The question is a little vague. Can you please state what you have and what you need to do with some more details.

    Thanks

       


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Thursday, May 24, 2012 2:55 PM
  • OK. I have a Database, and a code that writes on that database with LINQ.

    So far so good.

    But the problem is that there is a moment in the code that I need to bring the information stored in the fields of the database and save it as string. And I don't know any function of LINQ that can be used to convert a field of a table or the result of a query into a string.

    I don't know if I'm clear, but summing up I need to convert the fields of my database in strings in my Visual Basic code using LINQ.

    Thanks.


    nacho110987

    Thursday, May 24, 2012 3:22 PM
  • Hi nacho110987;

    There is no Linq method that will convert all the fields of a query to a string format of that field. But what you can do is convert all the fields with in the query into strings. For example lets say you have an Employee table in the database and you have the following fields:

    EmployeeID  int 
    LastName    string
    FirstName   string
    BirthDate   DateTime

    and you need to return all fields as string then the query will look like this:

    var results = from emp in DataContext.Employee
                  where ....
                  select new
                  {
                      EmployeeID = emp.EmployeeID.ToString(),
                      LastName = emp.LastName,
                      FirstName = emp.FirstName,
                      BirthDate = emp.BirthDate.ToString()
                  };

    All the fields of the results will be strings, please note that if a field is already a string such as LastName and FirstName you do not have to add the ToString() in the select. Also note that each field must be in an assignment statement which gives it a new variable name.  

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".


    Thursday, May 24, 2012 4:02 PM
  • Hi Fernando

    I'm trying to implement your code, but I don't understand 2 things:

    First, what is e in e.EmployeeID.ToString(), and what is EmployeeID in the statement, if I haven't declared it?

    Second, if there is more than one row, and I need to select all the fields how is your code applicable?

    Honestly, I don't get it.


    nacho110987

    Friday, May 25, 2012 4:50 PM
  • Hi nacho110987;

    Sorry error in the code that e should have been emp and has been corrected in the original post.

    To your question, "First, what is e in e.EmployeeID.ToString(), and what is EmployeeID in the statement, if I haven't declared it?", the emp variable is a single instance of the Employee table in the example and EmployeeID is a column in that table. Of course not knowing the shape of the table and column names except what you posted as what you wanted the results to be it is difficult to give a more meaningful solution. But simply the select clause should have in this example emp.XXX where XXX is the column name you are returning.

    To your question, "Second, if there is more than one row, and I need to select all the fields how is your code applicable?", yes the code is applicable, just make sure there is one emp.XXX where XXX is the column name for each column in the table to be returned and that all returning column if not already a string data type add the ToString() method call to that entry in the select clause.

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Friday, May 25, 2012 5:24 PM
  • Fernando

    I'm trying to use your advice, but I've got an error. I'm working with the Northwind Database (The Microsoft default Database), and my statement code is:

    Dim db As New DataClasses1DataContext

    Dim City As String

    Dim custo As New Customers
    Dim i As Integer       

    Dim londoncusts = From cust In db.Customers Where cust.City = "London" Select cust

           

    Dim base1= From dbdb In db.Customers Select New City = dbdb.City.ToString

    But there is an error in dbdb.City.Tostring, even when visual accepts the use of

    City = dbdb.City.ToString

    it says "The type City is not defined"

    Please Help me


    nacho110987

    Friday, May 25, 2012 9:11 PM
  • Hi nacho110987;

    This query will select all the customers who live in London and returns a collection of those customers  and NOT just a single customer.

    Dim db As New DataClasses1DataContext
    
    Dim londoncusts = From cust In db.Customers _
                      Where cust.City = "London" _
                      Select cust

    Now in your following query you are trying to use an anonymous type but the syntax is incorrect. Try it as follows:

    Dim base1= From dbdb In db.Customers _
               Select New With _
               { _
                   .City = dbdb.City.ToString() _
               }

    Please note the keyword With after the New keyword and that no class name follows the With keyword which means that we will be creating a Anonymous type. With Visual Basic the fields name in an anonymous type are preceded by a "." this is not so with C#. Also this field, City, is already a string type and does not need the ToString() method only those fields that are of a non string data type needs the ToString() method to convert them to a string field.

    Also all Linq queries will return a collection of objects unless you use a Single, SingleOrDefault, First, or FirstOrDefault method on the query.

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Saturday, May 26, 2012 1:38 AM
  • Hi Fernando

    i appreciate your help. I applied your sentence and the error message stopped. But now I don't know how to handle the new variable, I mean I know that we declared a variable with no type and there we put some field form the database, but how can I use it? or rather how can I know how many cities were assigned to that variable? and how can I call them?

    I have now this

    Dim fields= From proof In db.Customers Select New With {.City = fields.City.ToString()}

    But when I try to use the ".City" I become confused. e.g. msgbox(City)

    Thank you for helping me and sorry for bother you one more time.


    nacho110987

    Monday, May 28, 2012 3:47 PM
  • Hi nacho110987;

    This query is in the wrong syntax.

    Dim fields= From proof In db.Customers _
                Select New With 
                {
                    ' field in the below line is revieving 
                    ' the results of the query as can be seen from the first line
                    ' it should be the local variable of the query "proof"
                    .City = fields.City.ToString()
                }

    The query should be as follows:

    Dim fields= From proof In db.Customers _
                Select New With _
                { _
                    .City = proof.City.ToString() _
                }

    To your question, "But when I try to use the ".City" I become confused. e.g. msgbox(City)", the above query and all Linq queries return a collection of objects and in this case a collection of IEnumerable(Of Anonymous) types unless the query is acted upon by one of the methods that causes only one element to be returned such as the method First or Single. So to show all the cities you need to enumerable the collection such as the following:

    For Each c in fields
        ' Note that c represents a single Anonymous type that contains
        ' one property called City of type string.
        MessageBox.Show(c.City)
    Next

    To find out how many cities are in the collection you can do the following:

    fields.Count()

    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by nacho110987 Monday, May 28, 2012 7:43 PM
    Monday, May 28, 2012 4:28 PM
  • Hi Fernando

    Thank you very much, in spite of not being this the initial subject, your answer is as good and aplicable as the one I was looking for.

    Thanks for your time and your concern.


    nacho110987

    Monday, May 28, 2012 7:43 PM
  •   

    Not a problem, always glad to help.

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Monday, May 28, 2012 10:07 PM