I stole the little EF on Rails quip from Roger Jennings who asked on his blog “what the heck happened to Jasper?” (my words, not his)

Here’s an overview article on Jasper that I wrote a few months ago.

Andy Conrad, who is a PM on that replied that it’s still around and they have an internal revision of the original prototype, but that they need to see more interest in it before really making an investment in it. (Again, my words, not his.)

Check out Roger’s post and Andy’s comment. It’s a shame that the best place to learn things like this is in the comments of 3rd party blogger posts, rather than directly in the team blogs, but iwth the rapid pace that everyone’s moving at these days trying to get Orcas out the door, trying to get Entity Framework and Astoria wrapped up, I’ll take what I can get!

So if you’re interested, let them know. A good place is comments on Andy’s blog or in the Jasper forums.

Roger’s post are so chock full of information, it’s easy to miss things on the first read or two but they are alwasy worth returning to, especially as he updates them to keep them accurate.

In a recent post, he pointed to an “article” – 38 pages is quite long for an article – which is part of the WROX BLOX series that you can download for $3.99 Leveraging LINQ in ASP.NET 3.5 projects. Since I am doing a session next week at DevConnections on ASP.NET Databinding with LINQ, I think I’m going to grab this to help enforce or certainly add to what I already have in my head, my demos and my deck! The session is only 75 minutes long, so I guess I can’t just stand up in front of the room and read it out loud.  (just kidding!)

Therefore, back to work!

You can find Roger’s mention of it in this post, which itself is probably a good 38 pages as well.

Someone asked a common question in the forums today about being able to update an EDM when the datastore has changed.

Noam Ben-Ami, who is on the team working on the tools, says this:

We are hard at work on this feature right now and hopefully we’ll be able to get it into CTP2 of the designer.

Along with support for stored procedures, it is our highest priority.

So three things to get out of this:

1. There will (or will probably be…) a CTP2 of the designer. That indicates to me they still have a lot of ideas that they want to implement and are still working out which is why that doesn’t say “Beta”.
2. There is going to be one (or more?) ways to support db updates in the desiger. I am really curious if we’ll be able to add just “drop” new tables in without actually updating.
3. Designer support for sprocs is coming. Noam and I talked about this a while back and he described what I thought was a great idea for implementing this. I’m looking to see how that idea evolves.

In the meantime, I wrote a blog post this morning about how to apply db updates using the current version of the designer.

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.