CODEWITHSUNDEEP
c#entity-framework-6ado.netsqldatareader
VSB
VSB

Reputation: 10375

Read Entity objects from database using EntityDataReader

Due to some reason I need to read entity objects directly from database using ADO.Net.

I've found below snippet from Microsoft documentation. I want to know are there any methods to read whole row into an Onject ('contact' in this sample) using EntityDataReader instead of mapping every single field to every property? I mean instead of reading Contact.Id and Contact.Name and other fields one by one, are there any methods which read one row into one object or not?

using (EntityConnection conn =
    new EntityConnection("name=AdventureWorksEntities"))
{
    conn.Open();

    string esqlQuery = @"SELECT VALUE contacts FROM
            AdventureWorksEntities.Contacts AS contacts
            WHERE contacts.ContactID == @id";

    // Create an EntityCommand.
    using (EntityCommand cmd = conn.CreateCommand())
    {
        cmd.CommandText = esqlQuery;
        EntityParameter param = new EntityParameter();
        param.ParameterName = "id";
        param.Value = 3;
        cmd.Parameters.Add(param);

        // Execute the command.
        using (EntityDataReader rdr =
            cmd.ExecuteReader(CommandBehavior.SequentialAccess))
        {
            // The result returned by this query contains
            // Address complex Types.
            while (rdr.Read())
            {
                // Display CustomerID
                Console.WriteLine("Contact ID: {0}",
                    rdr["ContactID"]);
                // Display Address information.
                DbDataRecord nestedRecord =
                    rdr["EmailPhoneComplexProperty"] as DbDataRecord;
                Console.WriteLine("Email and Phone Info:");
                for (int i = 0; i < nestedRecord.FieldCount; i++)
                {
                    Console.WriteLine("  " + nestedRecord.GetName(i) +
                        ": " + nestedRecord.GetValue(i));
                }
            }
        }
    }
    conn.Close();
}

Upvotes: 0

Views: 402

Answers (1)

peterpie
peterpie

Reputation: 160

Your cleanest option is to use execute your query using EntityFramework as suggested by @herosuper

In your example, you'd need to do something like this:

EntityContext ctx = new EntityContext();
var contacts= ctx.Contacts
    .SqlQuery("SELECT * FROM AdventureWorksEntities.Contacts AS contacts" 
+ "WHERE contacts.ContactID =@id", new SqlParameter("@id", 3)).ToList();

From here, you would be able to:

var myvariable = contacts[0].ContactID;//zero is index of list. you can use foreach loop.
var mysecondvariable = contacts[0].EmailPhoneComplexProperty;

Alternatively, you might skip the whole SQL string by by doing this:

EntityContext ctx = new EntityContext();
var contact= ctx.Contacts.Where(a=> a.ContactID ==3).ToList();

I'm assuming the query returns more than one record, otherwise you would just use FirstOrDefault() instead of Where()

Upvotes: 1

Related Questions