Make a free query using the Entity frame MVC

Question

I would like to bring a result of a days calculation using mvc with Entity frame.

My appointment would be this :

select
   Notas_IdSoft, Notas_TipoLic, t.Notas_Validade,
   DATEDIFF(DAY, GETDATE(), t.Notas_Validade ) as Data
from Notas as t   
where
DATEDIFF(DAY, GETDATE(), t.Notas_Validade ) >10

I’d like to apply it to my page, but I don’t know how I do it.

Answer 1

You can create a specific class that will do it for you:

public static class UteisDB
{
    public static IEnumerable<dynamic> CollectionFromSql(this DbContext dbContext, string Sql)
    {
        using (var cmd = dbContext.Database.Connection.CreateCommand())
        {
            cmd.CommandText = Sql;
            cmd.CommandTimeout = 120;
            if (cmd.Connection.State != System.Data.ConnectionState.Open)
                cmd.Connection.Open();

            using (var dataReader = cmd.ExecuteReader())
            {

                while (dataReader.Read())
                {
                    var dataRow = GetDataRow(dataReader);
                    yield return dataRow;

                }
            }


        }
    }

    private static dynamic GetDataRow(System.Data.Common.DbDataReader dataReader)
    {
        var dataRow = new System.Dynamic.ExpandoObject() as IDictionary<string, object>;
        for (var fieldCount = 0; fieldCount < dataReader.FieldCount; fieldCount++)
            dataRow.Add(dataReader.GetName(fieldCount), dataReader[fieldCount]);
        return dataRow;
    }
}

Then it’s consuming this class like this:

SQL = "select count(*) as Qtd from minhaTabela";
var MyList1 = db.CollectionFromSql(SQL).ToList();

That’s it.