locked
Retrieving LINQ data from method within class? RRS feed

  • Question

  • Hello everyone,

    I have a problem with LINQ to SQL. Actually it is working quite well, but when I want to put my code into a class then it goes wrong. So let’s say I want to read data from out of my database into a datagridview. The class doesn’t recognize the datagridview. This means that I need to have a proper method within the class that returns the correct type. There it goes wrong.

    Public void GetUsersIntoTable()
            {
                DBPersonDataContext dc = new DBPersonDataContext();
                var q = from a in dc.GetTable<Person>()
                        select new { a.PersonID, a.FirstName, a.LastName };
                dataGridViewPerson.DataSource = q;
    		return q;
    		//or..
    		Return List<Person>
            }

    Any tips how it should work and why it isn’t working like I want to?

    Greetings,

    Spacelama


    Wednesday, April 25, 2012 8:09 PM

Answers

  • Create a new class "Person" or "PersonLight" and give it the properties that you need.  Instead of selecting into an anonomous type, create a new PersonLight and fill it so that you can return an IEnumerable<PersonLight>, IQueryable<PersonLight> or List<PersonLight>.
    • Marked as answer by Lie You Wednesday, May 9, 2012 5:29 AM
    Wednesday, April 25, 2012 8:30 PM
  • The query is a queryable of an anonymous type.  You can't do what you need to with an anonymous type.

    If the select really isn't important then just return "dc.GetTable<Person>" and be done with it.  If you want to have the Select so that you can limit the columns then you'll need to create a new class, PersonLight, as I suggested a long time ago, give it fields for everything that you have in your current Select statement, and then rather than creating a new anonymous type Select into that new PersonLight type like so:

    select new PersonLight { a.PersonID, a.FirstName, a.LastName };

    Then you can just return the query like that.  (Obviously you'll need to change the return type of the method appropriately.

    • Marked as answer by Lie You Wednesday, May 9, 2012 5:29 AM
    Monday, April 30, 2012 1:25 PM

All replies

  • On 4/25/2012 4:09 PM, SpaceLama wrote:
    > Public void GetUsersIntoTable()
    >          {
    >              DBPersonDataContext dc = new DBPersonDataContext();
    >              var q = from a in dc.GetTable<Person>()
    >                      select new { a.PersonID, a.FirstName, a.LastName };
    >              dataGridViewPerson.DataSource = q;
    >         return q;
    >         //or..
    >         Return List<Person>
    >          }
     
    Public List<Person> GetUsersIntoTable()
    {
        return from a in dc.GetTable<Person> select new {a.PersonID,
    a.Firstname, a.Lastname};
     
    }
     
    OR
     
    Public List<Person> GetUsersIntoTable()
    {
         var persons = new List<Person>();
          var q = from a in dc.GetTable<Person>()
                          select new { a.PersonID, a.FirstName, a.LastName };
         foreach(var person in q)
        {
             persons.Add(person);
        }
         return persons;
    }
       dataGridViewPerson.DataSource =  GetUsersIntoTable();
     
    Wednesday, April 25, 2012 8:22 PM
  • Create a new class "Person" or "PersonLight" and give it the properties that you need.  Instead of selecting into an anonomous type, create a new PersonLight and fill it so that you can return an IEnumerable<PersonLight>, IQueryable<PersonLight> or List<PersonLight>.
    • Marked as answer by Lie You Wednesday, May 9, 2012 5:29 AM
    Wednesday, April 25, 2012 8:30 PM
  • With the second example I get the following error:

    Error 1 The best overloaded method match for 'System.Collections.Generic.List<AddressBook.Person>.Add(AddressBook.Person)' has some invalid arguments 

    And the first example gives me this error:

    Error 3 'AddressBook.Person' is a 'type' but is used like a 'variable'

    And

    Error 4 Invalid expression term 'select' 

    Any tips or explanation why it is going wrong?

    Greetings,

    Spacelama

    Wednesday, April 25, 2012 8:39 PM
  • On 4/25/2012 4:39 PM, SpaceLama wrote:
    > With the second example I get the following error:
    >
    > Error 1 The best overloaded method match for
    > 'System.Collections.Generic.List<AddressBook.Person>.Add(AddressBook.Person)'
    > has some invalid arguments
    >
    > And the first example gives me this error:
    >
    > Error 3 'AddressBook.Person' is a 'type' but is used like a 'variable'
    >
    > And
    >
    > Error 4 Invalid expression term 'select'
    >
    > Any tips or explanation why it is going wrong?
    >
    > Greetings,
    >
    > Spacelama
    >
     
    What is the scheme for this Model?
     
    AddressBook
       Person
         Address
     What is this object?
     
     
    Wednesday, April 25, 2012 8:44 PM
  • With the second example I get the following error:

    [...]

    Any tips or explanation why it is going wrong?

    My previous post explains why his doesn't work.  You need to select the query into a class, rather than using an anonymous class.
    • Edited by servy42 Wednesday, April 25, 2012 8:56 PM
    Wednesday, April 25, 2012 8:56 PM
  • @ servy42:  What do you mean by anonymous class? Person is a class that is derrived from the database table. Or are you saying the method doesnt has the right return type?

    Database explanation:

    Addressbook contains 2 tables: Person and Address.

    Person is the child of Address.... which I dont know if that is correct, but I couldnt the other way around. Person contains name of the person and addressID and Address contains the properties to capture the total address.

    Thursday, April 26, 2012 7:10 PM
  • This line of code:

    select new { a.PersonID, a.FirstName, a.LastName };

    Is creating an anonymous type, not an instance of Person.  You can't return that type from the method because it's anonymous, there's no way for the caller to reference it.  You need to not use an anonymous type in that code segment.

    The code provided as an answer tried to put a query of that anonymous type into a List of people, but this anonymous type isn't a Person, you need to change it so that it is a person, or some other type at the very least.

    Thursday, April 26, 2012 7:18 PM
  • I am working on the method again and got the following code:

    public IQueryable<Person> GetUsersIntoTable()
            {
                using (AddressBookDataContext dc = new AddressBookDataContext(ConfigurationManager.ConnectionStrings["ConnP"].ConnectionString))
                {
                    var q = from a in dc.GetTable<Person>()
                            select new { a.PersonID, a.FirstName, a.LastName };
                    IQueryable<Person> persons = dc.Persons;
                    persons = (IQueryable<Person>)q;
                    return persons;
                }
            }

    I get the following error:

    Unable to cast object of type 'System.Data.Linq.DataQuery`1[<>f__AnonymousType0`3[System.Int32,System.String,System.String]]' to type 'System.Linq.IQueryable`1[AddressBook.Person]'.

    How can I solve this?

    Greetings,

    Spacelama

    Sunday, April 29, 2012 6:52 AM
  • The query is a queryable of an anonymous type.  You can't do what you need to with an anonymous type.

    If the select really isn't important then just return "dc.GetTable<Person>" and be done with it.  If you want to have the Select so that you can limit the columns then you'll need to create a new class, PersonLight, as I suggested a long time ago, give it fields for everything that you have in your current Select statement, and then rather than creating a new anonymous type Select into that new PersonLight type like so:

    select new PersonLight { a.PersonID, a.FirstName, a.LastName };

    Then you can just return the query like that.  (Obviously you'll need to change the return type of the method appropriately.

    • Marked as answer by Lie You Wednesday, May 9, 2012 5:29 AM
    Monday, April 30, 2012 1:25 PM