from s in db.ProjectName / from s in _context.ProjectName - .net 3.7 v .net Core 6 (2)

Question

from s in db.ProjectName / from s in _context.ProjectName - .net 3.7 v .net Core 6 (2)

Dean Everhart 1,536

A where statement on the property "Description" was yielding no results so I tried creating a where statement on a different field of the same field type "EntityName. The where statement on "EntityName" works as expected; the where statement on "Description" yields no results.

I've tried copy and pasting the "Description" property name from model to controller to avoid discrepancy in spelling / typos between the two.

            var variableName = from s in _context.Financial            // Toggling between two below...
                                 where s.Description == "Speedway"     //  <- Yields no results
                               //where s.EntityName == "Discover"      // <- Yields correct results
                               select s;

Controller

        // GET: Financial
        public async Task<IActionResult> IndexRecurring(string sortOrder, string currentFilter, string searchString, int? pageNumber)
        {
            ViewData["CurrentSort"] = sortOrder;

            ViewData["dateSort"] = String.IsNullOrEmpty(sortOrder) ? "dateDesc" : "";  // These are ternary statements (re: ternary conditional operators).
            ViewData["entitySort"] = sortOrder == "entity" ? "entityDesc" : "entity";
            ViewData["descriptionSort"] = sortOrder == "description" ? "descriptionDesc" : "description";

            if (searchString != null)
            {
                pageNumber = 1;
            }
            else
            {
                searchString = currentFilter;
            }

            ViewData["CurrentFilter"] = searchString;                     // Toggling between two below

            var variableName = from s in _context.Financial
                                 where s.Description == "Speedway"        // <- Yields no results
                               //where s.EntityName == "Discover"         // <- Yields correct results
                               select s;

            if (!String.IsNullOrEmpty(searchString))
            {
                variableName = variableName.Where(s => s.EntityName!.Contains(searchString)
                                               || s.AccountOwner!.Contains(searchString)
                                               || s.AccountIdentifier!.Contains(searchString)
                                               || s.Description!.Contains(searchString)
                                               || s.Category!.Contains(searchString)
                                               || s.Amount!.GetValueOrDefault().ToString().Contains(searchString)                   // int property type - requires conversion to string for search
                                                                                                                                    //|| s.DateTransaction!.GetValueOrDefault().ToString().Contains(searchString)
                                                                                                                                    //|| s.DatePost!.GetValueOrDefault().ToString().Contains(searchString)
                                               );
            }

            switch (sortOrder)
            {
                default:                                                                     // "fall-through case" in the switch statement
                    variableName = variableName.OrderByDescending(s => s.DatePost);
                    break;

                case "dateDesc":
                    variableName = variableName.OrderBy(s => s.DatePost);
                    break;

                // _________________________________

                case "entity":
                    variableName = variableName.OrderBy(s => s.EntityName);
                    break;
                case "entityDesc":
                    variableName = variableName.OrderByDescending(s => s.EntityName);
                    break;

                // _________________________________

                case "description":
                    variableName = variableName.OrderBy(s => s.Description);
                    break;
                case "descriptionDesc":
                    variableName = variableName.OrderByDescending(s => s.Description);
                    break;

            }
            int pageSize = 14;
            return View(await PaginatedList<Financial>.CreateAsync(variableName.AsNoTracking(), pageNumber ?? 1, pageSize));
        }

Model

using System.ComponentModel.DataAnnotations.Schema;
using System.ComponentModel.DataAnnotations;
using System.Xml.Linq;

namespace Dashboard.Models
{
    public class Financial
    {
        public int Id { get; set; }

        public string? EntityType { get; set; }

        public string? EntityName { get; set; }

        public string? AccountType { get; set; }

        public string? AccountOwner { get; set; }

        public string? AccountAccess { get; set; }

        public string? AccountDescription { get; set; }

        public string? AccountIdentifier { get; set; }

        // ___________________________________

        [Display(Name = "Details")]
        public string? TransactionType1 { get; set; }        // "Details"

        [Display(Name = "Transaction")]
        [DataType(DataType.Date)]
        public DateTime? DateTransaction { get; set; }      // [DataType(DataType.Date)] = only date, no time information

        [Display(Name = "Post")]
        [DataType(DataType.Date)]
        public DateTime? DatePost { get; set; }

        public string? Description { get; set; }

        [Column(TypeName = "decimal(18, 2)")]
        public int? Amount { get; set; }

        [Display(Name = "Type")]
        public string? TransactionType2 { get; set; }        // "Type"

        [Column(TypeName = "decimal(18, 2)")]
        public int? Balance { get; set; }

        public string? Category { get; set; }

        [Display(Name = "Renewal")]
        [DataType(DataType.Date)]
        public DateTime? DateRenewal { get; set; }

        // ___________________________________

        public int? CheckOrSlipNumber { get; set; }

        public string? Memo { get; set; }

        // ___________________________________

        public string? Category1a { get; set; }

        public string? Category1b { get; set; }

        public string? Category1c { get; set; }

        public string? Category2a { get; set; }

        public string? Category2b { get; set; }

        public string? Category2c { get; set; }

        public string? AccountType1 { get; set; }

        public string? AccountType2 { get; set; }

        public string? AccountType3 { get; set; }

        public string? Tag { get; set; }

        public string? Flag { get; set; }

        public bool? Private { get; set; }
    }
}

View

@model PaginatedList<Dashboard.Models.Financial>

@*************** Pre-Pagination ******************@

@*@model IEnumerable<Project01Name.Models.Class01Name>*@

@************* Pre-Pagination (end) **************@

@{
    ViewData["Recurring"] = "Recurring";
}

<h1>Recurring</h1>

<p>
    <a asp-action="Create">Create New</a>
</p>

<form asp-action="IndexRecurring" method="get">
    <div class="form-actions no-color">
        <p>
            Find by name: <input type="text" name="SearchString" value="@ViewData["CurrentFilter"]" />
            <input type="submit" value="Search" class="btn btn-default" />
            <a asp-action="IndexRecurring">Back to Full List</a>
        </p>
    </div>
</form>

<table class="table">
    <thead>
        <tr>
            <th>
                <a asp-action="IndexRecurring" asp-route-sortOrder="@ViewData["dateSort"]" asp-route-currentFilter="@ViewData["CurrentFilter"]">Post</a>
            </th>
            <th>
                <a asp-action="IndexRecurring" asp-route-sortOrder="@ViewData["entitySort"]" asp-route-currentFilter="@ViewData["CurrentFilter"]">Name</a>
            </th>
            <th>
                Owner
            </th>
            <th>
                Type
            </th>
            <th>
                ID
            </th>
            <th>
                <a asp-action="IndexRecurring" asp-route-sortOrder="@ViewData["descriptionSort"]" asp-route-currentFilter="@ViewData["CurrentFilter"]">Description</a>
            </th>
            <th>
                Amount
            </th>
            <th>
                Category
            </th>
            <th></th>
        </tr>
    </thead>
    <tbody>
        @foreach (var item in Model)
        {
            <tr>
                <td>
                    @Html.DisplayFor(modelItem => item.DatePost)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.EntityName)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.AccountOwner)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.AccountType)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.AccountIdentifier)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.Description)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.Amount)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.Category)
                </td>
                <td>
                    <a asp-action="Edit" asp-route-id="@item.Id">Edit</a> |
                    <a asp-action="Details" asp-route-id="@item.Id">Details</a> |
                    <a asp-action="Delete" asp-route-id="@item.Id">Delete</a>
                </td>
            </tr>
        }
    </tbody>
</table>

@{
    var prevDisabled = !Model.HasPreviousPage ? "disabled" : "";
    var nextDisabled = !Model.HasNextPage ? "disabled" : "";
}

<a asp-action="IndexRecurring"
   asp-route-sortOrder="@ViewData["CurrentSort"]"
   asp-route-pageNumber="@(Model.PageIndex - 1)"
   asp-route-currentFilter="@ViewData["CurrentFilter"]"
   class="btn btn-default @prevDisabled">
    Previous
</a>
<a asp-action="IndexRecurring"
   asp-route-sortOrder="@ViewData["CurrentSort"]"
   asp-route-pageNumber="@(Model.PageIndex + 1)"
   asp-route-currentFilter="@ViewData["CurrentFilter"]"
   class="btn btn-default @nextDisabled">
    Next
</a>

MSSMS

User's image

Sample Data
User's image

AgaveJoe 29,946 Reputation points

2023-02-20T21:43:24.13+00:00
There's probably no "Speedway" value in the Description column. We cannot see your data so you'll have to take a look yourself. Have you tried executing a SQL script using SSMS?

SELECT * FROM Financial WHERE Description = 'Speedway'

Or perhaps

SELECT * FROM Financial WHERE Description LIKE '%Speedway%'
Dean Everhart 1,536 Reputation points

2023-02-21T09:17:47.39+00:00

Great idea, to do same search in MSSMS. Thanks.

SELECT *

FROM Financial

WHERE Description LIKE '%Speedway%'

yields:

Accepted answer

0 additional answers

Your answer

AgaveJoe 29,946 Reputation points

2023-02-20T21:43:24.13+00:00

There's probably no "Speedway" value in the Description column. We cannot see your data so you'll have to take a look yourself. Have you tried executing a SQL script using SSMS?

SELECT * FROM Financial WHERE Description = 'Speedway'

Or perhaps

SELECT * FROM Financial WHERE Description LIKE '%Speedway%'
Dean Everhart 1,536 Reputation points

2023-02-21T09:17:47.39+00:00

Great idea, to do same search in MSSMS. Thanks.

SELECT *

FROM Financial

WHERE Description LIKE '%Speedway%'

yields:

Answer 1

Hi @Dean Everhart

From the sample data, we can see the Description like "SPEEDWAY 01530 COMMACK NY" or "SPEEDWAY 01530 COMMACK NY", then in the LINQ query statement, you should use Contains method to filter the data, instead of "==".

Code like this:

            var variableName = from s in _context.Financial
                               where s.Description.Contains("Speedway")         
                               //where s.EntityName == "Discover"         // <- Yields correct results
                               select s;

Or ignore the case:

            var variableName = from s in _context.Financials
                               where s.Description.ToLower().Contains("speedway")        //ignore the case
                               //where s.EntityName == "Discover"         // <- Yields correct results
                               select s;

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best regards,

Dillion

Dean Everhart 1,536 Reputation points

2023-02-21T09:42:45.9633333+00:00

That did it. Thanks.

Share via

from s in db.ProjectName / from s in _context.ProjectName - .net 3.7 v .net Core 6 (2)

0 additional answers

Your answer