LINQ help for a beginner selecting multiple tables

3
votes

I have the following repository that includes three methods for returning my db objects, the first two queries work fine as they simly return one list of data, however as the thrird method requires a selection from both tables im a bit confused with how to do this.

Can anyone kindly point me in the right direction as how to write a LINQ query to select from two related tables to pass into the last method (CustomerAndSites) in the repository shown below - the tables are related on a customer ID field, 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CustomerDatabase.Domain.Abstract;
using CustomerDatabase.Domain.Concrete;
using CustomerDatabase.Domain.Entities;
using System.Data.Linq.Mapping;
using System.Data.Linq;
using System.Web.Mvc;

namespace CustomerDatabase.Domain.Concrete
{
 class SqlCustomersAndSitesRepository : ICustomersAndSitesRepository
{
    public Table<CustomerSite> customerSitesTable;
    public Table<Customer> customerTable;                              


    public SqlCustomersAndSitesRepository(string connectionString)
    {
        customerSitesTable = (new  DataContext(connectionString)).GetTable<CustomerSite>();
        customerTable = (new DataContext(connectionString)).GetTable<Customer>();
    }

    public IQueryable<CustomerSite> CustomerSites
    {
        get { return customerSitesTable; }
    }

    public IQueryable<Customer> Customers
    {
        get { return customerTable; }
    }

    public IQueryable <ICustomersAndSitesRepository> CustomerAndSites
    {
        get { return CustomerAndSites; }
    }

}

}

====Update

This is my ICustomersAndSitesM interface, where do i define the CustomersAndSitesMix, do i need to create that as a seperate entity? i have a view model in my UI project that contains the properties for both objects.

using System; using System.Collections.Generic; using System.Linq; using System.Text; using CustomerDatabase.Domain.Entities;

namespace CustomerDatabase.Domain.Abstract { interface ICustomersAndSitesM { IQueryable Customers { get; } IQueryable CustomerSites { get; } }

}

2
c#linqasp.net-mvc-2repositoryviewmodel
Why would you want to return an IQueryable of ICustomersAndSitesRepository? It doesn;t make any sense to me – Chandu
What are you doing in your constructor? ! Have you run/tested any of this code? – Nix

2 Answers

0
votes

If your two tables are actually properly linked with a foreign key, LINQ will pull in the child records as well when you pull in the parent records. You need to examine the model that your DataContext returns when you are debugging and you should be able to see any linked records with enough digging. For example, if you have Sites linked to Customers, in your Customer domain model result, you should see a field called "Sites" that is a list of site domain models linked to the customer.

0
votes

Instead of returning an 'ICustomersAndSitesRepository', I guess you actually want to get an 'CustomersAndSitesMix'. If so, you could do this :

public IQueryable<ICustomersAndSitesM> CustomerAndSites
{
    get
    {
        return from customer in customerTable
               join site in customerSitesTable
                    on customer.PLACEKEYHERE equals site.PLACEKEYHERE
               select new CustomersAndSitesMix(customer, site);
    }
}