Fetching related data from database via sqldatareader and push into ViewModel/ViewModelCollections

Question

Hello all,

Lets assume I have the following 3 entities: Customer,Order,Product which interact in the View with the CustomerOrderProductViewModel.cs:

I have several controls like listbox,datagrid,etc.. to display those entities in my View.

Those Entities shall be fetched sort of eager loading. That would mean I have 3 sqldatareader in my DAL. Each sqldatareader read data from table Customer,Product,Order. What I have to consider now How do I get the Orders into the Products and the Products into the Customers List ? Read every related data in 3 for-loops into each other? And how do I get that releated data into my VMCollections so the Master Detail stays intact.

The MVVM purists and alpha geeks are very silent about that topic.

Answer 1

You can use LINQ for this assuming you have constructors setup on your Business objects that handles a DataReader and an object to copy. Although, I'm a little confused about the structure of your query, but I think this is what your saying.

public class ViewModel
{
    public ViewModel(DAL dal)
    {
        Customers = dal.GetCustomerFull().ToList();
    }

    public List<Customer> Customers { get; set; }
}

public class DAL
{
    public IEnumerable<Customer> GetCustomerFull()
    {
        var customers = GetCustomers().ToList();
        var products = GetProducts().ToList();
        var orders = GetOrders().ToList();

        var query = from c in customers
                    select new Customer(c)
                    {
                       Products = from p in products
                                  where p.Id = c.ProductId
                                  select new Product(p)
                                  {
                                     Orders = from o in orders
                                              where o.Id = p.OrderId
                                              select o;
                                  }
                   };

        return query;
    }

    public IEnumerable<Customer> GetCustomers()
    {
        // setup command
        var reader = new SqlDataReader(cmd);
        while (reader.Read())
        {
            yield return new Customer(reader);
        }
    }
}