saif saif - 21 days ago 6
SQL Question

get list of decision for a specific meetingtitle using linq asp.net

I have a database table. What I want is to get data using

group by
clause as I have used in below code.
Note that Decision is another table. now I want that all the decisions related to a specific Meeting Title should be shown in list.like
meetingtitle1=decision1,decison2,decison3
meetingtitle2=decision1,decison2
but below code returns only one decisiontitle.

public List<NewMeetings> GetAllMeetings()
{

var xyz = (from m in DB.MeetingAgenda
//join mp in Meeting on m.MeetingId equals mp.MeetingId
//where m.MeetingId == 2
group m by new { m.Meeting.MeetingTitle } into grp
select new NewMeetings
{
// meetingid = grp.Key.MeetingId,
meetingtitle = grp.Key.MeetingTitle,
decision = grp.Select(x => x.Decision.DecisionTitle).FirstOrDefault(),

total = grp.Count()
}).ToList();

List<NewMeetings> list = xyz.ToList();
return list;



}

public class NewMeetings
{
public int meetingid;
public string meetingtitle;
public string decision;
public int total;
}


Can somebody please tell me how to return a list of decisions to a specific Meetingtitle?

Answer

You are doing a FirstOrDefault on the list of decisions which obviously means you are only getting a single value. Instead you can join them all together into one longer string (separated by commas as you indicated in the question) by changing this line:

decision = grp.Select(x => x.Decision.DecisionTitle).FirstOrDefault(),

To this:

decision = string.Join(",", grp.Select(x => x.Decision.DecisionTitle)),

However, as the string.Join is not recognised by Linq to Entities, you need to do the string.Join after the data has been retrieved (i.e. after the ToList):

   var xyz = (from m in DB.MeetingAgenda
              group m by new { m.Meeting.MeetingTitle } into grp
              select new
              {
                  meetingtitle = grp.Key.MeetingTitle,
                  decisions = grp.Select(x => x.Decision.DecisionTitle),
                  total = grp.Count()
              })
              .ToList()
              .Select(m => new NewMeetings
              {
                  meetingtitle = m.meetingtitle,
                  decision = string.Join(",", m.decisions),
                  total = m.total
              });