Lamba experssion with Groupby Related Data Problems

I have (2) table class and (2) ViewModel

   public class MPInformation
    {
        public Guid ID { get; set; }
        public string Name {get;set;}
        public virtual ICollection QuestionAnswer { get; set; }
    }

    public class QuestionAnswer
    {
        public Guid ID { get; set; }
        public Guid? PersonalID { get; set; }
        public virtual MPInformation MPInformations { get; set; }

public string MPID {get;set;} // equal PersonalID this properties is add GroupBy not null state.

public string Answers {get;set;}
}

    public class MPViewModel
    {
        public Guid? ID { get; set; }
        public string Name { get; set; }

public CounterViewModel Counter {get;set;}

}

        public class CounterViewModel
   {
        public string CounterID { get; set; }
        public int Counter { get; set; }

}

IQueryable info = _context.MPInformationDB

.Select(s => new MPViewModel

{
ID = s.ID,
Name = s.Name,

Counter = _context.QuestionAnswerDB.GroupBy(g => g.MPID)

.Select(s => new Counter()
{
CounterID = s.Key,

Counter = s.Where(s => s.QuestionAnswerProposeID == 2).Count(),

}).FirstOrDefault()

});

The index.chtml file in

@foreach (var item in Model.MPInformation)
{

@item.Name

@item.Counter.Counter

}

Please Help me GroupBy Problems .

Hi Hemant Jindal

First thank you.Your Shared https://github.com/aspnet/EntityFrameworkCore/issues/14148

this link issue is equal my problems.

My entire data structure model is Looking and Checking

(1) MPInformation.cs (Model)

using Microsoft.AspNetCore.Mvc.ModelBinding;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Threading.Tasks;
namespace AyeyarHluttawProject.Models
{
public class MPInformation
{
public Guid ID { get; set; }
public string ProfilePhoto { get; set; }
public string Name { get; set; }
public string NationalAndReligious { get; set; }
public string NICCard { get; set; }
public string Birthday { get; set; }
public string EducationState { get; set; }
public string Address { get; set; }
public string FatherName { get; set; }
public string MotherName { get; set; }
public string Occupation { get; set; }
public string StartDate { get; set; }
public bool MarriedStatus { get; set; }
public bool Active { get; set; }
public string General { get; set; }
/*--------------------------------------------------------------*/
[BindRequired]
public int HluttawID { get; set; }
[ForeignKey("HluttawID")]
public virtual Hluttaw Hluttaws { get; set; }
[BindRequired]
public int PartyID { get; set; }
[ForeignKey("PartyID")]
public virtual PoliticalParty Partys { get; set; }
[BindRequired]
public int ConstituencyID { get; set; }
[ForeignKey("ConstituencyID")]
public virtual Constituency Constituency { get; set; }
[BindRequired]
public int TownshipID { get; set; }
[ForeignKey("TownshipID")]
public virtual Township Townships { get; set; }
[BindRequired]
public int DistrictID { get; set; }
[ForeignKey("DistrictID")]
public virtual District Districts { get; set; }
public virtual ICollection MPTags { get; set; }
public virtual ICollection QuestionAnswers { get; set; }
}
}

(2) QuestionAnswer.cs (Model)

using Microsoft.AspNetCore.Mvc.ModelBinding;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Threading.Tasks;
namespace AyeyarHluttawProject.Models
{
public class QuestionAnswer
{
public Guid ID { get; set; }
public DateTime Date { get; set; }
public string QuestionDate { get; set; }
public string Question { get; set; }
public string AnswerName { get; set; }
public string Answer { get; set; }
public string WorkDone { get; set; }
public string FileName { get; set; }
[BindRequired]
public int HluttawID { get; set; }
[ForeignKey("HluttawID")]
public virtual Hluttaw Hluttaw { get; set; }
[BindRequired]
public int MeetingID { get; set; }
[ForeignKey("MeetingID")]
public virtual HluttawMeetings HluttawMeetings { get; set; }
public Guid? MPID { get; set; }
[ForeignKey("MPID")]
public virtual MPInformation MPInformation { get; set; }
public Guid? MilitaryID { get; set; }
[ForeignKey("MilitaryID")]
public virtual MilitaryInformation MilitaryInformation { get; set; }
[BindRequired]
public int QuestionAnswerProposeID { get; set; }
[ForeignKey("QuestionAnswerProposeID")]
public virtual QuestionAnswerPropose QuestionAnswerPropose { get; set; }
[BindRequired]
public int DepartmentID { get; set; }
[ForeignKey("DepartmentID")]
public virtual GovernmentDepartments GovernmentDepartments { get; set; }
[BindRequired]
public int QuestionStateID { get; set; }
[ForeignKey("QuestionStateID")]
public virtual QuestionState QuestionState { get; set; }
[BindRequired]
public int WorkStatusID { get; set; }
[ForeignKey("WorkStatusID")]
public virtual WorkStatus WorkStatus { get; set; }
}
}

(3) MPViewModel.cs (View Model)

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Threading.Tasks;
namespace AyeyarHluttawProject.ViewModel
{
public class MPViewModel
{
public Guid? ID { get; set; }
public string ProfilePhoto { get; set; }
public string Name { get; set; }
public string NationalAndReligious { get; set; }
public string NICCard { get; set; }
public string Birthday { get; set; }
public string EducationState { get; set; }
public string Address { get; set; }
public string FatherName { get; set; }
public string MotherName { get; set; }
public string Occupation { get; set; }
public string StartDate { get; set; }
public bool MarriedStatus { get; set; }
public bool Active { get; set; }
public string General { get; set; }
/*--------------------------------------------------------------*/
public string HluttawName { get; set; }
public string PartyName { get; set; }
public string ConstituencyNo { get; set; }
public string TownshipName { get; set; }
public string DistrictName { get; set; }
public Counter Counter { get; set; }
}
}

(4) Counter.cs (ViewModel)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
namespace AyeyarHluttawProject.ViewModel
{
public class Counter
{
public Guid? CounterID { get; set; }
public int StarCounter { get; set; }
public int UnStarCounter { get; set; }
public int AsoCounter { get; set; }
public int PyeeStatus { get; set; }
public int SellStatus { get; set; }
public int KyanStatus { get; set; }
public int KateWinStatus { get; set; }
public int KateMaWinStatus { get; set; }
public int Total { get; set; }
}
}
Profile Page of Index.chtml.cs
IQueryable info = _context.MPInformationDB
.Include(i => i.Hluttaws)
.Include(i => i.Districts)
.Include(i => i.Townships)
.Include(i => i.Constituency)
.Include(i => i.Partys)
.Include(i => i.QuestionAnswers)
.Include(i => i.MPTags)
.ThenInclude(c => c.Committees)
.Include(i => i.MPTags)
.OrderByDescending(i => i.HluttawID)
.ThenBy(i => i.DistrictID)
.ThenBy(i => i.TownshipID)
.ThenBy(i => i.ConstituencyID)
.ThenBy(i => i.Active == true)
.Select(s => new MPViewModel
{
ID = s.ID,
ProfilePhoto = s.ProfilePhoto,
Name = s.Name,
NationalAndReligious = s.NationalAndReligious,
NICCard = s.NICCard,
Birthday = s.Birthday,
EducationState = s.EducationState,
Address = s.Address,
FatherName = s.FatherName,
MotherName = s.MotherName,
Occupation = s.Occupation,
StartDate = s.StartDate,
MarriedStatus = s.MarriedStatus,
Active = s.Active,
General = s.General,
HluttawID = s.Hluttaws.ShortNote,
PartyID = s.Partys.PartyName,
ConstituencyID = s.Constituency.ConstituencyNo,
TownshipID = s.Townships.TownshipName,
DistrictID = s.Districts.TownshipName,
Counter = _context.QuestionAnswerDB. GroupBy(g => g.MPID)
.Select(s => new Counter( )
{
CounterID = s.Key,
AsoCounter = s.Where(s => s.QuestionAnswerProposeID == 1).Count(),
StarCounter = s.Where(s => s.QuestionAnswerProposeID == 2).Count(), UnStarCounter = s.Where(s => s.QuestionAnswerProposeID == 3).Count(),
PyeeStatus = s.Where(s => s.WorkStatusID == 1).Count(),
SellStatus = s.Where(s => s.WorkStatusID == 2).Count(), KyanStatus = s.Where(s => s.WorkStatusID == 3).Count(),
KateWinStatus = s.Where(s => s.QuestionStateID == 1).Count(), KateMaWinStatus = s.Where(s => s.QuestionStateID == 2).Count(),
Total = s.Count(),
}).FirstOrDefault()
});

I think your understand of my model with data structure.Please Help me Teacher.

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

U Thu7y ago
Hi Hemant Jindal

First thank you.Your Shared https://github.com/aspnet/EntityFrameworkCore/issues/14148
this link issue is equal my problems.

My entire data structure model is Looking and Checking

(1) MPInformation.cs (Model)
using Microsoft.AspNetCore.Mvc.ModelBinding;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Threading.Tasks;

namespace AyeyarHluttawProject.Models
{
public class MPInformation
{
public Guid ID { get; set; }
public string ProfilePhoto { get; set; }
public string Name { get; set; }
public string NationalAndReligious { get; set; }
public string NICCard { get; set; }
public string Birthday { get; set; }
public string EducationState { get; set; }
public string Address { get; set; }
public string FatherName { get; set; }
public string MotherName { get; set; }
public string Occupation { get; set; }
public string StartDate { get; set; }
public bool MarriedStatus { get; set; }
public bool Active { get; set; }
public string General { get; set; }

/*--------------------------------------------------------------*/
[BindRequired]
public int HluttawID { get; set; }
[ForeignKey("HluttawID")]
public virtual Hluttaw Hluttaws { get; set; }

[BindRequired]
public int PartyID { get; set; }
[ForeignKey("PartyID")]
public virtual PoliticalParty Partys { get; set; }

[BindRequired]
public int ConstituencyID { get; set; }
[ForeignKey("ConstituencyID")]
public virtual Constituency Constituency { get; set; }

[BindRequired]
public int TownshipID { get; set; }
[ForeignKey("TownshipID")]
public virtual Township Townships { get; set; }

[BindRequired]
public int DistrictID { get; set; }
[ForeignKey("DistrictID")]
public virtual District Districts { get; set; }

public virtual ICollection MPTags { get; set; }
public virtual ICollection QuestionAnswers { get; set; }
}
}
(2) QuestionAnswer.cs (Model)
using Microsoft.AspNetCore.Mvc.ModelBinding;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Threading.Tasks;

namespace AyeyarHluttawProject.Models
{
public class QuestionAnswer
{
public Guid ID { get; set; }
public DateTime Date { get; set; }
public string QuestionDate { get; set; }
public string Question { get; set; }
public string AnswerName { get; set; }
public string Answer { get; set; }
public string WorkDone { get; set; }
public string FileName { get; set; }

[BindRequired]
public int HluttawID { get; set; }
[ForeignKey("HluttawID")]
public virtual Hluttaw Hluttaw { get; set; }

[BindRequired]
public int MeetingID { get; set; }
[ForeignKey("MeetingID")]
public virtual HluttawMeetings HluttawMeetings { get; set; }

public Guid? MPID { get; set; }
[ForeignKey("MPID")]
public virtual MPInformation MPInformation { get; set; }

public Guid? MilitaryID { get; set; }
[ForeignKey("MilitaryID")]
public virtual MilitaryInformation MilitaryInformation { get; set; }

[BindRequired]
public int QuestionAnswerProposeID { get; set; }
[ForeignKey("QuestionAnswerProposeID")]
public virtual QuestionAnswerPropose QuestionAnswerPropose { get; set; }

[BindRequired]
public int DepartmentID { get; set; }
[ForeignKey("DepartmentID")]
public virtual GovernmentDepartments GovernmentDepartments { get; set; }

[BindRequired]
public int QuestionStateID { get; set; }
[ForeignKey("QuestionStateID")]
public virtual QuestionState QuestionState { get; set; }

[BindRequired]
public int WorkStatusID { get; set; }
[ForeignKey("WorkStatusID")]
public virtual WorkStatus WorkStatus { get; set; }
}
}
(3) MPViewModel.cs (View Model)
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Threading.Tasks;

namespace AyeyarHluttawProject.ViewModel
{
public class MPViewModel
{
public Guid? ID { get; set; }
public string ProfilePhoto { get; set; }
public string Name { get; set; }
public string NationalAndReligious { get; set; }
public string NICCard { get; set; }
public string Birthday { get; set; }
public string EducationState { get; set; }
public string Address { get; set; }
public string FatherName { get; set; }
public string MotherName { get; set; }
public string Occupation { get; set; }
public string StartDate { get; set; }
public bool MarriedStatus { get; set; }
public bool Active { get; set; }
public string General { get; set; }

/*--------------------------------------------------------------*/
public string HluttawName { get; set; }
public string PartyName { get; set; }
public string ConstituencyNo { get; set; }
public string TownshipName { get; set; }
public string DistrictName { get; set; }

public Counter Counter { get; set; }
}
}
(4) Counter.cs (ViewModel)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace AyeyarHluttawProject.ViewModel
{
public class Counter
{
public Guid? CounterID { get; set; }
public int StarCounter { get; set; }
public int UnStarCounter { get; set; }
public int AsoCounter { get; set; }
public int PyeeStatus { get; set; }
public int SellStatus { get; set; }
public int KyanStatus { get; set; }
public int KateWinStatus { get; set; }
public int KateMaWinStatus { get; set; }
public int Total { get; set; }
}
}
Profile Page of Index.chtml.cs
IQueryable info = _context.MPInformationDB
.Include(i => i.Hluttaws)
.Include(i => i.Districts)
.Include(i => i.Townships)
.Include(i => i.Constituency)
.Include(i => i.Partys)
.Include(i => i.QuestionAnswers)
.Include(i => i.MPTags)
.ThenInclude(c => c.Committees)
.Include(i => i.MPTags)
.OrderByDescending(i => i.HluttawID)
.ThenBy(i => i.DistrictID)
.ThenBy(i => i.TownshipID)
.ThenBy(i => i.ConstituencyID)
.ThenBy(i => i.Active == true)
.Select(s => new MPViewModel
{
ID = s.ID,
ProfilePhoto = s.ProfilePhoto,
Name = s.Name,
NationalAndReligious = s.NationalAndReligious,
NICCard = s.NICCard,
Birthday = s.Birthday,
EducationState = s.EducationState,
Address = s.Address,
FatherName = s.FatherName,
MotherName = s.MotherName,
Occupation = s.Occupation,
StartDate = s.StartDate,
MarriedStatus = s.MarriedStatus,
Active = s.Active,
General = s.General,
HluttawID = s.Hluttaws.ShortNote,
PartyID = s.Partys.PartyName,
ConstituencyID = s.Constituency.ConstituencyNo,
TownshipID = s.Townships.TownshipName,
DistrictID = s.Districts.TownshipName,
Counter = _context.QuestionAnswerDB. GroupBy(g => g.MPID)
.Select(s => new Counter( )
{
CounterID = s.Key,
AsoCounter = s.Where(s => s.QuestionAnswerProposeID == 1).Count(),
StarCounter = s.Where(s => s.QuestionAnswerProposeID == 2).Count(), UnStarCounter = s.Where(s => s.QuestionAnswerProposeID == 3).Count(),
PyeeStatus = s.Where(s => s.WorkStatusID == 1).Count(),
SellStatus = s.Where(s => s.WorkStatusID == 2).Count(), KyanStatus = s.Where(s => s.WorkStatusID == 3).Count(),
KateWinStatus = s.Where(s => s.QuestionStateID == 1).Count(), KateMaWinStatus = s.Where(s => s.QuestionStateID == 2).Count(),
Total = s.Count(),
}).FirstOrDefault()
});
I think your understand of my model with data structure.Please Help me Teacher.
0
Hemant Jindal7y ago
Hi U Thu

can you please let me know the entire data structure model so that i can replicate at my end. meanwhile you can refer this link for better clarity

https://github.com/aspnet/EntityFrameworkCore/issues/14148
0
U Thu7y ago
Hi Hemat Jindal
Please explain for me example model with my problems. Is Not support in ASP.NET CORE 3.0 GroupBy could not be translated and will be evaluated locally?Thanks You.
0
Amit Kumar7y ago
Hi go through the below link it will sure help you. https://visualstudiomagazine.com/articles/2017/04/14/tip-group-linq-methods-visual-studio-dotnet.aspx?m=1 Or https://www.oreilly.com/library/view/c-60-cookbook/9781491921456/ch04.html
0
Hemant Jindal7y ago
Hi U Thu

i see your code might give you a run time error because in your class MPViewModel you have defined counter of integer type whereas in LINQ query you are trying to return and assign counter as an object.

for group linq queries you can refer microsoft documentation for better clarity

https://docs.microsoft.com/en-us/dotnet/csharp/linq/group-query-results
0