Turning SQL into LINQ Query C# mvc

Question

How can I turn this code into linq Query?

SELECT 
    max_valor, FORMAT(Data_cont, 'hh:mm') 
FROM 
    dbo.Conteudo
WHERE
    nomeAl = @id 
    AND FORMAT(Data_cont, 'dd') = FORMAT(GETDATE(), 'dd')
ORDER BY
    Data_cont

The @id is the page Id,

Thanks :)

Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. — NetMage, Aug 24 '20 at 17:07

score 0 · Answer 1 · answered Aug 24 '20 at 11:32

Try following. I simulated the Entity Class to give you a better visualization of the code :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Globalization;

namespace ConsoleApplication166
{
    class Program
    {
        static void Main(string[] args)
        {
            Context db = new Context();
            string id = "abc";

            var results = db.Conteudo
                .Where(x => (x.nomeAl == id) && (x.Data_cont.Day == DateTime.Now.Day))
                .OrderBy(x => x.Data_cont)
                .Select(x => new { max_valor = x.max_valor, date = x.Data_cont.ToString("hh:mm") })
                .ToList();
        }
    }
    public class Context
    {
        public List<Conteudo> Conteudo { get; set; }
    }
    public class Conteudo
    {
        public string nomeAl { get; set; }
        public DateTime Data_cont { get; set; }
        public string max_valor { get; set; }
    }
 
}

score 0 · Answer 2 · answered Aug 24 '20 at 11:35

You can check this.

1 If you want to get all the columns

int currentDay = DateTime.Now.Day;
var result = yourDbContext.Conteudo.OrderBy(x => x.Data_cont).Where(x => x.nomeAl == pageId && x.Data_cont.Day == currentDay)

2 If you only need max_valor, FORMAT(Data_cont, 'hh:mm')

int currentDay = DateTime.Now.Day;
var result = yourDbContext.Conteudo.OrderBy(x => x.Data_cont).Where(x => x.nomeAl == pageId && x.Data_cont.Day == currentDay)
.Select(x => new {
       max_valor = x.max_valor,
       formattedTime = x.Data_cont.ToString("hh:mm")
}).ToList()

Turning SQL into LINQ Query C# mvc

2 Answers2