Meta-Me

Tip 46 – How to exclude a property using Code-Only

This time a real simple one prompted by this question on StackOverflow.

Problem:

If you tell the Entity Framework about this class using Code-Only, by default every property becomes part of Entity, and as a result stored in the database.

Usually this is what you want.

But not always, imagine this class:

public class Person{
  public int ID {get;set;}
  public string Firstname {get;set;}
  public string Surname {get;set;}
  public string Fullname {get;set;}
}

Here Fullname is actually just the Firstname and Surname concatenated together, so storing the Fullname separately in the database would be redundant. You really want to ignore Fullname.

So how do you do that?

Solution 1 – Remove Setter – aka ‘generally not an option’

To be a property of the ‘Entity’, the Entity Framework must be-able to read & write the property, so if you remove the setter, the CLR property is no longer an ‘Entity’ property.

Unfortunately it isn’t always possible to do this sort of thing, and more importantly doing something like this *just* to work with Code-Only and EF is the opposite of persistence ignorance!

We need another solution…

Solution 2 – Map all the properties you want explicitly

If you explicitly map, rather than allowing Code-Only to map by convention, properties you omit from the mapping are ignored.

So this mapping:

builder.Entity<Person>().MapSingleType(p => new {
   p.ID,
   p.Firstname,
   p.Surname
});

along with specifying the mapping has a side-effect of telling Code-Only to ignore the FullName property.

Longer term solution?

Now obviously being forced to map every other property *just* to exclude one property is not an ideal solution, especially if everything else is ‘by convention’.

Something like this would be better:

builder.Entity<Person>().Exclude(p => p.Fullname);

This isn’t currently supported, in CTP 2 for Beta2, but it is being considered for future versions of Code-Only…

Tip 45 – How to swap EF metadata at runtime.

Background

By default the Entity Framework embeds its metadata inside your assembly as a resource.

It also puts a connection string in the App or Web Config that references those resources something like this:

<add name="BloggingEntities" connectionString="metadata=res://*/Blogging.csdl|res://*/Blogging.ssdl|res://*/Blogging.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=.\SQLEXPRESS;Initial Catalog=TipsDatabase;Integrated Security=True;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />

This makes it incredibly easy to get started:

using (BloggingEntities ctx = new BloggingEntities())
{

NOTE: the use of res://* to tell the EF to look inside the assemblies resources for the various bits of metadata.

Problem

But embedding the metadata as a resource also means it is essentially immutable.

What if you need to change it at runtime?

There are lots of reasons why you might want to change the metadata at runtime, but probably the most likely is that in production you have to deal with a DBA who has a different set of database design ideas.

Typically this means you have to make some database changes which require some storage model (SSDL) and mapping (MSL) changes.

Doing so is perfectly okay, indeed it is one of the big benefits of using the Entity Framework, so long as the conceptual model or CSDL -- what the developer programs against -- remains unchanged.

Solution

So how do you plug in a different MSL and SSDL at runtime?

Here are the steps involved:

Step 1:
Get hold of the ‘metadata artifacts’:

1. Right click on the designer canvas and click Properties:
   
   
   
2. Set 'Metadata Artifact Processing' to 'Copy to Output Directory':
   
    
   
3. Build and look in the bin\debug (or bin\release) directory:
   
   

Step 2:
Now you have the CSDL/MSL/SSDL as files, it is easy to replace one or more (generally the SSDL and MSL) with a version that matches your environment. So you can have one set for Development and another for Production.

Then all you need to do is modify your connection string to point to the right set of files, something like this:

var connStr =
@"metadata=.\Blogging.csdl|.\Production.ssdl|.\Production.msl;
    provider=System.Data.SqlClient;
    provider connection string=""
           Data Source=.\SQLEXPRESS;
           Initial Catalog=TipsDatabase;
           Integrated Security=True;
           MultipleActiveResultSets=True
   """;

Notice that now the connection string uses .\ rather than res://* which tells the EF to look in the same directory as the application for the metadata.

That’s all there is to it.

NOTE: EF connection strings can be a little tricky, what with nested provider connection strings, metadata and providers, thankfully though there is an EntityConnectionStringBuilder class to help out.

Tip 44 – How to navigate an OData compliant service

I recently did a crash course in Data Services and OData.

While doing so I realized my notes might be useful for you guys.

So here is my little cheat sheet to quickly get up to speed with OData Urls.

Note: OData Services may not necessarily support all of the following features: but if they do, this is how you would use them.

The Service:

It all starts with a Data Service hosted somewhere:

http://server/service.svc

Basic queries:

You access the Data Service entities through resource sets, like this:

http://server/service.svc/People

You request a specific entity using its key like this:

http://server/service.svc/People(16)

Or by using a reference relationship to something else you know:

http://server/service.svc/People(16)/Mother

This asks for person 16’s mother.

Once you have identified an entity you can refer to it’s properties directly:

http://server/service.svc/People(16)/Mother/Firstname

$value:

But the last query wraps the property value in XML, if you want just the raw property value you append $value to the url like this:

http://server/service.svc/People(16)/Mother/Firstname/$value

$filter:

You can filter resource sets using $filter:

http://server/service.svc/People?$filter=Firstname  eq ‘Fred’

Notice that strings in the filter are single quoted.

Numbers need no quotes though:

http://server/service.svc/Posts?$filter=AuthorId eq 1

To filter by date you have identity the date in the filter, like this:

http://server/service.svc/Posts?$filter=CreatedDate eq DateTime'2009-10-31'

You can filter via reference relationships:

http://server/service.svc/People?$filter=Mother/Firstname eq 'Wendy'

The basic operators you can use in a filter are:

There are also a series of functions that you can use in your filters if needed.

$expand:

If you want to include related items in the results you use $expand like this:

http://server/service.svc/Blogs?$expand=Posts

This returns the matching Blogs and each Blog’s posts.

$select:

Some Data Services allow you to limit the results to just the properties you require – aka projection – for example if you just want the Id and Title of matching Posts you would need something like this:

http://server/service.svc/Posts?$select=Id,Title

You can even project properties of related objects, so long as there is at most one of related object and you expand the relationship too, like this:

http://server/service.svc/Posts?$expand=Blog&$select=Id,Title,Blog/Name

This projects just the Id, Title and the Name of the Blog for each Post.

$count:

If you just want to know how many records would be returned, without retrieving them you need $count:

http://server/service.svc/Blogs/$count

Notice that $count becomes one of the segments of the URL – it is not part of the query string – so if you want to combine it with another operation like $filter you have to specify $count first, like this:

http://server/service.svc/Posts/$count?$filter=AuthorId eq 6

This query returns the number of posts authored by person 6.

$orderby:

If you need your results ordered you can use $orderby:

http://server/service.svc/Blogs?$orderby=Name

Which returns the results in ascending order, to do descending order you need:

http://server/service.svc/Blogs?$orderby=Name%20desc

To filter by first by one property and then by another you need:

http://server/service.svc/People?$orderby=Surname,Firstname

Which you can combine with desc if necessary.

$top:

If you want just the first 10 items you use $top like this:

http://server/service.svc/People?$top=10

$skip:

If you are only interested in certain page of date, you need $top and $skip together:

http://server/service.svc/People?$top=10&$skip=20

This tells the Data Service to skip the first 20 matches and return the next 10. Useful if you need to display the 3rd page of results when there are 10 items per page.

Note: It is often a good idea to combine $top & $skip with $orderby too, to guarantee the order results are retrieved from the underlying data source is consistent.

$inlinecount & $skiptoken:

Using $top and $skip allows the client to control paging.

But the server also needs a way to control paging – to minimize workload need to service both naive and malicious clients – the OData protocol supports this via Server Driven Paging.

With Server Driven Paging turned on the client might ask for every record, but they will only be given one page of results.

This as you can imagine can make life a little tricky for client application developers.

If the client needs to know how many results there really are, they can append the $inlinecount option to the query, like this:

http://server/service.svc/People?$inlinecount=allpages

The results will include a total count ‘inline’, and a url generated by the server to get the next page of results.

This generated url includes a $skiptoken, that is the equivalent of a cursor or bookmark, that instructs the server where to resume:

http://server/service.svc/People?$skiptoken=4

$links

Sometime you just need to get the urls for entities related to a particular entity, which is where $links comes in:

http://server/service.svc/Blogs(1)/$links/Posts

This tells the Data Service to return links – aka urls – for all the Posts related to Blog 1.

$metadata

If you need to know what model an OData compliant Data Service exposes, you can do this by going to the root of the service and appending $metadata like this:

http://server/service.svc/$metadata

This should return an EDMX file containing the conceptual model (aka EDM) exposed by the Data Service.

Wrap-up

This should help you get started.

I’ll be exploring Data Services and OData a lot more, and I’ll share with you what I learn, so stay tuned for more.

Tip 43 – How to authenticate against a Data Service

Problem:

When writing code against a Data Service, like say SharePoint, the client application must provide a valid set of credentials, or you will see a dreaded “401 Unathorized” response.

For Silverlight applications hosted on the same site as the DataService, this is generally handled for you automatically.

But WPF applications, for example, need manual intervension.

Solution:

The solution it to set the Credentials property on your DataServiceContext before you issue any queries or updates, like this:

Uri uri = new Uri("http://mflasko-dev/_vti_bin/listdata.svc"));
TeamSiteDataContext ctx = new TeamSiteDataContext(uri);
ctx.Credentials = System.Net.CredentialCache.DefaultCredentials;
…

That’s it.

Done.

Of course you can get fancy and provide another set of credentials if necessary, but generally the DefaultCredentials is what you want.

Interesting series of Posts exploring Code-Only

Daniel Wertheim left a couple of comments on Tip 42, that point to a series of posts he has written about using Code-Only to write clean code.

The series is really interesting as he delves into topics like: facades, relationships between non-public properties, integrating pluralization, using structure map etc.

All well worth a read, so check it out.

Nice job Daniel!

Tip 42 – How to create a dynamic model using Code-Only

Background:

When we give examples of how to use Code-Only we always start with a strongly typed Context derived from ObjectContext. This class is used to bootstrap the model.

For example this (property bodies omitted for simplicity sake):

public class MyContext : ObjectContext
{ 
   public ObjectSet<Category> Categories { get; }
   public ObjectSet<Product> Products { get; }
}

Tells CodeOnly that to bootstrap a model with 2 EntitySets, a set of Category entities called Categories, and a set of Product entities called Products.

Then if necessary you can further refine the model by manipulating the ContextBuilder.

Problem:

But what if you don’t have a strongly typed Context class?

What if you make a determination at runtime that you need a model, there isn't an appropriate strongly typed Context class lying around.

A customer asked me this very question earlier today.

Solution:

It turns out you can use ObjectContext directly. When you do this though Code-Only knows nothing about the model. But that isn’t that bad all you need to do is explicitly tell Code-Only about all the things it would normally learn from the strongly typed context.

For example this:

public ObjectSet<Person> People { get; }

Can be converted into this:

var builder = new ContextBuilder<ObjectContext>();
builder.RegisterSet<Person>(“People”);

Nifty huh?

End to end example:

This example, persists a Person (BillG) to the database and retrieves it again, all without a strongly typed ObjectContext:

First the Person class (which is POCO):

public class Person
{
    public int ID { get; set; }
    public string Firstname { get; set; }
    public string Surname { get; set; }
}

And now the code to setup the ObjectContext:

// Create the contextbuilder, and tell it about the People set.
var builder = new ContextBuilder<ObjectContext>();
builder.RegisterSet<Person>("People");

// Create a connection
string connstr = @"Data Source=.\SQLEXPRESS;Initial Catalog=PeopleDb;Integrated Security=True;Pooling=False;MultipleActiveResultSets=True";
var conn = new SqlConnection(connstr);

// Create an ObjectContext from the builder
using (ObjectContext ctx = builder.Create(conn))
{
    // Create the database if it doesn’t already exist
    if (!ctx.DatabaseExists())
       ctx.CreateDatabase();

    // Create Bill
    Person p = new Person {
        ID = 1,
        Firstname = "Bill",
        Surname = "Gates"
    };

    // Add Bill to the context
    // UPDATE: thanks to danny for the simplification
    ctx.CreateObjectSet<Person>().AddObject(p);
 using the general purpose
    // AddObject method.
    // The only tricky part is the EntitySet name with must 
    // be qualified with the the container name,  
    // in this case is ObjectContext.
    ctx.AddObject("ObjectContext.People", p);
    ctx.SaveChanges();

    // Issue a query against the People set.
    var bill = (from person in ctx.CreateObjectSet<Person>()
                where person.Firstname == "Bill"
                select person).Single();

    // Make and Save a change.
    bill.Firstname = "William";
    ctx.SaveChanges();
}

Pretty easy considering it isn’t strongly typed.

Tip 41 – How to execute T-SQL directly against the database

Sometimes you’ll find you need to issue a query or command that the Entity Framework can’t support. In fact this problem is common to most ORMs, which is why so many of them have a backdoor to the database.

The Entity Framework has a backdoor too…

.NET 3.5 SP1

In .NET 3.5 SP1 you can get to the underlying database connection through your ObjectContext.

Calling ObjectContext.Connection returns an IDbConnection, but not the one we need, it is an EntityConnection. However the EntityConnection has a StoreConnection property that does return what we need:

var entityConn = ctx.Connection as EntityConnection;
var dbConn = entityConn.StoreConnection as SqlConnection;

Once you have this connection you are free to issue a query or command in the normal ADO.NET way:

dbConn.Open();
var cmd = new SqlCommand("SELECT * FROM PRODUCTS", dbConn );
using (var reader = cmd.ExecuteReader())
{
    while (reader.Read())
    {
        Console.WriteLine("Product: ID:{0} Name:{1} CategoryID:{2}",
            reader[0].ToString(),
            reader[1].ToString(),
            reader[2].ToString()
        );
    }
}
dbConn.Close();

Pretty easy huh?

.NET 4.0:

In .NET 4.0 this is even better. There are 2 new methods hanging directly off ObjectContext.

• ExecuteStoreCommand(..) for executing commands
• ExecuteStoreQuery<T>(..) for executing queries

Using ExecuteStoreQuery<T>(..)

If you use ExecuteStoreQuery<T>(..), the EF will create and populate instances of the T for you. So you can write code like this:

foreach(var product in ctx.ExecuteStoreQuery<Product>(sql))
{
        Console.WriteLine("Product: ID:{0} Name:{1} CategoryID:{2}",
            product.Id,
            product.Name,
            product.CategoryId 
        );
}

To get this to work the Column names returned by the query must match the property names in the class and the class must have a default constructor. But the class doesn’t even need to be an Entity.

So for example if you have this class:

public class ProductView
{
    public int ID { get; set; }
    public string Name { get; set; }
    public string CategoryName { get; set; }
}

To materialize instances of it you just need to write your SQL such that it has ID, Name and CategoryName columns.

i.e. Something like this:

string SQL = @"SELECT P.ID, P.Name, C.Name AS CategoryName
               FROM Products P
               JOIN Categories C
               ON P.CategoryID = C.ID";

foreach (var pv in ctx.ExecuteStoreQuery<ProductView>(SQL))
{
    Console.WriteLine("{0} {1} {2}", 
                       pv.ID, 
                       pv.Name,
                       pv.CategoryName
    );
}

Now of course, this example is for demonstration purposes only, normally the query would be much more complicated, i.e. something that LINQ to Entities can’t handle natively.

In this particular example you could easily do this using standard LINQ to Entities code, see Tip 40 if you are wondering how.

Editing Entities returned from ExecuteStoreQuery<T>(..)

If the classes being created are indeed Entities, and you want to edit them, you have to provide a little more information:

var productToEdit = ctx.ExecuteStoreQuery<Product>(sql,
      "Products",
       MergeOption.PreserveChanges
).Single();
productToEdit.CategoryId = 6;
ctx.SaveChanges();

Here the second parameter is the name of the EntitySet that the Product belongs too, and the third parameter tells the EF how to merge these entities with any duplicates that might already be in the ObjectContext.

If you do this right, calling SaveChanges() will flush the changes made to productToEdit back to the database.

Using ExecuteStoreCommand():

This one is very simple, you issue some command, for example a batch update method, and you will get back whatever the underlying provider returns, typically the number of rows affected:

// 10% inflation day!
ctx.ExecuteStoreCommand(
  "UPDATE Products SET Price = Price * 1.1"
);

It is that simple.

Enjoy.

Tip 40 – How to materialize presentation models via L2E

Problem:

Imagine that you have these entities

public class Product
{
    public int ID { get; set; }
    public string Name { get; set; }
    public virtual Category Category { get; set; }
}

But for your UI you want to display the product id, product name and the product’s category’s name.

You might try passing this query to the presentation layer.

var displayData = from product in ctx.Products.Include("Category")
                  select product;

But then you are passing more than is required around, and binding the UI tightly to the conceptual model, which is probably a bad idea.

Your next try might be a query like this:

var displayData = from product in ctx.Products
                  select new {
                     ID = product.ID, 
                     Name = product.Name, 
                     CategoryName = product.Category.Name 
                  };

But then you’ll quickly discover you can’t pass the anonymous types to another method, at least not without a nasty hack.

Solution: 

Most people think LINQ to Entities can only materialize Entities and Anonymous types.

But in fact it turns out it can materialize any non-generic class that has a default constructor.

Anyway what this means is you can create a view class like this:

public class ProductView
{
    public int ID { get; set; }
    public string Name { get; set; }
    public string CategoryName { get; set; }
}

And then write this:

And then pass this to your view without any problems at all.

I only just found out about this myself, I had always assumed this would fail.

So this was a nice pleasant surprise.

Tip 39 – How to set overlapping Relationships – EF 4.0 only

Scenario:

In EF 4 we have FK relationships, available for the first time in .NET 4.0 Beta 2, so it is now possible to have a model something like this:

public class Division
{
   public int DivisionID {get;set} // Primary Key
   public string Name {get;set;}
   public virtual List<Lawyer> Lawyers {get;set;}
   public virtual List<Unit> Units {get;set;}
}
public class Lawyer
{
   public int LawyerID {get;set;} // Primary Key
   public int DivisionID {get;set;} // Primary Key + FK to Division
   public string Name {get;set;}
   public virtual Division Division {get;set;}
   public virtual List<Unit> Units {get;set;}
}
public class ProductTeam
{
    public int ProductID {get;set;} // Primary Key
    public int? DivisionID {get;set;} // FK to Division & Lawyer
    public int? LawyerID {get;set;} // FK to Lawyer
    public string Name {get;set;}
    public virtual Division Division {get;set;}
    public virtual Lawyer Lawyer {get;set;}
}

Notice that the Lawyer has a Compound key made up of both the LawyerID and DivisionID.

This makes things interesting when you start manipulating the ProductTeam class, which has both Lawyer and Division references and the necessary backing FK properties.

If you do something like this:

var team = (from t in ctx.ProductTeams
                  where t.Lawyer.Name == “Fred Bloggs”
                  select t).FirstOrDefault();
team.Lawyer = null;
ctx.SaveChanges();

What should this do exactly?

Does this mean clear team.LawyerID & team.DivisionID or just team.LawyerID?

From the relational standpoint nulling any FK property is sufficient to make the relationship go away.

Hmm…

It can be very hard to workout exactly what the user intended, so rather than introduce some magic rules based on naming conventions etc, the EF uses a consistent rule that you can rely on:

When the users sets a reference relationship to null, the EF nulls every nullable FK property that backs the relationship, whether that FK participates in another relationship or not.

Problem:

So in this case the EF nulls both DivisionID and LawyerID, because both back the Lawyer navigation property.

Which means nulling the Lawyer *also* nulls the Division.

Did you really want to do that?

Maybe maybe not.

Solution:

If you only wanted to null out the Lawyer, you have a couple of options:

Change the model so that the DivisionID FK isn’t nullable, in this case the EF can only null out the LawyerID, so the relationship to the Division would be left intact.

But a solution that changes the model isn’t always possible, what if the Division really needs to be nullable too?

The better option is to simply manipulate the relationships through the FK properties directly:

This leaves the DivisionID & Division untouched, as desired.

Tip 38 – How to use CodeOnly with Astoria

The normal way that you create an ADO.NET Data Services (aka Astoria) Service is by creating a class that derives from DataService<T>.

public class BloggingService : DataService<BloggingEntities>

And if you want to use Entity Framework under the hood the T you supply must derive from ObjectContext.

Now this works great most of the time, but not with CodeOnly, and here’s why.

Under the hood the DataService constructs an instance of the BloggingEntities, and gets the model from it, via its MetadataWorkspace.

The problem is if you’ve configured the model using Code-Only, the only way to construct the BloggingEntities is via the Code-Only ContextBuilder, which Astoria knows nothing about.

Hmm….

Thankfully there is a very simple workaround, you simply override CreateDataSource() on DataService<T> like this:

protected override BloggingService CreateDataSource()
{
    //Code-Only code goes here:
    var contextBuilder = GetConfiguredContextBuilder();
    var connection = GetSqlConnection();
    return contextBuilder.Create(connection);
}

As you can see this is pretty simple.

Fine Print

For performance reasons it is important to avoid the cost of re-configuring the ContextBuilder each time, so the GetConfiguredContextBuilder() method should create and configure the builder only once, and cache the builder for subsequent calls.

Caveats

This tip will only work on .NET 4.0 Beta 2 and above.

Code-Only only works on .NET 4.0 and is only available as a separate download. ADO.NET Data Services (aka Astoria) is *going* to ship as part of .NET 4.0, but isn’t in Beta1, so you can’t use CodeOnly with Astoria yet, you have to wait for Astoria to show up in .NET 4.0 in Beta2, and you will probably want to wait for another drop of Code-Only too.

Which simply means you will have a wait a little while before you can try this tip out.

Code-Only best practices

There have been lots of posts on the EFDesign blog talking about how the features in Code Only have evolved.

But very little covering what we think will be the best way to write the code.

You may have seen code like this:

var prodConf = builder.Entity<Product>();
productConf.HasKey(p => p.Id);
productConf.Property(p => p.Id).IsIdentity();
productConf.Property(p => p.Name)
           .HasMaxLength(100);
productConf.Relationship(p => p.Category)
           .FromProperty(c => c.Products)
           .HasConstraint((p,c) => p.CategoryId == c.Id);
productConf.MapSingleType(p =>
            new {
                Id = p.Id, 
                Nme = p.Name, 
                cid = p.CategoryId
            })
            .ToTable(“dbo.Products”);

And thought that this is what we recommend you write.

Well actually it isn’t.

I’m not making excuses here, but the problem is that sometimes showing best practices gets in the way of explaining features, its so much easier to just show the feature in isolation. 

And there is nothing wrong with that… so long as you go back later and give guidance.

So here goes:

The recommended way to do this is to create classes that derive from EntityConfiguration<TEntity> and put the configuration logic in the constructor like this:

public class ProductConfiguration: EntityConfiguration<Product>
{
    public ProductConfiguration()
    {
        HasKey(p => p.Id);
        Property(p => p.Id).IsIdentity();
        Property(p => p.Name).HasMaxLength(100);
        Relationship(p => p.Category)
            .FromProperty(c => c.Products)
            .HasConstraint((p,c) => p.CategoryId == c.Id);
        MapSingleType(p =>
             new {
                  Id = p.Id, 
                  Nme = p.Name, 
                  cid = p.CategoryId
             })
             .ToTable(“dbo.Products”);
    }
}

Next when you want to use this configuration you simply register it:

builder.Configurations.Add(new ProductConfiguration());
// & repeat for all types you want to configure

This nicely encapsulates the configuration and simplifies the code too.

Tip 37 – How to do a Conditional Include

Problem

Someone asked how to do a Conditional Include a couple of days ago on StackOverflow.

They wanted to query for some entity (lets say Movies) and eager load some related items (lets say Reviews) but only if the reviews match some criteria (i.e. Review.Stars == 5).

Unfortunately though this isn’t strictly supported by EF’s eager loading, i.e. ObjectQuery<Movie>.Include(…) because Include(..) is all or nothing.

Solution

But there is a workaround.

Here is an example scenario to make this ‘real’:

public class Movie
{
    public int ID {get;set;}
    public string Name {get;set;}
    public string Genre {get;set;}
    public List<Review> Reviews {get;set;}
}

public class Review
{
    public int ID {get;set;}
    public int Stars {get;set;} 

    public string Summary {get;set;}
    public Movie Movie {get;set;}
    public User User {get;set;}
}

Imagine if you want to retrieve all ‘Horror’ movies and all of their 5 star reviews.

You can do so like this:

var dbquery =
   from movie in ctx.Movies
   where movie.Genre == “Horror”
   select new {
      movie, 
      reviews = from review in movie.Reviews
                where review.Stars == 5
                select review
   };

var movies = dbquery
   .AsEnumerable()
   .Select(m => m.movie);

Now why does this work?

Well the first query creates a new instance of an anonymous type containing each Horror movie and just it’s 5 star ratings.

The second query runs in memory using LINQ to Objects thanks to the AsEnumerable() call, and simply ‘unwraps’ the movie from its anonymous type wrapper.

And interestingly each movie will also have just it’s five star reviews loaded!

So this code:

foreach(var movie in movies)
{
    foreach(var review in movie.Reviews)
        Assert(review.Rating == 5);
}

Will pass with no Asserts.

This works because EF implements something called relationship fix-up.

Relationship fix-up ensures that related objects are automatically linked when the second entity enters the ObjectContext.

And because we are loading both the Movie and a filtered list of it’s Reviews, both enter the ObjectContext, and EF makes sure they are automatically linked, which means the matching Reviews are present in the appropriate Movie.Reviews collection.

i.e. Conditional Include.

There are number of different twists on this theme:

• Issue two separate queries: one for the Movies, one for the Reviews, and let relationship fix-up do the rest.
• Issue a select many type query as shown here.
• Sorting relationship - see Tip1

Once you understand how relationship fix-up works you can really use it to your advantage.

Enjoy.

Tagging File Systems?

I had an interesting in passing conversation with someone today about tagging file systems… which reminded me of my own sordid past.

Before I joined Microsoft, before I was an MVP, before I released Base4, I wrote something called XTend.

At the time I called it a relational file system, but this was back in 2000, today it’d be a ‘tagging’ file-system.

I personally find it strange that fully 9 years later, I still can’t find a viable tagging / relational file system.

See I for one want more than just ‘keyword search’ to help with the ‘finding found things’ problem.

Anyone else feel the same way?

Tip 36 – How to Construct by Query

While writing my tips series and writing EF controllers for MVC I found that I regularly wanted to create and attach a stub entity.

Unfortunately it isn’t quite that simple, you have to make sure the entity isn’t already attached first, otherwise you’ll see some nasty exceptions.

To avoid these exceptions I often found myself having to write code like this:

Person assignedTo = FindPersonInStateManager(ctx, p => p.ID == 5);
if (assignedTo == null)
{
     assignedTo = new Person{ID = 5};
     ctx.AttachTo(“People”, assignedTo); 
}
bug.AssignedTo = assignedTo;

But that code is cumbersome, it pollutes my business logic with a whole heap of EF plumbing, which makes it hard to read and write.

I found myself wishing I could write code like this instead:

bug.AssignedTo = ctx.People.GetOrCreateAndAttach(p => p.ID == 5);

Now there is a lot of plumbing to make this possible, but the core problem is converting something like this:

(Person p) => p.ID == 5;

which is a predicate or query into something like this

() => new Person {ID = 5};

which is a LambdaExpression with a MemberInitExpression body.

Query By Example

Now those of you familiar with the history of ORMs might remember that in the ‘good old days’ a lot of ‘ORMs’ used a pattern called Query by Example:

Person result = ORM.QueryByExample(new Person {ID = 5});

With Query by Example you create an instance of the thing you want back from the database, fill in some fields, and the ORM uses this example object to create a query based on the values that have been set.

Construct By Query?

I bring this up, because the process of going from a Query to an Instance, looks like the exact opposite of going from an Instance to a Query (aka Query by Example).

Hence the title of this blog post: ‘Construct by Query’.

For me this analogy / comparison makes this idea all the more beautiful.

But hey that’s me!

Implementation

Anyway… so how do we actually do this:

Well first the plumbing, we need a method to look for an entity in the ObjectStateManager:

public static IEnumerable<TEntity> Where<TEntity>(
    this ObjectStateManager manager,
    Func<TEntity, bool> predicate
) where TEntity: class
{
    return manager.GetObjectStateEntries(
        EntityState.Added |
        EntityState.Deleted |    
        EntityState.Modified |
        EntityState.Unchanged
    )
   .Where(entry => !entry.IsRelationship)
   .Select(entry => entry.Entity)
   .OfType<TEntity>()
   .Where(predicate);
}

Then we actually write the GetOrCreateAndAttachStub(…) extension method:

public static TEntity GetOrCreateAndAttachStub<TEntity>(
    this ObjectQuery<TEntity> query,
    Expression<Func<TEntity, bool>> expression
) where TEntity : class
{
    var context = query.Context;
    var osm = context.ObjectStateManager;
    TEntity entity = osm.Where(expression.Compile())
                        .SingleOrDefault();

    if (entity == null)
    {
        entity = expression.Create();
        context.AttachToDefaultSet(entity);
    }
    return entity;
}

This looks in the ObjectStateManager for a match.

If nothing is found it converts the predicate expression into an LambdaExpression with a MemberInitExpression body, which is then compiled and invoked to create an instance of TEntity and attach it.

I’m not going to go into the AttachToDefaultSet method because I’ve shared the code for that previously in Tip 13.

So lets skip that and get right to…

The guts of the problem

The Create extension method, looks like this:

public static T Create<T>(
    this Expression<Func<T, bool>> predicateExpr)
{
    var initializerExpression = PredicateToConstructorVisitor
                                    .Convert<T>(predicateExpr);
    var initializerFunction = initializerExpression.Compile();
    return initializerFunction();
}

Where PredicateToConstructorVisitor is a specialized ExpressionVisitor that just converts from a predicate expression to an MemberInitExpression.

public class PredicateToConstructorVisitor
{
    public static Expression<Func<T>> Convert<T>(
        Expression<Func<T, bool>> predicate)
    {
        PredicateToConstructorVisitor visitor = 
           new PredicateToConstructorVisitor();
        return visitor.Visit<T>(predicate);
    }
    protected Expression<Func<T>> Visit<T>(
        Expression<Func<T, bool>> predicate)
    {
        return VisitLambda(predicate as LambdaExpression) 
           as Expression<Func<T>>;
    }
    protected virtual Expression VisitLambda(
        LambdaExpression lambda)
    {
        if (lambda.Body is BinaryExpression)
        {
            // Create a new instance expression i.e.
            NewExpression newExpr =
               Expression.New(lambda.Parameters.Single().Type);

            BinaryExpression binary = 
               lambda.Body as BinaryExpression;

            return Expression.Lambda(
                    Expression.MemberInit(
                        newExpr,
                        GetMemberAssignments(binary).ToArray()
                    )
                );
        }
        throw new InvalidOperationException(
            string.Format(
               "OnlyBinary Expressions are supported.\n\n{0}",
               lambda.Body.ToString()
            )
        );
    }

    protected IEnumerable<MemberAssignment> GetMemberAssignments(
         BinaryExpression binary)
    {
        if (binary.NodeType == ExpressionType.Equal)
        {
            yield return GetMemberAssignment(binary);
        }
        else if (binary.NodeType == ExpressionType.AndAlso)
        {
            foreach (var assignment in 
              GetMemberAssignments(binary.Left as BinaryExpression).Concat(GetMemberAssignments(binary.Right as BinaryExpression)))
            {
                yield return assignment;
            }
        }
        else
            throw new NotSupportedException(binary.ToString());
    }

    protected MemberAssignment GetMemberAssignment(
        BinaryExpression binary)
    {
        if (binary.NodeType != ExpressionType.Equal)
            throw new InvalidOperationException(
               binary.ToString()
            );

        MemberExpression member = binary.Left as MemberExpression;
       
        ConstantExpression constant
           = GetConstantExpression(binary.Right);

        if (constant.Value == null)
            constant = Expression.Constant(null, member.Type);

        return Expression.Bind(member.Member, constant);
    }

    protected ConstantExpression GetConstantExpression(
        Expression expr)
    {
        if (expr.NodeType == ExpressionType.Constant)
        {
            return expr as ConstantExpression;
        }
        else
        {
            Type type = expr.Type;

            if (type.IsValueType)
            {
                expr = Expression.Convert(expr, typeof(object));
            }

            Expression<Func<object>> lambda
               = Expression.Lambda<Func<object>>(expr);

            Func<object> fn = lambda.Compile();

            return Expression.Constant(fn(), type);
        }
    }
}

The real work is done in VisitLambda.

Basically it throws if:

1. The LambdaExpression isn’t a BinaryExpression.
2. There is more than one parameter to the LambdaExpression. We can only construct one thing!

Then we go about the job of walking the BinaryExpression until we get to Equal nodes i.e. (p.ID == 5) which we convert to MemberAssignments (ID = 5) so we can construct a MemberInitExpression.

When creating the MemberAssignments we convert all Right hand-sides to constants too. i.e. so if the lambda looks like this:

(Person p) => p.ID == GetID();

we evaluate GetID(), so we can use the result in our MemberAssignment.

Summary

Again I’ve demonstrated that mixing EF Metadata and CLR Expressions makes it possible to write really useful helper methods that take a lot of the pain out writing your apps.

Enjoy…

Tip 35 – How to write OfTypeOnly<TEntity>()

If you write a LINQ to Entities query like this:

var results = from c in ctx.Vehicles.OfType<Car>()
              select c;

It will bring back, Cars and any type that derives from Car, like say SportCar or SUV.

If you just want Cars and you don’t want derived types like SportCars or SUVs in LINQ to Objects you would write something like this:

var results = from c in vehiclesCollection
              where c.GetType() == typeof(Car)
              select c;

But unfortunately LINQ to Entities doesn’t know how to translate this.

NOTE:
In Entity SQL this is actually pretty easy. The OFTYPE(collection, [ONLY] type) function will exclude derived types if you include the optional ONLY keyword.

For example this Entity SQL:

SELECT VALUE(C)
FROM Container.Vehicles AS C
WHERE C IS OF(ONLY Model.Car)

will only return Cars: entities derived from Car, like say SUVs, will be filtered out.

Now about six months back, in Tip 5, I showed you a work-around. You basically had to write something like this:

But this solutions is cumbersome and error prone, so I decided I wanted a better solution.

You should be able to write something like this:

var results = from c in ctx.Vehicles.OfTypeOnly<Car>()
              select c;

Now under the hood this method has to:

1. Take the source ObjectQuery and call OfType<Car>() to get an ObjectQuery<Car>()
2. Figure out which EntityTypes derive from Car
3. Construct an LambdaExpression to exclude each of those Derived types from the result.
4. Take the ObjectQuery<Car> and call Where(Expression<Func<Car,bool>>) on it using the LamdaExpression

So lets look at what the code looks like.

This is the function that pulls everything together:

public static IQueryable<TEntity> OfTypeOnly<TEntity>(
    this ObjectQuery query)
{
    query.CheckArgumentNotNull("query");

    // Get the C-Space EntityType
    var queryable = query as IQueryable;
    var wkspace = query.Context.MetadataWorkspace;
    var elementType = typeof(TEntity);

    // Filter to limit to the DerivedType of interest
    IQueryable<TEntity> filter = query.OfType<TEntity>();

    // See if there are any derived types of TEntity
    EntityType cspaceEntityType =
        wkspace.GetCSpaceEntityType(elementType);

    if (cspaceEntityType == null)
        throw new NotSupportedException("Unable to find C-Space type");

    EntityType[] subTypes = wkspace.GetImmediateDescendants(cspaceEntityType).ToArray();

    if (subTypes.Length == 0) return filter;

    // Get the CLRTypes.
    Type[] clrTypes = subTypes
         .Select(st => wkspace.GetClrTypeName(st))
         .Select(tn => elementType.Assembly.GetType(tn))
         .ToArray();

    // Need to build the !(a is type1) && !(a is type2) predicate and call it
    // via the provider
    var lambda = GetIsNotOfTypePredicate(elementType, clrTypes);
    return filter.Where(
          lambda as Expression<Func<TEntity, bool>>
    );
}

As you can see we are using an extension method on MetadataWorkspace called GetCSpaceEntityType() that takes a CLR type and returns the corresponding EntityType.

It looks like this:

public static EntityType GetCSpaceEntityType(
     this MetadataWorkspace workspace,
     Type type)
{
    workspace.CheckArgumentNotNull("workspace");
    // Make sure the metadata for this assembly is loaded.
    workspace.LoadFromAssembly(type.Assembly);
    // Try to get the ospace type and if that is found
    // look for the cspace type too.
    EntityType ospaceEntityType = null;
    StructuralType cspaceEntityType = null;
    if (workspace.TryGetItem<EntityType>(
        type.FullName,
        DataSpace.OSpace,
        out ospaceEntityType))
    {
        if (workspace.TryGetEdmSpaceType(
            ospaceEntityType,
            out cspaceEntityType))
        {
            return cspaceEntityType as EntityType;
        }
    }
    return null;
}

If this method looks familiars, it is, I introduced it in Tip 13. In fact this method is a very handy for your EF toolbox.

Once we have the EntityType we can look for derived EntityTypes, which is where the GetImmediateDescendants() method comes in. It looks like this:

public static IEnumerable<EntityType> GetImmediateDescendants(
       this MetadataWorkspace workspace,
       EntityType entityType)
{
    foreach (var dtype in workspace
                 .GetItemCollection(DataSpace.CSpace)
                 .GetItems<EntityType>()
                 .Where(e =>
                      e.BaseType != null && 
                      e.BaseType.FullName == entityType.FullName))
    {
        yield return dtype;
    }
}

NOTE: I’m only interested in immediate descendants because when the immediate descendants are filtered out, their descendants will also get filtered out.

Next we need to get the CLR types for each of those EntityTypes. To do this I have a function that uses the EF metadata to find the CLR typename for each EntityType, which looks like this:

public static string GetClrTypeName(
     this MetadataWorkspace workspace,
     EntityType cspaceEntityType)
{
    StructuralType ospaceEntityType = null;

    if (workspace.TryGetObjectSpaceType(
            cspaceEntityType, out ospaceEntityType))
        return ospaceEntityType.FullName;
    else
        throw new Exception("Couldn’t find CLR type");
}

You can then compose this method with some code to get the CLR type for a particular typename.

Now writing something fool proof can get complicated, but in my case I’m just assuming all the Types are in the same assembly as TEntity. Which makes things very easy:

// Get the CLRTypes.
Type[] clrTypes = subTypes
      .Select(st => wkspace.GetClrTypeName(st))
      .Select(tn => elementType.Assembly.GetType(tn))
      .ToArray();

… and I’m pretty sure you can figure out how to make this a little more robust if necessary :)

At this point we leave the EF metadata APIs behind and move over to the Expression APIs,

Gulp!

Actually its a lot easier that I thought it was going to be.

We just need a lambda expression that will filter out all the derived CLR types. The equivalent of this:

(TEntity entity) => !(entity is TSubType1) && !(entity is TSubType2)

So I added this method, the first parameter is the type of the lambda parameter, and then you pass is all the types you want to exclude:

public static LambdaExpression GetIsNotOfTypePredicate(
       Type parameterType, 
       params Type[] clrTypes)
{
    ParameterExpression predicateParam =
               Expression.Parameter(parameterType, "parameter");

    return Expression.Lambda(
               predicateParam.IsNot(clrTypes),
               predicateParam
    );
}

As you can see this creates a parameter, and then calls another extension method to create the AndAlso expression needed:

public static Expression IsNot(
    this ParameterExpression parameter, 
    params Type[] types)
{
    types.CheckArgumentNotNull("types");
    types.CheckArrayNotEmpty("types");

    Expression merged = parameter.IsNot(types[0]);
    for (int i = 1; i < types.Length; i++)
    {
        merged = Expression.AndAlso(merged,
            parameter.IsNot(types[i]));
    }
    return merged;
}

public static Expression IsNot(
    this ParameterExpression parameter,
    Type type)
{
    type.CheckArgumentNotNull("type");

    var parameterIs = Expression.TypeIs(parameter, type);
    var parameterIsNot = Expression.Not(parameterIs);
    return parameterIsNot;
}

As you can see the first overload loops over the types and creates an IsNot expression (by calling the second overload) and merges it with the previously created expression, by creating an AndAlso expression.

NOTE: You may have noticed that this code is going to produce a deep AndAlso graph. I think this is probably fine, but if you have a particularly wide type hierarchy you might want to look at rewriting this query to balance the graph.

So by now we have a way to create a LambdaExpression that does the necessary filtering, all we need is cast it to Expression<Func<TEntity, bool>> and pass it to the Where(..) extension method, like this:

And we’re done!

Now I’m the first to admit this isn’t exactly ‘easy peasy lemon squeezy’, but I enjoyed developing this solution, it forced me to learn a little more about Expressions and EF metadata APIs.

Hopefully you’ve found it interesting too.