Anonymous type result from sql query execution entity framework

by

You have to use raw Sql for that, the entitity framework SqlQuery<T> will only work for objects with known types.

here is the method I use :

public static IEnumerable<dynamic> DynamicListFromSql(this DbContext db, string Sql, Dictionary<string, object> Params)
{
    using (var cmd = db.Database.Connection.CreateCommand())
    {
        cmd.CommandText = Sql;
        if (cmd.Connection.State != ConnectionState.Open) { cmd.Connection.Open(); }

        foreach (KeyValuePair<string, object> p in Params)
        {
            DbParameter dbParameter = cmd.CreateParameter();
            dbParameter.ParameterName = p.Key;
            dbParameter.Value = p.Value;
            cmd.Parameters.Add(dbParameter);
        }

        using (var dataReader = cmd.ExecuteReader())
        {
            while (dataReader.Read())
            {
                var row = new ExpandoObject() as IDictionary<string, object>;
                for (var fieldCount = 0; fieldCount < dataReader.FieldCount; fieldCount++)
                {
                    row.Add(dataReader.GetName(fieldCount), dataReader[fieldCount]);
                }
                yield return row;
            }
        }
    }
}

You can call it like this :

List<dynamic> results = DynamicListFromSql(myDb,"select * from table where a=@a and b=@b", new Dictionary<string, object> { { "a", true }, { "b", false } }).ToList();

More Related Contents:

  1. Fastest Way of Inserting in Entity Framework
  2. Entering keys manually with Entity Framework
  3. How can I log the generated SQL from DbContext.SaveChanges() in my Program? [duplicate]
  4. Validation failed for one or more entities while saving changes to SQL Server Database using Entity Framework
  5. LINQ to SQL multiple tables left outer join
  6. Entity Framework error: Cannot insert explicit value for identity column in table
  7. Entity Framework. Delete all rows in table
  8. Entity Framework – stored procedure return value
  9. EF 6 Parameter Sniffing
  10. Retrieve an object from entityframework without ONE field
  11. Join and Include in Entity Framework
  12. Calling a SQL User-defined function in a LINQ query
  13. Stored procedure returns int instead of result set
  14. How to use String property as primary key in Entity Framework
  15. Querying data using Entity Framework from dynamically created table
  16. Taking lot of time to read from SQL Server using c#
  17. SQL Date format BETWEEN query
  18. Add data annotations to a class generated by entity framework
  19. Circular reference detected exception while serializing object to JSON
  20. Passing List to SQL Stored Procedure
  21. Return/consume dynamic anonymous type across assembly boundaries
  22. Entity Framework – retrieve ID before ‘SaveChanges’ inside a transaction
  23. Entity Framework with XML Files [closed]
  24. The operation cannot be completed because the DbContext has been disposed error
  25. Database.BeginTransaction vs Transactions.TransactionScope [duplicate]
  26. Table name and table field on SqlParameter C#?
  27. Entity framework large data set, out of memory exception
  28. Parallel doesnt work with Entity Framework
  29. Entity Framework Multiple Object Contexts
  30. Watch for a table new records in sql database

Leave a Comment