ADO.NET Data Services Client context

One of the great points with EF is the eco-system around it. Even if it can be used without EF, I think that ADO .NET Data Services is one of the technologies in this eco-system. Indeed, using ADO.NET Data Services with EF implies almost no code to write some LINQ queries in the client tier or to save the changes.

However, when we use the generated proxy in the client tier there are some bad points:

  • For example with Northwind, if we add an orderDetail to the order.OrderDetails collection, the orderDetail.Order is still null. In reverse, if we set the orderDetail.Order, the orderDetail isn’t added to the order.OrderDetails collection.
  • Moreover, if we load the orders and then the orderDetails, the OrderDetails collection of the orders is empty and the orderDetails order is null. However, because OrderID is a part of the orderDetails key, we should be able to get automatically the relation as EF do.
    //To have this relation with ADO.NET Data Services, we have to use the Expand method in the query or to use the LoadProperty method.
    With EF4, we can have FK in the model (with EF v1, we have only the FKs which are included in the PK) which is really great because, in theory, it’s now possible to reform the relations.
  • Another point: if you want to add an order with some (new) orderDetails, you need to call the Add for the order, then the Add for all orderDetails (the MS client context ignore the navigation properties) and then you also need to call the methods SetLink and AddLink (because the order key is an Identity). So in fact this is the code you have to write:
context.AddToOrders(o);
foreach (var od in o.OrderDetails)
{
    context.AddToOrderDetails(od);
    context.AddLink(o, "OrderDetails", od);
    context.SetLink(od, "Order", o);
}
  • One more point, the context doesn’t track changes alone. You have to call yourself the UpdateObject method.
  • Finally, if we add an orderDetail to the context and only then the associated order, when we call the SaveChanges, we have an exception because the context doesn’t change the Add order

So my idea was to code another client tier context. But of course, I wanted a generic solution. It means that I wanted to code something usable with every model. To realize it, I used a T4 template based on the edmx (in the server tier). This implies two things:

  • We need to use EF in the server tier
  • We need to have the server tier edmx when we code the client tier

My template fixes all the above issues.

You can download it here.

You also can download all the solution (with unit tests) here and the DB creation script here.

//I admit that some parts of the code aren’t great but ADO.NET Data Services is an extremely “closed” technology so it isn’t so easy and I would probably have some better ideas if I didn’t write it so late in the night Wink

Generate a WCF service from an edmx with T4 v2

I recently published a T4 template to generate a WCF service. I updated it to be able to load its relationships from an entity.

When the template is written (what I’ve already done), it generates the WCF service for us (you just have to set the end point in the config file).

As I explained in my previous post, the great point with it is the fact that the code writing time is not dependant of the number of entities on the model. Moreover,  the template is generally (it’s the case here) not dependant of the model so we can use it with another model and more generally with another project.

dynamic and indexer

Remember one year ago, I wrote a post on how to use reflection with dynamic keyword. At this moment, I sent an email to MS to add an indexer in dynamic. I don’t know if my email changed any thing but I am very happy to see that from .NET 4.0 Beta 2, we are now able to use it.

So in my sample, instead of

result = dynamicSpBase.get_Item(name);

I can now directly write this:

result = dynamicSpBase[name];

Great! Smile

Posted by Matthieu MEZIL | with no comments
Filed under: ,

Entity Framework: the productivity way

One of the best points with Entity Framework is the developer productivity gain. If you associate EF with the T4 template code generation, this gain explodes. Imagine that we want a WCF service which exposes some data. For each entity type, you probably want a Get method which returns the entities, perhaps another Get which takes the entity id as parameter and which returns the entity with its relationships, a Add, a Update, perhaps a Delete.

EF allows an important productivity gain for the entities development and their use. However, in our case, to code to write is almost the same for each entity type. It means that it’s time to use the T4 template.

In all T4 samples I studied, this great template is used only for the entity generation. We will try here to go ahead.

WIth the T4 template, you will write your meta-code which will generate the WCF service! And what is very cool is the fact that you will be able to use your template in another project. I try to compare here the productivity between a code using ADO .NET 2.0 and a code using the Entity Framework. My first observation is: I forgot that it’s so boring and so long to use the classic ADO.

With EF and T4, the only thing I don’t generate is the relation list to load per entity type (I could use a fixed depth with T4 but I preferred to have a business logic for the relationships loading). With ADO .NET 2.0, I have to write all the code!

Moreover, “classic” ADO implies a string query and we can imagine to have some writing mistakes in our SQL queries and so it implies to write some unit tests which take time.

One of the first point we can see is the code reading. It’s so much easier with EF!

For the GetOrder(orderId) method, my ADO 2.0 code is the following:

private const string SELECT_ORDERS = "OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry FROM ORDERS";

private const string SELECT_ORDERDETAILS = "OrderID, ProductID, UnitPrice, Quantity, Discount FROM [Order Details]";

private const string SELECT_CUSTOMERS = "C.CustomerID, CompanyName, ContactName, ContactTitle, [Address], City, Region, PostalCode, Country, Phone, Fax, Since, Points, CardNumber, CAST((CASE M.CustomerID when NULL then 0 else 1 END) AS bit) AS IsMember FROM Customers AS C LEFT OUTER JOIN Members AS M ON C.CustomerID = M.CustomerID";

 

public Order GetOrder(int orderID)

{

    return ReadEntity<Order>(string.Format("SELECT TOP 1 {0} WHERE OrderID = @OrderID; \n SELECT {1} WHERE OrderID = @OrderID;", SELECT_ORDERS, SELECT_ORDERDETAILS), new[] { new SqlParameter("OrderID", orderID) }, reader => GetOrder(reader), (o, reader, connection) =>

    {

        o.OrderDetails = new List<OrderDetail>();

        reader.NextResult();

        while (reader.Read())

        {

            var orderDetail = GetOrderDetail(reader);

            orderDetail.Order = o;

            o.OrderDetails.Add(orderDetail);

        }

        if (o.CustomerID != null)

            o.Customer = ReadEntity<Customer>(string.Format("SELECT TOP 1 {0} WHERE C.CustomerID = @CustomerID", SELECT_CUSTOMERS), new[] { new SqlParameter("CustomerID", o.CustomerID) }, subReader => GetCustomer(subReader), connection);

        var pq = o.OrderDetails.Select(od => od.ProductID.ToString());

        if (pq.Any())

        {

            var products = ReadEntities<Product>(string.Format("SELECT {0} WHERE ProductID IN ({1})", SELECT_PRODUCTS, pq.Skip(1).Any() ? pq.Aggregate((p1, p2) => p1.Contains(string.Format(" {0} ", p2)) ? p1 : string.Concat(" ", p1, " , ", p2)) : pq.First()), subReader => GetProduct(subReader), connection);

            foreach (var p in products)

                p.OrderDetails = o.OrderDetails.Where(od => od.ProductID == p.ProductID).Select(od => { od.Product = p; return od; }).ToList();

        }

    });

}

 

private T ReadEntity<T>(string commandText, SqlParameter[] parameters, Func<SqlDataReader, T> getEntityFromReader, Action<T, SqlDataReader, SqlConnection> moreAction = null) where T : class

{

    SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindEntities"].ToString());

    connection.Open();

    try

    {

        return ReadEntity(commandText, parameters, getEntityFromReader, connection, moreAction);

    }

    finally

    {

        connection.Close();

    }

}

 

private T ReadEntity<T>(string commandText, SqlParameter[] parameters, Func<SqlDataReader, T> getEntityFromReader, SqlConnection connection, Action<T, SqlDataReader, SqlConnection> moreAction = null) where T : class

{

    SqlCommand command = connection.CreateCommand();

    command.CommandText = commandText;

    command.Parameters.AddRange(parameters);

    SqlDataReader reader = command.ExecuteReader();

    try

    {

        if (reader.Read())

        {

            var value = getEntityFromReader(reader);

            if (moreAction != null)

                moreAction(value, reader, connection);

            return value;

        }

        return null;

    }

    finally

    {

        reader.Close();

    }

}

 

private List<T> ReadEntities<T>(string commandText, Func<SqlDataReader, T> getEntityFromReader, SqlConnection connection) where T : class

{

    var value = new List<T>();

    SqlCommand command = connection.CreateCommand();

    command.CommandText = commandText;

    SqlDataReader reader = command.ExecuteReader();

    try

    {

        while (reader.Read())

            value.Add(getEntityFromReader(reader));

        return value;

    }

    finally

    {

        reader.Close();

    }

}

 

private Order GetOrder(SqlDataReader reader)

{

    return new Order { OrderID = reader.GetInt32(0), CustomerID = reader.IsDBNull(1) ? null : reader.GetString(1), EmployeeID = reader.IsDBNull(2) ? null : (int?)reader.GetInt32(2), OrderDate = reader.IsDBNull(3) ? null : (DateTime?)reader.GetDateTime(3), RequiredDate = reader.IsDBNull(4) ? null : (DateTime?)reader.GetDateTime(4), ShippedDate = reader.IsDBNull(5) ? null : (DateTime?)reader.GetDateTime(5), ShipVia = reader.IsDBNull(6) ? null : (int?)reader.GetInt32(6), Freight = reader.IsDBNull(7) ? null : (decimal?)reader.GetDecimal(7), ShipName = reader.IsDBNull(8) ? null : reader.GetString(8), ShipAddress = reader.IsDBNull(9) ? null : reader.GetString(9), ShipCity = reader.IsDBNull(10) ? null : reader.GetString(10), ShipRegion = reader.IsDBNull(11) ? null : reader.GetString(11), ShipPostalCode = reader.IsDBNull(12) ? null : reader.GetString(12), ShipCountry = reader.IsDBNull(13) ? null : reader.GetString(13) };

}

 

private OrderDetail GetOrderDetail(SqlDataReader reader)

{

    return new OrderDetail { OrderID = reader.GetInt32(0), ProductID = reader.GetInt32(1), UnitPrice = reader.GetDecimal(2), Quantity = reader.GetInt16(3), Discount = reader.GetFloat(4) };

}

 

private Customer GetCustomer(SqlDataReader reader)

{

    if (reader.GetBoolean(14))

        return GetMember(reader);

    return GetCustomer<Customer>(reader);

}

 

private T GetCustomer<T>(SqlDataReader reader) where T : Customer, new()

{

    return new T { CustomerID = reader.GetString(0), CompanyName = reader.GetString(1), ContactName = reader.IsDBNull(2) ? null : reader.GetString(2), ContactTitle = reader.IsDBNull(3) ? null : reader.GetString(3), Address = reader.IsDBNull(4) ? null : reader.GetString(4), City = reader.IsDBNull(5) ? null : reader.GetString(5), Region = reader.IsDBNull(6) ? null : reader.GetString(6), PostalCode = reader.IsDBNull(7) ? null : reader.GetString(7), Country = reader.IsDBNull(8) ? null : reader.GetString(8), Phone = reader.IsDBNull(9) ? null : reader.GetString(9), Fax = reader.IsDBNull(10) ? null : reader.GetString(10) };

}

 

private Member GetMember(SqlDataReader reader)

{

    var member = GetCustomer<Member>(reader);

    member.Since = reader.IsDBNull(11) ? null : (DateTime?)reader.GetDateTime(11);

    member.Points = reader.IsDBNull(12) ? null : (int?)reader.GetInt32(12);

    member.CardNumber = reader.IsDBNull(13) ? null : reader.GetString(13);

    return member;

}

 

private Product GetProduct(SqlDataReader dataReader)

{

    return new Product { ProductID = dataReader.GetInt32(0), ProductName = dataReader.GetString(1), SupplierID = dataReader.IsDBNull(2) ? null : (int?)dataReader.GetInt32(2), CategoryID = dataReader.IsDBNull(3) ? null : (int?)dataReader.GetInt32(3), QuantityPerUnit = dataReader.IsDBNull(4) ? null : dataReader.GetString(4), UnitPrice = dataReader.IsDBNull(5) ? null : (decimal?)dataReader.GetDecimal(5), UnitsInStock = dataReader.IsDBNull(6) ? null : (short?)dataReader.GetInt16(6), UnitsOnOrder = dataReader.IsDBNull(7) ? null : (short?)dataReader.GetInt16(7), ReorderLevel = dataReader.IsDBNull(8) ? null : (short?)dataReader.GetInt16(8), Discontinued = dataReader.GetBoolean(9) };

}

Crazy!

With EF, this is simply the following:

//Generated file

partial class NorthwindService

{

    private static Func<NorthwindEntities, System.Int32, Order> GetOrderCQ = CompiledQuery.Compile<NorthwindEntities, System.Int32, Order>((context, OrderID) => context.Orders.OfType<Order>().FirstOrDefault(e => e.OrderID == OrderID));

 

    private static Func<NorthwindEntities, System.Int32, Order> GetOrderWithIncludeCQ { get; set; }

 

    public Order GetOrder(System.Int32 OrderID)

    {

        using (var context = new NorthwindEntities())

        {

            if (GetOrderWithIncludeCQ != null)

                return GetOrderWithIncludeCQ(context, OrderID);

            return GetOrderCQ(context, OrderID);

        }

    }

}

 

//My partial part

partial class NorthwindService

{

    static NorthwindService()

    {

        GetOrderWithIncludeCQ = (context, orderID) => context.Orders.Include("OrderDetails.Product").Include("Customer").FirstOrDefault(o => o.OrderID == orderID);

    }

}

Moreover, as explained previously, with EF I write only one line of code:

GetOrderWithIncludeCQ = (context, orderID) => context.Orders.Include("OrderDetails.Product").Include("Customer").FirstOrDefault(o => o.OrderID == orderID);

What a productivity gain!

This is the dev time function of the number of entities:

Nb entités / temps de dev

1

2

3

4

5

6

7

8

9

10

11

12

13

14

100

500

1000

EF

3

3

4

4

4

4

5

5

5

5

6

6

6

6

28

128

253

ADO .NET 2.0

35

65

95

125

155

185

215

245

275

305

335

365

395

425

3005

15005

30005

So for 1000 entities, with ADO 2.0, it takes more than 66 days (7.5 h per day) against only 4 hours for Entity Framework. Ok I’m not honnest. If we have 1000 entities, it’s better to split our model into some models. SO we will say 2 days for EF against 2.5 months for ADO 2.0. Great isn’t it?

Moreover, the EF code is easier to read, there is not a lot of reason to keep on using ADO .NET 2.0.

I just want to tell one last point: it’s very important to have a training on EF (for this you can contact me (matthieu.mezil at live.fr)). This is very important because even if EF seems very easy to use, there are important concepts you have to know:

  • not to lose a lot of time
  • to get the result you wished (particularly with the non persisted entities)
  • not to have bad performance

You can download the tt I wrote here.

EF: Include with where clause

Several guys ask me if it’s possible to add a condition in the Include method and the answer is no.

However you can do an equivalent like this:

from cWithP in

    (from c in context.Categories

     select new

     {

         Category = c,

         Products = from p in c.Products

                    where p.UnitPrice > 20

                    select p

     }).AsEnumerable()

select cWithP.Category;

or the condensed equivalent:

context.Categories.Select(c => new { Category = c, Products = c.Products.Where(p => p.UnitPrice > 20) }).AsEnumerable().Select(cp => cp.Category);

I need you!

I submitted one Birds of Feather session for the next Tech Ed in Berlin.

My session submission depends on your votes.

I think that you will find by yourselves which is my session Wink

Thanks by advance for your votes

ADO.NET EntityObject Generator: how to get the EntityType’s EntitySet and how to get EntitySet’s EntityTypes?

One of my customers wanted to know how to get all EntityTypes for an EntitySet in his T4 template based on the MS ADO.NET EntityObject Generator one.

So I made a POC for him.

What do I do?

I change two classes included in the template (my changes are in bold):

private class EntitySetObjectSetPropertyWrapper : MetadataItemWrapper

{

    public EntitySetObjectSetPropertyWrapper(EntitySet entitySet, WrapperBase wrapper)

        : base(entitySet, wrapper)

    {

    }

    public new EntitySet Source { get { return (EntitySet)base.Source; } }

    public string ModelName { get { return Source.Name; } }

    public string PropertyName { get { return Escape(Source.Name); } }

    public string FieldName { get { return "_" + Source.Name; } }

    public string TypeName { get { return GetStructuralTypeName(Source.ElementType); } }

    public string GetterAccessibility { get { return GetAccessibility(Source, GETTER_ACCESS); } }

    public string NewModifier { get { return HasBaseMemberWithMatchingName(typeof(ObjectContext), Source.Name) ? "new " : ""; } }

 

    public IEnumerable<EntityType> GetAllEntityTypes(ItemCollectionWrapper edm)

    {

        var entityType = Source.ElementType;

        yield return entityType;

        var entityTypeWrapper = edm.SourceEntities.First(e => e.Source == entityType);

        foreach (var subEntityType in entityTypeWrapper.SubEntityTypes(edm))

            yield return subEntityType;

    }

}

 

private class EntityTypeWrapper : StructuralTypeWrapper

{

    public EntityTypeWrapper(EntityType entity, WrapperBase wrapper)

        : base(entity, wrapper)

    {

    }

    public new EntityType Source { get { return (EntityType)base.Source; } }

 

    public string AbstractOption { get { return Source.Abstract ? "abstract " : ""; } }

    public bool IsAbstract { get { return Source.Abstract; } }

    public string BaseTypeName { get { return Source.BaseType == null ? "EntityObject" : GetStructuralTypeName((StructuralType)Source.BaseType); } }

    public string TypeReferenceName { get { return GetStructuralTypeName(Source); } }

 

 

    public IEnumerable<NavigationPropertyWrapper> NavigationProperties { get { return Source.NavigationProperties.Where(n => n.DeclaringType == Source).Select(n => new NavigationPropertyWrapper(n, this)); } }

 

    public EntitySet GetEntitySet(ItemCollectionWrapper edm)

    {

        return edm.SourceEntityContainers.SelectMany(c => c.ObjectSetProperties).First(es => es.GetAllEntityTypes(edm).Contains(Source)).Source;

    }

    public IEnumerable<EntityType> SubEntityTypes(ItemCollectionWrapper edm, bool recursivity = true)

    {

        return edm.SourceEntities.Where(et => et.AllBaseEntityTypes.Contains(Source)).Select(et => et.Source);

    }

    public IEnumerable<EntityType> AllBaseEntityTypes

    {

        get { return GetBaseEntityTypes(Source); }

    }

    private static IEnumerable<EntityType> GetBaseEntityTypes(EntityType entityType)

    {

        EntityType baseType;

        if (entityType == null || (baseType = (EntityType)entityType.BaseType) == null)

            yield break;

        yield return baseType;

        foreach (var subEntityType in GetBaseEntityTypes(baseType))

            yield return subEntityType;

    }

}

Note that in your template, you probably don't have the syntax colors.

Then you can use it in the rest of your template as I did in my POC:

/// <summary>
/// <#=set.SummaryComment#>
/// </summary><#=set.GetLongDescriptionComment(_regionIndentLevel)#>
/// <remarks>
/// Entity types:
<# foreach (var entityType in set.GetAllEntityTypes(Edm))
{ #>
///  <#= entityType.Name #>
<# } #>
/// </remarks>
<#=set.NewModifier#><#=set.GetterAccessibility#> ObjectSet<<#=set.TypeName#>> <#=set.PropertyName#>
{
     get
     {
         if ((<#=set.FieldName#> == null))
         {
             <#=set.FieldName#> = base.CreateObjectSet<<#=set.TypeName#>>("<#=set.ModelName#>");
         }
         return <#=set.FieldName#>;
     }
}

[…]

/// <summary>
/// <#=entity.SummaryComment#>
/// </summary><#=entity.GetLongDescriptionComment(_regionIndentLevel)#>
/// <remarks>EntitySet <#= entity.GetEntitySet(Edm).Name #></remarks>
[EdmEntityTypeAttribute(NamespaceName="<#=entity.ModelNamespace#>", Name="<#=entity.ModelName#>")]
[Serializable()]
[DataContractAttribute(IsReference=true)]
<#
        foreach(EntityTypeWrapper subType in Edm.GetAllDirectSubTypes(entity.Source))
        {
#>
[KnownTypeAttribute(typeof(<#=subType.TypeReferenceName#>))]
<#
        }
#>
<#=entity.TypeAccessibility#> <#=entity.AbstractOption#>partial class <#=entity.ClassName#> : <#=entity.BaseTypeName#>
{
<#
        if(!entity.IsAbstract)
        {
            WriteFactoryMethod(entity.FactoryMethodWrapper);
        }
        WritePrimitiveTypeProperties(entity, typeof(EntityObject));
        WriteComplexTypeProperties(entity, typeof(EntityObject));
#>

I profit by this post to thank Jeff.

PFx is good

I am sure that the Parallel Framework (PFx) will take more and more importance in future developments.

However, demos often are not in phase with the reality. It’s true that a fractal demo on a 1000 cores machine is ideal for parallel demo, however in real life, I never used the fractal yet and I don’t think that I am the only one in this case.

So my idea was to make an example inspired from my daily work. So I made an example around… the Entity Framework. Smile

Imagine a DB featuring a Customers table with potentially a lot of rows (100 000 in my example which is quite a lot), a WPF application with a DataGrid to show the customers. Note that the WPF DataGrid is better than the Winforms DataGridView for this case because the WPF DataGrid loads only the visible items.

The ObjectQuery class implements IListSource, so it means that we can use it as ItemsSource (the Customers property is of type ObjectSet<Customer> which inherits of ObjectQuery). Then, we add some controls to filter the items (for example, one TextBox per string column). We won’t use the MVVM pattern to avoid complicating this sample. So to filter items, we can change the DataGrid ItemsSource Customers.Where(…) instead of Customers. The result of the Where is also an ObjectQuery instance so no problem. But… this implies a new query into the DB. We should avoid executing a new SQL query each time one of the filter control’s value change. Indeed, on the Window load, we get all the customers.

In addition to the useless query, we have another issue: new non persisted customers aren’t in the DataGrid since we change the filter and we can’t get it in the grid while we don’t call the context SaveChanges method. Conversely, the deleted customers still are in the grid while changes aren’t persisted. Indeed, EF queries get only the DB datas. To fix it, we can persist systematically, the add and delete and include all in a transaction so we are able to cancel the modifications however, this solution isn’t great.

We need to work with the ObjectContext cache as soon as the entities are loaded. For this, we can use the ObjectStateManager property but… with the ObjectStateManager, we will have only an IEnumerable<Customer>, so we won’t be able to automate the add and the deletion.

To fix it, I made my own class:

public class ObjectSetDataSource<T> : IList<T>, ICollection<T>, IEnumerable<T>, IList, ICollection, IEnumerable, INotifyCollectionChanged where T : class, new()

{

    […]

 

    private IEnumerable<T> AllEntities

    {

        get

        {

            if (_isLoaded == false)

            {

                foreach (var e in ObjectSet)

                    yield return e;

                _isLoaded = true;

            }

            else

            {

                foreach (var e in ObjectSet.Context.ObjectStateManager.GetObjectStateEntries(EntityState.Added | EntityState.Modified | EntityState.Unchanged).Select(ose => ose.Entity).OfType<T>())

                    yield return e;

            }

        }

    }

 

    private void CreateEntitiesList()

    {

        _entities = null;

        if (_allEntities == null)

            _allEntities = AllEntities;

        if (Predicate == null)

        {

            _entities = _allEntities.ToList();

            return;

        }

        _entities = _allEntities.Where(Predicate).ToList();

       }

 

    public Func<T, bool> Predicate

    {

        get { return _predicate; }

        set

        {

            _predicate = value;

            CreateEntitiesList();

               OnCollectionChanged(NotifyCollectionChangedAction.Reset);

        }

    }

}

Ok, it works. On the Window, we can use the TextChanged event like this:

private void lastNameTB_TextChanged(object sender, TextChangedEventArgs e)

{

    Filter();

}

 

private void firstNameTB_TextChanged(object sender, TextChangedEventArgs e)

{

    Filter();

}

 

private void Filter()

{

    string lastName = lastNameTB.Text;

    string firstName = firstNameTB.Text;

    _customers.Predicate = c =>

        {

            int i = 0;

            while (i < 100000) i++;

            return c.LastName.StartsWith(lastName) && c.FirstName.StartsWith(firstName);

        };

}

Note the loop to slow the process in order to see better what happens.

We will quickly see performance issues. Imagine that we want all the customers whose last name starts with “MEZ”.

What happens?

First issue: when we write MEZ, we probably write M then E and then Z. But with our sequential process, we will get the list of the customers whose last name starts with M then with ME and only then with MEZ. The problem is the fact that you can’t cancel the process because while it isn’t finished, the TextChanged event isn’t raised. Another issue is the fact that our code uses only one core of our processor and so 50% of the CPU power with my dual core:

image

If we parallelize our code, we will fix the second issue:

To use 100% of our CPU, we should execute our LINQ query on several threads. Indeed you can because the customers filter is independent between each customer. However, creating one thread per customer is out of the question! Tasks are very interesting for this and in our case, we will use PLINQ which is really fantastically easy to use. This is the code I propose:

public class ObjectSetDataSource<T> : IList<T>, ICollection<T>, IEnumerable<T>, IList, ICollection, IEnumerable, INotifyCollectionChanged where T : class, new()

{

    […]

 

    private void CreateEntitiesList()

    {

        _entities = null;

        if (_allEntities == null)

            _allEntities = AllEntities;

        if (Predicate == null)

        {

            _entities = _allEntities.ToList();

            return;

        }

        try

        {

            _entities = _allEntities.AsParallel.Where(Predicate).ToList();

        }

        catch (OperationCanceledException)

        {

        }

    }

 

    public Func<T, bool> Predicate

    {

        get { return _predicate; }

        set

        {

            _predicate = value;

            CreateEntitiesList();

            OnCollectionChanged(NotifyCollectionChangedAction.Reset);

        }

    }

}

The CPU use shows a real improvement:

image_thumb[1]

Only with the AsParallel extension method, It’s hard to do it more easily! Wink With this, our LINQ query is executed on all the cores of our computer. The extension method AsParallel will share the processes by splitting the source by n (2 for my dual core). Note that with execution on more than one thread, the result (without OrderBy) can be in another order than with our first code mono-threaded but in our case, it isn’t important.

However, there still is the first issue: we need to be able to cancel the list process if the filter changes in order not to have to generate the lists for M and ME when the wanted filter is MEZ. Of course, PFx proposes all we need to do it:

public class ObjectSetDataSource<T> : IList<T>, ICollection<T>, IEnumerable<T>, IList, ICollection, IEnumerable, INotifyCollectionChanged where T : class, new()

{

    […]

 

    private void CreateEntitiesList()

    {

        _entities = null;

        if (_allEntities == null)

            _allEntities = AllEntities;

        if (Predicate == null)

        {

            _entities = _allEntities.ToList();

            return;

        }

        try

        {

            _entities = _allEntities.AsParallel().WithCancellation(_cancellationToken.Token).Where(Predicate).ToList();

        }

        catch (OperationCanceledException)

        {

        }

    }

 

    public Func<T, bool> Predicate

    {

        get { return _predicate; }

        set

        {

            _predicate = value;

            if (_cancellationToken != null)

                _cancellationToken.Cancel();

            _cancellationToken = new CancellationTokenSource();

            new Task(t =>

                 {

                    CreateEntitiesList();

 

                     if (cancellationToken.IsCancellationRequested)

                         return;

 

                     var app = Application.Current;

                     if (app != null)

                        app.Dispatcher.BeginInvoke((Action)(() => OnCollectionChanged(NotifyCollectionChangedAction.Reset)));

                 }, cancellationToken).Start();

        }

    }

}

The CPU use shows one more important improvement:

image_thumb[3]

The WithCancellation extension method allows us to cancel the LINQ query during its execution.

The idea to create the task in the Predicate property is to not be forced to wait the end of the M filter process and then the end of the ME filter process to finally have our MEZ filter. The task runs the filter process asynchronously and avoids the frozen UI.

The last point is the BeginInvoke method which allow us to raise the CollectionChanged event into the principal thread which is inevitable to avoiding the following exception: “This type of CollectionView does not support changes to its SourceCollection from a thread different from the Dispatcher thread”.

This sample, very frequent, shows us the interest of using PFx. However, be careful, as all algorithms aren’t parallelisable. Moreover, the parallel code often adds more complexity and implies bringing parallelism algorithm under control. However if you have time to learn it, I suggest you very very very strongly to learn PFx and the parallelism in general. Indeed, as I wrote it at first, the Parallel Framework (PFx) will take more and more importance in the future.

You can download the complete ObjectSetDataSource class here.

How to get the typed instance in the abstract class?

Alex James, PM on EF, publish sometimes some C# posts.

He published a post to explain how to “link” the methods call.

To realize it, he returns the instance in his methods:

public abstract class PropertyConfiguration

{

    private bool _nullable;

    public virtual PropertyConfiguration Nullable()

    {

        _nullable = true;

        return this;

    }

    public virtual PropertyConfiguration NonNullable()

    {

        _nullable = false;

        return this;

    }

}

 

public class StringPropertyConfiguration : PropertyConfiguration

{

    private int? _maxLength;

    public StringPropertyConfiguration MaxLength(int maxLength)

    {

        _maxLength = maxLength;

        return this;

    }

}

With this way, he can do:

var nameConfiguration = new StringPropertyConfiguration()

                            .MaxLength(100)

                            .Nullable();

But the following code doesn’t compile:

 

var nameConfiguration = new StringPropertyConfiguration()

                            .Nullable()

                            .MaxLength(100);

The idea of Damien Guard (one of the last MS guy who works on LINQ to SQL) was to use the extension methods:

public abstract class PropertyConfiguration

{

    private bool _nullable;

    public bool Nullable

    {

        get { return _nullable; }

        set { _nullable = value; }

    }

}

 

public static class PropertyConfigurationExtension

{

    public static T Nullable<T>(this T propertyConfiguration) where T: PropertyConfiguration

    {

         propertyConfiguration.Nullable = true;

         return propertyConfiguration;

    }

}

Cool!

Now the question is: is it possible to do it with C#2 (so without extension methods)?

For this, I suggest the following solution:

public abstract class PropertyConfiguration<T> where T : PropertyConfiguration<T>

{

    private bool _nullable;

    public virtual T Nullable()

    {

        _nullable = true;

        return (T)this;

    }

    public virtual T NonNullable()

    {

        _nullable = false;

        return (T)this;

    }

}

 

public class StringPropertyConfiguration : PropertyConfiguration<StringPropertyConfiguration>

{

    private int? _maxLength;

    public StringPropertyConfiguration MaxLength(int maxLength)

    {

        _maxLength = maxLength;

        return this;

    }

}

This one is more complex but is interesting because I give the type of the derived class to the base class using generics.

The problem with this approach is the fact that it isn’t possible to use directly the base class (ie to have a parameter of type PropertyConfiguration or a List of PropertyConfiguration) because of the generics. To fix it, we can use an interface:

public interface IPropertyConfiguration

{

    IPropertyConfiguration Nullable();

    IPropertyConfiguration NonNullable();

}

 

public abstract class PropertyConfiguration<T> : IPropertyConfiguration where T : PropertyConfiguration<T>

{

    private bool _nullable;

    public virtual T Nullable()

    {

        _nullable = true;

        return (T)this;

    }

    public virtual T NonNullable()

    {

        _nullable = false;

        return (T)this;

    }

 

    IPropertyConfiguration IPropertyConfiguration.Nullable()

    {

        return Nullable();

    }

    IPropertyConfiguration IPropertyConfiguration.NonNullable()

    {

        return NonNullable();

    }

}

Enjoy Smile

Posted by Matthieu MEZIL | with no comments
Filed under: ,

How to have FK in EF v1?

Before answering this question, I will start with another one: why do we need FK?

  • to be able to change a relationship without loading the related entity but we can already do it with the EntityReference property.
  • for the binding

With the windows forms ComboBox, we don’t need it because you don’t have to systematically set the ValueMember property:

categoriesComboBox.DataSource = context.Categories;

categoriesComboBox.DisplayMember = "CategoryName";

categoriesComboBox.DataBindings.Add("SelectedItem", product, "Category");

But the problem is for the DataGridViewComboBoxColumn and the ASP DropDownList. In these cases, we really need the FK property.

My first idea was to add the property:

partial class Product

{

    public int CategoryID

    {

        get { return (int)CategoryReference.EntityKey.EntityKeyValues.First().Value; }

        set { CategoryReference.EntityKey = new EntityKey("NorthwindEntities.Categories", "CategoryID", value); }

    }

}

Ok, it works. However, the problem is the fact that we can been tempted to use it in a LINQ To Entities query. But, of course, in this case, it fails.

The binding doesn’t directly use the property of the class but the PropertyDescriptor which are, by default, generated from the properties. My idea is not to add a new property but to add a new PropertyDescriptor. For this, we have to implement the ICustomTypeDescriptor.

partial class Product : ICustomTypeDescriptor

{

    AttributeCollection ICustomTypeDescriptor.GetAttributes()

    {

        return TypeDescriptor.GetAttributes(this, true);

    }

 

    string ICustomTypeDescriptor.GetClassName()

    {

        return TypeDescriptor.GetClassName(this);

    }

 

    string ICustomTypeDescriptor.GetComponentName()

    {

        return TypeDescriptor.GetComponentName(this);

    }

 

    TypeConverter ICustomTypeDescriptor.GetConverter()

    {

        return TypeDescriptor.GetConverter(this);

    }

 

    EventDescriptor ICustomTypeDescriptor.GetDefaultEvent()

    {

        return TypeDescriptor.GetDefaultEvent(this);

    }

 

    PropertyDescriptor ICustomTypeDescriptor.GetDefaultProperty()

    {

        return TypeDescriptor.GetDefaultProperty(this);

    }

 

    object ICustomTypeDescriptor.GetEditor(Type editorBaseType)

    {

        return TypeDescriptor.GetEditor(this, editorBaseType);

    }

 

    EventDescriptorCollection ICustomTypeDescriptor.GetEvents(Attribute[] attributes)

    {

        return TypeDescriptor.GetEvents(attributes);

    }

 

    EventDescriptorCollection ICustomTypeDescriptor.GetEvents()

    {

        return TypeDescriptor.GetEvents(this);

    }

 

    PropertyDescriptorCollection ICustomTypeDescriptor.GetProperties(Attribute[] attributes)

    {

        var props = TypeDescriptor.GetProperties(this, attributes, true).Cast<PropertyDescriptor>().ToList();

        props.Add(new FKPropertyDescriptor<Product>(p => p.CategoryReference, "CategoryID", "CategoryCategoryID", typeof(int)));

        return new PropertyDescriptorCollection(props.ToArray());

    }

 

    PropertyDescriptorCollection ICustomTypeDescriptor.GetProperties()

    {

        return ((ICustomTypeDescriptor)this).GetProperties(null);

    }

 

    object ICustomTypeDescriptor.GetPropertyOwner(PropertyDescriptor pd)

    {

        return this;

    }

}

 

public class FKPropertyDescriptor<T> : PropertyDescriptor

{

    private Type _propertyDescriptorType;

    private Func<T, EntityReference> _getEntityReference;

 

    public string PropertyName { get; private set; }

 

    public FKPropertyDescriptor(Func<T, EntityReference> getEntityReference, string propertyName, string propertyDescriptorName, Type propertyDescriptorType)

        : base(propertyDescriptorName, new Attribute[0])

    {

        _getEntityReference = getEntityReference;

        PropertyName = propertyName;

        _propertyDescriptorType = propertyDescriptorType;

    }

 

    public override bool CanResetValue(object component)

    {

        return false;

    }

 

    public override Type ComponentType

    {

        get { return typeof(T); }

    }

 

    public override object GetValue(object component)

    {

        return _getEntityReference((T)component).EntityKey.EntityKeyValues.First(ekv => ekv.Key == PropertyName).Value;

    }

 

    public override bool IsReadOnly

    {

        get { return false; }

    }

 

    public override Type PropertyType

    {

        get { return _propertyDescriptorType; }

    }

 

    public override void ResetValue(object component)

    {

    }

 

    public override void SetValue(object component, object value)

    {

        var entityKey = _getEntityReference((T)component).EntityKey;

        _getEntityReference((T)component).EntityKey = new EntityKey(string.Concat(entityKey.EntityContainerName, ".", entityKey.EntitySetName), entityKey.EntityKeyValues.Where(ekm => ekm.Key != PropertyName).Union(new[] { new EntityKeyMember(PropertyName, value) }));

    }

 

    public override bool ShouldSerializeValue(object component)

    {

        return false;

    }

}

Great, it works!

However, it would be very boring to do it for each FK!

As Danny wrote here, it’s possible to use the T4 template with VS 2008.  So my idea is to change the default template to add it automatically.

You can download it here.

The most important part of the template is this one:

PropertyDescriptorCollection ICustomTypeDescriptor.GetProperties(Attribute[] attributes)

{

    var props = TypeDescriptor.GetProperties(this, attributes, true).Cast<PropertyDescriptor>().ToList();

<#        

    foreach(NavigationProperty property in entity.NavigationProperties.Where(n => n.DeclaringType == entity && n.ToEndMember.RelationshipMultiplicity != RelationshipMultiplicity.Many))

    {

        string elementTypeName = GetEntityType(property.ToEndMember).Name;

        EntityType elementType = Edm.AllEntities.First(et => et.Name == elementTypeName);

        foreach (EdmMember keyMember in elementType.KeyMembers)

        {

            string keyMemberName = keyMember.Name;

#>

    props.Add(new FKPropertyDescriptor<<#= entity.Name #>>(p => p.<#= property.Name #>Reference, "<#= keyMemberName #>", "<#= string.Concat(property.Name, keyMemberName) #>", typeof(<#= new CSharpCodeLanguage().Format(keyMember.TypeUsage) #>)));

<#

        }

    }

#>

    return new PropertyDescriptorCollection(props.ToArray());

}

What is cool now is the fact that with zero effort, you can use the FK in your bindings.

Enjoy Smile

Posted by Matthieu MEZIL | with no comments

T4 for View Generation

EF team blogged about it here and I think that it’s very useful.

First, I think that it’s easier to use than Edmgen.

Moreover, contrary to EdmGen, it allows you to use embedded metadata artifacts.

If you want to use the MS T4 template with VS 2010, don’t forget to change the xml namespaces.

XNamespace edmxns = "http://schemas.microsoft.com/ado/2008/10/edmx";
XNamespace csdlns = "http://schemas.microsoft.com/ado/2008/09/edm";
XNamespace mslns = "http://schemas.microsoft.com/ado/2008/09/mapping/cs";
XNamespace ssdlns = "http://schemas.microsoft.com/ado/2009/02/edm/ssdl";

So you should have the following:

<#@ template language="C#" hostspecific="true"#>
<#@ output extension=".cs" #>

<#@ assembly name="System.Core" #>
<#@ assembly name="System.Data" #>
<#@ assembly name="System.Data.Entity" #>
<#@ assembly name="System.Data.Entity.Design" #>
<#@ assembly name="System.Xml" #>
<#@ assembly name="System.Xml.Linq" #>

<#@ import namespace="System" #>
<#@ import namespace="System.IO" #>
<#@ import namespace="System.Diagnostics" #>
<#@ import namespace="System.Text" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ import namespace="System.Data.Entity.Design" #>
<#@ import namespace="System.Data.Metadata.Edm" #>
<#@ import namespace="System.Data.Mapping" #>
<#@ import namespace="System.Xml" #>
<#@ import namespace="System.Xml.Linq" #>

<#
    // Find EDMX file to process: Model1.Views.tt generates views for Model1.EDMX
    string edmxFileName = Path.GetFileNameWithoutExtension(this.Host.TemplateFile).ToLowerInvariant().Replace(".views", "") + ".edmx";
    string edmxFilePath = Path.Combine(Path.GetDirectoryName(this.Host.TemplateFile), edmxFileName);
    if (File.Exists(edmxFilePath))
    {
        // Call helper class to generate pre-compiled views and write to output
        this.WriteLine(GenerateViews(edmxFilePath));
    }
    else
    {
        this.Error(String.Format("No views were generated. Cannot find file {0}. Ensure the project has an EDMX file and the file name of the .tt file is of the form [edmx-file-name].Views.tt", edmxFilePath));
    }
    // All done!
#>

<#+
    private String GenerateViews(string edmxFilePath)
    {
        String generatedViews = String.Empty;
        try
        {
            using (StreamWriter writer = new StreamWriter(new MemoryStream()))
            {
                XmlReader csdlReader = null;
                XmlReader mslReader = null;
                XmlReader ssdlReader = null;

                // Crack open the EDMX file and get readers over the CSDL, MSL and SSDL portions
                GetConceptualMappingAndStorageReaders(edmxFilePath, out csdlReader, out mslReader, out ssdlReader);

                // Initialize item collections
                EdmItemCollection edmItems = new EdmItemCollection(new XmlReader[] { csdlReader });
                StoreItemCollection storeItems = new StoreItemCollection(new XmlReader[] { ssdlReader });
                StorageMappingItemCollection mappingItems = new StorageMappingItemCollection(edmItems, storeItems, new XmlReader[] { mslReader });

                // Initialize the view generator to generate views in C#
                EntityViewGenerator viewGenerator = new EntityViewGenerator();
                viewGenerator.LanguageOption = LanguageOption.GenerateCSharpCode;
                IList<EdmSchemaError> errors = viewGenerator.GenerateViews(mappingItems, writer);

                foreach (EdmSchemaError e in errors)
                {
                    // log error
                    this.Error(e.Message);
                }

                MemoryStream memStream = writer.BaseStream as MemoryStream;
                generatedViews = Encoding.UTF8.GetString(memStream.ToArray());
            }
        }
        catch (Exception ex)
        {
            // log error
            this.Error(ex.ToString());
        }

        return generatedViews;
    }

    private void GetConceptualMappingAndStorageReaders(string edmxFile, out XmlReader csdlReader, out XmlReader mslReader, out XmlReader ssdlReader)
    {
        csdlReader = null;
        mslReader = null;
        ssdlReader = null;

        XNamespace edmxns = "http://schemas.microsoft.com/ado/2008/10/edmx";
        XNamespace csdlns = "http://schemas.microsoft.com/ado/2008/09/edm";
        XNamespace mslns = "http://schemas.microsoft.com/ado/2008/09/mapping/cs";
        XNamespace ssdlns = "http://schemas.microsoft.com/ado/2009/02/edm/ssdl";

        XDocument edmxDoc = XDocument.Load(edmxFile);
        if (edmxDoc != null)
        {
            XElement edmxNode = edmxDoc.Element(edmxns + "Edmx");
            if (edmxNode != null)
            {
                XElement runtimeNode = edmxNode.Element(edmxns + "Runtime");
                if (runtimeNode != null)
                {
                    // Create XmlReader over CSDL in EDMX
                    XElement conceptualModelsNode = runtimeNode.Element(edmxns + "ConceptualModels");
                    if (conceptualModelsNode != null)
                    {
                        XElement csdlContent = conceptualModelsNode.Element(csdlns + "Schema");
                        if (csdlContent != null)
                        {
                            csdlReader = csdlContent.CreateReader();
                        }
                    }

                    // Create XmlReader over MSL in EDMX
                    XElement mappingsNode = runtimeNode.Element(edmxns + "Mappings");
                    if (mappingsNode != null)
                    {
                        XElement mslContent = mappingsNode.Element(mslns + "Mapping");
                        if (mslContent != null)
                        {
                            mslReader = mslContent.CreateReader();
                        }
                    }

                    // Create XmlReader over SSDL in EDMX
                    XElement storageModelsNode = runtimeNode.Element(edmxns + "StorageModels");
                    if (storageModelsNode != null)
                    {
                        XElement ssdlContent = storageModelsNode.Element(ssdlns + "Schema");
                        if (ssdlContent != null)
                        {
                            ssdlReader = ssdlContent.CreateReader();
                        }
                    }
                }
            }
        }
    }
#>

Posted by Matthieu MEZIL | with no comments

POCO T4

With EF4 features CTP1, we have a great POCO template which is divided in two tt files to allow us to have the entitiy POCO classes and the context in different projects.

It’s very very cool.

However, I think that it’s a shame not to have an interface for the context particularly to mock it.

So I change the .Context.tt like this:

 

<#@ template language="C#" debug="false" hostspecific="true"#>
<#@ include file="EF.Utility.ctp.CS.ttinclude"#><#@
output extension=".cs"#><#
// Copyright (c) Microsoft Corporation.  All rights reserved.

CodeGenerationTools code = new CodeGenerationTools(this);
MetadataLoader loader = new MetadataLoader(this);
CodeRegion region = new CodeRegion(this, 1);
MetadataTools ef = new MetadataTools(this);

string inputFile = @"Northwind.edmx";
EdmItemCollection ItemCollection = loader.CreateEdmItemCollection(inputFile);
string namespaceName = code.VsNamespaceSuggestion();

TemplateFileManager fileManager = TemplateFileManager.Create(this);
EntityContainer container = ItemCollection.GetItems<EntityContainer>().FirstOrDefault();
if (container == null)
{
    return "// No EntityContainer exists in the model, so no code was generated";
}

// Emit Entity Types
    string interfaceName = "I" + code.Escape(container);
    fileManager.StartNewFile(interfaceName + ".cs");
#>
using System;
using System.Collections.Generic;
using System.Data.Objects;
using System.Data.EntityClient;
using Entities;

<#
if (!String.IsNullOrEmpty(namespaceName))
{
#>
namespace <#=code.EscapeNamespace(namespaceName)#>
{
<#
    PushIndent(CodeRegion.GetIndent(1));
}
#>
<#=Accessibility.ForType(container)#> interface <#=interfaceName#>
{
<#
        foreach (EntitySet entitySet in container.BaseEntitySets.OfType<EntitySet>())
        {
#>
    IObjectSet<<#=code.Escape(entitySet.ElementType)#>> <#=code.Escape(entitySet)#> { get; }
<#
        }
#>
<#
        if (container.FunctionImports.Any())
        {
#>

<#
        }
        foreach (EdmFunction edmFunction in container.FunctionImports)
        {
            var parameters = FunctionImportParameter.Create(edmFunction.Parameters, code, ef);
            string paramList = String.Join(", ", parameters.Select(p => p.FunctionParameterType + " " + p.FunctionParameterName).ToArray());
            if(edmFunction.ReturnParameter == null)
            {
                continue;
            }
            string returnTypeElement = code.Escape(ef.GetElementType(edmFunction.ReturnParameter.TypeUsage));

#>
    IEnumerable<<#=returnTypeElement#>> <#=code.Escape(edmFunction)#>(<#=paramList#>);
<#
        }
#>
}
<#
if (!String.IsNullOrEmpty(namespaceName))
{
    PopIndent();
#>
}
<#
}
    fileManager.WriteFiles();
#>
//------------------------------------------------------------------------------
// <auto-generated>
//     This code was generated from a template.
//
//     Changes to this file may cause incorrect behavior and will be lost if
//     the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------

using System;
using System.Collections.Generic;
using System.Data.Objects;
using System.Data.EntityClient;
using Entities;

<#
if (!String.IsNullOrEmpty(namespaceName))
{
#>
namespace <#=code.EscapeNamespace(namespaceName)#>
{
<#
    PushIndent(CodeRegion.GetIndent(1));
}
#>
<#=Accessibility.ForType(container)#> partial class <#=code.Escape(container)#> : ObjectContext, <#= interfaceName #>
{
    public const string ConnectionString = "name=<#=container.Name#>";
    public const string ContainerName = "<#=container.Name#>";

    #region Constructors

    public <#=code.Escape(container)#>()
        : base(ConnectionString, ContainerName)
    {
        ContextOptions.DeferredLoadingEnabled = true;
    }

    public <#=code.Escape(container)#>(string connectionString)
        : base(connectionString, ContainerName)
    {
        ContextOptions.DeferredLoadingEnabled = true;
    }

    public <#=code.Escape(container)#>(EntityConnection connection)
        : base(connection, ContainerName)
    {
        ContextOptions.DeferredLoadingEnabled = true;
    }

    #endregion

<#
        region.Begin("ObjectSet Properties");

        foreach (EntitySet entitySet in container.BaseEntitySets.OfType<EntitySet>())
        {
#>

    <#=AccessibilityAndVirtual(Accessibility.ForReadOnlyProperty(entitySet))#> ObjectSet<<#=code.Escape(entitySet.ElementType)#>> <#=code.Escape(entitySet)#>
    {
        get { return <#=code.FieldName(entitySet) #>  ?? (<#=code.FieldName(entitySet)#> = CreateObjectSet<<#=code.Escape(entitySet.ElementType)#>>("<#=entitySet.Name#>")); }
    }
    private ObjectSet<<#=code.Escape(entitySet.ElementType)#>> <#=code.FieldName(entitySet)#>;
    IObjectSet<<#=code.Escape(entitySet.ElementType)#>> <#=interfaceName#>.<#=code.Escape(entitySet)#>
    {
        get { return <#=code.Escape(entitySet)#>; }
    }
<#
        }

        region.End();
#>

<#
        region.Begin("Function Imports");

        foreach (EdmFunction edmFunction in container.FunctionImports)
        {
            var parameters = FunctionImportParameter.Create(edmFunction.Parameters, code, ef);
            string paramList = String.Join(", ", parameters.Select(p => p.FunctionParameterType + " " + p.FunctionParameterName).ToArray());
            if(edmFunction.ReturnParameter == null)
            {
                continue;
            }
            string returnTypeElement = code.Escape(ef.GetElementType(edmFunction.ReturnParameter.TypeUsage));

#>
    <#=AccessibilityAndVirtual(Accessibility.ForMethod(edmFunction))#> ObjectResult<<#=returnTypeElement#>> <#=code.Escape(edmFunction)#>(<#=paramList#>)
    {
<#
            foreach (var parameter in parameters)
            {
                if (!parameter.NeedsLocalVariable)
                {
                    continue;
                }
#>

        ObjectParameter <#=parameter.LocalVariableName#>;

        if (<#=parameter.IsNullableOfT ? parameter.FunctionParameterName + ".HasValue" : parameter.FunctionParameterName + " != null"#>)
        {
            <#=parameter.LocalVariableName#> = new ObjectParameter("<#=parameter.EsqlParameterName#>", <#=parameter.FunctionParameterName#>);
        }
        else
        {
            <#=parameter.LocalVariableName#> = new ObjectParameter("<#=parameter.EsqlParameterName#>", typeof(<#=parameter.RawClrTypeName#>));
        }
<#
            }
#>
        return base.ExecuteFunction<<#=returnTypeElement#>>("<#=edmFunction.Name#>"<#=code.StringBefore(", ", string.Join(", ", parameters.Select(p => p.ExecuteParameterName).ToArray()))#>);
    }
    IEnumerable<<#=returnTypeElement#>> <#=interfaceName#>.<#=code.Escape(edmFunction)#>(<#=paramList#>)
    {
        return <#=code.Escape(edmFunction)#>(<#=String.Join(", ", parameters.Select(p => p.FunctionParameterName).ToArray())#>);
    }
<#
        }

        region.End();
#>
}
<#
if (!String.IsNullOrEmpty(namespaceName))
{
    PopIndent();
#>
}
<#
}
#>
<#+
string AccessibilityAndVirtual(string accessibility)
{
    if (accessibility != "private")
    {
        return accessibility + " virtual";
    }

    return accessibility;
}
#>

 

Enjoy Smile

Posted by Matthieu MEZIL | with no comments

SubObjectSet

With EF, when you use TPH or TPC inheritance mapping scenarii, the EntitySet is on the base class.

As I mentioned often in the past with EF v1, you can add a property in your context which returns the EntitySet.OfType<MySubType>().

Ok it’s interesting but… In EF v1, the EntitySet is an ObjectQuery<T> property and our property also but in EF v2 the EntitySet is an ObjectSet<T>. This class implements the IObjectSet<T> interface which has three methods to add, attach and delete entities.

One guy tells me that he wants to be able to use these methods directly on the “sub EntitySet” property.

To realize it, I made the following class:

public class SubObjectSet<TBase, TInherited> : ObjectQuery<TInherited>, IObjectSet<TInherited>

    where TBase : class

    where TInherited : class, TBase

{

    public ObjectSet<TBase> ObjectSet { get; private set; }

 

    public SubObjectSet(ObjectSet<TBase> objectSet)

        : base(objectSet.OfType<TInherited>().CommandText, objectSet.Context)

    {

        ObjectSet = objectSet;

    }

 

    #region IObjectSet<TInherited> Members

    public void AddObject(TInherited entity)

    {

        ObjectSet.AddObject(entity);

    }

    public void Attach(TInherited entity)

    {

        ObjectSet.Attach(entity);

    }

    public void DeleteObject(TInherited entity)

    {

        ObjectSet.DeleteObject(entity);

    }

    #endregion

}

Posted by Matthieu MEZIL | with no comments

Entity Framework v2: How to get only one entity easier with EF4

Alex James wrote an extension method which allows to get only one entity from a query and the entity key.

If we have the key, I think it’s useless to allow it for all queries and it’s useful only for EntitySet. With EF4, this extension method can be applied on ObjectSet class instead of ObjectQuery class.

// In the first version of Entity Framework, the ObjectSet class doesn’t exist, EntitySet were some ObjectQuery.

// ObjectSet<T> class inherits from ObjectQuery<T>

This simplifies the code because we can directly use the (Try)GetObjectByKey method:

public static class ObjectSetExtension

{

    public static T Get<T>(this ObjectSet<T> objectSet, object key) where T : class

    {

        object value;

        objectSet.Context.TryGetObjectByKey(new EntityKey(string.Concat(objectSet.Context.DefaultContainerName, ".", objectSet.EntitySet.Name), objectSet.EntitySet.ElementType.KeyMembers.Single().Name, key), out value);

        return (T)value;

    }

    public static T Get<T>(this ObjectSet<T> objectSet, params EntityKeyMember[] keys) where T : class

    {

        object value;

        objectSet.Context.TryGetObjectByKey(new EntityKey(string.Concat(objectSet.Context.DefaultContainerName, ".", objectSet.EntitySet.Name), keys), out value);

        return (T)value;

    }

}

We can use the second extension method for entities with composite key.

Entity Framework: Undo Redo v2

After my first Undo Redo POC version, one of my customers wanted to be able to manage many actions per Undo / Redo.

So I added two extension methods: BeginGroupOfUndoActions and EndGroupOfUndoActions.

My code is now this one:

public static class ObjectContextExtension

{

    private static Dictionary<ObjectContext, ObjectContextUndoRedo> _objectContextUndoRedo = new Dictionary<ObjectContext, ObjectContextUndoRedo>();

 

    public static void ActivateUndoRedoTracking(this ObjectContext context, int undoStackLength)

    {

        ObjectContextUndoRedo objectContextUndoRedo;

        _objectContextUndoRedo.TryGetValue(context, out objectContextUndoRedo);

        if (objectContextUndoRedo == null)

        {

            objectContextUndoRedo = new ObjectContextUndoRedo { Context = context };

            _objectContextUndoRedo.Add(context, objectContextUndoRedo);

        }

        objectContextUndoRedo.ActivateUndoRedoTracking(undoStackLength);

    }

 

    public static bool CanUndo(this ObjectContext context)

    {

        ObjectContextUndoRedo objectContextUndoRedo;

        _objectContextUndoRedo.TryGetValue(context, out objectContextUndoRedo);

        if (objectContextUndoRedo == null)

            throw new InvalidOperationException();

        return objectContextUndoRedo.CanUndo;

    }

 

    public static void Undo(this ObjectContext context)

    {

        ObjectContextUndoRedo objectContextUndoRedo;

        _objectContextUndoRedo.TryGetValue(context, out objectContextUndoRedo);

        if (objectContextUndoRedo == null)

            throw new InvalidOperationException();

        objectContextUndoRedo.Undo();

    }

 

    public static bool CanRedo(this ObjectContext context)

    {

        ObjectContextUndoRedo objectContextUndoRedo;

        _objectContextUndoRedo.TryGetValue(context, out objectContextUndoRedo);

        if (objectContextUndoRedo == null)

            throw new InvalidOperationException();

        return objectContextUndoRedo.CanRedo;

    }

 

    public static void Redo(this ObjectContext context)

    {

        ObjectContextUndoRedo objectContextUndoRedo;

        _objectContextUndoRedo.TryGetValue(context, out objectContextUndoRedo);

        if (objectContextUndoRedo == null)

            throw new InvalidOperationException();

        objectContextUndoRedo.Redo();

    }

 

    public static void BeginGroupOfUndoActions(this ObjectContext context)

    {

        ObjectContextUndoRedo objectContextUndoRedo;

        _objectContextUndoRedo.TryGetValue(context, out objectContextUndoRedo);

        if (objectContextUndoRedo == null)

            throw new InvalidOperationException();

        objectContextUndoRedo.MultipleActions = true;

    }

 

    public static void EndGroupOfUndoActions(this ObjectContext context)

    {

        ObjectContextUndoRedo objectContextUndoRedo;

        _objectContextUndoRedo.TryGetValue(context, out objectContextUndoRedo);

        if (objectContextUndoRedo == null)

            throw new InvalidOperationException();

        objectContextUndoRedo.MultipleActions = false;

    }

 

    private class ObjectContextUndoRedo

    {

        private List<List<UndoRedoAction>> _undo, _redo;

        private int _undoStackLength;

        private bool _trackChanges;

 

        public ObjectContext Context { get; set; }

 

        private bool _multipleActions;

        public bool MultipleActions

        {

            get { return _multipleActions; }

            set

            {

                _multipleActions = value;

                if (value)

                    _undo.Insert(0, new List<UndoRedoAction>());

            }

        }

 

        public void ActivateUndoRedoTracking(int undoStackLength)

        {

            _undoStackLength = undoStackLength;

            _undo = new List<List<UndoRedoAction>>(undoStackLength);

            _redo = new List<List<UndoRedoAction>>(undoStackLength);

            _trackChanges = true;

 

            var objectStateEntries = Context.ObjectStateManager.GetObjectStateEntries(EntityState.Added | EntityState.Deleted | EntityState.Modified | EntityState.Unchanged).ToList();

 

            PropertyChangingEventHandler entityModifing = null;

            entityModifing = (sender, e) =>

            {

                var propInfo = sender.GetType().GetProperty(e.PropertyName, BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic);

                var value = propInfo.GetValue(sender, null);

                var undoRedoAction = new UndoRedoAction { EntityState = EntityState.Modified, UndoAction = () => propInfo.SetValue(sender, value, null) };

                var inpc = sender as INotifyPropertyChanged;

                if (inpc != null)

                {

                    PropertyChangedEventHandler entityModified = null;

                    entityModified = (s, e2) =>

                        {

                            if (e.PropertyName == e2.PropertyName && _trackChanges)

                            {

                                var newValue = propInfo.GetValue(sender, null);

                                undoRedoAction.RedoAction = () => propInfo.SetValue(sender, newValue, null);

                                if (MultipleActions)

                                    _undo[0].Add(undoRedoAction);

                                else

                                    _undo.Insert(0, new List<UndoRedoAction>() { undoRedoAction });

                                if (_undo.Count > _undoStackLength)

                                    _undo.RemoveAt(_undoStackLength);

                                _redo.Clear();

                            }

                            inpc.PropertyChanged -= entityModified;

                        };

                    inpc.PropertyChanged += entityModified;

                }

            };

            foreach (var e in objectStateEntries.Select(ose => ose.Entity as INotifyPropertyChanging).Where(inpc => inpc != null))

                e.PropertyChanging += entityModifing;

 

            Context.ObjectStateManager.ObjectStateManagerChanged += (sender, e) =>

            {

                switch (e.Action)

                {

                    case CollectionChangeAction.Add:

                        var inpc = e.Element as INotifyPropertyChanging;

                        if (inpc != null)

                            inpc.PropertyChanging += entityModifing;

                        break;

                }

            };

        }

 

        public bool CanUndo

        {

            get { return _undo != null && _undo.Any(); }

        }

 

        public void Undo()

        {

            if (!CanUndo)

                throw new InvalidOperationException();

            var undoRedoAction = _undo.First();

            _undo.RemoveAt(0);

            _trackChanges = false;

            foreach (var undoAction in undoRedoAction)

                undoAction.UndoAction();

            _trackChanges = true;

            _redo.Insert(0, undoRedoAction);

        }

 

        public bool CanRedo

        {

            get { return _redo != null && _redo.Any(); }

        }

 

        public void Redo()

        {

            if (!CanRedo)

                throw new InvalidOperationException();

            var undoRedoAction = _redo.First();

            _redo.RemoveAt(0);

            _trackChanges = false;

            foreach (var redoAction in undoRedoAction)

                redoAction.RedoAction();

            _trackChanges = true;

            _undo.Insert(0, undoRedoAction);

        }

    }

 

    private class UndoRedoAction

    {

        public EntityState EntityState { get; set; }

        public Action UndoAction { get; set; }

        public Action RedoAction { get; set; }

    }

}

And to make a demo about it, I did an unit test:

[TestClass]

public class ObjectContextExtensionTest

{

    [TestMethod]

    public void Test()

    {

        using (var context = new NorthwindEntities())

        {

            var c = context.Categories.First();

            context.ActivateUndoRedoTracking(5);

            var cOriginalCategoryName = c.CategoryName;

            c.CategoryName = "CN";

            var c2 = context.Categories.OrderBy(c3 => c3.CategoryID).Skip(1).First();

            var c2OriginalCategoryName = c2.CategoryName;

            c2.CategoryName = "C2N";

            c.CategoryName = "CN2";

            context.Undo();

            Assert.AreEqual("CN", c.CategoryName);

            Assert.AreEqual("C2N", c2.CategoryName);

            context.Undo();

            Assert.AreEqual("CN", c.CategoryName);

            Assert.AreEqual(c2OriginalCategoryName, c2.CategoryName);

            context.Undo();

            Assert.AreEqual(cOriginalCategoryName, c.CategoryName);

            Assert.AreEqual(c2OriginalCategoryName, c2.CategoryName);

            context.Redo();

            Assert.AreEqual("CN", c.CategoryName);

            Assert.AreEqual(c2OriginalCategoryName, c2.CategoryName);

            context.Redo();

            Assert.AreEqual("CN", c.CategoryName);

            Assert.AreEqual("C2N", c2.CategoryName);

            context.Redo();

            Assert.AreEqual("CN2", c.CategoryName);

            Assert.AreEqual("C2N", c2.CategoryName);

 

            context.BeginGroupOfUndoActions();

            c.CategoryName = "CN3";

            c2.CategoryName = "C2N2";

            context.BeginGroupOfUndoActions();

            c.CategoryName = "CN4";

            c2.CategoryName = "C2N3";

            context.EndGroupOfUndoActions();

            c.CategoryName = "CN5";

            c2.CategoryName = "C2N4";

            context.Undo();

            Assert.AreEqual("CN5", c.CategoryName);

            Assert.AreEqual("C2N3", c2.CategoryName);

            context.Undo();

            Assert.AreEqual("CN4", c.CategoryName);

            Assert.AreEqual("C2N3", c2.CategoryName);

            context.Undo();

            Assert.AreEqual("CN3", c.CategoryName);

            Assert.AreEqual("C2N2", c2.CategoryName);

            context.Undo();

            Assert.AreEqual("CN2", c.CategoryName);

            Assert.AreEqual("C2N", c2.CategoryName);

            context.Redo();

            Assert.AreEqual("CN3", c.CategoryName);

            Assert.AreEqual("C2N2", c2.CategoryName);

            context.Redo();

            Assert.AreEqual("CN4", c.CategoryName);

            Assert.AreEqual("C2N3", c2.CategoryName);

            context.Redo();

            Assert.AreEqual("CN5", c.CategoryName);

            Assert.AreEqual("C2N3", c2.CategoryName);

            context.Redo();

            Assert.AreEqual("CN5", c.CategoryName);

            Assert.AreEqual("C2N4", c2.CategoryName);

        }

    }

}

 

 

EF : Undo Redo

How to use Undo/Redo with EntityFramework? This isn’t managed by EF.

So we will have to do it ourselves.

We could clone the entities and group them into a Stack but in this case I worry about my memory usage growing too much.

So I prefer another solution with Action.

Note that for this POC, I just manage Undo/Redo on scalar properties.

public static class ObjectContextExtension

{

    private static Dictionary<ObjectContext, ObjectContextUndoRedo> _objectContextUndoRedo = new Dictionary<ObjectContext, ObjectContextUndoRedo>();

 

    public static void ActivateUndoRedoTracking(this ObjectContext context, int undoStackLength)

    {

        ObjectContextUndoRedo objectContextUndoRedo;

        if (_objectContextUndoRedo.ContainsKey(context))

            objectContextUndoRedo = _objectContextUndoRedo[context];

        else

        {

            objectContextUndoRedo = new ObjectContextUndoRedo { Context = context };

            _objectContextUndoRedo.Add(context, objectContextUndoRedo);

        }

        objectContextUndoRedo.ActivateUndoRedoTracking(undoStackLength);

    }

 

    public static bool CanUndo(this ObjectContext context)

    {

        if (!_objectContextUndoRedo.ContainsKey(context))

            throw new InvalidOperationException();

        return _objectContextUndoRedo[context].CanUndo;

    }

 

    public static void Undo(this ObjectContext context)

    {

        if (!_objectContextUndoRedo.ContainsKey(context))

            throw new InvalidOperationException();

        _objectContextUndoRedo[context].Undo();

    }

 

    public static bool CanRedo(this ObjectContext context)

    {

        if (!_objectContextUndoRedo.ContainsKey(context))

            throw new InvalidOperationException();

        return _objectContextUndoRedo[context].CanRedo;

    }

 

    public static void Redo(this ObjectContext context)

    {

        if (!_objectContextUndoRedo.ContainsKey(context))

            throw new InvalidOperationException();

        _objectContextUndoRedo[context].Redo();

    }

 

    private class ObjectContextUndoRedo

    {

        private List<UndoRedoAction> _undo, _redo;

        private int _undoStackLength;

        private bool _trackChanges;

 

        public ObjectContext Context { get; set; }

 

        public void ActivateUndoRedoTracking(int undoStackLength)

        {

            _undoStackLength = undoStackLength;

            _undo = new List<UndoRedoAction>(undoStackLength);

            _redo = new List<UndoRedoAction>(undoStackLength);

            _trackChanges = true;

 

            var objectStateEntries = Context.ObjectStateManager.GetObjectStateEntries(EntityState.Added | EntityState.Deleted | EntityState.Modified | EntityState.Unchanged).ToList();

 

            PropertyChangingEventHandler entityModifing = null;

            entityModifing = (sender, e) =>

            {

                var propInfo = sender.GetType().GetProperty(e.PropertyName);

                var value = propInfo.GetValue(sender, null);

                var undoRedoAction = new UndoRedoAction { EntityState = EntityState.Modified, UndoAction = () => propInfo.SetValue(sender, value, null) };

                var inpc = sender as INotifyPropertyChanged;

                if (inpc != null)

                {

                    PropertyChangedEventHandler entityModified = null;

                    entityModified = (s, e2) =>

                        {

                            if (e.PropertyName == e2.PropertyName && _trackChanges)

                            {

                                var newValue = propInfo.GetValue(sender, null);

                                undoRedoAction.RedoAction = () => propInfo.SetValue(sender, newValue, null);

                                _undo.Insert(0, undoRedoAction);

                                if (_undo.Count > _undoStackLength)

                                    _undo.RemoveAt(_undoStackLength);

                                _redo.Clear();

                            }

                            inpc.PropertyChanged -= entityModified;

                        };

                    inpc.PropertyChanged += entityModified;

                }

            };

            foreach (var e in objectStateEntries.Select(ose => ose.Entity as INotifyPropertyChanging).Where(inpc => inpc != null))

                e.PropertyChanging += entityModifing;

 

            Context.ObjectStateManager.ObjectStateManagerChanged += (sender, e) =>

            {

                switch (e.Action)

                {

                    case CollectionChangeAction.Add:

                        var inpc = e.Element as INotifyPropertyChanging;

                        if (inpc != null)

                            inpc.PropertyChanging += entityModifing;

                        break;

                }

            };

        }

 

        public bool CanUndo

        {

            get { return _undo != null && _undo.Any(); }

        }

 

        public void Undo()

        {

            if (!CanUndo)

                throw new InvalidOperationException();

            var undoRedoAction = _undo.First();

            _undo.RemoveAt(0);

            _trackChanges = false;

            undoRedoAction.UndoAction();

            _trackChanges = true;

            _redo.Insert(0, undoRedoAction);

        }

 

        public bool CanRedo

        {

            get { return _redo != null && _redo.Any(); }

        }

 

        public void Redo()

        {

            if (!CanRedo)

                throw new InvalidOperationException();

            var undoRedoAction = _redo.First();

            _redo.RemoveAt(0);

            _trackChanges = false;

            undoRedoAction.RedoAction();

            _trackChanges = true;

            _undo.Insert(0, undoRedoAction);

        }

    }

 

    private class UndoRedoAction

    {

        public EntityState EntityState { get; set; }

        public Action UndoAction { get; set; }

        public Action RedoAction { get; set; }

    }

}

 The main question in my opinion is: does a complete unod/redo make sense? Indeed, we will have some big issues with cascade, Identity, concurrent access...

How to split your EDM v2?

After my previous post about it, my customer asks me the following question: how to get a complete graph (with categories, suppliers, products, order details, orders, customers and employees)?

To realize it, we have to add “sort of” navigation properties for the entities like this:

private Supplier _supplier;

/// <remarks>

/// Changes aren't saved

/// </remarks>

public Supplier Supplier

{

    get

    {

        if (_supplier == null)

            _supplier = this.GetSupplier();

        return _supplier;

    }

    set { _supplier = value; }

}

 

private IEnumerable<Orders.OrderDetail> _orderDetails;

/// <remarks>

/// Changes aren't saved

/// </remarks>

public IEnumerable<Orders.OrderDetail> OrderDetails

{

    get

    {

        if (_orderDetails == null)

            _orderDetails = this.GetOrderDetails();

        return _orderDetails;

    }

    set { _orderDetails = value; }

}

Then, the idea is to set these informations at first.

For this, we can do the following:

using (var stockContext = new StocksEntities())

{

    var categories = stockContext.Categories.Include("Products").ToList();

    foreach (var p in stockContext.ObjectStateManager.GetObjectStateEntries(EntityState.Unchanged).Select(e => e.Entity).OfType<Entities.Stocks.Product>())

    {

        using (var supplierContext = new SuppliersEntities())

        {

            p.Supplier = (from p2 in supplierContext.Products

                          where p2.ProductID == p.ProductID

                          select p2.Supplier).FirstOrDefault();

        }

        using (var orderContext = new OrdersEntities())

        {

            p.OrderDetails = (from od in orderContext.OrderDetails.Include("Order.Customer")

                              where od.ProductID == p.ProductID

                              select od).ToList();

            foreach (var o in orderContext.ObjectStateManager.GetObjectStateEntries(EntityState.Unchanged).Select(e => e.Entity).OfType<Entities.Orders.Order>())

            {

                using (var employeeContext = new EmployeesEntities())

                {

                    o.Employee = (from oe in employeeContext.Orders

                                  where oe.OrderID == o.OrderID

                                  select oe.Employee).FirstOrDefault();

                }

            }

        }

    }

}

However, this way can generate a lot of SQL queries and so the execution isn’t very fast. If we want to get only part of the categories (with all their graphs), we can use this code:

using (var stockContext = new StocksEntities())

{

    using (var supplierContext = new SuppliersEntities())

    {

        using (var orderContext = new OrdersEntities())

        {

            using (var employeeContext = new EmployeesEntities())

            {

                var categories = stockContext.Categories.Include("Products").ToList();

                var products = categories.SelectMany(c => c.Products);

                var suppliers = supplierContext.Suppliers.Where(BuildContainsExpression<Supplier, int>(s => s.SupplierID, (from p in products

               where p.SupplierID.HasValue

               select p.SupplierID.Value).Distinct())).ToList();

                var orderDetails = orderContext.OrderDetails.Include("Order.Customer").Where(BuildContainsExpression<Entities.Orders.OrderDetail, int>(od => od.ProductID, products.Select(p => p.ProductID))).ToList();

                var orders = orderDetails.Select(od => od.Order);

                var employees = employeeContext.Employees.Where(BuildContainsExpression<Employee, int>(e => e.

EmployeeID, (from o in orders

             where o.EmployeeID.HasValue

             select o.EmployeeID.Value).Distinct())).ToList();

                foreach (var p in products)

                {

                    p.Supplier = suppliers.FirstOrDefault(s => s.SupplierID == p.SupplierID);

                    p.OrderDetails = (from od in orderDetails

                                      where od.ProductID == p.ProductID

                                      select od);

                    foreach (var o in orders)

                        o.Employee = employees.FirstOrDefault(e => o.EmployeeID == e.EmployeeID);

                }

            }

        }

    }

}

If we want to get all entities, we can directly use the following code:

using (var stockContext = new StocksEntities())

{

    using (var supplierContext = new SuppliersEntities())

    {

        using (var orderContext = new OrdersEntities())

        {

            using (var employeeContext = new EmployeesEntities())

            {

                var categories = stockContext.Categories.Include("Products").ToList();

                var products = categories.SelectMany(c => c.Products);

                var suppliers = supplierContext.Suppliers.ToList();

                var orderDetails = orderContext.OrderDetails.Include("Order.Customer").ToList();

                var orders = orderDetails.Select(od => od.Order);

                var employees = employeeContext.Employees.ToList();

                foreach (var p in products)

                {

                    p.Supplier = suppliers.FirstOrDefault(s => s.SupplierID == p.SupplierID);

                    p.OrderDetails = (from od in orderDetails

                                      where od.ProductID == p.ProductID

                                      select od);

                    foreach (var o in orders)

                        o.Employee = employees.FirstOrDefault(e => o.EmployeeID == e.EmployeeID);

                }

            }

        }

    }

}

ValidateOnBuild

The VS 2008 SP1 EDM designer doesn’t support all the mapping scenarii:

  • Complex Types (supported with VS 2010 Beta 1)
  • TPC
  • Horizontal Entity Splitting

I know another (free) designer which does Wink.

The most irritating thing with VS 2008 EDM designer (it isn’t true, likely, with VS 2010 Beta 1) is the fact that it generates wrong errors. And these F… errors are shown when we compile our project even if our project do compile. Indeed, the emdx file isn’t compiled. It’s the code generated from the edmx which is compiled.

When the errors are like: “Error 11005: ComplexType elements are not supported in the Entity Designer.”, you can be irritated: but WTF! Angry

An edmx is divided into four parts:

  • the 3 EDM parts:
    • SSDL // DB description
    • CSDL // entity description
    • MSL // mapping between SSDL ans CSDL description
  • a designer own part

In this last part, you have an option.

<edmx:Options>

  <DesignerInfoPropertySet>

    <DesignerProperty Name="ValidateOnBuild" Value="false" />

  </DesignerInfoPropertySet>

</edmx:Options>

When it is false, no error message with compilation and you keep zen. Smile

How to split your EDM?

One of my customers wants to code an ERP. To make it, he wants to use EF. His DB has more than 600 tables with a lot of relationships between all of them. The problem is the fact that Entity Framework has some difficulties with big models. You should look at Srikanth Mandadi two posts about it (working-with-large-models-in-entity-framework-part-1.aspx et working-with-large-models-in-entity-framework-part-2.aspx).

If you have a big model, the best is to split it in multiple EDM.

An ERP is composed of several modules. The idea is to make one model per module. The problem is the fact that an entity can be useful in several modules. For example, with Northwind (I know it isn’t a big DB, comment joke are useless Smile), a product can be used:

  • by a stock management module

image

  • by a supplier module

image

  • by an order module

image

So, in theory, it implies that we shouldn’t be able to split Category (Stocks module), Product (Stocks, Suppliers and Orders modules), Supplier (Suppliers module), OrderDetail (Orders module), Order (Orders module) and Customer (Orders module).

We will add a fourth EDM used by an employee management module. This EDM will contain an entity Employee, Order and OrderDetail.

image

So, how to work with four EDM?

First point, we will make these four EDM. To reduce the conflict risk, we will consider that an entity can be modified by only one module. For this, we will set all the properties except the entity key properties private in other EDM.

It is also possible to “reduce” the entity types. For example, in Supplier module, we don’t need the stock management information.

Ok nice! Smile

So the problem now is when we want to do some queries relative to several modules.

At first, we will get the best supplier per category.

The supplier and the category entities aren’t in the same model. If this query is called often, it’s better to add Category entity in Suppliers module EDM (or the opposite). Then it will be possible to map it on the real table or on an SSDL view.

If the query speed isn’t extremely important and we can do two SQL queries instead of only one, we can use the following way.

The first idea is, for each “double” entity, to implement an interface which has only the properties of the entity key:

public interface IProduct

{

    int ProductID { get; }

}

 

public interface IOrder

{

    int OrderID { get; }

}

 

public interface IOrderDetail

{

    int ProductID { get; }

    int OrderID { get; }

}

Then we can use methods to get all the entity relationships:

public static class Product

{

    public static Category GetCategory(this IProduct product)

    {

        return GetCategory(product.ProductID);

    }

    public static Category GetCategory(int productID)

    {

        using (var context = new StocksEntities())

        {

            var p = new Stocks.Product { ProductID = productID };

            context.AttachTo("Products", p);

            p.CategoryReference.Load();

            return p.Category;

        }

    }

 

    public static Supplier GetSupplier(this IProduct product)

    {

        return GetSupplier(product.ProductID);

    }

    public static Supplier GetSupplier(int productID)

    {

        using (var context = new SuppliersEntities())

        {

            var p = new Suppliers.Product { ProductID = productID };

            context.AttachTo("Products", p);

            p.SupplierReference.Load();

            return p.Supplier;

        }

    }

 

    public static IEnumerable<IOrderDetail> GetOrderDetails(this IProduct product)

    {

        return GetOrderDetails(product.ProductID);

    }

    public static IEnumerable<IOrderDetail> GetOrderDetails(int productID)

    {

        using (var context = new OrdersEntities())

        {

            var p = new Orders.Product { ProductID = productID };

            context.AttachTo("Products", p);

            p.OrderDetails.Load();

            return p.OrderDetails.AsEnumerable().OfType<IOrderDetail>();

        }

    }

}

 

public static class Order

{

    public static Employee GetEmployee(this IOrder o)

    {

        return GetEmployee(o.OrderID);

    }

    public static Employee GetEmployee(int orderID)

    {

        using (var context = new EmployeesEntities())

        {

            var o = new Employees.Order { OrderID = orderID };

            context.AttachTo("Orders", o);

            o.EmployeeReference.Load();

            return o.Employee;

        }

    }

 

    public static Customer GetCustomer(this IOrder o)

    {

        return GetCustomer(o.OrderID);

    }

    public static Customer GetCustomer(int orderID)

    {

        using (var context = new OrdersEntities())

        {

            var o = new Orders.Order { OrderID = orderID };

            context.AttachTo("Orders", o);

            o.CustomerReference.Load();

            return o.Customer;

        }

    }

}

 

public static class OrderDetail

{

    public static IProduct GetProduct(this IOrderDetail od)

    {

        return GetProduct(od.OrderID, od.ProductID);

    }

    public static IProduct GetProduct(int orderID, int productID)

    {

        using (var context = new OrdersEntities())

        {

            var od = new Orders.OrderDetail { OrderID = orderID, ProductID = productID };

            context.AttachTo("OrderDetails", od);

            od.ProductReference.Load();

            return od.Product;

        }

    }

 

    public static Category GetCategory(this IOrderDetail od)

    {

        return Product.GetCategory(od.ProductID);

    }

 

    public static Employee GetEmployee(this IOrderDetail od)

    {

        return Order.GetEmployee(od.OrderID);

    }

 

    public static Customer GetCustomer(this IOrderDetail od)

    {

        return Order.GetCustomer(od.OrderID);

    }

}

Here, we can write the query like this:

using (var stockContext = new StocksEntities())

{

    var bestSupplierPerCategory = from c in stockContext.Categories.Include("Products").AsEnumerable()

                                  let productSuppliers = from p in c.Products

                                                         select p.GetSupplier()

                                  let suppliers = from s in productSuppliers.Distinct()

                                                  orderby productSuppliers.Count(su => su.SupplierID == s.SupplierID) descending

                                                  select s

                                  select new { Category = c, Supplier = suppliers.FirstOrDefault() };

}

However, it isn’t great because the most part of the query is a LINQ To Objects query. We can do better than this.

To illustrate it, we will take a more complex case: get the best employee per category.

To optimize the query, it’s better not to do the group by with LINQ To Object (getting the category products OrderDetails, then OrderDetail Employee). So we won’t use the extension methods defined previously. Instead of these, we will use two contexts and we will mix them in a LINQ query like this:

using (var stockContext = new StocksEntities())

{

    using (var employeesContext = new EmployeesEntities())

    {

        var bestEmployeePerCategory =

            from c in

                (from c in stockContext.Categories

                 select new

                 {

                     Category = c,

                     ProductsID = (from p in c.Products

                                   select p.ProductID)

                 }).AsEnumerable()

            let bestEmployee = (from od in employeesContext.OrderDetails.Where(BuildContainsExpression<Entities.Employees.OrderDetail, int>(od2 => od2.ProductID, c.ProductsID))

                                let e = od.Order.Employee

                                group od by new { e.EmployeeID, e.LastName, e.FirstName } into g

                                let sold = g.Sum(od => (double)od.UnitPrice * (double)od.Quantity * (1D - (double)od.Discount))

                                orderby sold descending

                                select new { g.Key.EmployeeID, g.Key.LastName, g.Key.FirstName, Sold = sold }).FirstOrDefault()

            select new { Category = c.Category, Employee = bestEmployee };

    }

}

You can find the DB generation script, the project and the unit tests here.

EF4 POCO: my tests

I published an unit tests project to show how POCO works with EF4. I also wrote a BetaBugs test class in order to illustrate the POCO implementation Beta bugs.

Posted by Matthieu MEZIL | with no comments
More Posts Next page »