WCF RIA Services, Joining Tables in Domain Service

Question

I'm making a Silverlight WCF RIA Services application. Although, I'm stuck attempting different ways to use multiple Tables in the database. Currently, I'm trying to join tables in the Domain Service Class and return it to the service agent. I started this project from a template located at: http://simplemvvmtoolkit.codeplex.com/wikipage?title=WCF%20RIA%20Services

I'm trying to join tables like:

    public IQueryable<Invoice> GetInvoices()
    {
        return (from i in this.ObjectContext.Invoices 
                join o in this.ObjectContext.otherTable equals condition 
                join s in this.ObjectContext.otherTable equals condition 
                select i); 
    }

This joins the tables correctly based on the given conditions. But I actually need to Project Fields from both the i.Invoices Tables & s.otherTable. Any suggestions to make this projection work in the DomainServiceClass.cs ?

SAMPLE CODE FROM USER "Chris" Suggestion:

    public class Invoice_PM
    {
    [Key]
    public int InvoiceId { get; set; }

    public string PaymentNum { get; set; }

    public DateTime? PaymentExpDate { get; set; }

    public DateTime? InvoiceDateTime { get; set; }

    [Include, Association("name", "InvoiceID", "InvoiceDateTime")]
    public IEnumerable<InvoiceSoldToShipTo_PM> SoldToShipTo { get; set; }
}

Answer 1

Your LINQ query is only using the joins to basically filter the Invoices since you are selecting the i variable at the end. I think the answer to this post will get you you the result you want:

Error: The entity or complex type cannot be constructed in a LINQ to Entities query

I suggest you create a custom class with all of the properties that you want to load and select the results of your LINQ statement into your custom class.

Custom Class:

public class CustomInvoice
{
    public int InvoiceID { get; set; }
    public int InvoiceNumber { get; set; }
    public int ProductID { get; set; }
    public string ProductName { get; set; }
    public int InvoiceID { get; set; }
    public string CustomerName { get; set; }
}

Domain Service Function:

public IQueryable<CustomInvoice> GetInvoices() 
{ 
    return (from i in this.ObjectContext.Invoices  
            join p in this.ObjectContext.Product equals condition  
            join c in this.ObjectContext.Customer equals condition  
            select new CustomInvoice
            { 
                InvoiceID = i.ID, 
                InvoideNumber = i.InvoiceNumber, 
                ProductID = p.ID, 
                ProductName = p.Name, 
                CustomerID = c.ID, 
                CustomerName = c.Name
            }; );  
} 

I haven't tried tested this code, but the idea should get you there.