Having managed to get to SSDL, CSDL and MSDL files for my Northwind database it's perhaps time to do something with them. Note that this is still just using the default 1:1 mapping that the toolset has given me.

I took the three files that the edmgen.exe tool gave me and wrote a bit of a query, added them to my project and set their build action to "Copy to output directory" so that they're available to my code.

Having done that, I can start to cruft up an EntityConnection;

using System.Data.EntityClient;

class Program
{
  static void Main(string[] args)
  {
    using (EntityConnection con = new EntityConnection("Name=ConString"))
    {
      con.Close();
    }
  }
}

That "Name=ConString" on the connection string means "look into the configuration file for the connection string" where I have the setting;

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <connectionStrings>
    <add name="ConString" 
         connectionString="metadata=.\northwind.csdl|.\northwind.ssdl|.\northwind.msl;provider=System.Data.SqlClient;provider connection string=&quot;server=.;database=Northwind;Integrated Security=True&quot;" providerName="System.Data.EntityClient" />
  </connectionStrings>
</configuration>

So the connection string points to the 3 metadata files, the SqlClient provider and the connection string for that provider.

And then we can go and run a miniature query using eSQL...

using System;
using System.Data;
using System.Data.EntityClient;
using System.Data.Common;

class Program
{
  static void Main(string[] args)
  {
    using (EntityConnection con = new EntityConnection("Name=ConString"))
    {
      con.Open();

      using (EntityCommand com = new EntityCommand(
        "select top(3) c.customerid, c.companyname from NorthwindContext.Customers as c", con))
      {
        using (EntityDataReader reader = com.ExecuteReader(CommandBehavior.SequentialAccess))
        {
          while (reader.Read())
          {
            PrintData(reader);
          }
          reader.Close();
        }
      }
      con.Close();
    }
  }
  static void PrintData(IDataRecord reader)
  {
    Console.WriteLine("Record");

    for (int i = 0; i < reader.FieldCount; i++)
    {
      Console.WriteLine("\tField [{0}], Value [{1}]", 
        reader.GetName(i), reader[i]);
    }
  }
}

What  does this print out?

Record
        Field [customerid], Value [ALFKI]
        Field [companyname], Value [Alfreds Futterkiste]
Record
        Field [customerid], Value [ANATR]
        Field [companyname], Value [Ana Trujillo Emparedados y helados]
Record
        Field [customerid], Value [ANTON]
        Field [companyname], Value [Antonio Moreno Taquería]

Now, with the EntityDataReader you can not only treat is as an IDataRecord but also an IExtendedDataRecord. This looks like this;

  public interface IExtendedDataRecord : IDataRecord
  {
    DataRecordInfo DataRecordInfo { get; }
    DbDataReader GetDataReader(int i);
    DbDataRecord GetDataRecord(int i);
  }

So, what does that mean? It means that you can get hold of a DataRecordInfo which has a bunch of metadata in it. It also means that, when reading a IDataRecord you might also encounter somewhere in the result-set another DbDataRecord and you might want to get a reader at that point (i.e. GetDataRecord/GetDataReader). So, it's setting up an infrastructure that supports records within records and sets of records within records.

Yikes! :-) So, if I want to prepare myself for these kinds of resultsets then I might need a slightly different approach (sorry about the recursion, might get a bit expensive if we got deep hierarchies here);

using System;
using System.Data;
using System.Data.EntityClient;
using System.Data.Common;

class Program
{
  static void Main(string[] args)
  {
    using (EntityConnection con = new EntityConnection("Name=ConString"))
    {
      con.Open();

      using (EntityCommand com = new EntityCommand(
        "select top(3) c.customerid, c.companyname from NorthwindContext.Customers as c", con))
      {
        using (EntityDataReader reader = com.ExecuteReader(CommandBehavior.SequentialAccess))
        {
          DumpReader(reader, 0);
        }
      }
      con.Close();
    }
  }
  static void DumpReader(IDataReader reader, int level)
  {
    while (reader.Read())
    {
      IExtendedDataRecord extended = reader as IExtendedDataRecord;

      Tabify(level);

      Console.WriteLine("Record. Type is [{0}]",
        extended == null ? "unknown" : extended.DataRecordInfo.RecordType.ToString());

      DumpRecord(reader, level);
    }
    reader.Close();
  }
  static void Tabify(int level)
  {
    for (int i = 0; i < level * 3; i++)
    {
      Console.Write(" ");
    }
  }
  static void DumpRecord(IDataRecord record, int level)
  {
    for (int i = 0; i < record.FieldCount; i++)
    {
      object o = record[i];

      if (o is IDataReader)
      {
        DumpReader((IDataReader)o, level + 1);
      }
      else if (o is IDataRecord)
      {
        DumpRecord((IDataRecord)o, level + 1);
      }
      else
      {
        Tabify(level + 1);

        Console.WriteLine("Field [{0}], Value [{1}]",
          record.GetName(i), o);
      }
    }
  }
}

Now this prints out;

Record. Type is [Transient.rowtype[(customerid,Edm.String(Nullable=True,DefaultValue=,MaxLength=5,Unicode=True,FixedLength=True)),(companyname,Edm.String(Nullable=True,DefaultValue=,MaxLength=40,Unicode=True,FixedLength=False))]]
   Field [customerid], Value [ALFKI]
   Field [companyname], Value [Alfreds Futterkiste]
Record. Type is [Transient.rowtype[(customerid,Edm.String(Nullable=True,DefaultValue=,MaxLength=5,Unicode=True,FixedLength=True)),(companyname,Edm.String(Nullable=True,DefaultValue=,MaxLength=40,Unicode=True,FixedLength=False))]]
   Field [customerid], Value [ANATR]
   Field [companyname], Value [Ana Trujillo Emparedados y helados]
Record. Type is [Transient.rowtype[(customerid,Edm.String(Nullable=True,DefaultValue=,MaxLength=5,Unicode=True,FixedLength=True)),(companyname,Edm.String(Nullable=True,DefaultValue=,MaxLength=40,Unicode=True,FixedLength=False))]]
   Field [customerid], Value [ANTON]
   Field [companyname], Value [Antonio Moreno Taquería]

Check out those record types. Transient.rowtype(col1, col2, col3) etc - as a row wrapper sitting around my 3 projected columns. My slightly more complicated code will deal with slightly more complicated queries too. For instance, I can select the whole entity rather than just properties from it by changing the query text to;

"select top(3) c from NorthwindContext.Customers as c"

What does this give me? I only included the first row but it looks like this;

Record. Type is [Transient.rowtype[(c,Northwind.Customers(Nullable=True,DefaultValue=))]]
      Field [CustomerID], Value [ALFKI]
      Field [CompanyName], Value [Alfreds Futterkiste]
      Field [ContactName], Value [Maria Anders]
      Field [ContactTitle], Value [Sales Representative]
      Field [Address], Value [Obere Str. 57]
      Field [City], Value [Berlin]
      Field [Region], Value []
      Field [PostalCode], Value [12209]
      Field [Country], Value [Germany]
      Field [Phone], Value [030-0074321]
      Field [Fax], Value [030-0076545]

"Interesting" :-) So, we have a row and that row has only one column now which is of type Northwind.Customers and we're then treating that row as a IDataRecord in order to reach in and pull out the fields from it. That is, it's a row that has one column and that column is a record.

What if we used the VALUE query that Entity SQL supports and changed our query text to;

"select value top(3) c from NorthwindContext.Customers as c"

This now gives me;

Record. Type is [Northwind.Customers]
   Field [CustomerID], Value [ALFKI]
   Field [CompanyName], Value [Alfreds Futterkiste]
   Field [ContactName], Value [Maria Anders]
   Field [ContactTitle], Value [Sales Representative]
   Field [Address], Value [Obere Str. 57]
   Field [City], Value [Berlin]
   Field [Region], Value []
   Field [PostalCode], Value [12209]
   Field [Country], Value [Germany]
   Field [Phone], Value [030-0074321]
   Field [Fax], Value [030-0076545]

So, the record type is no longer a Transient.rowtype - it's now a Northwind.Customers entity type but I can still reach into it as an IDataRecord and pull out those fields. What if we went and used something like a cross-product in order to introduce another entity set and changed the query to be;

"select top (3) c,o from NorthwindContext.Customers as c, NorthwindContext.Orders as o"

Now, this brings back quite a bit of data so again I'm just including the first "row";

Record. Type is [Transient.rowtype[(c,Northwind.Customers(Nullable=True,DefaultValue=)),(o,Northwind.Orders(Nullable=True,DefaultValue=))]]
      Field [CustomerID], Value [ALFKI]
      Field [CompanyName], Value [Alfreds Futterkiste]
      Field [ContactName], Value [Maria Anders]
      Field [ContactTitle], Value [Sales Representative]
      Field [Address], Value [Obere Str. 57]
      Field [City], Value [Berlin]
      Field [Region], Value []
      Field [PostalCode], Value [12209]
      Field [Country], Value [Germany]
      Field [Phone], Value [030-0074321]
      Field [Fax], Value [030-0076545]
      Field [OrderID], Value [10248]
      Field [OrderDate], Value [04/07/1996 00:00:00]
      Field [RequiredDate], Value [01/08/1996 00:00:00]
      Field [ShippedDate], Value [16/07/1996 00:00:00]
      Field [Freight], Value [32.3800]
      Field [ShipName], Value [Vins et alcools Chevalier]
      Field [ShipAddress], Value [59 rue de l'Abbaye]
      Field [ShipCity], Value [Reims]
      Field [ShipRegion], Value []
      Field [ShipPostalCode], Value [51100]
      Field [ShipCountry], Value [France]

Very interesting. We bring back a Transient.rowtype( Northwind.Customers, Northwind.Orders). So, each tuple that we're projecting here is a 2-column row in which both columns can be treated as IDataRecord because one is a "Customers record" and the other is an "Orders record". The code I wrote just reaches into each of those IDataRecords and so it kind of makes it looks like one big "flat" record but it's not - it's one record with 2 columns.

Now, can we use that VALUE query trick to turn this into a flattened record like we did with the previous query? No, as far as I know you can only do the VALUE trick when you're projecting a single thing - not when you're projecting two things like I'm doing here.

So, we've seen;

1. Records that contain fields (that's so ADO.NET 2.0 these days I guess :-))
2. Records that contain records.

but we've not seen records that contain resultsets. What about a query like the following one that tries to traverse to the Orders property of my customer entity;

"select top (3) c, navigate(c, Northwind.FK_Orders_Customers) from NorthwindContext.Customers as c"

(or the equivalent)

  "select top (3) c, c.Orders from NorthwindContext.Customers as c"

Note, this only works because in my CSDL file I have this relationship;

    <NavigationProperty Name="Orders" Relationship="Northwind.FK_Orders_Customers" FromRole="Customers" ToRole="Orders" />

on the Customers EntityType and, furthermore, that links up with this AssociationSet;

    <AssociationSet Name="FK_Orders_Customers" Association="Northwind.FK_Orders_Customers">
      <End Role="Customers" EntitySet="Customers" />
      <End Role="Orders" EntitySet="Orders" />
    </AssociationSet>

and that links up with this Association;

  <Association Name="FK_Orders_Customers">
    <End Role="Customers" Type="Northwind.Customers" Multiplicity="0..1" />
    <End Role="Orders" Type="Northwind.Orders" Multiplicity="*" />
  </Association>

and that's why I can do this navigation. Anyway, back to the plot, what does this query give me using the code I've been running all along?

Record. Type is [Transient.rowtype[(c,Northwind.Customers(Nullable=True,DefaultValue=)),(_##0,Transient.collection[Transient.reference[Northwind.Orders](Nullable=False,DefaultValue=)](Nullable=True,DefaultValue=))]]
      Field [CustomerID], Value [ALFKI]
      Field [CompanyName], Value [Alfreds Futterkiste]
      Field [ContactName], Value [Maria Anders]
      Field [ContactTitle], Value [Sales Representative]
      Field [Address], Value [Obere Str. 57]
      Field [City], Value [Berlin]
      Field [Region], Value []
      Field [PostalCode], Value [12209]
      Field [Country], Value [Germany]
      Field [Phone], Value [030-0074321]
      Field [Fax], Value [030-0076545]
   Record. Type is [Transient.reference[Northwind.Orders]]
      Field [Value], Value [System.Data.EntityKey]
   Record. Type is [Transient.reference[Northwind.Orders]]
      Field [Value], Value [System.Data.EntityKey]
   Record. Type is [Transient.reference[Northwind.Orders]]
      Field [Value], Value [System.Data.EntityKey]

Note - I only included the first customer record's worth of output and the first 3 order's worth of output for that customer. But, we can see that we now have a Transient.rowType(Customers, Transient.collection[Trasient.reference[Orders]]) and that Transient.reference relates to an EntityKey.

Now, my limited understanding of EntityKey at this point is that it's a nice way that the EF can say "Hey, look, there's a record here but I've not bothered to load it yet because that'd be a bit tedious but I've got the key value, ok?".

If you want the full gory details of the record "pointed to" by that EntityKey then I think you've got to "dereference" it and that involves the DEREF keyword. This next query achieved that for me but I'm a long way from knowing that it's right;

"select top (3) c, (select deref(o) from navigate(c, Northwind.FK_Orders_Customers) as o) from NorthwindContext.Customers as c"

and what results does that give me? Those EntityKeys are gone and now we can see the contents of the order records (pretty neat). Again, I only include the first customer here and 2 orders;

Record. Type is [Transient.rowtype[(c,Northwind.Customers(Nullable=True,DefaultValue=)),(_##1,Transient.collection[Transient.rowtype[(_##0,Northwind.Orders(Nullable=True,DefaultValue=))](Nullable=True,DefaultValue=)](Nullable=True,DefaultValue=))]]
      Field [CustomerID], Value [ALFKI]
      Field [CompanyName], Value [Alfreds Futterkiste]
      Field [ContactName], Value [Maria Anders]
      Field [ContactTitle], Value [Sales Representative]
      Field [Address], Value [Obere Str. 57]
      Field [City], Value [Berlin]
      Field [Region], Value []
      Field [PostalCode], Value [12209]
      Field [Country], Value [Germany]
      Field [Phone], Value [030-0074321]
      Field [Fax], Value [030-0076545]
   Record. Type is [Transient.rowtype[(_##0,Northwind.Orders(Nullable=True,DefaultValue=))]]
         Field [OrderID], Value [10643]
         Field [OrderDate], Value [25/08/1997 00:00:00]
         Field [RequiredDate], Value [22/09/1997 00:00:00]
         Field [ShippedDate], Value [02/09/1997 00:00:00]
         Field [Freight], Value [29.4600]
         Field [ShipName], Value [Alfreds Futterkiste]
         Field [ShipAddress], Value [Obere Str. 57]
         Field [ShipCity], Value [Berlin]
         Field [ShipRegion], Value []
         Field [ShipPostalCode], Value [12209]
         Field [ShipCountry], Value [Germany]
   Record. Type is [Transient.rowtype[(_##0,Northwind.Orders(Nullable=True,DefaultValue=))]]
         Field [OrderID], Value [10692]
         Field [OrderDate], Value [03/10/1997 00:00:00]
         Field [RequiredDate], Value [31/10/1997 00:00:00]
         Field [ShippedDate], Value [13/10/1997 00:00:00]
         Field [Freight], Value [61.0200]
         Field [ShipName], Value [Alfred's Futterkiste]
         Field [ShipAddress], Value [Obere Str. 57]
         Field [ShipCity], Value [Berlin]
         Field [ShipRegion], Value []
         Field [ShipPostalCode], Value [12209]
         Field [ShipCountry], Value [Germany]

Ok, I've played with some bits and this post is getting long so I'll wrap it up here. I want to carry on playing with some more eSQL in my next post as there's lots of things in that language that are fun to look at :-)