I've written a set of Entity Framework (and one which is more general for LINQ to Entities and LINQ to SQL) tutorials that are on DataDeveloper.NET Tutorials page.

Most of these are beginner basics. The "101 Tutorials" are designed to make sure new programmers don't get completely lost. So if you need the extra handholding, it's there, if not just skim over it to get to the juicy bits.

The Tutorials so far are:

101 Tutorial: Use an Entity Framework Entity as a WinForms Data Source

Many to Many Relationships in the Entity Data Model

Using Stored Procedures for Insert, Update & Delete in an Entity Data Model

Naming conventions in an Entity Data Model: Cleaning up the wizard-generated EDM

Adding items to a LINQ Query of anonymous types - after the fact

101 Tutorial: ASP.NET DataBinding with the Entity Framework

101 Tutorials: WPF Databinding with Entity Framework

I followed the great walkthrough on Brad's blog showing how to use MVC together with Entity Framework. But I did it my own way - in VB, using a different database and trying to write more effective EF queries. I have a simple solution that renders these views from the AdventureWorksLT database:

Start with a list of customers who have orders in the system (that's less than 10% of the full customer list)

Then I can click on a customer and see a list of their orders

Then drill in to see the order details. (The Edit button is not implemented yet, in case you were wondering!)

So here is what's significantly different from Brad's walkthrough.

• My EDM is created from AdventureWorksLT.
• The relationship from AW's SalesOrderHeaders to Customer is the same as the relationship from Northwind's Products to Category. Therefore, where he use Products, I use SalesOrderHeaders and  where he uses Categories, I use Customers.
• One of the keys for getting data easily to a view is that we need to send ONE object (and not an anoymous type) to the view. Yet what we really desire in the case of the List of Order (which also has the Customer Name) and the list of details (which also has data from the order and the customer) is an object graph.

So on the 2nd page, I need to pass in an set of orders with their related Customer entity so that I can have access to the customer name.

On the 3rd page, I need to pass in a set of order details with their related Order entiteis AND the order's related customer.

Brad achieves this by starting with the desired entity and then using entity references and some scary looking LINQ to Entities queries.

List<Product> products =TheProducts.Where(c => c.Category.CategoryName == category).ToList();
//prepare the view by explicitly loading the categories  
products.FindAll(p => p.Category == null).ForEach(p => p.CategoryReference.Load()); 

Only because I've spent a lot of time with LINQ to Entities, do I happen to know a little trick.

If I start with the "parent", i.e. category and query it's property collection (i.e. products), when I return the property collection, the "parent" entity is still attached.

So taking Brad's query, I can get the same effect with this query (still in C#):

     var _prod = Northwind.Categories.
              Where(c => c.CategoryName == id).
              OrderBy(c => c.CategoryName).
              Select(c => c.Products).
              First().ToList(); 

(Update: turns out this only works because of a bug which will get fixed - see THIS POST for an even better way!)

This returns a list of products that belong to the category. However I do not have to do any extra loading to get to Product.Category.CategoryName. Beasue my query began with the Category, it's already there. (I learned this by trial and error by the way.)

Therefore, in my SalesOrderController (my versoin of his productController), the List ControllerAction code is a little different.

I use the same type of querying to get the order details.

Another thing that I spent some time thinking about and asking about was about the ObjectContext. In a web app you want an ojbectcontext to be as short-lived as possible. I notice that Brad was instantiating in the class level declarations. This is okay because in the background, MVC  instantiates  the class for each ControllerAction and then disposes it when it's finished. It doesn't hang around waiting for another method call. (This is one of the key premises of MVC. As Kevin Hoffman explained to me, it works in "short bursts" long enough to get something out to the browser. I have much to learn!)

Brad uses the CategoryName as the basis for the view creation so that he gets a pretty URL http://host/products/list/Beverages.

I'm still seeing if there is a way around this, but I don't like querying on a string like this. I like my keys! So I'm passing in CustomerID and SalesOrderID and my urls aren't as pretty.

Here is what my controller looks like and you can see my queries that populate the Customer List, the customer's order list and the order's detail list.

Imports System.Web.Mvc
Imports System.Linq
Imports MvcApplication.awModel
Namespace MvcApplication.Controllers 

  Public Class SalesOrdersController
    Inherits System.Web.Mvc.Controller

    <ControllerAction()> _
    Sub Index()
      REM Add Action Logic here
    End Sub

    'example URL:http://localhost:xxxx/SalesOrders/Customers
    <ControllerAction()> _
    Public Sub Customers()
      Using aw = New awEntities
        Dim _customers = aw.Customers. _
                         Where(Function(c) c.SalesOrderHeaders.Any). _
                         OrderBy(Function(c) c.CompanyName).ToList
        RenderView("Customers", _customers)
      End Using
    End Sub

    'example URL:http://localhost:xxxx/SalesOrders/List/[CustomerID]
    <ControllerAction()> _
    Public Sub List(ByVal id As String)
      Using aw = New awEntities
        Dim _salesorders = (From cust In aw.Customers _
                          Where cust.CustomerID = id _
                          Select cust.SalesOrderHeaders).FirstorDefault.ToList
        RenderView("SalesOrdersbyCustomer", _salesorders)
      End Using
    End Sub

    'example URL:http://localhost:xxxx/SalesOrders/List/#### (order number)
    <ControllerAction()> _
    Public Sub Detail(ByVal id As String)
      Using aw = New awEntities
        Dim _order = (From ord In aw.SalesOrderHeaders.Include("SalesOrderDetails.Product") _
                     Where ord.SalesOrderID = id _
                     Select ord).First
        Dim _order2 = (From cust In aw.Customers.Include("SalesOrderHeaders.SalesOrderDetails.Product") _
                   Where cust.SalesOrderHeaders.Any(Function(so As SalesOrderHeader) so.SalesOrderID = id) _
                   Select cust.SalesOrderHeaders).First.ToList.First
        RenderView("SalesOrder", _order2)
      End Using
    End Sub 

  End Class
End Namespace 

The markup is not really different from Brad's since I can drill from the ViewData into my references (Customer, Product, SalesOrder) thanks to my queries (which make me feel so clever!)

The last page just uses tables to do the trick.

<asp:Content ID="Content1" ContentPlaceHolderID="MainContentPlaceHolder" runat="server">
<h2><%=ViewData.Customer.CompanyName%></h2>
<h3>
<%=String.Format("Sales Order #: {0}", ViewData.SalesOrderNumber)%>
<%=String.Format("Order Date: {0:d}", ViewData.OrderDate)%>
<%=String.Format("Order Total: {0:C2}", ViewData.TotalDue)%>
</h3>
 <table>
 <tr><td style="width: 225px">Product</td><td style="width: 154px">Quantity</td>
   <td style="width: 256px">Line Item Total</td></tr>
            <% For Each detail As awModel.SalesOrderDetail In ViewData.SalesOrderDetails%>
            <tr>
               <td style="width: 225px"><%=detail.Product.Name%></td>  
               <td align="center" style="width: 154px"><%=detail.OrderQty%></td>
               <td style="width: 256px"> <%=String.Format("{0:C2}", detail.LineTotal)%></td>
            </tr>
        <% Next%>
    </table>
</asp:Content>

As an aside, this was my first time really playing with client side code in VS2008 and I am enamored of all the intellisense in there!

In the TabletPC SDKs and in WPF it's very easy to take an ink image and save it to an image format - BMP, JPG, etc.

Then came Silverlight and the InkPresenter and naturally I wanted to do the same. But it wasn't so easy.

Silverlight itself isn't bogged down with that functionality. So you first need to get the XAML representation of the Ink and send it to a service where either the TabletPC SDK or the WPF APIs are available. Even then you are not home free because the Silverlight ink is not quite the same as either of the other two. So you then need to extract data from the XAML representation of the Silverlight ink and create a new object for whichever API you choose.

This was all done in Silverlight 1.0. I haven't pulled this into Silverlight 1.1/2.0 yet, but it should all be the same and you still have to do the conversion on the server side. THe only difference of course, is the javascript needs to be converted to .NET code on the client side.

Even then, there is still some more trickery because there is something strange with using the width from the Silverlight object and I spent hours just experimenting with getting the proportions to display properly in the image. I also spent a lot of time struggling with the colors because the javascript output of the color values doesn't line up with what WPF wants. You'll see in the code comments all of the conversions going on.

Once I had all of that worked out (and this represents hours of effort) there were still some issues. Luckily, Stefan Wick, who is the ultimate guru on this topic and finally has a blog - hoorah!, was able to set me straight (and trim some  of my code down significantly).

I hadn't thought much of this nor, apparently had anyone else, until someone recently emailed asking me how I did it so that he can use it as part of a solution in a competition. (I hope that the requirements of the competition don't say anything about original work!), so I thought I would blog the steps.

1) Convert the InkPresenter data to XAML . This does two things. FIrst it enables you to serialize it and pass it to a web service and secondly, it is the lowest reasonable common denominator for sharing between different objects. This javascript code comes from Gavin Gear.

This javascript code reads through the StrokeCollection property of an InkPresenter and builds up a string of xml that is the XAML representation of the StrokeCollection. You could also take the resulting string and pass it to CreateFromXAML to recreate the Silverlight StrokeCollection object.

  if (strokeCollection.Count>0)
  { 
   var xaml = "<StrokeCollection>";
   if (strokeCollection != null)
   {
    for (var i = 0; i < strokeCollection.Count; i++)
    {
        var stroke = strokeCollection.GetItem(i);
        if (stroke.Name>"")
          xaml += "<Stroke Name='" + stroke.Name + "'><Stroke.DrawingAttributes>";
        else
          xaml += "<Stroke><Stroke.DrawingAttributes>";
        xaml += "<DrawingAttributes ";
        xaml += "Color='" + BrowserColorConverter(stroke.DrawingAttributes.Color) + "' ";
        xaml += "OutlineColor='" + convertColorToHexString(stroke.DrawingAttributes.OutlineColor) + "' ";
        xaml += "Width='" + stroke.DrawingAttributes.Width + "' ";
        xaml += "Height='" + stroke.DrawingAttributes.Height + "' ";
        xaml += "/></Stroke.DrawingAttributes>";
        xaml += "<Stroke.StylusPoints>";
        for (var j = 0; j < stroke.StylusPoints.Count; j++)
        {
            var stylusPoint = stroke.StylusPoints.GetItem(j);
            xaml += "<StylusPoint X='" + roundToTwoDecimalPlaces(stylusPoint.X) + "' Y='" +
              roundToTwoDecimalPlaces(stylusPoint.Y) + "' />";
        }
        xaml += "</Stroke.StylusPoints></Stroke>";
    }
  }
  xaml += "</StrokeCollection>"; 

2) Pass this string to a web service method that will do the following to it

3) Create a WPF InkObject from the XAML. Now that I'm in the web service, I can use .NET code. Phew. Note that I did this before I knew how to use LINQ to XML so I struggled through XPath to get this. Watch for an upcoming MSDN Mag article that will have updated code.

      private static StrokeCollection InkObjectfromXAML(XmlNode StrokeColl)
      {
          StrokeCollection objStrokes = new StrokeCollection();
          XmlNodeList strokeElements =
          StrokeColl.SelectNodes("Stroke");
          foreach (XmlNode strokeNodeElement in strokeElements)
          { 

              //step 1: create a new stroke from the stylus point elements in the XAML
              XmlNodeList stylusPointElements =
                  strokeNodeElement.SelectNodes("./Stroke.StylusPoints/StylusPoint");
              XmlNode drawAttribs = strokeNodeElement.SelectSingleNode("./Stroke.DrawingAttributes");
              //points node is sent to GetStrokePOints method to convert to a type 
              //that can be used by the new stroke
              System.Windows.Input.StylusPointCollection strokeData = GetStrokePoints(stylusPointElements);
              Stroke newstroke = new Stroke(strokeData); 

              //step 2: grab color metadata about stroke from the xaml
              //color is a hex value
              //the stroke object requires a System.Windows.Media.Color type
              //following code performs the conversion
              string mycolor = drawAttribs.FirstChild.Attributes["Color"].Value;
              System.Drawing.Color drwColor = System.Drawing.ColorTranslator.FromHtml(mycolor);
              //build the new color from the a,r,g,b values of the drawing.color
              System.Windows.Media.Color newColor = new System.Windows.Media.Color();
              newColor.A = drwColor.A;
              newColor.R = drwColor.R;
              newColor.G = drwColor.G;
              newColor.B = drwColor.B;
              //Step 3: extract width data from xaml, convert to int
              int myIntWidth;
              bool parseSuccess = int.TryParse(drawAttribs.FirstChild.Attributes["Width"].Value, 
               out myIntWidth);
              //Step 4: apply width & color to stroke
              //some really wierd unexplainable transformations that I had to get
              // around until the final images looked right.

              if (myIntWidth == 3)
                  newstroke.DrawingAttributes.Width = 1.5;
              else
                  newstroke.DrawingAttributes.Width = 2;
              newstroke.DrawingAttributes.Color = newColor;
              //Step 5: add stroke to the stroke collection
              objStrokes.Add(newstroke);
          }
          return objStrokes;
      } 

    //This method (called from the method above, is an abstraciton of some sample code from Microsoft
     private static System.Windows.Input.StylusPointCollection GetStrokePoints(XmlNodeList stylusPointElements)
        {

            
System.Windows.Input.StylusPointCollection pointData = new System.Windows.Input.StylusPointCollection();

            //The object requires HiMetric point values, create multiplier for conversion
            double pixelToHimetricMultiplier = (2540d / 96d) / 100;

            foreach (XmlNode stylusPointElement in stylusPointElements)
            {
                string xStr = stylusPointElement.Attributes["X"].Value;
                string yStr = stylusPointElement.Attributes["Y"].Value;

                //x and y are in pixels, we need to multiply them to get them into HIMETRIC
                //space, which is what the InkAnalyzerBase expects
                int xInHimetric = (int)(System.Convert.ToDouble(xStr) * pixelToHimetricMultiplier);
                int yInHimetric = (int)(System.Convert.ToDouble(yStr) * pixelToHimetricMultiplier);
                pointData.Add(new System.Windows.Input.StylusPoint(xInHimetric, yInHimetric));
            }

            return pointData;
        }

Now we have an inkObject that WPF will be happy with!

4) Convert WPF Ink to PNG format bytes This is with a BIG thanks to Stefan - we need to start a separate thread to do the conversion from WPF Ink ojbect to PNG. That conversion happens inside the thread. Also, thank to his deep understanding of the ink object, Stefan was able to accomplish in a much smaller amount of code what I had achieved in about 3 times as much code. I was definitely doing loop-dee-loops, but it was the best I could come up at the time.

Note that I am not saving to an actual file here, just creating the bytes because my goal was to store that in a database.

   private static void ThreadforConverttoPNG()
   {
    Thread t = new Thread(new ThreadStart(ConverttoPNG));
    t.SetApartmentState(ApartmentState.STA);  

    // Start ThreadProc.  Note that on a uniprocessor, the new 
    // thread does not get any processor time until the main thread 
    // is preempted or yields.  Uncomment the Thread.Sleep that 
    // follows t.Start() to see the difference.
    t.Start();
   }
   private static void ConverttoPNG()
   {
    //I had originally achieved this with a LOT more code. This is Stefan's more trimmed down method

    //create temporary InkCanvas
    InkCanvas inkCanvas = new InkCanvas();
    inkCanvas.Strokes = strokes;
    //render InkCanvas to a RenderBitmapTarget
    Rect rect = inkCanvas.Strokes.GetBounds();
    RenderTargetBitmap rtb = new RenderTargetBitmap((int)rect.Right, 
      (int)rect.Bottom, 96d, 96d, System.Windows.Media.PixelFormats.Default);
    rtb.Render(inkCanvas);
    //endcode as PNG
    BitmapEncoder pngEncoder = new PngBitmapEncoder();
    pngEncoder.Frames.Add(BitmapFrame.Create(rtb)); 

    //save to memory stream
    System.IO.MemoryStream ms = new System.IO.MemoryStream(); 

    pngEncoder.Save(ms);
    ms.Close();
    strokeBytes= ms.ToArray(); 
   } 

5) My next step was actually to store the bytes into a database. I wasn't actually saving out to a file. But to do that is simple. System.IO.File lets you create a new file on the fly from a byte array.

   System.IO.File.WriteAllBytes("C:\\myfile.png",strokeBytes); 

So, those are all of the pieces from converting a silverlight inkpresenter image to an image file.

I thought I would give the Xceed WPF DataGrid (it's free!) a whirl with Entity Framework databinding. So far I have only explored the basics - no anonymous types or sprocs and I am only working with a READ-ONLY scenario. (I did notice something in their docs about being able to modify and delete but not add when binding to LINQ to SQL queries.) I have, however, at least used a multi-level object graph.

Here's what I've come up with.

Add the grid

1. Drop an Xceed WPF DataGrid on a WPF Window

Preparing the data

1. I created an EDM from AdventureWOrksLT. The namespace is awModel and the EntityContainer is awEntities. I also fixed up some naming, plurazing the EntitySets and the navigation properties that point to collections. (This is my standard routine when creating EDMs)
2. In the Loaded event for the WIndow, add the following

Dim aw = New awModel.awEntities
Me.DataGridControl1.ItemsSource = From ord In aw.SalesOrderHeaders.Include("Customer") _
                                  Select ord 

Setting up the grid for Databinding

If you've never used WPF, or done databinding in WPF, there are definitely a lot of new things to learn! The easiest thing to do for now is just copy and paste all of this XAML below.

Here's what the entire XAML looks like.

<Window x:Class="Window2"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    Title="Window2" Height="300" Width="300" Name="Window1" xmlns:xcdg="http://schemas.xceed.com/wpf/xaml/datagrid"
        xmlns:local="clr-namespace:WpfApplication1.awModel">
    <Grid>
    <Grid.Resources>
      <DataTemplate DataType="{x:Type local:SalesOrderHeader}">
        <TextBlock Text="{Binding TotalDue}"/>
      </DataTemplate>

      <DataTemplate DataType="{x:Type local:Customer}">
        <TextBlock Text="{Binding Customer.CompanyName}"/>
      </DataTemplate>

    </Grid.Resources>

    <xcdg:DataGridControl Margin="10,9,4,0" Name="DataGridControl1" ItemsSource="{Binding}" >
      <xcdg:DataGridControl.Columns>

        <xcdg:Column FieldName="Customer.CompanyName" Title="Company" />
        <xcdg:Column FieldName="TotalDue" Title="Total Due"/>
      </xcdg:DataGridControl.Columns>

    </xcdg:DataGridControl>

  </Grid>
</Window>

The xml namespace tag xdcg get's added automatically when you drop the DataGrid on the window.

The xml namespace tag "local" is something I added. It's necessary for subsequent references to classes from my EDM. Intellisense will help you pick the right namespace (your app and your model name) if you start with clr-namespace:.

In my query, I queried for SalesOrderHeaders plus their customer EntityRefs. In the DataTemplates, you can see that I'm referencing the actual object model types and then binding to the property from the SalesOrderHeader that is returned in my query.  "TotalDue" gets me "SalesOrderHeader.TotalDue" and "Customer.CompanyName" gets me "SalesOrderHeader.Customer.CompanyName". The DataTemplate provides binding to the data source (defined in the ItemsSource setting in the code above). Then the DataGrid column tie back to the bindings by way of the FieldName property.

Run the app

Note that I did not sort in my query.  I wanted to demonstrate the grid's built in sorting , but for some reason it's not working in this scenario (stay tuned...I'll get to the bottom of that).

The automatic grouping does work, though. AdventureWorksLT is not great for seeing this. Only Thrifty Parts and Sales happens to have more than one order.

Note: This blog post explains theory as well as steps to implementing the stored proc. If you read my blog regularly,  you are used to this.

As with the previous CTP Wizard, the Wizard in the new EF Tools (CTP2) will create functions in the Store Layer of the model to represent stored procedures in the database that the wizard is building a model from.

And as with the previous CTP, that's all you get. It doesn't implement it all the way through to the conceptual layer to be used in your applications. The documentation for the previous CTP showed how to complete DML stored procedures (Insert, Update & Delete) but not SELECT stored procedures that return data. So, I wrote a blog post in September that describes how to implement a SELECT stored procedure in EDM.

With the new tools, the Insert/Update/Delete functions are very simple to create now. Which is great. Select stored procedures with results that match an entity is also a breeze (see Guy Burstein's blog post that walks through these).

But as of yet, there is no help for the Select stored procs that return miscellaneous results.

Therefore I wanted to update the instructions since you can do a few more things with the wizard for this. But you'll still need to work with the raw XML of the EDMX file.

Let's start with a simple example.

There is a stored procedure that I added to the AdventureWorksLT db that takes a year as a parameter and returns a list of Company Names with their total sales for the year. The resulting schema contains TotalSales, CustomerID and CompanyName. This result set doesn't line up with any tables/views in the db or entities in my conceptual layer.

When the EDM is generated from the database the stored proc is realized in the StoreLayer. You can see this in two ways.

The Model Browser

Or in the raw xml of the EDMX file, in the Storage Section:

      <Function Name="annualCustomerSales" Aggregate="false" 
                BuiltIn="false" NiladicFunction="false" 
                IsComposable="false" 
                ParameterTypeSemantics="AllowImplicitConversion" 
                Schema="dbo">
           <Parameter Name="fiscalyear" Type="char" Mode="In" />
       </Function>

You can see that it does not determine the output parameters, which would be a daunting task to build a parser for.

So you need to create the following:

1) An entity that matches the schema of the results (TotalSales, CustomerID, CompanyName)
2) A function in the conceptual layer that maps back to the function in the store layer (called an import function)
3) Every entity needs to map to something in the store layer, therefore you need a fake table in the store layer that matches the entity in the conceptual layer .
4) The entity in the store layer needs an entity set
5) Lastly, the entity in the conceptual layer needs to map back to the entity in the store layer.

#3 through #5 are extraneous to what we are trying to do, but EDM has rules that need to be followed, so we need to throw all that extra stuff in there. It would be great if we could somehow just map the function to the entity - or if we could just use the function and return an anonymous type. But we can't. So, let's just move forward.

This seems like a lot of steps, but once you do it, it will make sense.

Step 1) You can create entities using the designer, so this isn't so bad. Create a new entity and add the properties that are returned in the results. Here's what mine looks like:

The customerID is flagged as a Key for the entity. An EntitySet will automatically be created. In my model it was named "result_annualCustomerSalesSet". Be sure to set the properties of the properties correctly (type, length, etc.) You can use properties of other entities in the model for clues as to what those values should be.

Save the model.

Step 2) Create an import function. In the Model Browser (right click in the designer and choose Model Browser if it isn't visible), locate the stored procedure inside the Store layer (see screenshot above for a reminder). Right click on the stored proc and choose Create Function Import. You'll get a screen where you need to fill out the Function Import Name and select an entity from a drop down list.

After you hit okay, the EDM will be updated and you will see the function import in the Model Browser.

Note that if the results matched an existing entity (a mapped entity, that is) you would be finished here.

Step 3) Create an Entity in the Store Layer that the result_annualCustomerSales entity will get mapped to. I cheat a little here by copying and pasting the Entity from the conceptual layer into the store layer then make appropriate edits.

In the conceptual layer it looks like this:

          <EntityType Name="result_annualCustomerSales">
              <Key>
                  <PropertyRef Name="CustomerID" />
              </Key>
              <Property Name="CustomerID" Type="Int32" Nullable="false" />
              <Property Name="TotalSales" Type="Decimal" Nullable="true" 

                 Precision="19" Scale="4"   />
              <Property Name="CompanyName" Type="String" Nullable="true" 

                 MaxLength="128" Unicode="true" FixedLength="false" />
          </EntityType>

After pasting this into the store layer section with the other EntityType elements, I edit so that the 
store entity looks like this

          <EntityType Name="result_annualCustomerSales">
              <Key>
                  <PropertyRef Name="CustomerID" />
              </Key>
              <Property Name="CustomerID" Type="int" Nullable="false" />
              <Property Name="TotalSales" Type="money" Nullable="true" />
              <Property Name="CompanyName" Type="nvarchar" Nullable="true" />
          </EntityType>

If you need help determining what the property properties (eg type) should be, look at some of the other Entity Types in the store model for clues. I use the same trick with the conceptual entity to figure out precision and scale for the TotalSales property.

Step 4) You also need the EntitySet in the store layer. Copy and paste that, too.

Entity Set in Conceptual Layer
        <EntitySet Name="result_annualCustomerSalesSet" 
             EntityType="AdventureWorksLTModel.result_annualCustomerSales" />
 
Entity Set in Store Layer        
        <EntitySet Name="result_annualCustomerSalesSet" 
                   EntityType="AdventureWorksLTModel.Store.result_annualCustomerSales" 
                   Schema="dbo"/>

Almost done!

Step 5) Map the Conceptual Entity to the Store Entity.

If the Mapping Details are not showing, click anywhere in the designer and choose Mapping Details. By default it will show up in the same area as the error list, etc. in the IDE.

In the designer, select the new Entity. You will see a little info about it in the Mapping Details window. Drop down the list for "Add a Table or View" and choose the table definition you created in the store layer.

All matching properties will automatically be mapped.

FINALLY YOU ARE DONE!

Now let's go to the code and see the sproc in action!

    Dim aw As New AdventureWorksLTModel.AdventureWorksLTEntities
    Dim qsales = aw.annualCustomerSales("2004")
    For Each cust In qsales
      Console.WriteLine(String.Format("{0}: Sales= {1:C}", cust.CompanyName, cust.TotalSales))
    Next

Here's a bit of the output

Aerobic Exercise Company: Sales= $2,361.64
Bulk Discount Store: Sales= $98,138.21
Eastside Department Store: Sales= $92,663.56
Coalition Bike Company: Sales= $2,669.32
Instruments and Parts Company: Sales= $70,698.99
Many Bikes Store: Sales= $81,834.98
Closest Bicycle Store: Sales= $39,531.61
West Side Mart: Sales= $87.09
Riding Cycles: Sales= $86,222.81
Futuristic Bikes: Sales= $272.65
Vigorous Sports Store: Sales= $1,170.54
Thrilling Bike Tours: Sales= $15,275.20
Trailblazing Sports: Sales= $45,992.37
Paints and Solvents Company: Sales= $14,017.91
Channel Outlet: Sales= $608.18
Thrifty Parts and Sales: Sales= $1,379.17
Engineered Bike Systems: Sales= $3,754.97
Essential Bike Works: Sales= $45.20

One of the new features in Entity Framework Beta3 is the ability to embed the csdl, msl and ssdl schema files into the assembly which contains the model.

This is useful in scenarios where, like in a few of my solutions, you want to have a separate project to host the model and then use that project in various applications. In that case you need to make a reference to the dll with the model and the application using the model will need to know where to find the schema files through the metadata property of the special connection string.

At design time, you work with the EDMX file in the designer and you can also manually edit it's XML if and when necessary. When the project is compiled, the 3 key sections of the EDMX file are spit out to the separate schema files (*.csdl, *.mls, *.ssdl) which are used by the EF APIs.

With the schema files embedded into the dll, we don't have to keep track of the files.

Here's how to pull it off. Note that there is a workaround in these instructions for a known bug.

My project that hosts the model is called AdventureWorksModel. In it are the model as well as some additional code that extends the functionality of the code generated classes.

By default, the EDMX file's Build Action property is set to "EntityDeploy". This is important to note in case you change it for some reason. It does need to be "EntityDeploy". (I learned that the hard way, which is why I make a point of mentioning it.

Open the EDMX file in the design window and then you will see a new property in the property window called Metadata Artifact Processing. The default of this is "Copy to Output".

To get around a current known bug, build the project with the property set to "Copy to Output". Then change the Metadata Artifact Processing property to "Embed in Output Assembly". Eventually you won't need to build with Copy to Output first.

Build the project. After building, if you use a tool like Reflector, you can see the files have been compiled into the dll as in the screenshot below.

If you check the connection string in the app.config for the project you will see that there is a reference to the projects resources in the metadata properties, rather than to a file path.

<add name="Entities"
connectionString="metadata=res://*/AWModel.csdl|res://*/AWModel.ssdl|res://*/AWModel.msl;
provider=System.Data.SqlClient;provider connection string=&quot;
Data Source=127.0.0.1;Initial Catalog=AdventureWorksLT;Integrated Security=True;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />

Now you can reference the project or the dll directly, I have been successful with both methods.

The connection string in the model's project needs to get copied into the config file of the app.

Then you should be good to go.

I did deploy the solution copying only the exe, the dll for the model and the app.config for the exe and it worked like a charm. No ssdl, csdl or msl files came along for the ride.

Thanks to Mike Kaufman and some others on the team for help as I tried to figure out how to get through this.

An arhitectural note... embedding the files will be useful in a number of scenarios. However there will also be many scenarios in which you do not want the schema files compiled into the dll so that you can replace them easily as needed without having to redeploy a dll. Granted with .NET, deploying a dll can be just as easy as deploying a new xml file, but I know there will be cases where I will prefer the loose coupling.

For those cases, I'll want to work with the EntityDeploy msbuild task directly so that I can define the output location of the files that are built and easily maintain a common location as I did in this blog post which showed how to do it in Beta 2 (though that particular solution is no longer applicable). I'll be fiddling with the msbuild task shortly.

I've done this demo many times in conference and user group sessions. Someone on the ADO.NET forums asked how to do it and I thought I would just do a quick tutorial with screenshots.

What this Tutorial does is demonstrate how to create and use a data source from a particular entity in the model. I'm just doing simple drag and drop and no filtering or anything here with the goal of just a quick basic walkthrough for getting started.

Start by creating a Windows Application.

Add an Entity Data Model.

Select Data from the menu and choose Add New Data Source

In the first page of the wizard, choose Object as your Data Source Type.

The next screen of the wizard will show the namespaces in the current solution. Open up the namespace for the application

then choose the entity which you want to use as a Data Source. I will pick customer.

Then you can Finish the Wizard.

Now to get easy access to the datasource, go back to the menu and choose Show Data Sources from the Data menu.

The DataSources Window will be placed in your IDE in it's default location. Mine docks with the windows on the left.

When it's not pinned it gets tucked away with the others. You can undock it and put it wherever you want.

I can now drag and drop the customer data source onto the windows form to get the automatic DataGridView and Navigation toolbar (this is normal behavior for DataSource and not specific to Entity Framework).

There are a few more steps to actually getting user interaction with this. You need to populate the Binding Source and if you want to edit, you'll need to add a little code to the save button on the toolbar.

You'll need to enable the BindingNavigatorSaveButton (just click the save icon on the navigator toolbar and change it's Enabled property to True). Additionally, you'll probably want to format the grid which you can do easily from it's SmartTag and more thoroughly through the properties window.

Here's what the code behind looks like in my form enabling me to view, add, delete, edit and save data.

Imports WindowsApplication1.AdventureWorksLTModel
Public Class Form1
  Private aw As AdventureWorksLTEntities
  Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    aw = New AdventureWorksLTEntities
    CustomerBindingSource.DataSource = aw.Customer.OrderBy(Function(cust) cust.CompanyName)
  End Sub

  Private Sub CustomerBindingNavigatorSaveItem_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles CustomerBindingNavigatorSaveItem.Click
    aw.SaveChanges()
  End Sub
End Class

Note that because the AdventureWorks Customer table has the following constraints: ModifiedDate must be a valid date value and the PasswordHash and PasswordSalt fields cannot be Null, I have extended the entity class to take care of these things when SaveChanges is called.

In a recent ADO.NET forum post, someone asked how to do read-only views of data. There were two responses - use a QueryView and use a Defining Query.

What's the difference?

A Query view is an Entity SQL Query that exists right in your mapping layer. It allows you to solve two problems. THe first is creating complex queries that you don't want your coders to have to worry about building. You can just build the query in the mapping layer, expose the resulting entity in the conceptual layer and the classes that the developers code & query against will just be there for them to use.

The second benefit is that you can use this to limit access to particular data from the database. For example, if you have a table which has GUIDs or hashed passwords in it, you may not want those to ever be surfaced in the application. You can "filter those out", by creating a QueryView. FWIW, you could also do this by just deleting the columns in the store layer definition.

The QueryVIew then queries against the storage schema, not directly against your database, and then the results are output to an entity in your conceptual layer. So in effect, it is a different way of mapping between your store layer and your conceptual layer.

The QueryView is part of the EntitySet mapping and looks like this:

  <EntitySetMapping Name="Categories">
      <QueryView>
        SELECT VALUE Microsoft.CDP.Samples.Northwind.Category(C.CategoryID, C.CategoryName, C.Description)
          FROM dbo.Categories as C
      </QueryView>
    </EntitySetMapping>

This simple query (note the syntax; it's an Entity SQL query) is replacing the mapping for Categories. It gets 3 columns from the Category entity in the store model (dbo) and maps the results to the conceptual entity, Category. Remember that I have created a read-only view, so you might not want to use this to just filter out columns if you still need to do updates and want to do them using the mappings (vs. using sprocs to do the updates).

You can also invent new queries and new entities to go along with them.

Defining Query

I love that MIke Pizzo refers to Defining Query as "the ultimate escape hatch".

Defining Queries are defined in the storage layer. They are really nothing more than database views - directly against the database. So you have a really complicated query that you can't describe with LINQ or with Entity SQL but you can describe it in TSQL, but and the dba is not around, unable or unwilling (for a variety fo very good reasons) to add a new view into the database for you, you can just create it in the storage layer. In fact, when you run the EDM Wizard against a database and it finds views, the wizard represents these views in the store layer as Defining Queries.

There are two parts to a Defining Query.

The query itself which is an EntitySet in the store model and an Entity in the store model that describes/defines the result set.

The rest of the model then treats the query as it would any other table. You have entities in your conceptual layer and they map to the entity that describes the result set of the Defining Query.

Here's what one looks like.

<EntitySet Name="custview" EntityType="AdventureWorksLTModel.Store.custview">
  <DefiningQuery>SELECT [custview].[CustomerID] AS [CustomerID],
   [custview].[FirstName] AS [FirstName],
   [custview].[LastName] AS [LastName],
   [custview].[CompanyName] AS [CompanyName]
   FROM [dbo].[custview] AS [custview]
  </DefiningQuery>
</EntitySet>

And the entity that you will map to

<EntityType Name="custview">
  <Key>
   <PropertyRef Name="CustomerID" />
   <PropertyRef Name="FirstName" />
   <PropertyRef Name="LastName" />
  </Key>
  <Property Name="CustomerID" Type="int" Nullable="false" StoreGeneratedPattern="identity" />
  <Property Name="FirstName" Type="nvarchar" Nullable="false" MaxLength="50" />
  <Property Name="LastName" Type="nvarchar" Nullable="false" MaxLength="50" />
  <Property Name="CompanyName" Type="nvarchar" MaxLength="128" />
</EntityType>

As I said above, you can then have an entity in your conceptual layer that just maps to this entity and nobody will ever know the difference. Which beg the reminder that this is read-only!

With the current CTP of the ENtity Data Model Tools, there is no way to automatically update or refresh the EDM when a change is made to the data store.

One of the beauties of the EDM is that fixing up the EDM in these cases is not only easy, but it doesn't need to have any impact on your application, because you can leave the conceptual layer which you program against in tact.

Here's an example of a minor change (inspired by a question in the forums) in the database and what you need to do today (I keep saying that because I don't know what the next increments of the tools will bring) to fix up your model.

Scenario: DBA changed fname and lname columns in the Customer table to firstname and lastname.

In a non-EDM application this could be dealt with in a few places, depending on how your db and app are architected:

• Modify any stored procs and views that spit out those fields so that they now know how to grab firstname and lastname but continue to surface them as fname and lname.
• Modify the data layer of your application (and redeploy it) so that it now is aware of firstname and lastname. Modify your business layer so that it knows that the properties have to look for firstname and lastname.
• If your app doesn't use these layers (hopefully that's a big "if"), then you need to make those changes even closer to the UI.

In an EDM here's how you would solve the problem.

1. Change the column name in the storage schema.
2. Change the column name in the mapping schema for the two mappings that link those fields.

Step by step

Open up the EDMX in the XML Editor. (Right click on EDMX in the solution explorer, choose open with, then XML Editor).

You can do all of this in the raw xml, which to me is easy. THe current designer does let you change the mappings. SO I will show how to do #1 in the raw xml then #2 using the designer.

Locate the node for the Storage Model. You can find that quickly by searching for it's comment "SSDL Content" or it's name "StorageModels".

IN there, locate the entity that represents the table with the change.

Edit the name of the entity.

Close the EDMX and re-open it in the designer.

Another part of what makes EDM so great is that when I first created this model, I didn't like "fname" and "lname" and changed them in my conceptual layer to FIrstName and LastName. At that point, they were properly mapped to the fname and lname columns and all was well.  This is what the mapping looked like before I started making the changes. YOu can see that my db column was called "fname" and my property in my conceptual layer was "FirstName".

Now that the dba decided to change the column names in the database as well, I just need to remap my FirstName and LastName properties to the newly named database columns.

Select the Customer Entity and then open the Entity Mapping Details. (If you don't see that page, right click on the model (in the white space) and select SHow Entity Mapping Details.)

The mapping details shows  you the columns from the storage entity first (e.g. the database table) and then on the right which property in your conceptual entity that column is mapped to.

You'll see that the columns fname and lname are gone from the mapping and it is now showing you the FirstName and LastName from the store schema, but they aren't mapped to any properties in your conceptual entity.

Just drop down the value/property list and it you will see all of the properties in the entity that this is mapped to.

That's it. And the impact with respect to deployment is that the new versions of the xml files that describe the schema need to be deployed to whatever tier the data layer that needs to know how to do the mappings. The client apps most likely just have the classes that were derived from the conceptual layer and since we didn't change that part, they don't need to know anything and can go on their merry way.

I had to figure this out, so here are a billion screenshots so anyone who wants to do this will have an easier time of it!

One of the niceties about being able to customize your conceptual models is not having to bother the dba while they are doing important things like indexing tables and optimizing the database. If you find you have some tables that came into your entity data model that have primary/foreign key relationships that were never defined in the database, then the wizard won't have built associations for you.

Imagine I have two entities, client and title. A client has many titles (kinda like a publisher but I used a different db for my example, so just live with "client".) The title entity has a property called clientid. I want to build an association between the two

Right click in the model (white area, not on an entity) and you will get a context menu. Select Add, then As