I am intended to load related entities without using include in C#/ Entity Framework. In my example I have using left join to load all the questions options which only show if I explicitly say in select statement in LINQ query. My question how can I ensure it load related entities without defining related entity in select inside LINQ query .
public class QuestionDataModel : BasicDataModel
{
public QuestionDataModel()
{
QuestionOptions = new HashSet<QuestionOptionDataModel>();
}
public Guid Id { get; set; }
public virtual ICollection<QuestionOptionDataModel> QuestionOptions { get; set; }
}
var q1 = (
from question in Context.Questions
join options in Context.QuestionOptions on question.Id equals options.QuestionId into qo
where question.ConsultationId == Guid.Parse("10324003-0012-4D99-95D8-7E7189CA3888")
select new
{
question
//,qo // it only loads questionOption if qo is here, I need to do without that, since it is collection property in QuestionDataModel class
}
).ToList();
One of the slower elements of a database conversation is the transfer of the selected data from the database management system to your process. It is wise not to send any more data to your process than you actually plan to use.
Apparently you have a sequence of Questions
where every Question
has zero or more QuestionOptions
, and every QuestionOption
belongs to exactly one Question
, namely the Question
with the Id
equal to the QuestionOption.QuestionId
. A simple one-to-many with a foreign key in QuestionId
.
If you would fetch the Question
with Id == 4 with its ten thousand QuestionOptions
, you know that every QuestionOption
will have a QuestionId
that has a value of 4. You will be transferring the value 4 a 10 thousand times, while you'll probably wouldn't even use it, as you already know it equals the Question.Id
.
The solutions is: only use Include if you plan to update a database item. In all other cases use Select. Select only the properties you actually plan to use.
In Method syntax (or use a similar query syntax if you like)
var result = context.Questions.Join(context.QuestionOptions, // join Questions and QuestionOptions
question => question.Id, // from every Question take the Id
questionOption => questionOption.QuestionId, // from every Option take the QuestionId
(question, questionOption) => new // when they match make a new object
{ // Select only the properties you plan to use:
Question = new
{
Id = question.Id,
... other question properties
},
Option = new
{
Id = questionOption.Id,
// not needed: questionOption.QuestionId, it equals Question.Id
... other properties you plan to use
}
});
Only do ToList
if you really plan to use all fetched elements. Keep your result an IQueryable as long as possible.
If you rather have each Question with its QuestionOptions
, consider using Queryable.GroupBy
var questionsWithTheirOptions = context.Questions
.GroupJoin(context.QuestionOptions, // GroupJoin Questions and QuestionOptions
question => question.Id, // from every Question take the Id
questionOption => questionOption.QuestionId, // from every Option take the QuestionId
(question, optionsOfQuestion) => new // when they match make a new object
{ // desired Question Properties
Id = question.Id,
...
// The options of this question:
Options = optionsOfQuestion.Select(option => new
{
Id = questionOption.Id,
// not needed: questionOption.QuestionId, it equals Question.Id
...
})
.ToList()
});