How do I associate custom made virtual objects to link to relational objects working like Entity Framework?

user_1978421 0
Viewed 44

I have a CustomerDetails and a CustomerAddress object. The constructors are as follow:

public class CustomerDetails
{
    [Key]
    public int CustomerID { get; set; }

    [Required]
    [StringLength(50)]
    public string CustomerName { get; set; }

    public int AddressID { get; set; }

    public virtual CustomerAddress customerAddress { get; set; }
}

public class CustomerAddress
{
    [Key]
    public int AddressID { get; set; }

    [Required]
    [StringLength(200)]
    public string Address { get; set; }
    public virtual ICollection<CustomerDetails> CustomerDetails { get; set; }
}

I have copied these two constructors from generated code of Entity Framework.

I have used SqlDataReader to populate the customer list and address list

List<CustomerDetails> customers = new List<CustomerDetails>();
List<CustomerAddress> addresses = new List<CustomerAddress>();

SqlCommand cmd = new SqlCommand("select * from Customerdetails", con);

using (IDataReader rdr = cmd.ExecuteReader())
{
    while (rdr.Read())
    {
        CustomerDetails customer = new CustomerDetails
                    {
                        CustomerID = Convert.ToInt32(rdr["CustomerID"]),
                        AddressID = Convert.ToInt32(rdr["AddressID"]),
                        CustomerName = rdr["CustomerName"].ToString()
                    };
        customers.Add(customer);
    }
}

cmd = new SqlCommand("select * from CustomerAddress", con);

using (IDataReader rdr = cmd.ExecuteReader())
{
    while (rdr.Read())
    {
        CustomerAddress address = new CustomerAddress
                    {
                        AddressID = Convert.ToInt32(rdr["AddressID"]),
                        Address = rdr["Address"].ToString()
                    };
                    addresses.Add(address);
    }
}

When I try to bind data using LINQ to display customer's address, it doesn't work as intended.

GridView1.DataSource = from c in customers
                       select new { c.CustomerName, c.customerAddress.AddressID};
GridView1.DataBind();

I get an error:

Object reference not set to an instance of an object

I knew it's going to happen. But what's the trick to make the virtual objects linking to the relational object so that the reference will work?

entity-framework linq c#
1 Answers

I think you might need to populate the virtual properties yourself, or just remove them and use an inner join in your query.

// populate
For Each CustomerDetail from db        
    IF CustomerDetail.AddressId NOT IN addresses
       Fetch CustomerAddress from Db
       addresses.Add(CustomerAddress )
    ELSE
       FETCH CustomerAddress from addresses
    END
    CustomerDetail.customerAddress = CustomerAddress
    CustomerAddress.CustomerDetails.Add(CustomerDetail)
    customers.Add(CustomerDetail)


//inner join
from c in customers
      join a in addresses on c.AddressID equals a.AddressID
      select new { c.CustomerName, a.Address}
user_70143750
Related