Asp.net core how to loading related data in viewmodel Entity Framework
asp.net-core c# entity-framework entity-framework-core
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.
Accepted Answer
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();
Popular Answer
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)
Related Questions
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow