Join Lambda Expression

问题:

im just trying to make a join from two entities.

the two entieties are as folows:

public partial class AnswerSet
{
    public int Id { get; set; }
    public string Ans { get; set; }
    public bool IsTrue { get; set; }
    public int QuestionId { get; set; }

    public virtual QuestionSet QuestionSet { get; set; }
}

and

public partial class QuestionSet
{
    public QuestionSet()
    {
        this.AnswerSets = new HashSet<AnswerSet>();
    }


    public int Id { get; set; }

    public string Quest { get; set; }

    public virtual ICollection<AnswerSet> AnswerSets { get; set; }
}

So, there are a question and an Answer Entity on the database. 1 Question has more answers (in my example 4). So now im tried this:

 var v1 = db.QuestionSets
    .Select(q => q)
    .Where(q=> q.Id == 11)
    .Join(db.AnswerSets, 
            q => q.Id, 
            a => a.QuestionId, 
            (a, q) => new { question = q });

So, now i have the following output when the expression is as above (see image 1):

Here i have just the Answers. when i chage the expression to:

var v1 = db.QuestionSets
    .Select(q => q)
    .Where(q=> q.Id == 11)
    .Join(db.AnswerSets, 
            q => q.Id, 
            a => a.QuestionId, 
            (q, a) => new { question = q });

then i have the following output (see image 2): (just the question, but 4 times. as many answers i have.)

So my question is, how can i join these two entities, that the Answers are a set in the QuestionSet entity?

thank you

Image 1 Image 2

回答1:

You want a group join (see http://msdn.microsoft.com/en-us/library/bb311040.aspx)

var QAs =
    from q in db.QuestionSets
    join a in db.AnswerSets 
       on q.Id equals a.QuestionId into answers
    select new { Question = q, Answers = answers };

In Extension Method syntax:

var QAs = db.QuestionSets
    .GroupJoin(
         db.AnswerSets, 
         q => q.Id, 
         a => a.QuestionId,
         (q, answers) => new {Question = q, Answers = answers});

Hope that helps

PS. Use it like so:

foreach (var qa in QAs)
{
    Console.WriteLine("Question #{0} has {1} answers", 
         qa.Question.Id, qa.Answers.Count());
}

回答2:

What about leaving the Join technique for complex query and use Ef Eager Loading by using Include

var v1 = db.QuestionSets.Include(b = b.Answer);

or

var v1 = db.QuestionSets.Include("Answer");