How to add a where clause to a field of a class with a one-many relationship with other class?

Question

Goal: Get all panels whose due date is <= today's date.

Entity/db diagram: enter image description here Controller:

    public ActionResult Index(PanelViewModel panelViewModel) 
    {
        panelViewModel.Panels = _panelRepository.GetPanels()
                                  .Where(p => p.PanelApplicationForms.Count > 0
                                          && p.IsPublish
                                          && p.PanelApplicationForms != null)
                                  .OrderBy(t => t.Title);

        return View(panelViewModel);
    }

Eranga · Accepted Answer · 2012-12-19T04:50:26.417

0

Try

public ActionResult Index(PanelViewModel panelViewModel) 
{
    var proj  = _panelRepository.GetPanels()
                              .Where(p => p.PanelApplicationForms.Count > 0
                                      && p.PanelApplicationForms
                                         .Any(f => f.PanelApplicationFormVersions
                                           .Any(v => v.DueDate <= today))
                                      && p.IsPublish
                                      && p.PanelApplicationForms != null)
                              .OrderBy(t => t.Title)
                              .Select(p => new { 
                                          Panel = p, 
                                          PanelApplicationForms = p.PanelApplicationForms
                                           .Where(f => f.PanelApplicationFormVersions
                                           .Any(v => v.DueDate <= today))
                                          })
                              .ToList();

    panelViewModel.Panels = proj.Select(p => p.Panel);

    return View(panelViewModel);
}

edited Dec 19 '12 at 04:50

answered Dec 18 '12 at 04:27

Eranga

32,181
5
97
96

I know it is from the OP's code, but yeah... `p.PanelApplicationForms.Count > 0` throws an exception in EF, it should be Any(). Likewise `p.PanelApplicationForms != null` throws an exception in EF (and is redundant anyway). Apart from that, this does not exclude Panels that _also_ have dates > today. But the OP is not specific whether this is a problem. Inituitively I would say that the condition should be `All(f => f.PanelApplicationFormVersions(All(v => v.DueDate <= today))`. – Gert Arnold Dec 18 '12 at 13:07
Difference between Any() and All() http://stackoverflow.com/questions/9027530/linq-not-any-vs-all-dont – Diganta Kumar Dec 19 '12 at 01:46
The Eranga code worked for me, but there is one problem. It is showing the all the 'PanelApplicationForms' even if only one record of the 'PanelApplicationFormVersion' satisfy the DueDate condition. How to not show the 'PanelApplicationForms' if the condition of DueDate is not satisfy? – Diganta Kumar Dec 19 '12 at 01:50

score 0 · Answer 2 · answered Jan 09 '13 at 00:24

@Eranga thank you for the answer and comment above. I got it working doing the following,

Controller:

panelViewModel.Panels = _panelRepository.GetPanels()
                                 .Where(p => p.PanelApplicationForms.Count > 0
                                                    && p.PanelApplicationForms
                                                           .Any(f => f.IsPublish
                                                                     &&
                                                                     f.PanelApplicationFormVersions.OrderByDescending(v => v.VersionNumber).
                                                                         First().DueDate >= DateTime.Today
                                                                     && f.PanelApplicationFormVersions.Count > 0
                                                           )
                                                    && p.PanelApplicationForms != null)
                                          .OrderBy(t => t.Title);

View:

                       @foreach (var panelApplicationFormItem in panelItem.PanelApplicationForms.Where(panelApplicationFormItem => panelApplicationFormItem.IsPublish))
                        {
                            var panelApplicationFormVersion = panelApplicationFormItem.PanelApplicationFormVersions.OrderByDescending(v => v.VersionNumber).First();
                            if (panelApplicationFormVersion.DueDate >= DateTime.Today)
                            {
                                <tr>
                                    <td>
                                        @Html.DisplayFor(modelItem => panelApplicationFormItem.Title)
                                    </td>
                                    <td class="span2">
                                        @if (panelApplicationFormItem.PanelApplicationFormVersions.Count > 0)
                                        {

                                            @Html.DisplayFor(modelItem => panelApplicationFormVersion.DueDate)
                                        }
                                    </td>
                                    <td class="span2">
                                        @{
                                            //var isApplicationAlreadyExist = panelApplicationFormVersion.Id.IsApplicationAlreadyExist()
                                        }
                                        @if (panelApplicationFormVersion != null)
                                        {
                                            <a href="~/Application/Apply/@panelApplicationFormVersion.Id?versionNumber=@panelApplicationFormVersion.VersionNumber" class="btn">Apply</a>
                                        }
                                        else
                                        {
                                            <a href="#" class="btn">Sum</a>
                                        @*<p>No versions available to apply</p>*@
                                        }
                                    </td> 
                                </tr>
                            }
                        }

How to add a where clause to a field of a class with a one-many relationship with other class?

2 Answers2