Meta-Me

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 class Context?

What if you make a determination at runtime that you need a model, but you haven’t got 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 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.

Tip 34 – How to work with Updatable Views

UPDATE: thanks Zeeshan for pointing out that by default only non-nullable columns end up in the key for view backed entities.

Imagine this situation, you have a view in your database, and it is updatable.

Next you decide to use this view with the Entity Framework, so you go ahead and import it.

The resulting entity will look something like this:

As you can see every property icon has a ‘key’ overlay.

Because the entity is based on a view, the EF doesn’t know which columns make up the primary key, so it assumes every non nullable column is part of the primary key.

Fixing the Key

The first step is to change the key. In this case the ID is really the key.

You can do this by opening the EDMX in the XML editor and changing the EntityType so that rather than looking like this:

Where every property is referenced in the <Key>, change it to this:

 

It is important to note that you have to make this change in both the <edmx:StorageModels> and the <edmx:ConceptualModels> sections of the EDMX, because both models must agree about the shape of the primary key.

Treat the view as a table

At this point you can happily query for Employees use the Entity Framework.

But the Entity Framework won’t allow you to do updates.

The normal way around this is to create stored procedures and use them as modification functions.

But given that the view is already updatable that is obviously not ideal.

Luckily there is a workaround: Simply convince the EF that the view is a table.

To do this you have to change the definition of the EntitySet in the StorageModel. Generally it will start off looking like this:

<EntitySet Name="Employees" 
           EntityType="Tip34Model.Store.Employees"
           store:Type="Views"
           store:Schema="dbo"
           store:Name="Employees">
  <DefiningQuery>SELECT
  [Employees].[ID] AS [ID],
  [Employees].[Firstname] AS [Firstname],
  [Employees].[Surname] AS [Surname],
  [Employees].[Email] AS [Email]
  FROM [dbo].[Employees] AS [Employees]
  </DefiningQuery>
</EntitySet>

In order to treat it as a table replace that with this:

<EntitySet Name="Employees" 
           EntityType="Tip34Model.Store.Employees" 
           store:Type="Tables"
           Schema="dbo" />

Now you can perform every CRUD operation.

Pretty easy if you ask me.

Tip 33 – How cascade delete really works in EF

Imagine that in your database you have a cascade delete on an FK relationship.

Something like this:

Here the Delete Rule says that when a Category is deleted all the related Products should be deleted too.

If you generate an EF model from this database you get a model that on the surface looks no different from normal:

But if you dig into the CSDL section of the XML you will see this:

<Association Name="FK_Products_Categories">
       <End Role="Categories" Type="TipsModel.Store.Categories" Multiplicity="1">
              <OnDelete Action="Cascade" />
       </End>
       <End Role="Products" Type="TipsModel.Store.Products" Multiplicity="*" />
       <ReferentialConstraint>
              <Principal Role="Categories">
                    <PropertyRef Name="ID" />
              </Principal>
              <Dependent Role="Products">
                    <PropertyRef Name="CategoryID" />
              </Dependent>
       </ReferentialConstraint>
</Association>

Notice the <OnDelete> element, this tells the EF that when a Category is deleted the related Products *will* be too.

I deliberately said *will* and rather than *should*, because the EF does not take responsibility for cascading the delete in the database.

The EF is responsible for the correctness of the ObjectContext after SaveChanges(). So the EF attempts to synchronize the ObjectContext, with the expected database state after the expected cascade in the database.

A tell tale sign of this is that if you open up something like SqlProfiler, you will notice the EF issuing DELETE requests for dependent entities that it knows about (i.e. that are loaded in the ObjectContext) when a principal is deleted.

Essentially what is happening here is that the Entity Framework expects that deleting the principal in the database, will delete all it’s dependents in the database. So it issues, what should be, a redundant DELETE to request itself so the dependents already loaded are deleted from the ObjectContext.

The key thing to note is that the EF *does not* retrieve all the dependent entities and issue deletes for them: It only deletes dependents that are already in memory.

So here are the golden rules:

1. If you add an Cascade delete rule to the model, you MUST have a corresponding DELETE rule in the database.
2. If you absolutely insist on breaking rule (1) for some reason, Cascade will only work if you have all the dependents loaded in memory.
3. (2) is *not* recommended!!!

While we do our best to keep the ObjectContext and database in sync, our attempts can fail if you have multiple levels of cascade delete.

For example, if you have this:

Category –> Product –> Order

And deleting a Category deletes its Products which in turn deletes its Orders.

The EF can, in rare circumstances, fail to sync up with the database when you delete a Category.

For example if you have an Order loaded that is related to a Category via an unloaded Product, and you delete the Category,the EF won’t know to delete the Order.

This means the Order will remain in the ObjectContext in the unchanged state, despite it having been deleted in the database.

Forewarned is forearmed.

Tip 32 – How to create a database from SSDL – EF 4 only

We recently released a CTP that extends EF 4 Beta 1 which included Code Only.

You can read more about Code Only here, here and here.

If you look at the walkthroughs for Code Only you will see code that looks something like this:

// Create a builder and configure it
var builder = new ContextBuilder<MyContext>();
…

// Create a context
var mycontext = builder.Create(sqlConnection);

// Prepare the Context
if (!myContext.DatabaseExists())
   myContext.CreateDatabase();

CreateDatabase(), DropDatabase(), DatabaseExists() and CreateDatabaseScripts() are all extension methods that ship in the Code Only assembly.

And here’s the neat thing: These extension methods are orthogonal to the rest of Code Only.

You can use these extension methods with any ObjectContext, regardless of whether Code-Only was used to create it.

So you can call these methods on *any* ObjectContext.

Imagine this scenario: Someone else on your team has checked-in an EDMX as part of the project, but when you check out you find their are no database scripts. You could now use Code-Only to create a local database.

These methods look at the database model described in ObjectContext.MetadataWorkspace, and for example produce and execute DDL.

Cool huh?

Now as always this tip comes with some caveats*:

1. At the moment this only works with EF 4 Beta1. We will ship new versions of Code Only to work with later versions of EF 4 as and when they drop.
2. CreateDatabase() doesn’t know how to handle everything in the storage model. So for example if your EDMX references database Views or Stored Procedures, Code Only won’t know how to fabricate the database equivalents.
3. At the moment this only works with SQL Server. We have plans to add a provider model to Code-Only, but that is not there yet.

Despite these limitations, there are doubtless situations where CreateDatabase() and its chums will be useful.

So enjoy!

*It wouldn’t be a tip without caveats :)

Tip 31 – How to compose L2O and L2E queries

Imagine you want to write a query like this:

var possibleBuyers=
     from p in ctx.People
     where p.Address.City == “Sammamish” && InMarketForAHouse(p)
     select p;

Now theoretically this is possible so long as there is a SQL translation for InMarketForAHouse. 

In EF 4.0 you do this by creating a CLR stub for the required Model or Database function.

Imagine however there is no SQL translation.

Perhaps the function needs to use all sort of things that aren’t in the database.

Now you have to ‘partition’ your query. I.e. separate the query into a base LINQ to Entities query and a dependent LINQ to Objects query.

You might try something like this:

var partialFilter = from p in ctx.People
                    where p.Address.City == “Sammamish”
                    select p;

var possibleBuyers = from p in partiallyFilter
                     where InMarketForAHouse(p);
                     select p;

But this has virtually no effect on the behavior of the code. The IQueryable (ctx.People) will still be asked to translate InMarketForAHouse(..).

You need a call to AsEnumerable(), which effectively isolates the two sections of the query:

AsEnumerable() ensures that LINQ to Objects handles all subsequent requests. So the LINQ to Entities provider (i.e. ctx.People) never sees InMarketForAHouse(..).

Now of course there are some caveats.

While the final query might yield only a few records, the query actually sent to the database might return a LOT of data.

So you need to think about what is happening here.

Ask yourself questions like: How much data am I getting from the database?

You might even be okay with *enumerating* a lot of records.

The ‘problem’ is that by default you aren’t just enumerating. The ObjectContext also does Identity resolution for each Entity, which is relatively heavy, even for entities you later discard in the LINQ to Objects query.

This particular problem is ‘easy’ to get around using a NoTracking query.

But then you get another set of problems, you can’t update the results, unless you attach them.

Anyway hopefully next time you need to ‘partition’ your query you will know more about the tradeoffs

Tip 30 – How to use a custom database function

Imagine you have a database function like the DistanceBetween function in Nerd Dinner:

CREATE FUNCTION [dbo].[DistanceBetween](
   @Lat1 as real,
   @Long1 as real,
   @Lat2 as real,
   @Long2 as real)
RETURNS real
AS
BEGIN
…
END

And you want to use it with the Entity Framework.

Declaring the Function

The first step is to open your EDMX file in the XML editor and add a <Function> inside the <Schema> inside the <edmx:StorageModels> element.

When you are done it should look like this:

Using the Function in eSQL

Now this function can be called in eSQL like this:

SELECT VALUE(D) FROM MyModel.Dinners AS D
WHERE StorageNamespace.DistanceBetween(
      D.Latitude,D.Longitude,-34,174) < 50

MyModel is simply the name of your EntityContainer (generally the same as your ObjectContext) and StorageNamespace is your storage model schema namespace.

Using the Function in LINQ

Most of you don’t use eSQL so you are probably wondering can I do this with LINQ?

In 3.5 SP1 this is how:

var nearbyDinners =
     from d in ctx.Dinners.Where(
“StorageNamespace.DistanceBetween(it.Latitude, it.Longitude, –34, 174) < 50”
) select d;

Here we are mixing LINQ with eSQL by using a query builder method, which takes an eSQL fragment, in which we call our database function. Notice how this snippet refers to the current item using the ‘it’ keyword. You can even refer to parameters if necessary.

This is great.

But it would be much better without any strings.

Improvements in EF 4.0

Well in EF 4.0 you can write something like this instead:

var nearbyDinners =
    from d in ctx.Dinners
    where DistanceBetween(d.Latitude, d.Longitude, –34,174) < 50
    select d;

Which looks a lot better. No magic strings *and* compile time checking.

To make this work you need a method like this:

You may be wondering why the method throws an exception?

Well we never actually need to execute this method directly. We just use it to write LINQ queries, which are in translated into SQL without ever really calling the method.

The EF uses the EdmFunction attribute to known which database function needs to be called instead.