ExecuteQuery<> with hierarchy objects and LINQ to SQL

Question

Let's say I have two objects:

public class Person{
     string Name { get; set;}
     Address Home { get; set;}
}

public class Address{
     string Street{ get; set;}
     string City { get; set;}
}


    string sql = "Select Name, Home_street, Home_city from user";
    var results = dc.ExecuteQuery<Person>(sql);

The problem here is that the Home street and home city are not populated. Is there a way I can make it work? I think it's solvable by using Association attribute, but not sure how.

I have the same problem if I use stored procedure. I'm trying to build a query tool so a lot of variables are not known ahead of time.

Answer 1

You need to make a data object with a flat shape to catch the results, then project those results into whatever shape you want.

public class PersonQueryResult
{
  string Name {get;set;}
  string Home_street {get;set;}
  string Home_city {get;set;}
}

string sql = "Select Name, Home_street, Home_city from user";
List<PersonQueryResult> results = dc.ExecuteQuery<PersoneQueryResult(sql).ToList();
List<Person> projectedResults = results.Select(pqr => new Person()
  {
    Name = pqr.Name,
    Home = new Address()
    {
      Street = pqr.Home_street,
      City = pqr.Home_city
    }
  }
).ToList();

---

What would you do with dynamically generated classes if you had them? It's not as though one can write compiler-checked code against those.

I would think the non-compiler-checked world of ADO.NET would solve this problem more appropriately. To wit.

string queryString = 
  "SELECT CustomerID, CompanyName FROM dbo.Customers";
SqlDataAdapter adapter = new SqlDataAdapter(queryString, connection);

DataSet customers = new DataSet();
adapter.Fill(customers, "Customers");