SQL Conversion to Linq conversion for .NET Core

Question

Here is my SQL query. I'm trying to produce the same results in my .NET Core web app using a linq query.

SELECT * 
FROM dbo.BUD
WHERE LEFT(PeriodD, 4) = YEAR(GETDATE())

Here is what I've tried with no success in my linq query

List<SelectListItem> budNames = _ctx.Budgets.AsNoTracking()     
            .Where(n => (n.Period, 4) == GETDATE())  
            .Select(n => new SelectListItem
                             {
                                 // values
                             }).ToList();

Its a character fields YYYYMM, sorry that probably would have been helpful. — Andy Williams
– Andy Williams, Commented Jun 25, 2021 at 13:06
The comparison must be all means include YEAR(GETDATE()) - otherwise, you're comparing to one specific moment in time - not a whole yeaR! — marc_s
– marc_s, Commented Jun 25, 2021 at 14:12

Emaro · Accepted Answer · 2021-06-25 13:28:11Z

Mainly this line causes you problems:

.Where(n=> (n.Period,4) == GetDate())

To get the first 4 characters of a string, use the Substring method:

n.Period.Substring(0, 4) // beginn 0 away from the first char, take 4 of them

To get a sting of the current year (DateTime):

var currentYear = DateTime.Now.ToString("yyyy");

Applied to your example:

var currentYear = DateTime.Now.ToString("yyyy");
List<SelectListItem> budNames = _ctx.Budgets.AsNoTracking()     
    .Where(n => n.Period.Substring(0, 4) == currentYear)  
    .Select(n => new SelectListItem
    {
        // values
    }).ToList();

Or, you could use the StartsWith method:

var currentYear = DateTime.Now.ToString("yyyy");
List<SelectListItem> budNames = _ctx.Budgets.AsNoTracking()     
    .Where(n => n.Period.StartsWith(currentYear))  
    .Select(n => new SelectListItem
    {
        // values
    }).ToList();

Collectives™ on Stack Overflow

SQL Conversion to Linq conversion for .NET Core

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related