locked
Where clause yields no results RRS feed

  • Question

  • User-973886032 posted

    hi guys

    in my home controller, I am trying to filter out database products e.g. mugs or tshirts

    HOwever my code yields no results when I add the include and where clause, at runtime. It shows the results panel and all values exist, but nothing is displayed in the razor view.

    If however I remove the include and where clause, then I get all the results from the db. Please advise

    Ehi

      [Route("/{C}")]
            public async Task<IActionResult> Product(String C)
            {
    
                return View(await _context.Departments_SubCategory_Registration.Include(c => c.Departments_Category_Registration)
    
    
                    .Where(d => d.Departments_Category_Registration.Category_Name == C)
    
                      .Where(r => r.IsEnabled == true).Select(u => new Departments_SubCategory_Registration
    
    
    
                     {
    
                         CategoryID = u.CategoryID,
                         SubCategory_Name = u.SubCategory_Name
    
                     }).ToListAsync();



    //and my razor view
    
    
    @model IEnumerable<Root.Models.Department.Departments_SubCategory_Registration>
    
    @{
        ViewData["Title"] = "Index";
    }
    
    <h1>Index</h1>
    
    <p>
        <a asp-action="Create">Create New</a>
    </p>
    <table class="table">
        <thead>
            <tr>
                <th>
                    @Html.DisplayNameFor(model => model.CategoryID)
                </th>
                <th>
                    @Html.DisplayNameFor(model => model.SubCategory_Name)
                </th>
                <th>
                    @Html.DisplayNameFor(model => model.EntryDate)
                </th>
                <th>
                    @Html.DisplayNameFor(model => model.Description_Short)
                </th>
                <th>
                    @Html.DisplayNameFor(model => model.Description_Detailed)
                </th>
                <th>
                    @Html.DisplayNameFor(model => model.Notes)
                </th>
                <th>
                    @Html.DisplayNameFor(model => model.UniqueId)
                </th>
                <th>
                    @Html.DisplayNameFor(model => model.IsEnabled)
                </th>
                <th></th>
            </tr>
        </thead>
        <tbody>
            @foreach (var item in Model)
            {
                <tr>
                    <td>
                        @Html.DisplayFor(modelItem => item.CategoryID)
                    </td>
                    <td>
                        @Html.DisplayFor(modelItem => item.SubCategory_Name)
                    </td>
                    <td>
                        @Html.DisplayFor(modelItem => item.EntryDate)
                    </td>
                    <td>
                        @Html.DisplayFor(modelItem => item.Description_Short)
                    </td>
                    <td>
                        @Html.DisplayFor(modelItem => item.Description_Detailed)
                    </td>
                    <td>
                        @Html.DisplayFor(modelItem => item.Notes)
                    </td>
                    <td>
                        @Html.DisplayFor(modelItem => item.UniqueId)
                    </td>
                    <td>
                        @Html.DisplayFor(modelItem => item.IsEnabled)
                    </td>
                    <td>
                        <a asp-action="Edit" asp-route-id="@item.SubCategoryID">Edit</a> |
                        <a asp-action="Details" asp-route-id="@item.SubCategoryID">Details</a> |
                        <a asp-action="Delete" asp-route-id="@item.SubCategoryID">Delete</a>
                    </td>
                </tr>
            }
        </tbody>
    </table>
    

    my model

      public class Departments_SubCategory_Registration
        {
            [Key]
            public int SubCategoryID { get; set; }              // This is the PK
    
    
            public int CategoryID  { get; set; } // this is a FK
            [ForeignKey("SubCategoryID")]
            public Departments_Category_Registration Departments_Category_Registration { get; set; }
            public string SubCategory_Name { get; set; }
            public DateTime EntryDate { get; set; }
            public string Description_Short { get; set; }
            public string Description_Detailed { get; set; }
            public string Notes { get; set; }
    
            public Guid UniqueId { get; set; }
            public bool IsEnabled { get; set; }
            
    
        }

    Tuesday, July 9, 2019 10:07 PM

Answers

  • User711641945 posted

    Hi afrika,

    1.I use your code and it works well, could you share more code to reproduce your issue?

    2.As you annotated ,SubCategoryID is the PK and CategoryID is a FK, there is no need to define [ForeignKey("SubCategoryID")].

    3.There is a working demo as bellow:

    Model:

    public class Departments_SubCategory_Registration
        {
            [Key]
            public int SubCategoryID { get; set; }              // This is the PK
            public int CategoryID { get; set; } // this is a FK
           // [ForeignKey("SubCategoryID")]
            public Departments_Category_Registration Departments_Category_Registration { get; set; }
            public string SubCategory_Name { get; set; }
            public DateTime EntryDate { get; set; }
            public string Description_Short { get; set; }
            public string Description_Detailed { get; set; }
            public string Notes { get; set; }
    
            public Guid UniqueId { get; set; }
            public bool IsEnabled { get; set; }
    }
    
    public class Departments_Category_Registration
        {
            [Key]
            public int CategoryID { get; set; }
            public string Category_Name { get; set; }
    
            public List<Departments_SubCategory_Registration> Departments_SubCategory_Registrations { get; set; }
        }
    

    Controller:

    [Route("/{C}")]
            public async Task<IActionResult> Product(String C)
            {
                return View(await _context.Departments_SubCategory_Registration.Include(c => c.Departments_Category_Registration)
                    .Where(d => d.Departments_Category_Registration.Category_Name == C)
                      .Where(r => r.IsEnabled == true).Select(u => new Departments_SubCategory_Registration
                      {
                          CategoryID = u.CategoryID,
                          SubCategory_Name = u.SubCategory_Name
                      }).ToListAsync());
            }
    

    Product.cshtml:

    @model IEnumerable<Departments_SubCategory_Registration>
    <table class="table">
        <thead>
            <tr>
                <th>
                    @Html.DisplayNameFor(model => model.CategoryID)
                </th>
                <th>
                    @Html.DisplayNameFor(model => model.SubCategory_Name)
                </th>
                <th></th>
            </tr>
        </thead>
        <tbody>
            @foreach (var item in Model)
            {
                <tr>
                    <td>
                        @Html.DisplayFor(modelItem => item.CategoryID)
                    </td>
                    <td>
                        @Html.DisplayFor(modelItem => item.SubCategory_Name)
                    </td>
                   
                    <td>
                        <a asp-action="Edit" asp-route-id="@item.SubCategoryID">Edit</a> |
                        <a asp-action="Details" asp-route-id="@item.SubCategoryID">Details</a> |
                        <a asp-action="Delete" asp-route-id="@item.SubCategoryID">Delete</a>
                    </td>
                </tr>
            }
        </tbody>
    </table>
    

    Best Regards,

    Rena

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 10, 2019 5:30 AM

All replies

  • User1120430333 posted


    .Where(d => d.Departments_Category_Registration.Category_Name == C)

    1) How do you know the two stings will match?

        a) the length of the stings can be different, like trailing spaces

          b) case sensitivity 

    Maybe you need to be using Contains() instead of using '=='.

    Wednesday, July 10, 2019 1:07 AM
  • User711641945 posted

    Hi afrika,

    1.I use your code and it works well, could you share more code to reproduce your issue?

    2.As you annotated ,SubCategoryID is the PK and CategoryID is a FK, there is no need to define [ForeignKey("SubCategoryID")].

    3.There is a working demo as bellow:

    Model:

    public class Departments_SubCategory_Registration
        {
            [Key]
            public int SubCategoryID { get; set; }              // This is the PK
            public int CategoryID { get; set; } // this is a FK
           // [ForeignKey("SubCategoryID")]
            public Departments_Category_Registration Departments_Category_Registration { get; set; }
            public string SubCategory_Name { get; set; }
            public DateTime EntryDate { get; set; }
            public string Description_Short { get; set; }
            public string Description_Detailed { get; set; }
            public string Notes { get; set; }
    
            public Guid UniqueId { get; set; }
            public bool IsEnabled { get; set; }
    }
    
    public class Departments_Category_Registration
        {
            [Key]
            public int CategoryID { get; set; }
            public string Category_Name { get; set; }
    
            public List<Departments_SubCategory_Registration> Departments_SubCategory_Registrations { get; set; }
        }
    

    Controller:

    [Route("/{C}")]
            public async Task<IActionResult> Product(String C)
            {
                return View(await _context.Departments_SubCategory_Registration.Include(c => c.Departments_Category_Registration)
                    .Where(d => d.Departments_Category_Registration.Category_Name == C)
                      .Where(r => r.IsEnabled == true).Select(u => new Departments_SubCategory_Registration
                      {
                          CategoryID = u.CategoryID,
                          SubCategory_Name = u.SubCategory_Name
                      }).ToListAsync());
            }
    

    Product.cshtml:

    @model IEnumerable<Departments_SubCategory_Registration>
    <table class="table">
        <thead>
            <tr>
                <th>
                    @Html.DisplayNameFor(model => model.CategoryID)
                </th>
                <th>
                    @Html.DisplayNameFor(model => model.SubCategory_Name)
                </th>
                <th></th>
            </tr>
        </thead>
        <tbody>
            @foreach (var item in Model)
            {
                <tr>
                    <td>
                        @Html.DisplayFor(modelItem => item.CategoryID)
                    </td>
                    <td>
                        @Html.DisplayFor(modelItem => item.SubCategory_Name)
                    </td>
                   
                    <td>
                        <a asp-action="Edit" asp-route-id="@item.SubCategoryID">Edit</a> |
                        <a asp-action="Details" asp-route-id="@item.SubCategoryID">Details</a> |
                        <a asp-action="Delete" asp-route-id="@item.SubCategoryID">Delete</a>
                    </td>
                </tr>
            }
        </tbody>
    </table>
    

    Best Regards,

    Rena

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 10, 2019 5:30 AM
  • User-973886032 posted

    hi guys

    thanks for your contribution. I found the problem was that, though I had added FK to my model, I had not added the correct key name in the Departments_SubCategory_Registration model, unfortunately it did not give an error  at runtime.

    Its working now, many thanks

    Wednesday, July 10, 2019 11:19 AM