load related enties without using include in Entity Framework Core

Question

load related enties without using include in Entity Framework Core

entity-framework-core linq

Question

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 .

Data Model

public class QuestionDataModel : BasicDataModel
{
    public QuestionDataModel()
    {            
        QuestionOptions = new HashSet<QuestionOptionDataModel>();

    }

    public Guid Id { get; set; }

    public virtual ICollection<QuestionOptionDataModel> QuestionOptions { get; set; }

}

LINQ Query

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();

Toxic

View more on Stack Overflow

Harald Coppoolse · Accepted Answer · 6/26/2018 12:02:53 PM

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()
    });

load related enties without using include in Entity Framework Core

Question

Data Model

LINQ Query

Fastest Entity Framework Extensions

Accepted Answer

Performance

Expression Evaluator

Related