Allan
asked on
LINQ To Entity - Easy Left Join
Hi Experts!
Thanks for reading this.
Trying to convert this T-SQL to Linq to Entity:
Stuck trying to put the two pieces together.
The query would return All questions and if the user has answered the question it would be null for UserAnswered.
Any ideas? TIA!
If you need more info... This will create the class:
Thanks for reading this.
Trying to convert this T-SQL to Linq to Entity:
SELECT
sq.SurveyQuestionText
, sac.SurveyScaleText
FROM [dbo].SurveyQuestions sq
LEFT JOIN
(
SELECT
sa.SurveyQuestionID
, sc.SurveyScaleText
FROM SurveyUserAnswers sa
INNER JOIN SurveyScales as sc
ON sa.SurveyScaleID = sc.SurveyScaleID
WHERE sa.UserID = 2
) As sac
ON sq.SurveyQuestionID = sac.SurveyQuestionID
WHERE sq.SurveyQuestionCounter <= 10
This is the INNER JOIN:SomeDB db = new SomeDB();
var UserAnswered = (from ua in db.SurveyUserAnswers
join s in db.SurveyScales on ua.SurveyScaleID equals s.SurveyScaleID
where ua.UserID == 2
select new
{
ua.SurveyQuestionID,
s.SurveyScaleText
}).ToList();
A LEFT JOIN looks like this:var query = (from sq in SurveyQuestions
from sac in //UserAnswered above
.Where(x => sq.SurveyQuestionID == x.SurveyQuestionID)
.DefaultIfEmpty()
select new
{sq.SurveyQuestionText
,sac.SurveyScaleText}).ToList();
Stuck trying to put the two pieces together.
The query would return All questions and if the user has answered the question it would be null for UserAnswered.
Any ideas? TIA!
If you need more info... This will create the class:
public class SurveyUserAnswer
{
[Key, Required]
public int SurveyUserAnswerID { get; set; }
[Required]
public int SurveyScaleID { get; set; }
[Required]
public int SurveyQuestionID { get; set; }
[Required]
public int UserID { get; set; }
public DateTime DateCreated { get; set; }
public virtual SurveyScale SurveyScale { get; set; }
public virtual SurveyQuestion SurveyQuestion { get; set; }
public virtual User User { get; set; }
public SurveyUserAnswer()
{
DateCreated = DateTime.UtcNow;
}
}
public class SurveyQuestion
{
[Key, Required]
public int SurveyQuestionID { get; set; }
[Required]
public int SurveyQuestionCounter { get; set; }
[Required, MaxLength(500)]
public string SurveyQuestionText { get; set; }
public DateTime DateCreated { get; set; }
public virtual ICollection<SurveyUserAnswer> SurveyUserAnswer { get; set; }
public SurveyQuestion()
{
DateCreated = DateTime.UtcNow;
}
}
public class SurveyScale
{
[Key, Required]
public int SurveyScaleID { get; set; }
[Required]
public int SurveyScaleValue { get; set; }
[Required, MaxLength(8)]
public string SurveyScaleText { get; set; }
public DateTime DateCreated { get; set; }
public virtual ICollection<SurveyUserAnswer> SurveyUserAnswer { get; set; }
public SurveyScale()
{
DateCreated = DateTime.UtcNow;
}
}
This will create the dummy data content: var SurveyScales = new List<SurveyScale>
{
new SurveyScale {SurveyScaleValue = 0, SurveyScaleText = "Never"},
new SurveyScale {SurveyScaleValue = 1, SurveyScaleText = "Seldom"},
new SurveyScale {SurveyScaleValue = 2, SurveyScaleText = "Sometime"},
new SurveyScale {SurveyScaleValue = 3, SurveyScaleText = "Always"}
};
SurveyScales.ForEach(r => db.SurveyScales.Add(r));
db.SaveChanges();
var SurveyQuestions = new List<SurveyQuestion>
{
new SurveyQuestion {SurveyQuestionCounter = 1, SurveyQuestionText = "Question A"},
new SurveyQuestion {SurveyQuestionCounter = 2, SurveyQuestionText = "Question B"},
new SurveyQuestion {SurveyQuestionCounter = 3, SurveyQuestionText = "Question C"},
new SurveyQuestion {SurveyQuestionCounter = 4, SurveyQuestionText = "Question D"}
};
SurveyQuestions.ForEach(r => db.SurveyQuestions.Add(r));
db.SaveChanges();
var SurveyUserAnswers = new List<SurveyUserAnswer>
{
new SurveyUserAnswer {SurveyScaleID = 1, SurveyQuestionID = 1, UserID = 1},
new SurveyUserAnswer {SurveyScaleID = 2, SurveyQuestionID = 2, UserID = 1},
new SurveyUserAnswer {SurveyScaleID = 3, SurveyQuestionID = 3, UserID = 1},
new SurveyUserAnswer {SurveyScaleID = 4, SurveyQuestionID = 4, UserID = 1},
new SurveyUserAnswer {SurveyScaleID = 1, SurveyQuestionID = 1, UserID = 2},
new SurveyUserAnswer {SurveyScaleID = 2, SurveyQuestionID = 2, UserID = 2}
};
SurveyUserAnswers.ForEach(r => db.SurveyUserAnswers.Add(r));
db.SaveChanges();
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
took your solution and extended it:
var UserAnswered = (from ua in db.SurveyUserAnswers
join s in db.SurveyScales on ua.SurveyScaleID equals s.SurveyScaleID
where ua.UserID == 2
select new
{
ua.SurveyQuestionID,
s.SurveyScaleText,
s.SurveyScaleValue
});
var query = (from sq in db.SurveyQuestions
join sac in UserAnswered
on sq.SurveyQuestionID equals sac.SurveyQuestionID into leftJoin
from result in leftJoin.DefaultIfEmpty()
where sq.SurveyQuestionCounter <= 10
select new
{
sq.SurveyQuestionText,
SurveyScaleText = result.SurveyScaleText != null ? result.SurveyScaleText : null,
SurveyScaleValue = result.SurveyScaleValue != null ? result.SurveyScaleValue : (int?)null
});
ASKER
Thanks for your help!!
ASKER