EF 4.1 Code First Multiple Result Sets

Question

I need to execute a Raw SQL query that returns multiple result sets. Is that possible in EF CF ?

Thanks!

Answer 1

Description

Yes you can! ;) You can perform a raw sql query using DbCommand in Entity Framework Code First too.

You can perform queries with multiple result sets and jump to the next result set using NextResult() method of the SqlDataReader class.

Sample

namespace MyNamespace
{
    public class MyDbContext : DbContext
    {

    }

    public class Test
    {
        public void Test()
        {
            MyDbContext context = new MyDbContext();

            DbCommand db = context.Database.Connection.CreateCommand();
            db.CommandText = "SELECT propertie1 FROM Table1; SELECT propertie1 from Table2";
            try
            {
                DbDataReader reader = db.ExecuteReader();

                while (reader.Read())
                {
                    // read your data from result set 1
                    string value = Convert.ToString(reader["propertie1"]);
                }

                reader.NextResult();

                while (reader.Read())
                {
                    // read your data from result set 2
                    string value = Convert.ToString(reader["propertie1"]);
                }
            }
            catch
            {
                // Exception handling
            }
            finally
            {
                if (db.Connection.State != ConnectionState.Closed)
                    db.Connection.Close();
            }
        }
    }
}

More Information

• Using DbContext in EF 4.1 Part 10: Raw SQL Queries