There was a problem with the Data Builder 4.1 included in the setup of the Build 16 for 4.1 – it wasn’t the correct binary…  This is what happens when things get rushed.  Normally a full change of a build number takes about 12 hours of testing to recertify.  But because people had grabbed the 15 test setup we had to bump the version and didn’t get a chance to fully retest it – just no time.

Data Builder Only

So those with binaries for 4.1 can download the 4.1 Data Builder Build 17.  It is just a ZIP file with the correct Data Builder, and to avoid confusion I bumped it’s number to Build 17.  It was the one that was supposed to be included.  I am not going to redo an entire setup for that one file.  It would require days of testing, or the possibility of introducing yet another subtle error.  So just unzip the exe into your install directory and overwrite the 4.1 Build 16 included.

Ok, hopefully this blog post will explain the most common upgrade paths for all existing VistaDB 3.x and 4.0 customers.  There are a lot of edge cases around people who bought something from a reseller and then upgraded to another thing, only to downgrade later, etc.  I am sure there are a few that I missed.  It is not a conspiracy or a plot against you, just an edge case.

How to purchase upgrades

Login to your account

Click the Upgrades link to see available upgrades and discounts.

Upgrades SKUs

There are a few SKUs around the upgrade path.  There is only one new SKU for purchase. 

New Purchases

The VistaDB 4.1 Binary/Source/Addons version, and yes the price is $1,499. 

The SKU includes the binary version of 4.1, 4.1 engine source code, ASP.Net membership pak, Data Migration Wizard Pak, and the DBA Sample Tool Pak.  That is an $8,500 value.

Minor Upgrade

Upgrading from 4.0 to 4.1 you have two choices:  Binary Only, Binary + Source

The binary only upgrade is just the same setup and Visual Studio tools, Data Builder, etc just like it has always been.  There is no source, no Add on tools, etc.  The upgrade price is a minor upgrade fee $179.40 (40% off the $299 list price).

The binary and source upgrade includes the VistaDB 4.1 database engine source, and the add on tools.  The upgrade price is $899.40 (40% off the $1,499 price).

Major Upgrade

Upgrading from 3.x to 4.1 you have only one choice:  Binary, Source and Tools Upgrade

Upgrading from 3.x to 4.1 entails purchasing the full 4.1 product SKU listed above.  There is a discount in your account for being an existing customer.  See the upgrades panel for the details.

Upgrade Paths

There are quite a few paths to upgrades, this is not an exhaustive list.  Just the common ones.

• VistaDB 4.0 – Bought in last 30 days – You get 4.1 Binary for free, you may purchase the source and add ons bundle
• VistaDB 4.0 – Subscription Valid – You get 4.1 Binary for free, you may purchase the source and add ons bundle
• VistaDB 4.0 – No / Expired Subscription – You must purchase 4.0 either the binary only, or the source and add ons bundle
• VistaDB 3.x – You must purchase the 4.0 Binary with Source and Tools

Multiple Upgrades in your Account?

Depending upon what SKUs you have purchased in the past you may have more than one price and upgrade package in your upgrade panel.  Obviously choose the one that makes the best sense for you.  Also check your serial number panel to make sure you didn’t already receive the upgrade as a part of some earlier purchase!

No sob stories please

Sorry, please don’t bother to contact me with offers of $100 because we are closing or can you get it for free because you have a sob story.  Honestly my sympathy meter has run out.  This is about me trying to recoup costs that I have incurred building and supporting VistaDB, not about charity.

Upgrades are in accounts

As of 7 pm eastern daylight time all upgrades that are automatic are now in users accounts.  Check your account to see what you have. 

Best of Luck

Best of luck to each of your in your future endeavors.  I still don’t know for certain what the future holds, but I am sure it will be interesting. 

Ok, VistaDB 4.1 has been released as Build 16.  We had posted a Build 15 last night for testing, but it was never intended to go live.  So we had to bump the build number today to the final build number of 16.

There are a number of changes to this release, this post is about release notes.  Upgrade paths will be in a separate post (in general login to your account and look at the Upgrades panel to see what your options are).

Release Notes

License system has been removed from the current codebase.  There is no need for licenses.licx files.  But the assembly has been bumped to 4.1 as a result of the change in the interfaces, you must rebind your application in most cases to use 4.1.

The .Net Provider name now reads "VistaDB 4 ADO.NET Provider for .Net".  This is to cover all VistaDB 4.x versions.  The binding is now 4.1.0.0, so make sure you update any provider factory versions in your applications.

Provider Name Change

The VistaDB Provider was named VistaDB.NET20 to indicate it was the .Net 2 version of the provider (back when we had a .Net 1.1 version).  But to better match the framework we have now changed it to System.Data.VistaDB. 

If you have strongly typed datasets you need to edit the xml using a text editor (the wizards do not allow this) to change the name in the Connections section from VistaDB.NET20 to System.Data.VistaDB.

Visual Studio 2010

Entire product is now built in Visual Studio 2010.  We still produce .Net 2 assemblies, but we are using VS 2010 as our main development IDE.

Obfuscation Change

We are no longer using the Obfuscation engine.  There were problems using the assembly under Mono, and with sporadic bugs in the product we could not get addressed by the vendor.  The obfuscation is no longer enabled by default.  We may use another one at some point in the future to compact the assembly, but for now it is full size.   This means slightly larger DLL and more RAM usage due to the namespaces.

Data Builder Changes

Data Builder is built against .Net 3.5 SP1 now because we have added LINQ query ability to the main form.  We have also removed the pulling of the RSS feed onto the main window (trying to remove all external dependencies in the code).

The load screen has changed to a list of the most recently used databases (double click to choose one).  You can change the number of recent item from the Tools | Options dialog.

LINQ Queries

There is a new item in the database tree view named LINQ.  These are for writing dynamic EF queries against your database.  Once you click the LINQ item you will see a new panel that shows a query partially constructed for you.  You must hit the Generate Model to build an EF model out of the current database.  Then you can write LINQ queries against it and run them in Data Builder. 

The idea here is to allow for rapid writing of LINQ queries against a database for quick tests.  You can write them in Visual Studio (to get intellisense) and then paste them in here as long as you name your data context object "context".

There is no intellisense support, but the resulting code can pretty much be copied and pasted into your C# code (there is no VB support, sorry).  The queries must be written against the temporary data context object named context.  If you use some other naming in your code then you must obviously update the name once you paste it into your code.

SQL Script Export

A lightweight SQL Script Export has been added to Data Builder to output schema to a SQL file.  It does not handle data, or stored procs, or anything else.  Just schema for the entire database.  It is not for use as an API, it is only for use internal to Data Builder.

Customer Experience Removed

The CEIP program has been removed.  It may be added back at a future date, but I wanted to make sure everything that talks externally is out of this version just in case.

Issues and Fixes

3891 - Data Builder Stored Proc list detail panel now shows the stored proc colorized rather than the black and white text box that was there previously.

3892 - Data Migration Wizard will migrate indexes and FK's with names containing [] symbols, but those are not valid.  We now rename them if they are present.  Any illegal character is replaced with an _.

3905 - Subquery with an aggregate function (like sum) would not auto convert to another type without a cast. This would result in a type unknown error when the projection is applied to the parent of the subquery.  In most cases we are now able to determine the target column type and auto convert the output.

3933 - Remove CLR Proc should ask about Force Unload - Attempting to unload a CLR Assembly that has references will fail until all references are removed.  You can force unload in DDA, but Data Builder never gives you this option.  Added to Data Builder if an unload fails, it will ask about force.  If force is used we recommend you pack the database after.

3934 - Attempting to remove a trigger results in an "UNKNOWN ERROR" dialog in Data Builder.  The trigger error was referencing an error code that had a typo in it, so it could not even load the error message and was failing.  Added correct error message, and the unload code works correctly now.

3942 - Data Builder did not correctly re-enable some of the menus after a pack operation, this has been fixed.  Some of the table menu items were enabled on initial launch when they should not have been, this has been fixed.

3940 - All samples were updated to not reference a licenses.licx file, and bind against the new version of the engine.

3936 - Saving a SQL Script that is readonly fails without an error.  Fixed to show the user the errors during saving of a script.

Summary

VistaDB 4.1 will likely be the last release from me.  It has been a fun trip, and hopefully the product can live on for many years at your company, and possibly with a new company as a product.

If you haven’t already tried out PowerPivot, or if you need a little help educating your IT department on SQL Server 2008 R2, Office 2010 and SharePoint 2010, check out these two reports from leading research firm, Gartner, Inc. These important notes highlight the value of PowerPivot and how IT should prepare for and position PowerPivot. They also offer best practices based on real-world customer scenarios and feedback for building business intelligence (BI) and performance management architecture.

Read the full Gartner reports here:

What is SQL Server PowerPivot?

Q&A: What are Customer Experiences with SQL Server PowerPivot

BI is a top priority for our customers who are looking for solutions to bring better business insights to employees making decisions every day. PowerPivot introduces Microsoft’s unique ‘managed self-service’ approach that gives companies and IT departments the ability to enable and empower their business users to create reports and conduct their own analysis while still maintaining all the insight and oversight of IT.  It’s this focus on users that differentiates Microsoft BI. Microsoft has long been tracking on a vision of bringing BI to more users and PowerPivot is another step in this direction to making BI truly pervasive. 

Another recent note by Gartner’s John Hagerty (formerly AMR) titled: “Microsoft SQL Server PowerPivot for Excel: Unleashing the Business User” discusses the critical need for companies to embrace the business user, enabling them to get important work done and freeing up IT for more strategic projects.

For more information, see our previous posts here and here. For a free trial, visit http://powerpivot.com/.

We just released NCover 3.4.12. It's a regular bugfix release but also includes an exciting reduction to NCover's memory usage when running applications that spawn lots of subprocesses. You can read all about the release in the release notes.

Download NCover 3.4.12

There is a new pane in the Data Builder for VistaDB 4.1.  The LINQ query pane is a very handy way to build and test LINQ queries against a simple Entity Framework model.  First understand that this pane is a work in progress.  It was designed to be an easy way to write LINQ queries (similar to LINQPad) for your VistaDB database.  The feature is of limited usefulness since the model cannot be customized at generation, or loaded from your own assembly.  But this is where we were with the feature when we had to ship, so here it is.  We have been using it a lot to help write LINQ queries, we hope you find it useful too.

The current implementation only supports C# (sorry VB guys, it was on the list).  You can right click on the Entity Model tree and Script the main query window with all your basic CRUD operations.

By default the dialog is grayed out until you generate a model.  The code shown will still be present once the model is generated.

Generate a Model

By default the dialog is grayed out until you generate a model.  The code shown will still be present once the model is generated.

Click the Generate Model button to have Data Builder call the EDMGEN tool and build a default model for the current database.  The default is to output one type per table.  This is the same as if you add a new model to your application and accept all the defaults.  If any errors occur during the generation of the model they will be output in the Log window at the bottom of the screen.

When to generate a model

Each time Data Builder loads you will have to regenerate a new model (they are not saved across application runs).  The models are kept loaded after the initial generation.  If you make schema or foreign key changes you should regenerate the model in order for the changes to be present in the model.

How to view the current model

The current model is displayed on the second tab (after a model has been generated).

 

Intellisense

Sorry, there is no intellisense in the current version for the LINQ queries.  It REALLY does help to have it, but there just was not time to get it implemented.  Use the Entity Model tab to see what the entity names look like.

Right Click for Scripting

Right clicking on an entity (the table name) will allow you to generate some default LINQ queries for the Query pane.

 

Each of the basic CRUD operations are demonstrated for the object.  Selecting them will put the script in the query window.

Putting queries into your code

Once you have a query working the way you want, you can copy and paste it into your code.  The only requirements are that you have a model that matches the default model, and that you have a data context of your model called context (or you can rename the internal context to be what your variable is named).

A best practice for this code might look something like this:

using( RealModel.ModelEntities context = new RealModel.ModelEntities(connection))
{
   var query = from i in context.OrderDetails select new { i.OrderID, i.Orders.OrderDate };
   // Your custom code to work with the LINQ results
}

Example LINQ Queries

The example script code will not all execute by default.  They are templates for you to edit.  For example the delete template doesn’t know what your criteria is for the delete.  It leaves a string in the place for you to change to your actual criteria.

The idea was to eventually give the user an interactive wizard type of experience to build up their LINQ queries against the model.  But what is there today is still useful.

Summary

The LINQ panel in Data Builder 4.1 is a very useful way to write LINQ queries against a default EF model quickly with VistaDB.  It is not feature complete, but is still useful enough for us to release it with this version.

We hope you find it useful for writing LINQ queries, and possibly to interest you enough to learn more about Entity Framework.

The final version of the Feature Builder Power Tool designed to work seamlessly with Visual Studio 2010 has been released to the gallery.

Download it from here. There are a number of videos up on channel9 that you can check out to understand what Feature Builder is exactly by clicking on this link.

A real world example of how an internal Microsoft team has taken advantage of Feature Builder can be seen in this video. It is basically a video talking about how the Feature Builder was used in the latest Hands On Lab for Windows Workflow 4.

Nice!

The Entity Framework (EF) approach to concurrency is to mark one or more fields as ‘ConcurrencyMode=Fixed’. This can be done either directly in the edmx file or via the Entity Data Model Designer. The EF will then use the original value of the field as a condition on any update. If the value has changed the update will fail and a concurrency exception will be thrown.

This works well for a long running context and so we been happy to use it for thick-client implementations of Naked Objects. However the MVC implementation uses a context that only survives for the duration of the web transaction. The EF concurrency check will then only check for changes during that transaction – this is of some value but it doesn’t catch the situation where an object is opened for edit (in one transaction) changes are made and then submitted (in another transaction).

As a slight aside here we need to be aware of how Naked Objects MVC handles the persistence of an object from one transaction to the next. Naked Objects passes an identity token (in the URL) which is then used to repopulate objects from the data store. These are then used to make any changes or run any actions and then save changes to the store. This has the advantages that it is completely stateless – everything is in the Http Request – and cleanly decoupled from the object store implementation. It does though have the disadvantage of an additional object store query.

With EF an alternative would be to serialize all or some of the object state, recreate the entity and attach to the context. This is an option for the future but there’s quite a few hoops to jump through to get this working, especially in an object store independent way and within the constraints of the existing Naked Objects architecture. So for the moment simplicity and object store independence win the day.

So back to concurrency, it seems to be possible to set the token in the object and tell EF to accept it. Then when other changes are applied and the object saved the standard EF concurrency checking should kick in. This is obviously a good solution in that it’s a common concurrency mechanism across thick-client and MVC Naked Objects implementations. This would be easiest if we had adopted the reattaching EF mechanism but should be possible with our current object retrieval mechanism. However it once again means coupling ourselves to EF or building a lot of EF specific stuff into our generic object store API.

Finally then the simple approach we adopted is to save the concurrency token as a hidden field on object edits and views. For edit sessions the original value survives the length of the edit (which may involve intermediate posts). Then when the edited object is saved or an action is invoked we check against the current database value (which we’ve populated in the object anyway) and fail concurrency if it’s changed. This is simple, obvious what’s going on if you want to customize the application and fits in cleanly with our existing architecture. The only disadvantage is that we need a way of identifying the concurrency token fortunately Microsoft have provided a new attribute – ConcurrencyCheckAttribute – which we use. As this is also used in EF Code First to mark those properties that are “ConcurrencyMode=Fixed”  this is all that is necessary for Code First development. For Model and Database first the same fields marked “ConcurrencyMode=Fixed”  should also be annotated ‘ConcurrencyCheckRequired’.

If you use Visual Studio 2010 at all, stop what you are doing and install the Productivity Power Tools. It is extraordinarily easy to do so ( go to Tools->Extension Manager…, and search for “Productivity Power Tools” ):

There are a number of fantastic features in the Power Tool deliverable, but the one I want to point out is the new Solution Navigator. ( There have been a number of posts about these capabilities today, but I simply couldn’t help myself on this. I had to write about it! :) )

Solution Navigator: A Solution Explorer Replace

After you install the power tool and open your favorite solution, notice the new Solution Navigator tool window. The Solution Navigator gives a number of new features that you will quickly find invaluable. There are a number of little capabilities that you will find valuable, such as the ability to collapse all nodes in the navigator with one click of a button:

But this is just the start. You can also filter the Navigator by typing in a search string in the search toolbox as seen below:

In the example above, you’ll notice that I typed in “Main” in the search box, which highlights the “Main” method found in the Program class located in the Program.cs file in the OrderProcessor project. This exemplifies yet another fantastic addition, which is the ability to drill down into individual files to see the types found in those files. Nice!

You can do the same thing with Assembly references as well, which allows you to drill down into the assembly, getting at contained namespaces and types:

Another great touch is the ability to root the navigator to any arbitrary node. You do this by clicking the icon to the right of the node you want to root, or select the node and hit Ctrl-Right Arrow:

And here’s a shot of the Project node I selected above rooted in the navigator.

The great thing about this of course, is that it removes some of the noise in the UI when you are working in one particular area of your source base.

You’ll also notice the “All, Open, Unsaved, Edited” words below the solution. Below I’m showing you what happens to the navigator when I’ve got a three documents opened in the document well and want to filter the navigator so that I can quickly see what projects those files belong to:

I just can’t say enough how great this new capability is.

It has simply made my day! :)

Cameron

Chris Lovett has created another fantastic video that explains the ins and outs of manipulating the look and feel of DGML documents via the <Styles> markup capabilities that shipped with Visual Studio 2010 Ultimate.

Check it out here. Absolutely fantastic!

Microsoft has recently released a new Silverlight control called PivotViewer. This new control helps us to make better use of the growing amounts of information around us by visualizing thousands of things at once in a way that reveals the relationships which connect them. At the heart of the PivotViewer control are "Collections." They combine large groups of similar items, so we can begin viewing the relationships between individual pieces of information in a new way. By visualizing hidden patterns, PivotViewer enables users to discover and act on new insights.

The Business Intelligence engineering team have prototyped a new concept that couples the PivotViewer control with a utility that uses Reporting Services to automatically generate this type of collections. We showcased this concept at the BI conference in New Orleans with overwhelming support and interest from our community of BI enthusiasts and are making this demo available here to those that would like to evaluate it in their own sandbox environments. View the video here to find out more.

As you view and try this prototype, treat it like a "Concept Project". Although the Silverlight PivotViewer is now a final shipping product, the PivotViewer extension for SQL Server Reporting Services is just a preview, and isn’t a supported product or a feature of Microsoft Business Intelligence. As such, it may not work perfectly under all conditions. We look forward to your feedback and participation in this experiment and will continue working hard to bring cutting edge visualization technologies like PivotViewer to you as fast as possible. Enjoy!

To someone who has used emacs or a basic text editor for programming, jumping into Visual Studio 2010 can seem like going from a Cessna to a fighter jet.  That’s the experience I had last week starting out at Microsoft, and my goal with this post is to provide others taking the plunge into the Entity Framework – or considering taking the plunge – with a handy guide for what it takes to get up and running.  I hope that my fresh perspective will help you in a way that an experienced expert cannot.

The Essentials

The Entity Framework bridges the gap between how developers commonly manipulate conceptual objects (customers, orders, products; posts, tags, members; wall posts, private messages, friend connections) and the way data is actually stored (records in database tables).  The technical term for a tool that provides this abstraction is object relational mapper (ORM).  ORMs help developers be more efficient and focused, since they don't need to spend brain cycles thinking about how to communicate with the database.  It also means that the code is more portable – switching database software requires changing a setting in the ORM, not a rewrite of the whole codebase to match the new database's dialect.  As someone who has programmed using the ORM in Django, I can tell you how such a tool makes development less tedious and more enjoyable when you don't have to consider SELECTS and INSERTS.  In fact, I've never before written a line of SQL, yet I was able to build a rich web application thanks to an ORM.

Getting Started

One of the nice features of using the Entity Framework is its out-of-the-box simplicity.  To my pleasant surprise, I didn't need to download, install, or patch anything to get started with my first EF app.  I booted up VS 2010, opened a new project, designed my entities, wrote my code, and off I went.  You can probably get a very simple app up and running in about 10 minutes.  To prove it, I'll do exactly that as a step-by-step walkthrough below.  For this example, I'll make a simple events calendar.

Walkthrough

Begin by opening Visual Studio 2010.

 

From the 'File' menu select 'New' >> 'Project'.  I’ve chosen Visual C# as my language and a Console Application as my format for simplicity.  I will accept the default sequential naming that gets filled in for me, "ConsoleApplication2," so you can follow along by matching this with what gets filled in for you, too, though any real application should have an actual descriptive name such as "EventCalendar" or "PayrollProcessor."

 

Visual Studio will now set you up with a basic C# application.  You may wish to rename the file from 'Program.cs' to something more meaningful to your project, but once again I'll stick with the default names so you can sync up and provide your own in its place.

 

The next step is adding your entities to the project.  To do this, we’ll need to add a new file to our project which will contain the specifications for our entities.  Open up the Solution Explorer panel.  (If it’s not already in your workspace, you can bring it up by 'View' >> 'Solution Explorer'.)  Right-click on the name of your application, in my case “ConsoleApplication2,” and click on the option 'Add' >> 'New Item'. Then, select 'ADO.NET Entity Data Model'.  A good naming convention for your data model is to append the word "Model" to the end of your database name or application name, such as "EventCalendarModel" or "HumanResourcesModel."

 

If there was already a database in place that you had to use, then you would select the 'Generate from database' option, and the Entity Framework would do its best to create models and associations that fit the data.  If you are starting an application from scratch and get to design the database, as I’ll be demonstrating in this walkthrough, select 'Empty model.' This is known as the model-first approach, instead of the database-first approach.

 

This will now open a new file – a canvas onto which you can design your data model.  Rather than having to directly write the underlying XML file to specify the models and properties, this designer is a pretty slick way to configure your models and associate them.  You start with an empty canvas.

Begin by adding you first entity.  To add an entity, right-click on the design surface (the white canvas area) and click 'Add' >> 'Entity' from the context menu.  As I mentioned before, I’m going to use an events calendar as my sample.  We will have Users and Events as our two entities.  Type a name for the entity, and notice how it automatically attempts to pluralize it in the EntitySet field.  EntitySets are collections of entities (that map to tables in the database), which allow you to add, delete, or update entities (rows in the database).  In other words, you'll be using this often in your code.  Although the system usually gets the pluralization right, you can tweak it as you please.  You can leave all of the other settings as their defaults to continue, unless you have a special need to customize them.

The new entity you created will now appear on the design surface.  You can drag it around the designer as you please.

The next step will be to add properties to that entity.  Properties are essentially "instance data" in the lexicon of object-oriented programming or "columns" in database parlance.  You can add a property to an entity by right-clicking the entity and selecting 'Add' >> 'Scalar Property'.  When you add the property, you can then name it whatever makes sense. 

I’ll name the first one “Title” – the title of the Event.  When you have one of the properties highlighted – while renaming it – you can simply press the 'Enter' key to keep adding more.

 

Notice how I left out one pretty important "property" – the Event's creator.  That's because I plan to keep track of information about each User and which Events he creates, so a User of the calendar will be it's own entity that is associated with Events the User created.  This type of property is called a 'navigation property'.  As above, I add the User entity by right-clicking the canvas and clicking 'Add' >> 'Entity'.

I’ll now add two properties to it as above: a username and a password.

 

Finally, I'll add an association between the Event and the User to represent which User created an Event.  To do this, right click anywhere on the design surface and select 'Add' >> 'Association'.

To configure the association:

1.    Select the two “ends” of the association, or the two entities you will be associating, and their multiplicity to match the association you have in mind.  In this case, I am setting up a many-to-one association between an Event and its creator; each Event has only one creator, but each User may have created many Events. 

2.    Since you may have multiple associations between the same two entities, it’s a good practice to be specific about the association name rather than just using the two entity names.  I’ll name this “EventCreator” to be specific about what this association represents and avoid collisions or ambiguity.

3.    Customize the navigation properties using terms that make sense relative to this relationship.  Rather than just ‘User’ and ‘Events,’ I’ll use the terms ‘Creator’ and ‘CreatedEvents.’  These are the property names used by one entity to get to the other.  You would use Event.Creator to get the single User who created that event and User.CreatedEvents for a list of events created by a certain User.

Click OK to create the relationship, and you will see this new linkage appear on the design surface. Notice how the multiplicity of the relationship is represented by the number 1 and the *, one and many, respectively.  Also see how the new navigation properties have been added to each entity with the names specified.

 

To demonstrate why it makes sense to be specific with names of relationships and navigation properties, I’ll go one step further and allow Users to add Events to their personal calendar.  I will keep track of this by a many-to-many relationship, a second association between an Event and a User.  Here’s how that will look:

The extra association appears on the design surface, and the new navigation properties have been added to each entity.  Notice how being specific with the naming of the navigation properties makes it very easy to understand – and use – the models, especially when there are multiple associations between the same two entities.

Now I will go through each scalar property I added before and verify that it has the right attributes.  Right-click on any property of an entity and click the 'Properties' menu button.  This will bring up the 'Properties' panel.  Since I’ll be using this panel consistently while I make these tweaks, I decided to pin it to the right of my workspace by clicking the pushpin icon.

Although there's a lot to see in the properties panel, the most important parts right now are the 'Nullable' and 'Type' options specific to that property.  I leave the other settings to a more advanced guide.

If you are using an ORM because you have limited database experience, then the term 'Nullable' may need some explanation.  A property that is nullable is one that can contain a 'null' value.  For example, if 'description' for an Event is optional, then it could be nullable (set this property to 'true').  If, however, you want to require that every Event must have a time, that field should not be nullable (false).  In some senses, the word "optional" can be used as a loose analog. 

The 'Type' property should be fairly familiar to a programmer – it's just the data type.  So a time is probably best represented as a DateTime type, and an ID number is best stored as a 32-bit integer.  One nice feature of the Entity Framework is how it abstracts the database underneath.  Whereas one database may require a String, another may require that it be called a VarChar.  When using the EF, this minor annoyance is abstracted by the database provider.

For this simple example, I have chosen to first add all of the names of the properties to each entity and then return to specify the type and nullability.  You can also pause at any time while adding entities or properties to entities to do this.

Each property defaults to a string type.  In this example, I've gone through and modified only the 'Time' property of the Event to make it a DateTime type.  For all of the others, the default to string (or Int32 for keys) was appropriate.

Now my entities are ready, so it's time to ready the database and start writing code.  Right click the canvas and select 'Generate Database from Model.'

From here, select the database connection you'd like to use, or select 'New Connection…'.  I'll make a new connection in this example.  The specifics of getting your database up and running are beyond the scope of this walkthrough.  I happen to be borrowing a colleague's server.  One notable feature I will cover is that you can specify a database name in the 'Select or enter a database name' field, and it will create a new database on the server for you if it one with that name does not already exist and you have the proper permissions.  Note that clicking 'Test Connection' when you've specified a new database name (one to be created) will fail, since the database doesn't exist yet.  Simply press 'OK' to establish the connection and attempt to create the database.

If creating a new database, you should see this dialog box:

Click 'Yes' to try to make the new database on the server.  Assuming you've succeeded, you'll return to the original Generate Database Wizard and resume setting it up.  The connection string, which tells the EF how to connect to the database, should be filled in for you.  Click 'Next' to proceed.  Make note of the value in the bottom field, which in my case is Model1Container.  This will be needed later in the code to communicate with the database by forming a context.

After it's done thinking, you'll have a DDL file ready to go.  Don't get tripped up by the term DDL; all we care about is that this file contains instructions for the database that will get it set up so that you can use your models with it.

This file contains the script that is appropriate for your database to set it up.  Click 'Finish' to save that file.  It will subsequently open up in VS, and may indicate that it is not connected to the database yet.  The next step is to make that connection and run the script.  Right-click inside the document and select 'Execute SQL'.  Since I'm using SQL Server 2008, the window below pops up and I follow the steps to run the script.  Depending on your database, what you have to do to execute that script may vary.

 

After the operation is complete, you'll get a confirmation that it was successful. 

 

At this point, the database is all ready to go, and it's time to start interacting with it via code.  Here’s a code snippet you can run if you were following along.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

 

namespace ConsoleApplication2

{

    class Program

    {

        static void Main(string[] args)

        {

            /* In order to communicate with the database using the EF,

             * set up a context as in the line below. Notice how 'Model1Container'

             * follows through to here from where I mentioned it above. In order

             * to interact with data from the database in any way, you must establish

             * a context. */

            using (Model1Container context = new Model1Container())

            {

               

                /* One way to create a new entity is to give it all of its

                 * values right away as below. Use the entity names from

                 * your model, and assign the properties that you can. */

                User mike = new User

                {

                    Username = "mike",

                    Password = "123456"

                };

 

                Console.WriteLine("Users before add: " + context.Users.Count()); // 0

                /* Notice how the new user does not get added to the database or the context yet */

 

                context.Users.AddObject(mike);

                Console.WriteLine("Users before save: " + context.Users.Count()); // 0

                /* At this point, the context knows about 'mike', but it hasn't yet

                 * saved 'mike' to the database. */

 

                context.SaveChanges();

                Console.WriteLine("Users after save: " + context.Users.Count()); // 1

                /* Now 'mike' has been saved to the database.  Any changes to 'mike' from

                 * this point forward will not be saved until context.SaveChanges() is

                 * called again. */

 

                /* Here's an alternate approach to adding a new entity.

                 * You can continue assembling the entity at various parts of the code.

                 * Warning: Don't try to save an entity if one of its properties has not

                 * yet been set and that property is not nullable. */

                User matt = new User();

                matt.Username = "matt";

 

                /* If you tried to save here, before setting 'Password', you'd get an error,

                 * since Password is not a nullable field. */

                matt.Password = "654321";

                context.Users.AddObject(matt);

                context.SaveChanges();

 

                /* Now it's possible to add an associated entity */

                Event bbq = new Event

                {

                    Title = "Barbeque",

                    Description = "BBQ at the office",

                    Time = new DateTime(2010, 07, 04, 18, 0, 0),

                    Location = "1 Microsoft Way, Redmond",

                    /* The simplicity in the following line is EF magic at work;

                     * setting up an association is effortless, despite the extra

                     * work done in the database for such a relationship */

                    Creator = mike

                };

                context.Events.AddObject(bbq);

                context.SaveChanges();

 

                /* Here, too, a many-to-many relationship becomes simple to establish

                 * despite the extra work being done in the database to organize this */

                matt.ConfirmedEvents.Add(bbq);

                context.SaveChanges();

 

                /* Now we've got some meaningful data in the database to start querying */

 

                /* One way to find something you’re looking for is by forming a query as follows.

                 * The "FirstOrDefault" should be used when you expect there to be only one entity

                 * with the properties you specify. */

                User findMatt = context.Users.FirstOrDefault(user => user.Username == "matt");

                Console.WriteLine("Found user: " + findMatt.Username);

 

                /* Let's see how we can follow a navigation property. Since ConfirmedEvents

                 * is a navigation property for a many-to-many relationship, it will be

                 * something over which we can iterate in a foreach */

                Console.WriteLine("Matt is attending " + findMatt.ConfirmedEvents.Count() + " events, including: ");

                foreach (Event mattsEvents in findMatt.ConfirmedEvents)

                {

                    Console.WriteLine(mattsEvents.Title);

                }

 

                /* We can also explore the many-to-many relationship in the other direction. */

                Console.WriteLine("Attendees of the bbq:");

                foreach (User attendee in bbq.Attendees)

                {

                    Console.WriteLine(attendee.Username);

                }

 

                /* Pause at the end of execution */

                Console.Read();

            }

        }

    }

}

 

/* Output:

 

Users before add: 0

Users before save: 0

Users after save: 1

Found user: matt

Matt is attending 1 events, including:

Barbeque

Attendees of the bbq:

matt

 

*/

 

The code above belies the complexity of what is actually happening behind the scenes.  This simplicity is what makes using the Entity Framework so powerful; it makes the developer’s job a lot easier.  Beyond what I’ve been able to show in this brief walkthrough, there’s still plenty more to learn about the rest of the Entity Framework before you can fully realize its full potential.  Here are some additional resources for going deeper:

Background on Entity Framework: http://channel9.msdn.com/posts/egibson/MSDN-Simulcast-All-DataAll-Day-Dive-into-NET-Data-Access-Deeper-Look-at-ADONET-Entity-Framework/

Great video tutorials: http://msdn.microsoft.com/en-us/data/videos.aspx

Quickstart Documentation: http://msdn.microsoft.com/en-us/library/bb399182.aspx

Helpful Glossary of Terms: http://msdn.microsoft.com/en-us/library/bb387161.aspx

Hub for all things EF: http://msdn.microsoft.com/en-us/library/bb399572.aspx

 

-Michael Yaroshefsky
Program Manager
Microsoft

Guest partner post by Shimon Shlevich, Product Manager at Panorama Software

As Microsoft PowerPivot is gaining more popularity and exposure, BI professionals ask more and more questions about PowerPivot’s role in the organization in trying to understand what value the new in-memory BI solution from Microsoft brings, along with the benefits and the limitations of it. Is PowerPivot going to replace SQL Server Analysis Services? If so, how soon? What should be done with the existing BI solution? Or maybe both can coexist and serve different needs?

In order to answer these questions and understand both short and long term impacts of the new products on your BI solution we need to understand what motivated Microsoft to release this new creature and where do they position it. Microsoft are trying to achieve two main goals – introduce a new in-memory engine for data processing and promote the self-service BI concept extending the usage of BI systems to a wider audience.

The new in-memory engine is called “Vertipaq”. Vertipaq is claimed to perform much better than classic SSAS engine doing the aggregations and calculations as well as temporary data storage in a computer’s RAM eliminating the slow disk lookup overhead. The first version of this engine is currently released as a part of both Microsoft Office Excel 2010 and the SQL Server 2008 R2 enabling SSAS to work either in classic or the new in-memory mode. The in-memory mode for SSAS is currently only available for PowerPivot created cubes and not for all your classic cubes, however, eventually the new engine will make it to a major SSAS release and will become the new default engine of the SSAS.

Meanwhile, classic SSAS is more functional than PowerPivot in terms of analytics and administration. SSAS has more semantics such as hierarchies, and more administration support such as robust data security functionality. SSAS is probably the richest multidimensional engine on the market today, scalable to support large data amounts and completely enterprise ready. The downside of these capabilities is that SSAS project requires design and planning of the BI solution, implementation, deployment, testing and additional phases. A team of BI developers, IT support, long development cycle and not that frequent updates result with a highly customized, less flexible solution which is good for years and relies on enterprise data which structure does not change that often.

Analysis Services is the corner stone of any corporate infrastructure and it enables users to analyze data that has already been pre-modeled for them by IT. So users can create standard reports, dashboards and KPI’s based on the data there, in a sense, answering ‘known’ questions. PowerPivot, on the other hand, enables users to connect to any data and instantly start modeling and analyzing it “on-the-fly” (without IT defining the cubes and modeling it in advance). PowerPivot essentially enables users to answer those ‘unknown’ questions that can often exist.

How often have you had data was missing from the cube? Or a business user come to ask for a missing metric and you postponed its creation for the next data warehouse update which was postponed and never actually happened? This is where we need self-service BI and this is where PowerPivot comes to help both the business user and the IT team. PowerPivot authoring environment is the same beloved Microsoft Office Excel that everybody has and knows how to use. The simplicity and the familiarity of this desktop tool eliminates the need for additional training and increases the adoption rate. Give them a tool they are not afraid to use and they’ll know how to work with the metrics. Business users are able to just go through any data on their flat spreadsheet and produce a cube from it in a pivot table with only a single mouse click. There are certain limitations there, but the value is still huge – self service BI with zero training required and remarkable engine performance providing instant business value.

That’s why we say SSAS answers your “known” questions and PowerPivot solves the “unknown” ones. Panorama NovaView 6.2 supports both systems and supplies our customers with the same interface and same tools for both SSAS and PowerPivot. NovaView’s unified security layer secures both data sources at the same time and with the same security definitions making administrators’ life easier and making PowerPivot ready for a large enterprise deployment. NovaView BI Server resides in the center of the BI solution and implements the business logic, additional data semantics, and security applied on both SSAS and PowerPivot. It also delivers the data insights over both data sources via the entire suite of NovaView front end tools such as Flash Analytics, Dashboard, Smart Report, Spotlight and more.

By adopting the Microsoft roadmap of self-service BI, Panorama offers intuitive and easy to learn tools which allow business users to connect to either SSAS or PowerPivot cube within seconds. Following the initial connection NovaView users can manipulate the data, build extra calculations, exceptions, charts, KPIs and more. Users can save their work and share it with colleagues by making it publically available, sending by email or via a SharePoint portal. Specifically for PowerPivot, Panorama’s data security layer and rich analytical and dashboarding abilities extend PowerPivot cubes and create an enterprise ready, self-service, in-memory driven BI solution.

Learn more about Panorama for PowerPivot >>

Introduction

We recently announced the release of Entity Framework Feature  Community Technology Preview 4 (CTP4) . Feature CTP4 contains a preview of new features that we are considering adding to the core framework in the future and would like to get community feedback on. Feature CTP4 builds on top of the existing Entity Framework 4 (EF4) functionality that shipped with .NET Framework 4.0 and Visual Studio 2010.

This post will provide a walkthrough of the fundamental components of the Entity Framework Code First feature. Our Productivity Improvement work, which is also included in CTP4, provides a streamlined Code First experience that reduces the amount of code you need to write by providing a façade on top of the building blocks described in this post. This experience is covered in our CTP4 Productivity Improvement Walkthrough.

If you need assistance with CTP4 we have an Entity Framework Pre-Release Forum.

1.     Install EF CTP4

If you haven’t already done so then you need to install Entity Framework Feature CTP4.

2.     Create the Application

To keep things simple we’re going to build up a basic console application that uses Code First to perform data access.

·         Open Visual Studio 2010

·         File -> New -> Project…

·         Select “Windows” from the left menu and “Console Application”

·         Enter “EF.CodeFirst.Walkthrough” as the name

·         Select “OK”

3.     Create the Model

Code First is all about describing our model using classes so we are going to start by building out a simple model in code. We want our model to be persistence ignorant (i.e. not have any dependencies on Entity Framework) so we’ll add our model in a separate project.

·         Add a new project for the model

 o   File -> Add -> New Project…

 o   Select “Windows” from the left menu and “Class Library”

 o   Enter “EF.CodeFirst.Walkthrough.Model” as the name

 o   Select “OK”

 

·         Right click on the model project and add a class called “Book” with the following implementation

using System;

 

namespace EF.CodeFirst.Walkthrough.Model

{

    public class Book

    {

        public string ISBN { get; set; }

        public string Title { get; set; }

        public DateTime FirstPublished { get; set; }

        public bool IsFiction { get; set; }

 

        public virtual Publisher Publisher { get; set; }

        public virtual Author Author { get; set; }

    }

}

 

·         Right click on the model project and add a class called “Person” with the following implementation

 

namespace EF.CodeFirst.Walkthrough.Model
{

    public class Person

    {

        public int PersonId { get; set; }

        public string FirstName { get; set; }

        public string LastName { get; set; }

    }

}

 

·         Right click on the model project and add a class called “Author” with the following implementation

using System.Collections.Generic;

 

namespace EF.CodeFirst.Walkthrough.Model

{

    public class Author : Person

    {

        public int AuthorId { get; set; }

 

        public virtual ICollection<Book> Books { get; set; }

    }

}

 

·         Right click on the model project and add a class called “Publisher” with the following implementation

using System.Collections.Generic;

 

namespace EF.CodeFirst.Walkthrough.Model

{

    public class Publisher

    {

        public int PublisherId { get; set; }

        public string Name { get; set; }

 

        public virtual ICollection<Book> Books { get; set; }

    }

}

4.     Model Builder & Fluent API

If you’ve used the previous CTPs of Code First then this is where things to start to look a little different. As the name suggests ModelBuilder is used to configure the model, ModelBuilder then produces an immutable DbModel that can be used to construct either ObjectContext or DbContext instances.

Once you have created a DbModel this should be cached and re-used throughout your application to avoid incurring the performance hit of model creation multiple times. If you opt for the Code First approach described in the Productivity Improvements walkthrough then DbContext takes care of this caching for you.

·         First we need to reference our model from the main application

 o   Right click on the console application project -> Add Reference…

 o   Select the “Project” tab

 o   Select the model project from the list

 o   Click “OK”

 

·         We also need to reference the CTP4 assembly and the core Entity Framework assembly

 o   Right click on the console application project -> Add Reference…

 o   Select the “.NET” tab

 o   Select “Microsoft.Data.Entity.Ctp” from the list

 o   Click “OK”

 o   Repeat the above steps for “System.Data.Entity”

 

·         We also need to add a few using statement to the top of Program.cs

using System.Data.Entity.ModelConfiguration;

using System.Data.SqlClient;

using EF.CodeFirst.Walkthrough.Model;

 

·         Next add the following code to the Main method in Program.cs to configure your model

static void Main(string[] args)

{

    var builder = new ModelBuilder();

           

    builder.Entity<Book>().HasKey(b => b.ISBN);

    builder.Entity<Book>().Property(b => b.Title).IsRequired();

    builder.Entity<Book>().HasRequired(b => b.Author).WithMany(a => a.Books);

 

    builder.Entity<Person>();

 

    builder.Entity<Publisher>().Property(p => p.Name).IsRequired().HasMaxLength(50);

}

 

Note: In the final section we will cover creating a derived context that exposes sets for the types in your model. Once you have a derived context you can use the ModelBuilder.DiscoverEntitiesFromContext(Type contextType) method to have the types that are exposed in sets automatically registered for you. This avoids the need to manually register types that do not require any additional configuration, such as Person in the code above.

Why Introduce DbModel?

In previous CTPs ContextBuilder basically did the work of both ModelBuilder and DbModel, introducing a separate representation for the final model does add an extra step in the process. In the future DbModel will become more fundamental to EF in general as we look at other ways to build models beyond ModelBuilder. DbModel allow us to have a single representation for a model that can then be used to construct both ObjectContext and DbContext instances.

Conventions

You’ll notice there are a lot of things I’m not specifying about the model, for example that Person.PersonId is a primary key or that Book.Publisher and Publisher.Books are inverse navigation properties of the same relationship. This is because Code First now includes an extended set of conventions to take care of common configuration tasks for you. The conventions are discussed in detail in this Conventions design blog post, the conventions included in CTP4 are:

• Primary Key
• Relationship Inverse
• Foreign Key
• Pluralization of Table Names

 

Any configuration that you specify explicitly via the Fluent API will take precedence over what is discovered via convention.

Data Annotations

Code First will also now consume Data Annotations so rather than configuring Book.ISBN as the primary key via the Fluent API we could have added the Key attribute (System.ComponentModel.DataAnnotations.KeyAttribute) to the ISBN property.

Data Annotations are described in detail in this design blog post, the annotations supported in CTP4 are:

• Key
• StringLength
• ConcurrencyCheck
• Required
• Timestamp
• DataMember
• RelatedTo
• MaxLength
• StoreGenerated

Data Annotations take precedence over conventions but explicit configuration via the Fluent API still has the highest precedence.

Relationship API Changes

If you’ve used the previous CTPs of Code First you’ll also notice we have changed the syntax for specifying relationships in an attempt to make it more intuitive. The single Relationship method has now been replaced with HasRequired, HasOptional and HasMany. Inverse navigations are then specified via WithRequired, WithOptional and WithMany. If you expose foreign key properties on your entities then these can be configured via the HasConstaint method.

The main benefits of the refactoring are:

• Reduced number of calls required to configure a relationship
•  API Signatures are more understandable
  • The single Relationship method with overloads for references and collections was proving to be confusing
• Removal of invalid methods
  • The previous API exposed Required and Optional for ‘many’ ends of relationships
• Ability to full specify relationships that only expose one navigation property

Using the one-to-many relationship between Book and Author as an example, this would previously have been configured as follows

builder.Entity<Book>().Relationship(b => b.Author).IsRequired().FromProperty(a => a.Books);

 

With the API changes this is now

 

builder.Entity<Book>().HasRequired(b => b.Author).WithMany(a => a.Books);

 

If the Author.Books property was not included in the model it is now possible to still completely specify the relationship

 

builder.Entity<Book>().HasRequired(b => b.Author).WithMany();

 

Or alternatively if the Book.Author property was not included

 

builder.Entity<Author>().HasMany(a => a.Books).WithRequired();

5.     Configuration Classes

Now let’s say we want to encapsulate the configuration for Book in a separate class. We can do this by creating a derived configuration class for Book and then registering it with our model builder. This is useful if you want to re-use configurations within an application or even between applications. Configuration classes also nicely separate configuration related code from the rest of our application code.

·         Right click on the console application project and add a class called “BookConfiguration” with the following implementation

using System.Data.Entity.ModelConfiguration;

using EF.CodeFirst.Walkthrough.Model;

 

namespace EF.CodeFirst.Walkthrough

{

    public class BookConfiguration : EntityConfiguration<Book>

    {

        public BookConfiguration()

        {

            this.HasKey(b => b.ISBN);

            this.Property(b => b.Title).IsRequired();

            this.HasRequired(b => b.Author).WithMany(a => a.Books);

        }

    }

}

 

·         We can then modify the Main method in Program.cs to be as follows

static void Main(string[] args)

{

    var builder = new ModelBuilder();

 

    builder.Configurations.Add(new BookConfiguration());

 

    builder.Entity<Person>();

 

    builder.Entity<Publisher>().Property(p => p.Name).IsRequired().HasMaxLength(50);

}

6.     Create a Derived Context

Now that we have a model configured the easiest way to interact with the classes is through a derived context. With the introduction of the Productivity Improvements that we mentioned earlier there are two options here. You can either derive from our existing ObjectContext type or the new alternative DbContext type, which has a simpler API surface. We’ll look at both options in this walkthrough.

6.1.  Option 1: Object Context

·         Right click on the console application project and add a class called “BookCatalog” with the following implementation

using System.Data.EntityClient;

using System.Data.Objects;

using EF.CodeFirst.Walkthrough.Model;

 

namespace EF.CodeFirst.Walkthrough

{

    public class BookCatalog : ObjectContext

    {

        public BookCatalog(EntityConnection connection)

            : base(connection)

        { }

 

        private ObjectSet<Book> _books;

        public ObjectSet<Book> Books

        {

            get

            {

                return this._books == null

                    ? this._books = this.CreateObjectSet<Book>()

                    : this._books;

            }

        }

 

        private ObjectSet<Person> _people;

        public ObjectSet<Person> People

        {

            get

            {

                return this._people == null

                    ? this._people = this.CreateObjectSet<Person>()

                    : this._people;

            }

        }

 

        private ObjectSet<Publisher> _publishers;

        public ObjectSet<Publisher> Publishers

        {

            get

            {

                return this._publishers == null

                    ? this._publishers = this.CreateObjectSet<Publisher>()

                    : this._publishers;

            }

        }

    }

}

 

·         With a derived context defined we can now modify the Main method in Program.cs to use it for data access:

static void Main(string[] args)

{

    var builder = new ModelBuilder();

    builder.Configurations.Add(new BookConfiguration());

    builder.Entity<Person>();

    builder.Entity<Publisher>().Property(p => p.Name).IsRequired().HasMaxLength(50);

 

    var model = builder.CreateModel();

 

    using (var connection = new SqlConnection(@"Server=.\SQLEXPRESS;Database=CodeFirstWalkthrough;Trusted_Connection=True;"))

    {

        using (var context = model.CreateObjectContext<BookCatalog>(connection))

        {

            if (!context.DatabaseExists())

            {

                context.CreateDatabase();

            }

 

            var book = new Book

            {

                ISBN = "1111",

                Title = "Intro to Code First",

                FirstPublished = DateTime.Today,

                IsFiction = false,

                Author = new Author { FirstName = "Rowan", LastName = "Miller" },

                Publisher = new Publisher { Name = "EF Books" }

            };

 

            context.Books.AddObject(book);

            context.SaveChanges();

        }

    }

}

 

6.2. Option 2: DbContext

We’re going to look at constructing a DbContext from a DbModel but as previously mentioned there is simpler Code First experience available that avoids the need to externally create a ModelBuilder and DbModel and also takes care of model caching, this is described in the Productivity Improvements walkthrough.

·         Right click on the console application project and add a class called “SimpleBookCatalog” with the following implementation:

using System.Data.Entity;

using EF.CodeFirst.Walkthrough.Model;

using System.Data.Entity.Infrastructure;

 

namespace EF.CodeFirst.Walkthrough

{

    public class SimpleBookCatalog : DbContext

    {

        public SimpleBookCatalog(DbModel model)

            : base(model)

        { }

 

        public DbSet<Book> Books { get; set; }

        public DbSet<Person> People { get; set; }

        public DbSet<Author> Authors { get; set; }

        public DbSet<Publisher> Publishers { get; set; }

    }

}

 

·         With a derived context defined we can now modify the Main method in Program.cs to use it for data access
Note that DbContext takes care of creating a database for us so we don’t need to specify a connection, this convention can be overridden and is described in the Productivity Improvements walkthrough.

static void Main(string[] args)

{

    var builder = new ModelBuilder();

    builder.Configurations.Add(new BookConfiguration());

    builder.Entity<Person>();

    builder.Entity<Publisher>().Property(p => p.Name).IsRequired().HasMaxLength(50);

 

    var model = builder.CreateModel();

 

    using (var context = new SimpleBookCatalog(model))

    {

        var book = new Book

            {

                ISBN = "2222",

                Title = "Intro to Code First",

                FirstPublished = DateTime.Today,

                IsFiction = false,

                Author = new Author { FirstName = "Rowan", LastName = "Miller" },

                Publisher = new Publisher { Name = "EF Books" }

            };

 

            context.Books.Add(book);

            context.SaveChanges();

    }

}

7.     Summary

In this walkthrough we looked at how the fundamental Code First components can be used to configure a model and create a context to perform data access. We saw the extended default conventions, Data Annotations and the updated relationship API that are new in CTP4.

As mentioned during the post these building blocks are also used to provide a simpler and more streamlined Code First experience in the Productivity Improvement work described in this post.

 

Rowan Miller
Program Manager
ADO.NET Entity Framework

Introduction

We recently announced the release of Entity Framework Feature  Community Technology Preview 4 (CTP4) . Feature CTP4 contains a preview of new features that we are considering adding to the core framework in the future and would like to get community feedback on. Feature CTP4 builds on top of the existing Entity Framework 4 (EF4) functionality that shipped with .NET Framework 4.0 and Visual Studio 2010.

If you need assistance with CTP4 we have an Entity Framework Pre-Release Forum.

This walkthrough provides an introduction to the Productivity Improvement work that is included in our Entity Framework Feature CTP4 release. Our recent Design Blog post provides a detailed background on the Productivity Improvements, here is a quick summary extract from that post:

We’ve been paying attention to the most common patterns that we see developers using with the EF and have been brewing up a set of improvements to the Entity Framework designed to allow developers to accomplish the same tasks with less code and fewer concepts.

These improvements provide a cleaner and simpler API surface that focuses your attention on the most common scenarios but still allows you to drill down to more advanced functionality when it’s needed.  We hope you will enjoy this simpler experience, but we should be quick to assure you that this is NOT a new data access technology.  These improvements are built on the same technology for mapping, LINQ, providers and every other part of the Entity Framework. 

Database First & Model First

The Productivity Improvement work benefits developers using Database First, Model First and Code First development patterns. This walkthrough will use the Code First development pattern. The T4 Templates to support Database First and Model First development didn’t make it into CTP4 but we will provide some sample templates in a separate post in the coming weeks.

1.     Install EF CTP4

If you haven’t already done so then you need to install Entity Framework Feature CTP4.

2.     Create the Application

To keep things simple we’re going to build up a basic console application that uses the EF Productivity Improvements to perform data access.

·         Open Visual Studio 2010

·         File -> New -> Project…

·         Select “Windows” from the left menu and “Console Application”

·         Enter “EF.PI.Walkthrough” as the name

·         Select “OK”

3.     Create the Model

Let’s define a very simple model using classes. I’m just defining them in the Program.cs file but in a real world application you would split your classes out into separate files and probably a separate project.

·         Below the Program class definition in Program.cs I am defining the following two classes

public class Category

{

    public string CategoryId { get; set; }

    public string Name { get; set; }

 

    public virtual ICollection<Product> Products { get; set; }

}

 

public class Product

{

    public int ProductId { get; set; }

    public string Name { get; set; }

    public string CategoryId { get; set; }

 

    public virtual Category Category { get; set; }

}

 

4.     Create a Context

The simplest way to start using the classes for data access is to define a context that derives from System.Data.Entity.DbContext and exposes a typed DbSet<TEntity> for each class in my model.

·         We’re now starting to use types from the CTP so we need to add a reference to the CTP assembly

 o   Project -> Add Reference…

 o   Select the “.NET” tab

 o   Select “Microsoft.Data.Entity.Ctp” from the list

 o   Click “OK”

 

·         You’ll also need a reference to the existing Entity Framework assembly

 o   Project -> Add Reference…

 o   Select the “.NET” tab

 o   Select “System.Data.Entity” from the list

 o   Click “OK”

 

·         Add a using statement for System.Data.Entity at the top of Program.cs

using System.Data.Entity;

·         Add a derived context below the existing classes that we’ve defined

public class ProductCatalog : DbContext
{

    public DbSet<Category> Categories { get; set; }

    public DbSet<Product> Products { get; set; }

}

 

That is all the code we need to write to start storing and retrieving data. Obviously there is quite a bit going on behind the scenes and we’ll take a look at that in a moment but first let’s see it in action.

5.     Access Data

·         I’m padding out the Main method in my program class as follows

class Program

{

    static void Main(string[] args)

    {

        using (var context = new ProductCatalog())

        {

            var food = new Category { CategoryId = "FOOD", Name = "Foods" };

            context.Categories.Add(food);

            int recordsAffected = context.SaveChanges();

 

            Console.WriteLine(

                "Saved {0} entities to the database, press any key to exit.",

                recordsAffected);

 

            Console.ReadKey();

        }

    }

}

 

You can now run the application and see that the new category is inserted.

Where’s My Data?

DbContext has created a database for you on localhost\SQLEXPRESS. The database is named after the fully qualified name of your derived context, in our case that is “EF.PI.Walkthrough.ProductCatalog”. We’ll look at ways to change this later in the walkthrough.

Model Discovery

DbContext worked out what classes to include in the model by looking at the DbSet properties that we defined. It then uses the default Code First conventions to find primary keys, foreign keys etc. The conventions are discussed in detail in this Conventions Design Blog post, the conventions included in CTP4 are:

• Primary Key
• Relationship Inverse
• Foreign Key
• Pluralization of Table Names
  
  

 

Simplified API Surface

If you’re familiar with ObjectContext you’ll notice that intellisense on your derived context now only shows you 10 members, as opposed to the 47+ that showed up on ObjectContext.

You’ll see we have started to factor functionality away so that only the most commonly used operations are exposed at the root level. For example all members related to the underlying database are available from context. Database. This is just one example of the work we are doing to provide a more discoverable and intuitive API surface, more detail is available in the API Surface section of our Productivity Improvements Design Blog post.

6.     Access More Data

Let’s pad out the program we just wrote to show a bit more functionality. We are going to make use of the Find method on DbSet that will locate an entity based on primary key. If no match is found then Find will return null. We’re also making use of LINQ to query for all products in the Food category ordered alphabetically by name. Querying uses the exiting LINQ to Entities provider so it supports the same queries that are possible with ObjectSet/ObjectQuery in EF4.

·         I’m replacing the Main we wrote above with the following

class Program

{

    static void Main(string[] args)

    {

        using (var context = new ProductCatalog())

        {

            // Use Find to locate the Food category

            var food = context.Categories.Find("FOOD");

            if (food == null)

            {

                food = new Category { CategoryId = "FOOD", Name = "Foods" };

                context.Categories.Add(food);

            }

 

            // Create a new Food product

            Console.Write("Please enter a name for a new food: ");

            var productName = Console.ReadLine();

 

            var product = new Product { Name = productName, Category = food };

            context.Products.Add(product);

 

            int recordsAffected = context.SaveChanges();

 

            Console.WriteLine(

                "Saved {0} entities to the database.",

                recordsAffected);

 

            // Query for all Food products using LINQ

            var allFoods = from p in context.Products

                            where p.CategoryId == "FOOD"

                            orderby p.Name

                            select p;

 

            Console.WriteLine("All foods in database:");

            foreach (var item in allFoods)

            {

                Console.WriteLine(" - {0}", item.Name);

            }

 

            Console.WriteLine("Press any key to exit.");

            Console.ReadKey();

        }

    }

}

 

7.     Changing the Database Name

If you want to change the name of the database that is created for you then there is a constructor on DbContext that allows you to specify the name.

·         Say we want to change the name of the database to “MyProductCatalog” we could add a default constructor to our derived context that passes this name down to DbContext:

public class ProductCatalog : DbContext

{

    public ProductCatalog()

       : base("MyProductCatalog")

    { }

 

    public DbSet<Category> Categories { get; set; }

    public DbSet<Product> Products { get; set; }

}

 

Other Ways to Change the Database

There are a number of other ways to specify which database should be connected to. We’ll cover these in more detail in a separate post in the near future.

• App.config Connection String
  Create a connection string in the App.Config file with the same name as your context.
• DbConnection
  There is a constructor on DbContext that accepts a DbConnection.
• Replace the Default Convention
  The convention used to locate a database based on the context name is an AppDomain wide setting that you can change via the static property System.Data.Entity.Infrastructure.Database.DefaultConnectionFactory.

8.     Setting an Initialization Strategy

In the next section we are going to start changing our model which in turn means the database schema needs to change as well. Currently there is no out of the box solution to migrate your existing schema in place, although this is something we are looking at addressing. There is however the opportunity to run some custom logic to initialize the database the first time a context is used in an AppDomain. This is handy if you want to insert seed data for test runs but it’s also useful to re-create the database if the model has changed. In CTP4 we include a couple of strategies you can plug in but you can also write custom ones.

For the walkthrough we just want to drop and re-create the database whenever the model has changed.

·         At the top of the Main method in my Program class I’ve added the following code

Database.SetInitializer<ProductCatalog>(new RecreateDatabaseIfModelChanges<ProductCatalog>());

 

We’ll provide more details on this feature and the scenarios that it enables in a separate post.

9.     Data Annotations

So far we’ve just let EF discover the model using its default conventions but there are going to be times when our classes don’t follow the conventions and we need to be able to perform further configuration. There are two options for this; we’ll look at Data Annotations in this section and then the Code First Fluent API in the next section.

·         Let’s add a supplier class to our model

public class Supplier

{

    public string SupplierCode { get; set; }

    public string Name { get; set; }

}

 

·         And we also need to add a set to our derived context

public class ProductCatalog : DbContext

{

    public ProductCatalog()

        : base("MyProductCatalog")

    { }

 

    public DbSet<Category> Categories { get; set; }

    public DbSet<Product> Products { get; set; }

    public DbSet<Supplier> Suppliers { get; set; }

}

 

Now if we ran our application we’d get an InvalidOperationException saying “Unable to infer a key for entity type 'EF.PI.Walkthrough.Supplier'.” because EF has no way of knowing that SupplierCode should be the primary key for Supplier.

·         We’re going to use Data Annotations now so we need to add a reference

 o   Project -> Add Reference…

 o   Select the “.NET” tab

 o   Select “System.ComponentModel.DataAnnotations” from the list

 o   Click “OK

 

·         Add a using statement at the top of Program.cs

using System.ComponentModel.DataAnnotations;

 

·         Now we can annotate the SupplierCode property to identify that it is the primary key:

public class Supplier

{

    [Key]

    public string SupplierCode { get; set; }

    public string Name { get; set; }

}

 

Data Annotations are described in detail in this Design Blog post, the annotations supported in CTP4 are:

• Key
• StringLength
• ConcurrencyCheck
• Required
• Timestamp
• DataMember
• RelatedTo
• MaxLength
• StoreGenerated

10.           Fluent API

We looked at configuring the model using Data Annotations but you may not want to add attributes to your classes or in some cases you may not be able to if they belong to another class library. The other option is to use the Code First Fluent API within DbContext.

Let’s say we want to configure Name to be a required property for Supplier.

·         Add a using statement for System.Data.Entity.ModelConfiguration at the top of Program.cs

using System.Data.Entity.ModelConfiguration;

 

·         Override the OnModelCreating method in the derived context, as highlighted below
Note: The base implementation of OnModelCreating is blank so there is no need to call the base method.

public class ProductCatalog : DbContext

{

    public ProductCatalog()

        : base("MyProductCatalog")

    { }

 

    protected override void OnModelCreating(ModelBuilder modelBuilder)

    {

        modelBuilder.Entity<Supplier>().Property(s => s.Name).IsRequired();

    }

 

    public DbSet<Category> Categories { get; set; }

    public DbSet<Product> Products { get; set; }

    public DbSet<Supplier> Suppliers { get; set; }

}

Summary

In this walkthrough we looked at the core patterns for Code First development using the EF Productivity Improvements included in EF Feature CTP4. We looked at defining and configuring a model, storing and retrieving data, configuring the database connection and updating the database schema as our model evolved.

We’d like to hear any feedback you have on these new Productivity Improvements for EF.

Rowan Miller
Program Manager
ADO.NET Entity Framework

 

We’ve release an updated Entity Framework Feature Community Technology Preview (CTP) which is available for download. This is our fourth CTP and includes updates to the Code First feature along with the first preview of our Productivity Improvements for EF.

Feature CTP4 contains a preview of new features that we are considering adding to the core framework in the future and would like to get community feedback on. Feature CTP4 builds on top of the existing Entity Framework 4 (EF4) functionality that shipped with .NET Framework 4.0 and Visual Studio 2010.

Getting Started

We’ve created a couple of walkthroughs to get you started with these new features, we’ll also be providing more detailed posts that dive down into interesting areas of each feature in the coming weeks.

• Productivity Improvements Walkthrough 
• Code First Walkthrough

If you need assistance with CTP4 we have an Entity Framework Pre-Release Forum.

Productivity Improvements

We recently posted here about a set of productivity improvements we have been working on to simplify the process of writing data access code with the Entity Framework. CTP4 includes the core API surface and functionality that is described in the post. We’d love to hear your feedback on this simplified experience, be sure to check out the walkthrough.

Code First

Updates and enhancements to Code First include:

• Moved to the System.Data.Entity.ModelConfiguration namespace
• Refactored ContextBuilder into ModelBuilder and DbModel
  ModelBuilder is a high level component that lets you tweak a model, ModelBuilder then produces an immutable DbModel that can be used to construct a context. In the future DbModel will become a fundamental component of the EF stack as we look at other ways to build models beyond ModelBuilder.
• More Model Discovery Conventions
  We posted details about the complete set of conventions we plan to support here
  In CTP4 we have implemented the following conventions:
  • Primary Key
  • Relationship Inverse
  • Foreign Key
  • Pluralization of Table Names
• Support for Data Annotations
  We posted details about all the Data Annotations we plan to support here
  The annotations supported in CTP4 are:
  • Key
  • StringLength
  • ConcurrencyCheck
  • Required
  • Timestamp
  • DataMember
  • RelatedTo
  • MaxLength
  • StoreGenerated
• Parameterless overload of MapSingleType()
  This allows you to configure table names without having to explicitly map every property
• Improved Relationship Configuration API
  We’ve made some changes to the Fluent API methods used to configure relationships to make the API more explicit and intuitive. These changes are covered in the Code First Walkthrough .

Road Map

We are still working through the best ship vehicle to deliver a go-live release for these features. Your feedback has had a big impact on the Code First feature so far and we want to be careful not to lock down the API too quickly. That said we are getting consistent asks for a go-live for Code First and we are working to get to an RTM in the earliest feasible ship vehicle.

 

Please keep the feedback coming!

ADO.NET Entity Framework Team

We are excited to announce that CodeSmith Insight Build 11619 is now live! This release pushes more than just simple updates and bug fixes, it also introduces an all new client assembly for WPF, as well as .NET Framework 4.0 support for all clients. Insight...(read more)

For anyone coming from a background using T-Sql as their primary query language, Linq to Entities can be quite daunting. I, for one, am a huge fan of the Entity Framework.  I consider it another step towards keeping data driven application developers inside the .Net framework. And LINQ is a perfect way for programmers to think about data queries.  Sql is another language for most developers to learn, and each database has slightly different syntax for operations.  LINQ and Entity Framework make it much easier for programmers to think in their native programming language while writing queries.

In this article I am going to be showcasing a few common Sql query patterns, and explaining their Linq to Entities (EF) equivalents.  This will hopefully show some of the power of LINQ to developers who have yet to start learning the LINQ syntax.

Single Column T-Sql query

First we are going to take a look at a simple T-Sql query that selects one column from a table named Employees. This query is used in a DataAdapter to fill a DataTable then bound to a WinForms GridView.

select Age from employees

The column “Age” is of type Int, so the query will return a list of all Age’s(int’s) from the employees table. This simple query can be edited in several ways to return different results to the naming of the Age context.

select AGE from employees
select employees.Age as NewAge from employees

T-Sql of course is for the most part is case insensitive, but when it comes to LINQ column projection, case is taken into consideration. The first query (above) will return a list of Int’s in a column named “AGE”, the second will return a  column named “NewAge” that contains the same result.

This process is called projection. All of the queries return the same results but the projected schema naming has been altered dynamically.

Linq to Entities Projection

Projection in Linq to Entities (Linq with Entity Framework models) is very similar to the way it is done in T-Sql, a column or columns are queried and set to return naming different than the original column name. The great thing about Linq to Entities is the query can be debugged to see what exactly is being returned. We will start off again with a simple single column query.

var query = from emps in context.Employees select emps.Age;

By examining the query above you would think for sure its the same as our first T-Sql query. The Linq query does return the same results, a list of Int’s, but the result set does not contain any contextual information. The query above will not bind directly to a GridView because it is only a list of Int’s, not a list of type “Age” (Int’s). 

The image to the right is what the result view looks like in Visual Studio of the executed query. The result is of type System.Data.Objects.ObjectQuery<int?> which you can see has no reference to the Age column at all. There is a different approach needed to project columns into a new value.

Anonymous Type Projection

var query = from emps in context.Employees select new { emps.Age };
var query = from emps in context.Employees select new { NewAge = emps.Age };

Both queries above will perform the proper projection, similar to the T-Sql queries. The First will return a list of Int’s under the column “Age” and the second “NewAge”. The Select new syntax is how Linq to Entities handles projecting data into an anonymous type.

The image to the right is what the result view looks like for the new projected query. The results are returned not only as Int’s, but still contain their contextual information which will allow them to be bound directly to a GridView. The returned type has also changed to System.Data.Objects.ObjectQuery<<>f__AnonymousType0<int?>>. The anonymous type would be “Age” which is of type int?.

Summary

To developers that are new to LINQ and the Entity Framework, functional differences like these can be confusing. With enough research and testing almost any T-Sql query can be converted to Linq to Entities and its often very rewarding.

Entity Framework is here to stay and it is definitely something you will want to become well versed in.  Microsoft is continuing to pour huge amounts of resources into Entity Framework for the future.  Take to time to become versed in the basic concepts and terms will serve you well for the future.

In preparation for enhancing Visual Studio design-time experience testing, we’d like to collect a catalog of real world applications to use for performance, stress, and ad hoc testing by the Web Platform and Tools team.  We’re looking for small, medium, and large applications covering a range of architectures, languages, frameworks, and features.

Your contributions will help us to ensure stability and performance in the areas of greatest interest to you.  These additional testing opportunities will also help us ensure that we will have more real world samples to verify every release of Visual Studio whether it is Beta, RC or RTM.

If you are interested in helping us, please provide the following information:

1. Please send an email to WptApps@live.com indicating that you are willing to help.  Include your name and your company name in the message.
2. Tell us about the app or apps that you can share with us.  What are their sizes, what technologies do they use, and what areas are you particularly interested in concerning stability and performance?  Please be specific about performance: responsiveness, memory usage, end-to-end elapsed time, etc. Remember that we are testing and investigating behavior in the IDE (Visual Studio), not the runtime behavior on the web server.
3. We’re working on a standard NDA proposal, but your company may have special requirements or concerns.  We will work with together with you on this. Rest assured that we understand the sensitivity of your source code as Intellectual Property and will use the project purely for testing purposes to make your Visual Studio experience better.

All we can offer you in return is our appreciation, but we will work to incorporate as many of your contributions as possible into frequent and thorough design-time perf and stress testing.  We hope the real world apps that you provide will help us to better measure the product stability, reliability and performance from early stages of the product development to ensure the quality of releases are at acceptable level and there are no major regressions.

Thanks!
--The Web Platform and Tools Team

Microsoft announces new solutions and offerings to help customers and partners realize the benefits of cloud computing, including the Windows Azure Platform appliance.  Read the blog post by Robert Wahbe, corporate vice president of Server and Tools.