Executing a stored procedure in Entity Framework without Mapping

by David Kiff 23. March 2010 08:44

We had a requirement to call a stored procedure in order to insert some data into a table as part of the products setup.  My first impressions were great, this should be easy, I know Entity Framework supports stored procedures!

I was expecting to update my model, select the new stored procedure and Entity Framework would import it and create me a nice strongly typed method like so:

Stored procedure name: CreateProductDefaultsForSetup

Expected Entity Framework result: _context.CreateProductDefaultsForSetup(productId);

After importing the stored procedure I searched the generated file for the procedure name with no luck.  I searched online and found loads of posts explaining how it can be achieved when you want to map it back to an entity, which I did not!

Eventually I found out that I needed to write some code that looks like this:

public void Execute(string storedProcedureName, params KeyValuePair<string, object>[] arguments)
{
    using (EntityConnection connection = (EntityConnection)_context.Connection)
    {
        using (EntityCommand command = connection.CreateCommand())
        {
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = storedProcedureName;
            foreach (KeyValuePair<string, object> argument in arguments)
            {
                command.Parameters.AddWithValue(argument.Key, argument.Value);
            }
            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
        }
    }
}

I could then call the method like so:

Execute("Entities.CreateProductDefaultsForSetup", new KeyValuePair<string, object>("ProductID", productId));

Not quite as clean as I wanted!  Note that the command text is not just the name of the stored procedure.

After all this, it still didn’t work, giving me an error of:

“The FunctionImport CreateProductDefaultsForSetup could not be found in the container”

This took a while to fix with no documentation found online.  You need to right-click some whitespace in the Entity Framework designer and select Add > Function Import from the context menu, then follow the simple online prompt.

Finally I had my stored procedure being executed from Entity Framework, shame it wasn’t as straightforward as I have thought!

Tags:

Entity-Framework

Submit to DotNetKicks...
Permalink | Comments (0)

EF Strongly Typed ObjectQuery<T>.Include(“”);

by David Kiff 27. August 2009 09:39

In my most recent project, I have been asked to use EntityFramework.  To decouple this from the rest of the system, I wanted to encapsulate its implementation behind a repository. 

By default the Entity Framework does not load the entire object graph into memory, nor does it load it on demand, for example, if you have an Order entity that has a number of OrderDetails, then the OrderDetails will not be present until you include it in the query like so:

 

using (CommuicationsEntities entities = new CommuicationsEntities()) 
{ 
    entities.Order.Include("OrderDetails"); 
}

This approach has the obvious flaw that it uses magic strings.  If you change your model and forget to change the property name, you wont receive an error until runtime.  It would also be great to leave it up to the repository client to decide how much of the object graph they need.  There is no point loading the entire graph into memory and only use a small part of it.

After some looking around I found a great post by Kim Major here.  This article explains an approach that I like, using an “IncludeBuilder” to build up the includes in a strongly type manor and passing them to the repository.  Unfortunately the post does not have any code samples, leaving me to work out how to write it.  This is the implementation I came up with:

 

public class IncludeBuilder<T>
{
    private readonly List<string> _propertiesToInclude;

    public IncludeBuilder()
    {
        _propertiesToInclude = new List<string>();
    }

    public void Add(Expression<Func<T, object>> propertySelector)
    {
        MemberExpression memberExpression = propertySelector.Body as MemberExpression;
        if (memberExpression == null)
            throw new ArgumentException("Parameter propertySelector must be a MemberExpression");
        _propertiesToInclude.Add(memberExpression.Member.Name);
    }

    public ObjectQuery<T> Includes(ObjectQuery<T> query)
    {
        foreach (string include in _propertiesToInclude)
        {
            query = query.Include(include);
        }
        return query;
    }
}

 

It took me a while to realise you need to re-assign the query back to the main query as soon as you have added the include:

query = query.Include(include);

Now the repository consumer can do the following:

IRepository<Order> repository = new OrderRepository(new FulfilmentEntities());

 

IncludeBuilder<Order> includeBuilder = new IncludeBuilder<Order>();
includeBuilder.Add(a=> a.OrderDetails);

Order alerts = repository.GetSingle(11);

 

The code within the repository can add the includes in the following way:

public Alert GetSingle(int orderId, IncludeBuilder<Order> includeBuilder)
{
      return includeBuilder.Includes(_entities.Orders).Select(a => a).First();
}

Hopefully this helps someone :)

Tags:

Entity-Framework

Submit to DotNetKicks...
Permalink | Comments (0)