2

I have three models (Applicant, Meeting, City) and I joined three of them. I want to get Count by grouping MeetingId in Applicant model. Here are my models and method I use for populating Dropdownlist in Controller. So, like the "Expired" property in the Controller, how can I obtain the count for the MeetingId by grouping on "TotalMeetingById" property?


Applicant Model:

public class Applicant
{
    public int ApplicantID { get; set; }
    public DateTime? SubmitDate { get; set; }
    public string Name { get; set; }
    public string Surname { get; set; }   
    public int MeetingId { get; set; }
}


Meeting Model:

public class Meeting
{
    public int MeetingID { get; set; }
    public string MeetingName { get; set; }
    public DateTime MeetingStartDate { get; set; }       
    public DateTime? MeetingEndDate { get; set; }
    public int? TotalParticipant { get; set; }
    public int? MeetingCityId { get; set; } 

    public int? ParticipantCityAId { get; set; }
    public int? ParticipantCityBId { get; set; }
}


City Model:

public class City
{
    public int CityID { get; set; }
    public string CityName { get; set; }
}    


Controller:

private void PopulateDropDownList()
{
    var meetingsQuery = repository.Meetings
        .GroupJoin(repository.Cities, m => m.MeetingCityId, c => c.CityID, (m, c) => new { m, cA = c.DefaultIfEmpty() })
        .SelectMany(z => z.cA.Select(cA => new { m = z.m, cA }))
        .GroupJoin(repository.Cities, m => m.m.ParticipantCityAId, c => c.CityID, (m, c) => new { m.m, m.cA, cB = c.DefaultIfEmpty() })
        .SelectMany(w => w.cB.Select(cB => new { m = w.m, cA = w.cA, cB }))
        .GroupJoin(repository.Cities, m => m.m.ParticipantCityBId, c => c.CityID, (m, c) => new { m.m, m.cA, m.cB, cC = c.DefaultIfEmpty() })
        .SelectMany(t => t.cC.Select(cC => new { m = t.m, cA = t.cA, cB = t.cB, cC }))
        .Select(
            m =>
                new
                {
                    CityID = m.cA.CityID,
                    CityName = m.cA.CityName,
                    MeetingDate = m.m.MeetingStartDate,
                    MeetingName = m.m.MeetingName,
                    NameofMeetingCityIdA = m.cA != null ? m.cA.CityName : null,
                    NameofMeetingCityIdB = m.cB != null ? m.cB.CityName : null,
                    NameofMeetingCityIdC = m.cC != null ? m.cC.CityName : null
                })
        .OrderBy(x => x.CityID)
        .AsEnumerable()
        .Select(
            i => new
            {
                Value = i.CityID.ToString(),
                DisplayValue = string.Format("{0} ({1:dd MMMM yyyy})", i.NameofMeetingCityIdA, i.MeetingDate),
                Expired = i.MeetingDate < DateTime.UtcNow,
                TotalMeetingById= ??? >>> I cannot get the total count for the related MeetingId at here
            }
        ).ToList();

    var selectItems = new List<MyHelpers.MySelectItem>(meetingsQuery.Count);
    foreach (var record in meetingsQuery)
    {
        var item = new MyHelpers.MySelectItem
        {
            Text = record.DisplayValue,
            Value = record.Value
        };
        if (record.Expired)
        {
            item.Class = "disabled";
            item.Disabled = "disabled";
        }
        selectItems.Add(item);
    }
    ViewBag.MeetingData = selectItems;
} 



Here are sample data for models:


Applicant:

 ApplicantID : 100
 SubmitDate : 01/11/2013
 Name : Christof
 Surname : Jahnsen 
 MeetingId : 1


Meeting:

 MeetingID : 1
 MeetingName : City Information Days
 MeetingStartDate : 01/01/2014       
 MeetingEndDate : 02/01/2014
 TotalParticipant : 2 (for example) 
 MeetingCityId : 500
 ParticipantCityAId : 501
 ParticipantCityBId : 502


City:

CityID : 500 / 501 / 502
CityName : London / Paris / NY


Update -----------------------------------------------------------------------

Razor:

@Html.LabelFor(m => m.Applicant.MeetingId)
@Html.MyDropdownListFor(m => m.Applicant.MeetingId, ViewBag.MeetingData as List<MyHelpers.MySelectItem>, "---- Select ----",
        new { name = "meetingId", id = "meetingId" })
@Html.ValidationMessageFor(m => m.Applicant.MeetingId, null, new { @class = "ValidationErrors" })


Controller:

public ViewResult Add()
{
    PopulateDropDownList();

    ApplicantViewModel model = new ApplicantViewModel
    {
        Applicant = new Applicant(),
        Applicants = repository.Applicants,
        Lookups = repository.Lookups,
        Cities = repository.Cities
        .ToList()
        };
        return View(model);
    }

[HttpPost]
public ActionResult Add(ApplicantViewModel model)
{
    ApplicantViewModel viewModel;

    if (ModelState.IsValid)
    {
        model.Applicant.SubmitDate = DateTime.Now;
        repository.SaveApplicant(model.Applicant);
        PopulateDropDownList(model.Applicant);
        return View("Completed", ViewBag.ApplicantId = model.Applicant.ApplicantID);
    }
    else
    {
        // there is something wrong with the data values
        PopulateDropDownList();
        TempData["message"] = "Please try again.";
        viewModel = new ApplicantViewModel
        {
            Applicant = new Applicant(),
            Applicants = repository.Applicants,
            Lookups = repository.Lookups,
            Cities = repository.Cities
            .ToList()
        };
        return View(viewModel);
    }
}
16
  • 3
    It would be a lot easier to follow your code if you used more meaningful names than "m", "cC", "cA" etc. Also, it would probably be simpler as a query expression. Commented Nov 30, 2013 at 9:03
  • Thanks for reply. But using a name for letter maybe makes it much more difficult to read. If it is possible, could you please change them as you want on VS and help? Thanks in advance. Commented Nov 30, 2013 at 9:27
  • 4
    You think giving meaningful names to variables makes code more difficult to read? I don't have time to go through and try to work out what your code is meant to do - but you should do that yourself. And with a query expression, you'll find you have fewer names to work out in the first place. Commented Nov 30, 2013 at 9:31
  • 1
    so use groupby extension Queryable.GroupBy or Enumerable.GroupBy Commented Nov 30, 2013 at 11:08
  • 1
    @H.Johnson can you provide how you bind this list in html, and action on postback? Commented Dec 2, 2013 at 16:50

2 Answers 2

1

You need grouping, so use something like this

var meetingsQuery = from meeting in repository.Meetings
                    join cityA in repository.Cities on meeting.MeetingCityId equals cityA.CityID into CitiesA
                    join cityB in repository.Cities on meeting.ParticipantCityAId equals cityB.CityID into CitiesB
                    join cityC in repository.Cities on meeting.ParticipantCityBId equals cityC.CityID into citiesC
                    from cityA in citiesA.DefaultIfEmpty()
                    from cityB in citiesB.DefaultIfEmpty()
                    from cityC in citiesC.DefaultIfEmpty()
                    orderby cityA.CityID
                    select new
                        {
                            CityID = cityA.CityID,
                            CityName = cityA.CityName,
                            MeetingDate = meeting.MeetingStartDate,
                            MeetingName = meeting.MeetingName,
                            NameofMeetingCityIdA = cityA != null ? cityA.CityName : null,
                            NameofMeetingCityIdB = cityB != null ? cityB.CityName : null,
                            NameofMeetingCityIdC = cityC != null ? cityC.CityName : null
                        }

var meetings = from meeting in meetingsQuery.AsEnumerable()
               group meeting by new {
                                     meeting.CityID, 
                                     meeting.MeetingDate,
                                     meeting.NameofMeetingCityIdA
                                    } into grouppedMeeting
               select new {
                   Value = grouppedMeeting.Key.CityID.ToString(),
                   DisplayValue = string.Format("{0} ({1:dd MMMM yyyy})", grouppedMeeting.Key.NameofMeetingCityIdA, grouppedMeeting.Key.MeetingDate),
                   Expired = grouppedMeeting.Key.MeetingDate < DateTime.UtcNow,
                   TotalMeetingById= grouppedMeeting.Count()
               }

UPDATE

in this code

    // there is something wrong with the data values
    PopulateDropDownList();
    TempData["message"] = "Please try again.";
    viewModel = new ApplicantViewModel
    {
        Applicant = new Applicant(),
        Applicants = repository.Applicants,
        Lookups = repository.Lookups,
        Cities = repository.Cities
        .ToList()
    };
    return View(viewModel);

you don't mark any item as selected, you only add items to viewbag

Sign up to request clarification or add additional context in comments.

7 Comments

Thanks you so much for your help, it was useful (I marked as useful) although using linq instead of lambda. I tested but as there is no where condition on TotalMeetingById= grouppedMeeting.Count() line, I cannot distinguish counts for per city. I tried to use TotalMeetingById = grouppedMeeting.Where(p => p.CityID==6).Count() but unfortunately it did not worked (TotalMeetingById value is 1 although there are 2 records for the related Meeting its CityID=6). Any idea?
can you provide sample input data, so I could check?
I added to the last part of the question. Thanks again.
what output expected for this input?
I retrieve Dropdownlist values with City and Meeting date with format. In addition to this I can determine Expired parameter according to the Meeting date. I just want to check if the total count for the current meeting (like retrieved for Expired parameter) is equal to a number like 2 in this example.
|
0

Finally I solved the problem with the help of using an external method out of Lambda expression.

private void PopulateMeetingsDropDownList(object selectedMeetings = null)
    {
        var meetingsQuery = repository.Meetings
            .GroupJoin(repository.Cities, m => m.MeetingCityId, c => c.CityID, (m, c) => new { m, cA = c.DefaultIfEmpty() })
            .SelectMany(z => z.cA.Select(cA => new { m = z.m, cA }))
            .GroupJoin(repository.Cities, m => m.m.ParticipantCityAId, c => c.CityID, (m, c) => new { m.m, m.cA, cB = c.DefaultIfEmpty() })
            .SelectMany(w => w.cB.Select(cB => new { m = w.m, cA = w.cA, cB }))
            .GroupJoin(repository.Cities, m => m.m.ParticipantCityBId, c => c.CityID, (m, c) => new { m.m, m.cA, m.cB, cC = c.DefaultIfEmpty() })
            .SelectMany(t => t.cC.Select(cC => new { m = t.m, cA = t.cA, cB = t.cB, cC }))
            .Select(
                m =>
                    new
                    {
                        TotalParticipant = m.m.TotalParticipant,
                        MeetingID = m.m.MeetingID,
                        CityID = m.cA.CityID,
                        CityName = m.cA.CityName,
                        MeetingDate = m.m.MeetingStartDate,
                        MeetingName = m.m.MeetingName,
                        NameofMeetingCityIdA = m.cA != null ? m.cA.CityName : null,
                        NameofMeetingCityIdB = m.cB != null ? m.cB.CityName : null,
                        NameofMeetingCityIdC = m.cC != null ? m.cC.CityName : null
                    })
            .OrderBy(x => x.CityName)
            .AsEnumerable()
            .Select(
                i => new
                {
                    Value = i.MeetingID.ToString(),
                    DisplayValue = string.Format("{0} ({1:dd MMMM yyyy})", i.NameofMeetingCityIdA, i.MeetingDate)),

                    Expired = i.MeetingDate < DateTime.UtcNow,
                    MaksimumCount = i.TotalParticipant,
                    CurrentCount = GetMeetingCount(i.MeetingID)
                }
            ).ToList();

        var selectItems = new List<MyHelpers.MySelectItem>(meetingsQuery.Count);
        foreach (var record in meetingsQuery)
        {
            var item = new MyHelpers.MySelectItem
            {
                Text = record.DisplayValue,
                Value = record.Value
            };
            //If Meeting Date is expired or Count for the current record >= Total Participant
            if (record.Expired || record.CurrentCount >= record.MaksimumCount)
            {
                item.Class = "disabled";
                item.Disabled = "disabled";
            }
            selectItems.Add(item);
        }
        ViewBag.MeetingData = selectItems;
    }


  public int GetMeetingCount(int meetingId)
  {
      return repository.Applicants.Count(x => x.MeetingId == meetingId);
  }


The first running of the code I encountered "There is already an open DataReader associated with this Command which must be closed first." error. But adding "MultipleActiveResultSets=True;" parameter to teh ConnectionString solved this problem and now it works properly.

1 Comment

@Grundy: I think this is the only way to solve the problem and it is impossible to provide this by using lambda only. Is that true?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.