Linq to SQL: Multi-table join return type not generated by dbml

Question

Hello, I'm trying to figure out the best way to handle a simple problem: I have a simple LINQ join to two tables. I know how to return the type for one table, since it is the same as the generated dbml class. However, what if I want to return data from both tables- isn't there a way to return both and use their relationships? Do I really have to create another return type to return the data from both tables? FYI- I don't want to return an output parameter with the other table object; I'm also not really interested in returning an anonymous type. What is the best practice recommendation?

    public IQueryable<Consumer_Question> GetQuestions(int subCategoryId)
    {
        //create DataContext
        MototoolsDataContext mototoolsDataContext = new MototoolsDataContext();
        mototoolsDataContext.Log = Console.Out;

        var subcategoriestag = (from subCatTag in mototoolsDataContext.Consumer_SubCategoriesTags
                                join tagQuestion in mototoolsDataContext.Consumer_TagQuestions on subCatTag.TagID equals tagQuestion.TagID
                                join question in mototoolsDataContext.Consumer_Questions on tagQuestion.QuestionsID equals question.ID
                                where subCatTag.SubCategoriesID == subCategoryId
                                orderby subCatTag.ID descending
                                select question);
                                //select new { question, tagQuestion });

        return subcategoriestag;
    }

Thanks for any help,

Answer 1

If you have defined your relationships in the LINQ-to-SQL designer then your above query doesn't need the join syntax at all, simply 'walk the tree' as needed, e.g.:

var subCategoriesTag = (
    from subCatTag in motoToolsDataContext
    from tagQuestion in subCatTag.TagQuestions
    from question in tagQuestion
    where subCatTag.SubCategoriesID == subcategoryId
    orderby subCatTag.ID descending
    select question
);

Note that the 2nd and 3rd 'from' statements are using the object from the previous one, since LINQ-to-SQL should already know about the relationship.

Without knowing more about your relationships it's harder to give a more exact answer. I had to make some assumptions about what the related properties were.

Answer 2

Sounds to me like what you are looking for is DataLoadOptions.LoadWith<>. That way you return your Question object and the related objects are populated at the same time through the defined associations. Something like this:

public IQueryable<Consumer_Question> GetQuestions(int subCategoryId)
{
    //create DataContext
    using (MototoolsDataContext mototoolsDataContext = new MototoolsDataContext())
    {
     mototoolsDataContext.Log = Console.Out;
     DataLoadOptions options = new DataLoadOptions();
     options.LoadWith<Consumer_Questions>(q => q.Consumer_TagQuestions);
     options.LoadWith<Consumer_TagQuestions>(tag => tag.Consumer_SubCategoriesTags);
     mototoolsDataContext.LoadOptions = options;

     var questions = (from subCatTag in mototoolsDataContext.Consumer_SubCategoriesTags
           join tagQuestion in mototoolsDataContext.Consumer_TagQuestions on subCatTag.TagID equals tagQuestion.TagID
           join question in mototoolsDataContext.Consumer_Questions on tagQuestion.QuestionsID equals question.ID
           where subCatTag.SubCategoriesID == subCategoryId
           orderby subCatTag.ID descending
           select question);
           //select new { question, tagQuestion });

     return questions;
    }
}