Asp.net core how to loading related data in viewmodel Entity Framework

Question

I am working on a Asp.net core 2.2 project and have a problem about loading related data in ViewModel.

First of all i have a table named QuestionTbl :

public class Question
{
    [Key]
    public int questionID { get; set; }
    public string questionTitle { get; set; }
    public string GroupID { get; set; }
}

As you see i have a string property called GroupID in Question table that shows groups of each question.

For Example

1 row in questionTbl
---------
questionID = 1
questionTitle = 'What is Your Name ?'
GroupID = '3,4,5'

In the example above question with ID = 1 is in 3 groups (3 and 4 and 5).

And GroupTbl :

public class Group
{
    [Key]
    public int GroupID { get; set; }
    public string GroupName { get; set; }
}

Now i want to show list of question with related groups.

I have a ViewModel like this :

public class GetQuestionViewModel
{
    public int questionID { get; set; }
    public string questionTitle { get; set; }
    public ICollection<Group> QuestionGroups { get; set; }
}

And my entity framework query :

var questionQuery = (from a in _db.QuestionTbl
                             select new GetQuestionViewModel()
                             {
                                 questionID = a.questionID,
                                 questionTitle = a.questionTitle
                             })
                             .Include(q => q.QuestionGroups)
                             .ToList();

I want to have a list contains questions and groups of each question. But QuestionGroups returns null in my query. I also read this link but it did not help me.

Your tables are not normalized, and thus your `Include` will not work in EF Core. I would highly recommend you change your models so that they are. — chakeda, Jun 19 '19 at 19:29
@chakeda Thank you. I think I should change the structure of the tables — topcool, Jun 19 '19 at 19:33
Possible duplicate of [Entity Framework Core 2.0.1 Eager Loading on all nested related entities](https://stackoverflow.com/questions/49593482/entity-framework-core-2-0-1-eager-loading-on-all-nested-related-entities) — Reyan Chougle, Jun 20 '19 at 05:49

score 0 · Answer 1 · answered Jun 19 '19 at 19:39

Normalize your tables, following the Microsoft Docs:

Question:

public class Question
{
    [Key]
    public int questionID { get; set; }
    public string questionTitle { get; set; }

    public int GroupID { get; set; }
    [ForeignKey("Group_ID")]
    public virtual Group Group { get; set; }
}

Group:

public class Group
{
    [Key]
    public int GroupID { get; set; }
    public string GroupName { get; set; }

    public virtual List<Question> questions { get; set; }
}

At this point you can query the records like so:

db.Question.Include(q => q.Group)

score 0 · Accepted Answer · answered Jun 20 '19 at 05:46

You could not use Include here for a viewModel directly.I advice that you could use many-to-many relationship for your Question and Group models.

Models:

public class Question
{
    [Key]
    public int questionID { get; set; }
    public string questionTitle { get; set; }

    public List<QuestionGroup> QuestionGroups { get; set; }
    //public List<Group> Groups { get; set; }
    //public string GroupID { get; set; }
}
public class Group
{
    [Key]
    public int GroupID { get; set; }
    public string GroupName { get; set; }

    public List<QuestionGroup> QuestionGroups { get; set; }
}

public class QuestionGroup
{
    public int QuestionId { get; set; }
    public Question Question { get; set; }

    public int GroupId { get; set; }
    public Group Group { get; set; }
}

public class GetQuestionViewModel
{
    public int questionID { get; set; }
    public string questionTitle { get; set; }
    public ICollection<Group> QuestionGroups { get; set; }
}

DbContext;

protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<QuestionGroup>()
       .HasKey(t => new { t.QuestionId, t.GroupId });

        modelBuilder.Entity<QuestionGroup>()
            .HasOne(qg => qg.Question)
            .WithMany(q => q.QuestionGroups)
            .HasForeignKey(qg => qg.QuestionId);

        modelBuilder.Entity<QuestionGroup>()
            .HasOne(qg=>qg.Group)
            .WithMany(g => g.QuestionGroups)
            .HasForeignKey(qg => qg.GroupId);
   }

EF Core query;

var questionQuery = (from a in _context.QuestionTbl.Include(q => q.QuestionGroups)
                             select new GetQuestionViewModel()
                             {
                                 questionID = a.questionID,
                                 questionTitle = a.questionTitle,
                                 QuestionGroups = a.QuestionGroups.Select(qg => qg.Group).ToList()

                             })
                         .ToList();

Asp.net core how to loading related data in viewmodel Entity Framework

2 Answers2