title: Linq mapping to multiple tables
body: <p>I working my way through the Apress "Pro ASP.NET MVC Framework" ( <a href="http://www.apress.com/book/view/9781430210078" rel="nofollow noreferrer">http://www.apress.com/book/view/9781430210078</a> ) book, and in an example the author creates a link to a db table (as well as a fake repository) using linq like this:-</p>
  
  <pre><code>using System;
  using System.Collections.Generic;
  using System.Linq;
  using System.Text;
  using System.Data.Linq.Mapping;
  
  namespace DomainModel.Entities
  {
      [Table(Name = "Products")]
      public class Product
      {
          [Column(IsPrimaryKey = true, IsDbGenerated = true, AutoSync = AutoSync.OnInsert)]
          public int ProductID { get; set; }
  
          [Column] public string Name { get; set; }
          [Column] public string Description { get; set; }
          [Column] public decimal Price { get; set; }
          [Column] public string Category { get; set; }
          public string this[string propName]
          {
            get {
              if ((propName == "Name") && string.IsNullOrEmpty(Name))
                  return "Please enter a product name";
              if ((propName == "Description") && string.IsNullOrEmpty(Description))
                  return "Please enter a description";
              if ((propName == "Price") && (Price &lt; 0))
                  return "Price must not be negative";
              if ((propName == "Category") && string.IsNullOrEmpty(Category))
                  return "Please specify a category";
              return null;
          }
      }
      public string Error { get { return null; } } // Not required    }
  }
  
  </code></pre>
  
  <p>creating an interface:-</p>
  
  <pre><code>using System;
  using System.Collections.Generic;
  using System.Linq;
  using System.Text;
  using DomainModel.Entities;
  
  namespace DomainModel.Abstract
  {
      public interface IProductsRepository
      {
          IQueryable Products { get; }
          void SaveProduct(Product product);
          void DeleteProduct(Product product);
      }
  }
  </code></pre>
  
  <p>a fake repository (not included) and then a real DB connection repository:-</p>
  
  <pre><code>using System.ComponentModel;
  using System.Collections.Generic;
  using System.Linq;
  using System.Text;
  using DomainModel.Abstract;
  using System.Data.Linq;
  using DomainModel.Entities;
  
  namespace DomainModel.Concrete
  {
      public class SqlProductsRepository : IProductsRepository
      {
          private Table productsTable;
          public SqlProductsRepository(string connectionString)
          {
              productsTable = (new DataContext(connectionString)).GetTable();
          }
  
          public IQueryable Products
          {
              get { return productsTable; }
          }
  
          public void SaveProduct(Product product)
          {
              EnsureValid(product, "Name", "Description", "Category", "Price");
  
              // If it's a new product, just attach it to the DataContext
              if (product.ProductID == 0)
                  productsTable.InsertOnSubmit(product);
              else {
                  // If we're updating an existing product, tell the DataContext
                  // to be responsible for saving this instance
                  productsTable.Attach(product);
                  // Also tell the DataContext to detect any changes since the last save
                  productsTable.Context.Refresh(RefreshMode.KeepCurrentValues, product);
              }
  
              productsTable.Context.SubmitChanges();
          }
  
          public void DeleteProduct(Product product)
          {
              productsTable.DeleteOnSubmit(product);
              productsTable.Context.SubmitChanges();
          }
  
          private void EnsureValid(IDataErrorInfo validatable, params string[] properties)
          {
              if (properties.Any(x => validatable[x] != null))
                  throw new InvalidOperationException("The object is invalid.");
          }
      }
  </code></pre>
  
  <p>off to a DB table "Products" with the column name specified and it works nicely.  I am using this technique in a real-world app, as it handles the DB access layer nicely, but I need to be able to access several tables from my object.  How can I do this - do I need to split my object up into a hierarchy that reflects its tables or can I access more than 1 table from the main object, with other additional objects hanging off it that have their own tables?  If so then how do I create the ORM links between the objects and tables?</p>
  
  <p>Cheers</p>
  
  <p>MH</p>