Kiwi Ingenuity

I have been struggling today to Read data from multiple SQL tables.

I am using the MySQL Net Connector (http://dev.mysql.com/downloads/connector/net/) and I have imported the MySQL database into Visual Studio (ADO.NET Entity Data Model) and created the relations.

I now want to collect some data from the database into my custom Business Entity.  If I query a single table I have no issues.  If I query multiple tables however, the query fails.

Here is the code I have been using:

public List<Property> GetProperties()
{
   using (ncsEntities dataContext = this.ncsDataContext)
   {
      // Begin Test #1
      var q1 = from p in dataContext.ra_properties.Top("3")
               where p.street_id > 0
               select p;

      List<ra_properties> list1 = q1.ToList();
      // End Test #1   list2 is populated as expected
      // The property ra_streets is populated and is not null


      // Begin Test #2
      var q2 = from p in dataContext.ra_properties.Top("3")
               where p.street_id > 0
               select new Property
               {
                  Key2 = p.valuation_id,
                  Address = "Some Dummy Value"
               };

       List<Property> list2 = q2.ToList();
       // End Test #2
       // list2 is populated as expected.


       // Begin Test #3
       var q3 = from p in dataContext.ra_properties.Top("3")
                where p.street_id > 0
                select new Property
                {
                    Key2 = p.valuation_id,
                    Address = (p.ra_streets == null || p.ra_streets.address_1 == null) ? string.Empty : p.ra_streets.address_1
                };

       List<Property> list3 = q3.ToList();
       // End Test #3
       // This Test Fails.  The exception message is
       // Object reference not set to an instance of an object.

        return list3;

   }
}

I posted the issue on Stack Overflow and I still awaiting a reply that resolves this issue.

As a work around I changed the code as follows:

public List<Property> GetProperties3()
{
  using (ncsEntities dataContext = this.ncsDataContext)
  {
    string query = "SELECT ra_properties_0.valuation_id AS Key2, 
                    ra_streets_0.address_1 as Address 
                    FROM 
                    ncs.ra_properties ra_properties_0, 
                    ncs.ra_streets ra_streets_0 
                    WHERE ra_streets_0.street_id = ra_properties_0.street_id 
                    LIMIT 0,3;";
    IEnumerable<Property> result = dataContext.ExecuteStoreQuery<Property>(query);
    List<Property> list = result.ToList();
    return list;
  }
}