Left join using Linq without the "messy" join

Question

A Cost is linked to an AnsweredQuestion in a PropertySurvey via QuestionID and AnswerID foreign keys. A Cost may also be linked to an AnsweredQuestion in the same PropertySurvey via the UnitsQuestionID and again via the ReplacementQuestionID. Here is the query as an inner join - written in a way that avoids the join syntax that @CraigStuntz regards as "messy"

var propertyCosts = from aq in answeredQuestions
    from a in aq.Answers
    from c in costs
    where aq.QuestionID == c.QuestionID && a.ID == c.AnswerID
    from uq in answeredQuestions
    where uq.QuestionID == c.UnitsQuestionID 
                        && uq.PropertySurveyID == aq.PropertySurveyID
    from rq in answeredQuestions
    where rq.QuestionID == c.ReplacementQuestionID 
                        && rq.PropertySurveyID == aq.PropertySurveyID
    select new PropertyCost(aq.Question.Text, 
    a.Text, 
    c.Amount, 
    uq.IntegerAnswer.GetValueOrDefault(1), 
    rq.IntegerAnswer.GetValueOrDefault(0));

Is it possible to rewrite this with left joins to UnitQuestion and ReplacementQuestion without that DefaultIfEmpty stuff?

If answers are already linked to questions why does the cost need to be linked to a question and answer, surely you can just link to the question? — Bob Vale, Jul 08 '13 at 12:03
If the question is "Roof Replacement Required?" and the answer is "Yes" then there is a cost specified by c.Amount. If the answer is "No" then there are no costs. — Colin, Jul 08 '13 at 12:09
But a UnitQuestion contains the value in its IntegerAnswer property - so no link to a separate answer in that relationship — Colin, Jul 08 '13 at 12:11
Ignoring the UnitQuestion and replacementQuestion. If a cost is associated with an answer and the answer is associated with with a question, surely for that case there is no need to link to a question, the foreign key just needs to link AnswerId. — Bob Vale, Jul 08 '13 at 12:48
The cost is associated with a question and an answer. Answers have a many to many relationship with questions. So "Yes" is a valid answer to many questions. If the question is "Is the roof in good condition?" and the answer is "Yes" then there is no cost. — Colin, Jul 08 '13 at 12:54
What about UnitQuestion and ReplacementQuestion are the many-many, 1-many or 1-1? — Bob Vale, Jul 08 '13 at 13:09
One to many. A cost can have 1 UnitQuestion. A UnitQuestion can be linked to many Costs. Same relationship for ReplacementQuestion — Colin, Jul 08 '13 at 13:29

score 0 · Answer 1 · answered Jul 08 '13 at 15:18

In order to follow Craig Stuntz you need to have associations that your logic can follow.

So to model your Many-Many you would have an intermediate table that had a 1-Many association back to Questions and Answers. You could use Cost here and have a zero cost for no cost.

So

Question(QuestionId) 1 -> 1..* Cost(QuestionId) Answer(AnswerId) 1 -> 1..* Cost(AnswerId) Question(QuestionId) 1 -> 0..* Cost(UnitQuestionId) Question(QuestionId) 1 -> 0..* Cost(ReplacementQuestionId)

Then in your model create the associations

Question (Property = Costs) -- (Property = Question) Costs
Question (no mapping) -- (Property = UnitQuestion) Costs
Question (no mapping) -- (Property = ReplacementQuestion) Costs
Answer (Property = Costs) -- (Property = Answer) Costs

This query should match the behaviour of your query.

from aq in answeredQuestions
from c in aq.Costs
select new PropertyCost(aq.Question.Text, 
                        c.Answer.Text, 
                        c.Amount, 
                        c.UnitQuestion == null ? 0 : c.UnitQuestion.IntegerAnswer
                        c.ReplacementQuestion == null ? 0 : c.ReplacementQuestion.IntegerAnswer

I now think the problem is not with my navigational properties or the type of relationship. The problem is that the sql equivalent is a left join with an additional constraint on the join. See this question http://stackoverflow.com/q/14740121/150342 and my answer — Colin, Jul 09 '13 at 13:57

score 0 · Accepted Answer · edited May 23 '17 at 12:29

//Make this problem less complicated to explain by abstracting the inner join 
//that we always need.
//For efficiency this should probably be put back in line
var A = from aq in answeredQuestions
        from a in aq.Answers
        from c in costs
        where aq.QuestionID == c.QuestionID && a.ID == c.AnswerID
        select new
        {
            QuestionText = aq.Question.Text,
            AnswerText = a.Text,
            Amount = c.Amount,
            PropertySurveyID = aq.PropertySurveyID,
            PropertySurvey = aq.PropertySurvey,
            UnitsQuestionID = c.UnitsQuestionID,
            UnitsQuestion = c.UnitsQuestion
        };


//This is how to do it using the explicit join keyword
var B = from a in A
        join lj in answeredQuestions on
            new { 
                   a.PropertySurveyID, 
                   UnitsQuestionID = a.UnitsQuestionID.GetValueOrDefault(0) 
                }
            equals new { 
                          lj.PropertySurveyID, 
                          UnitsQuestionID = lj.QuestionID 
                       }
            into unitQuestions
        from uq in unitQuestions.DefaultIfEmpty()
        select new PropertyCost(a.QuestionText,
                    a.AnswerText,
                    a.Amount,
                    uq == null ? 1 : uq.IntegerAnswer.GetValueOrDefault(1));

//I thought this might do it but the filter on the navigational property 
//means it's equivalent to an inner join
var C = from a in A
        from uq in a.PropertySurvey
                    .AnsweredQuestions
                    .Where(x => x.QuestionID == a.UnitsQuestionID)
        select new PropertyCost(a.QuestionText,
                    a.AnswerText,
                    a.Amount,
                    uq == null ? 1 : uq.IntegerAnswer.GetValueOrDefault(1));

//This is the solution
//Back to the basics described by @CraigStuntz
//Just that we have to navigate further to get to the Units value
//Is this less "messy" than a join? Not sure. Maybe if you can think in Linq...
var D = from a in A
        select new PropertyCost
        (
            a.QuestionText,
            a.AnswerText,
            a.Amount,
            a.PropertySurvey
                .AnsweredQuestions
                .Where(x => x.QuestionID == a.UnitsQuestionID)
                .FirstOrDefault() == null
            ?
            1
            : a.PropertySurvey
                .AnsweredQuestions
                .Where(x => x.QuestionID == a.UnitsQuestionID)
                .FirstOrDefault()
                .IntegerAnswer.GetValueOrDefault(1)
        );

//And here I have further refined it by putting the initial inner join back 
//inline and using the let keyword define how to retrieve the unit question. 
//This makes it much more readable:

var E = from aq in answeredQuestions
        from a in aq.Answers
        from c in costs
        let unitquestion = aq.PropertySurvey
            .AnsweredQuestions
            .Where(x => x.QuestionID == c.UnitsQuestionID)
            .FirstOrDefault()
        where aq.QuestionID == c.QuestionID && a.ID == c.AnswerID
        select new 
        {
            QuestionText = aq.Question.Text,
            AnswerText = a.Text,
            UnitCost = c.Amount,
            NumUnits = unitquestion == null ? 1 : unitquestion.IntegerAnswer ?? 1,
        };

Took me a long time to get this. I still think in sql and I can't help but be tempted to Use Views in Entity Framework instead. Please note I have simplified this to show only the first left join to UnitQuestion

Left join using Linq without the "messy" join

2 Answers2