dealing with property that does not map directly onto column on the database

Question

I have the following tables

Client Table and Product Table

ID
Name

ClientProduct Table

ID
ClientID
ProductID

Product class

 private int id;
    public int ID
    {
        get { return id; }
        set { id = value; }
    }
     protected string name;

    public Product () { }

    public Product (string name)
    {
        this.name = name;
    }

    public Product (string name)
    {
        this.name = name;
    }   
    public string Name
    {
        get { return name; }
        set { name = value; }
    }

Client class

     private int id;
    public int ID
    {
        get { return id; }
        set { id = value; }
    }
     protected string name;

    public Client () { }

    public Client (string name)
    {
        this.name = name;
    }

    public Client (string name)
    {
        this.name = name;
    }   
    public string Name
    {
        get { return name; }
        set { name = value; }
    }

ClientProduct class

        protected Client client;
    protected Product product;

    public ClientProduct  () { }

    public ClientProduct  (Client client,  Product product)
    {
        this.client= client;
        this.product= product;
    }

    public Client client        {
        get { return client; }
        set { client= value; }
    }

    public Product product      {
        get { return product; }
        set { product= value; }
    }

How can I do the following in petaPOCO?

    public static System.Collections.Generic.IList<ClientProduct> LoadForClient(Client client)
    {
        if (null != client)
            return Load("ClientID = " + client.ID);
        else
            return null;
    }

such that I can have list of all products for that client that I will later used in my view as

 private void LoadProducts(Client client )
    {
        Products = ClientProduct.LoadForClient(client)
            .Select(x => x.Product.Name)
            .OrderBy(x => x).ToArray();
    }

Answer 1

The 1:M and M:1 relationships seem like what you're after http://www.toptensoftware.com/Articles/115/PetaPoco-Mapping-One-to-Many-and-Many-to-One-Relationships

You can define a custom relator callback; Brad's example for two tables (if your products mapped directly to the client) would look something like this:

var posts = db.Fetch<Product, Client, ClientProduct>(
    (p,c)=> { p.client_obj = c; return p; },
    @"SELECT * FROM Product
    LEFT JOIN Client ON Product.clientId = Client.id ORDER BY Product.clientId 
    ");

I realize your dealing with a M:M relationship so you would need to update the above to map across the three objects, but the concept is the same. The key is that your 3rd argument in the call (ClientProduct) represents the joined row, and you can then reference the Client and/or Products directly from the single list.