Linq mapping to multiple tables

Answer 1

Just add the related tables to your repository interface, just as you have for Product and then create the concrete implementations in your repository class, again just as you have for Product.

I've used the same pattern on my app, I have two repositories, each handling 5-10 tables. There are two distinct groups of tables which are related, hence two repositories.

I would change the SQLRepository constructor thus:

    public SqlProductsRepository(string connectionString)
    {
        DataContext dc = new DataContext(connectionString);
        productsTable = dc.GetTable<Product>();
    }

You can then extend it easily thus e.g.:

    private Table<Order> ordersTable;

    public SqlProductsRepository(string connectionString)
    {
        DataContext dc = new DataContext(connectionString);
        productsTable = dc.GetTable<Product>();
        ordersTable = dc.GetTable<Order>();
    }

    IQueryable<Order> Orders
    {
        get { return from o in ordersTable select o; }
    }

EDIT - Answering comment

Here's an example of how to deliver subordinate objects (related tables) via this method:

[Table(Name="Projects")]
public class Project
{
    [Column(IsPrimaryKey = true, IsDbGenerated = true, AutoSync = AutoSync.OnInsert)]
    public Guid ID { get; set; }
    [Column]
    public String Name { get; set; }
    [Column]
    public bool Active { get; set; }

    [Association(ThisKey="ID", OtherKey = "ProjectID")]
    private EntitySet<ProjectDate> _projectDates = new EntitySet<ProjectDate>();
    public IQueryable<ProjectDate> ProjectDates
    {
        get { return _projectDates.AsQueryable(); }
    }
}

And the ProjectDate class for completeness

[Table(Name="ProjectDates")]
public class ProjectDate
{
    [Column(IsPrimaryKey = true, IsDbGenerated = true, AutoSync = AutoSync.OnInsert)]
    public Guid ID { get; set; }
    [Column]
    public Guid ProjectID { get; set; }
    [Column]
    public DateTime TargetDate { get; set; }
    [Column(CanBeNull = true)]
    public DateTime? ActualDate { get; set; }
    [Column(CanBeNull=true, IsDbGenerated = true)]
    public DateTime? Created { get; set; }

    private EntityRef<Project> _project;
    [Association(ThisKey = "ProjectID", Storage = "_project", OtherKey = "ID")]
    public Project Project
    {
        get { return _project.Entity; }
        set { _project.Entity = value; ProjectID = _project.Entity.ID; }
    }
}