Matthieu MEZIL : Entity Framework (EF)

Data oriented WCF service Agile with EF4 and a “sort of” provider LINQ

Matthieu MEZIL — Wed, 25 Nov 2009 00:17:38 GMT

I use a different approach than Julie’s one for EF Agile development. Indeed, I use here Self-Tracking Entities and I massively use T4.

The first step is to create a project DAL in which we will add an edmx.

In my sample, I use Northwind DataBase with only the Customers, Orders and [Order Details] tables.

When it’s done, we will add a new item of type ADO.NET Self-Tracking Entity Generator to our project.

It will add two T4 templates to our project:

one with entities + some classes and interfaces for Self-Tracking

one with the context + a static class with extension methods.

We will move our entities template into another project Entities.

Then we have to add the Entities reference in our DAL project. We will also set the edmx path in our templates.

Then, we will create a new project Repositories. In this project, we will define an interface INorthwindRepository.

We can see the redundancy of INorthwindRepository. That’s why, in my sample, these interfaces (INorthwindRepository and IEntitySet) are also generated with a T4 template which uses my edmx. What is great here is the fact that if we change our data model (my edmx), we have no more code to write. We just have to regenerate all my T4 templates.

Now, we will go back to my context. It has to implement INorthwindRepository. For this, we will update my T4 template to have this:

For WCF, we need a contract. So I will add two new projects: Services and ServiceContracts (with T4).

So now, if you already have the T4 templates (which are independent of the project), you wrote 0 lines of code!

We can see the assemblies independence with EF (except for the DAL of course) :

I wanted to use Unity. To do it, I use Alexey Zakharov approach. So I integrate his WCFFacility project and classes Bootstrapper and UnityServiceLocatorAdapter in my WCFService (web project host). After creating the svc and the config file, my service is finished!

Now in the client tier, I wanted to use a “sort of” provider LINQ. For this, I inspired from one of my old demo made for ALT.NET French community.

We will create a Client.LINQ project in which we will define two classes: ClientLINQ and MyQueryable.

public static class ClientLINQ
{
    public static MyQueryable<T> Where<T>(this MyQueryable<T> source, Expression<Func<T, bool>> where)
    {
        source.WhereValue = string.Concat(source.WhereValue ?? "", where.Body.ToString().Replace(string.Format("{0}.", where.Parameters[0].Name), "it.").Replace("\"", "'").Replace("||", " OR ").Replace("&&", " AND "));
        return source;
    }

    public static MyQueryable<T> OrderBy<T, T2>(this MyQueryable<T> source, Expression<Func<T, T2>> orderBy)
    {
        source.OrderByValue = orderBy.Body.ToString().Replace(string.Format("{0}.", orderBy.Parameters[0].Name), "it.");
        return source;
    }

    public static MyQueryable<T> Include<T>(this MyQueryable<T> source, string include)
    {
        source.IncludeValues.Add(include);
        return source;
    }

    public static MyQueryable<T> Skip<T>(this MyQueryable<T> source, int number)
    {
        source.SkipValue = number;
        return source;
    }

    public static MyQueryable<T> Take<T>(this MyQueryable<T> source, int number)
    {
        source.TakeValue = number;
        return source;
    }

    public static T FirstOrDefault<T>(this MyQueryable<T> source)
    {
        source.TakeValue = 1;
        return source.AsEnumerable().FirstOrDefault();
    }

    public static T First<T>(this MyQueryable<T> source)
    {
        source.TakeValue = 1;
        return source.AsEnumerable().First();
    }

    public static MyQueryable<T> ToMyQueryable<T>(this IEnumerable<T> source)
    {
        var value = source as MyQueryable<T>;
        if (value == null)
            value = new MyQueryable<T>(source);
        return value;
    }
}

public class MyQueryable<T> : IEnumerable<T>
{
    public MyQueryable()
    {
    }
    public MyQueryable(IEnumerable<T> enumerable)
    {
        Enumerable = enumerable;
    }

public IEnumerable<T> Enumerable { get; set; }

    public IEnumerator<T> GetEnumerator()
    {
        return Enumerable.GetEnumerator();
    }
    System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator()
    {
        return GetEnumerator();
    }

    private bool _allEntities = true;
    public bool AllEntities
    {
        get { return _allEntities; }
        internal set
        {
            _allEntities = value;
            if (value)
            {
                IncludeValues.Clear();
                WhereValue = null;
                OrderByValue = null;
                SkipValue = null;
                TakeValue = null;
            }
        }
    }
    private ObservableCollection<string> _includeValues;
    public ObservableCollection<string> IncludeValues
    {
        get
        {
            if (_includeValues == null)
            {
                _includeValues = new ObservableCollection<string>();
                _includeValues.CollectionChanged += (sender, e) =>
                    {
                        if (e.Action == NotifyCollectionChangedAction.Add)
                            AllEntities = false;
                    };
            }
            return _includeValues;
        }
    }
    private string _whereValue;
    public string WhereValue
    {
        get { return _whereValue; }
        internal set
        {
            if (value != null)
                AllEntities = false;
            _whereValue = value;
        }
    }
    private string _orderByValue;
    public string OrderByValue
    {
        get { return _orderByValue; }
        internal set
        {
            if (value != null)
                AllEntities = false;
            _orderByValue = value;
        }
    }
    private int? _skipValue;
    public int? SkipValue
    {
        get { return _skipValue; }
        internal set
        {
            if (value.HasValue)
                AllEntities = false;
            _skipValue = value;
        }
    }
    private int? _takeValue;
    public int? TakeValue
    {
        get { return _takeValue; }
        internal set
        {
            if (value.HasValue)
                AllEntities = false;
            _takeValue = value;
        }
    }
}

Then, we will create our client project (a Test project in my sample). In this project, I add Entities project and Client.LINQ references. Client.LINQ is not mandatory. I can also duplicate my entities code but in order to have Self-Tracking, I can’t use the default generated code. Then, we will add the WCF service reference. To use my “sort of” provider LINQ, I need some MyQueryable of my entities. Here also, (I hope that you guess it), we can use a T4 template.

public partial class NorthwindClientContext
{
private INorthwindService _service;

      public NorthwindClientContext(INorthwindService service)
      {
            _service = service;
      }

      public MyQueryable<Customer> Customers
      {
            get
            {
                  var value = new MyQueryable<Customer>();
                  value.Enumerable = GetCustomers(value);
                  return value;
            }
      }
      private IEnumerable<Customer> GetCustomers(MyQueryable<Customer> myQueryable)
      {
            IEnumerable<Customer> value;
            if (myQueryable.AllEntities)
                  value = _service.GetAllCustomers();
            else
                  value = _service.GetCustomers(myQueryable.IncludeValues.ToList(), myQueryable.WhereValue, myQueryable.OrderByValue, myQueryable.SkipValue, myQueryable.TakeValue);
            foreach (var entity in value)
                  yield return entity;
      }

      public MyQueryable<Order> Orders
      {
            get
            {
                  var value = new MyQueryable<Order>();
                  value.Enumerable = GetOrders(value);
                  return value;
            }
      }
      private IEnumerable<Order> GetOrders(MyQueryable<Order> myQueryable)
      {
            IEnumerable<Order> value;
            if (myQueryable.AllEntities)
                  value = _service.GetAllOrders();
            else
                  value = _service.GetOrders(myQueryable.IncludeValues.ToList(), myQueryable.WhereValue, myQueryable.OrderByValue, myQueryable.SkipValue, myQueryable.TakeValue);
            foreach (var entity in value)
                  yield return entity;
      }

      public MyQueryable<OrderDetail> OrderDetails
      {
            get
            {
                  var value = new MyQueryable<OrderDetail>();
                  value.Enumerable = GetOrderDetails(value);
                  return value;
            }
      }
      private IEnumerable<OrderDetail> GetOrderDetails(MyQueryable<OrderDetail> myQueryable)
      {
            IEnumerable<OrderDetail> value;
            if (myQueryable.AllEntities)
                  value = _service.GetAllOrderDetails();
            else
                  value = _service.GetOrderDetails(myQueryable.IncludeValues.ToList(), myQueryable.WhereValue, myQueryable.OrderByValue, myQueryable.SkipValue, myQueryable.TakeValue);
            foreach (var entity in value)
                  yield return entity;
      }
}

The use of the yield return will postpone the execution and allow MyQueryable properties to be set when GetCustomers / GetOrders / GetOrderDetails methods are called.

So the following code:

var order = (from o in new NorthwindClientContext(service).Orders.Include("Customer").Include("OrderDetails")
             where o.ShipCity == "PARIS"
             orderby o.OrderDate
             select o).Skip(2).First();

will call this method

_service.GetOrders(myQueryable.IncludeValues.ToList(), myQueryable.WhereValue, myQueryable.OrderByValue, myQueryable.SkipValue, myQueryable.TakeValue)

with these parameters:

wich will generate the following SQL query:

SELECT
[Project1].[OrderID] AS [OrderID],
[Project1].[CustomerID] AS [CustomerID],
[Project1].[EmployeeID] AS [EmployeeID],
[Project1].[OrderDate] AS [OrderDate],
[Project1].[RequiredDate] AS [RequiredDate],
[Project1].[ShippedDate] AS [ShippedDate],
[Project1].[ShipVia] AS [ShipVia],
[Project1].[Freight] AS [Freight],
[Project1].[ShipName] AS [ShipName],
[Project1].[ShipAddress] AS [ShipAddress],
[Project1].[ShipCity] AS [ShipCity],
[Project1].[ShipRegion] AS [ShipRegion],
[Project1].[ShipPostalCode] AS [ShipPostalCode],
[Project1].[ShipCountry] AS [ShipCountry],
[Project1].[CustomerID1] AS [CustomerID1],
[Project1].[CompanyName] AS [CompanyName],
[Project1].[ContactName] AS [ContactName],
[Project1].[ContactTitle] AS [ContactTitle],
[Project1].[Address] AS [Address],
[Project1].[City] AS [City],
[Project1].[Region] AS [Region],
[Project1].[PostalCode] AS [PostalCode],
[Project1].[Country] AS [Country],
[Project1].[Phone] AS [Phone],
[Project1].[Fax] AS [Fax],
[Project1].[C1] AS [C1],
[Project1].[OrderID1] AS [OrderID1],
[Project1].[ProductID] AS [ProductID],
[Project1].[UnitPrice] AS [UnitPrice],
[Project1].[Quantity] AS [Quantity],
[Project1].[Discount] AS [Discount]
FROM ( SELECT
      [Limit1].[OrderID] AS [OrderID],
      [Limit1].[CustomerID1] AS [CustomerID],
      [Limit1].[EmployeeID] AS [EmployeeID],
      [Limit1].[OrderDate] AS [OrderDate],
      [Limit1].[RequiredDate] AS [RequiredDate],
      [Limit1].[ShippedDate] AS [ShippedDate],
      [Limit1].[ShipVia] AS [ShipVia],
      [Limit1].[Freight] AS [Freight],
      [Limit1].[ShipName] AS [ShipName],
      [Limit1].[ShipAddress] AS [ShipAddress],
      [Limit1].[ShipCity] AS [ShipCity],
      [Limit1].[ShipRegion] AS [ShipRegion],
      [Limit1].[ShipPostalCode] AS [ShipPostalCode],
      [Limit1].[ShipCountry] AS [ShipCountry],
      [Limit1].[CustomerID2] AS [CustomerID1],
      [Limit1].[CompanyName] AS [CompanyName],
      [Limit1].[ContactName] AS [ContactName],
      [Limit1].[ContactTitle] AS [ContactTitle],
      [Limit1].[Address] AS [Address],
      [Limit1].[City] AS [City],
      [Limit1].[Region] AS [Region],
      [Limit1].[PostalCode] AS [PostalCode],
      [Limit1].[Country] AS [Country],
      [Limit1].[Phone] AS [Phone],
      [Limit1].[Fax] AS [Fax],
      [Extent3].[OrderID] AS [OrderID1],
      [Extent3].[ProductID] AS [ProductID],
      [Extent3].[UnitPrice] AS [UnitPrice],
      [Extent3].[Quantity] AS [Quantity],
      [Extent3].[Discount] AS [Discount],
      CASE WHEN ([Extent3].[OrderID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
      FROM   (SELECT TOP (1) [Filter1].[OrderID], [Filter1].[CustomerID1], [Filter1].[EmployeeID], [Filter1].[OrderDate], [Filter1].[RequiredDate], [Filter1].[ShippedDate], [Filter1].[ShipVia], [Filter1].[Freight], [Filter1].[ShipName], [Filter1].[ShipAddress], [Filter1].[ShipCity], [Filter1].[ShipRegion], [Filter1].[ShipPostalCode], [Filter1].[ShipCountry], [Filter1].[CustomerID2], [Filter1].[CompanyName], [Filter1].[ContactName], [Filter1].[ContactTitle], [Filter1].[Address], [Filter1].[City], [Filter1].[Region], [Filter1].[PostalCode], [Filter1].[Country], [Filter1].[Phone], [Filter1].[Fax]
            FROM ( SELECT [Extent1].[OrderID] AS [OrderID], [Extent1].[CustomerID] AS [CustomerID1], [Extent1].[EmployeeID] AS [EmployeeID], [Extent1].[OrderDate] AS [OrderDate], [Extent1].[RequiredDate] AS [RequiredDate], [Extent1].[ShippedDate] AS [ShippedDate], [Extent1].[ShipVia] AS [ShipVia], [Extent1].[Freight] AS [Freight], [Extent1].[ShipName] AS [ShipName], [Extent1].[ShipAddress] AS [ShipAddress], [Extent1].[ShipCity] AS [ShipCity], [Extent1].[ShipRegion] AS [ShipRegion], [Extent1].[ShipPostalCode] AS [ShipPostalCode], [Extent1].[ShipCountry] AS [ShipCountry], [Extent2].[CustomerID] AS [CustomerID2], [Extent2].[CompanyName] AS [CompanyName], [Extent2].[ContactName] AS [ContactName], [Extent2].[ContactTitle] AS [ContactTitle], [Extent2].[Address] AS [Address], [Extent2].[City] AS [City], [Extent2].[Region] AS [Region], [Extent2].[PostalCode] AS [PostalCode], [Extent2].[Country] AS [Country], [Extent2].[Phone] AS [Phone], [Extent2].[Fax] AS [Fax], row_number() OVER (ORDER BY [Extent1].[OrderDate] ASC) AS [row_number]
                  FROM [dbo].[Orders] AS [Extent1]
                  LEFT OUTER JOIN [dbo].[Customers] AS [Extent2] ON [Extent1].[CustomerID] = [Extent2].[CustomerID]
                  WHERE [Extent1].[ShipCity] = 'PARIS'
            ) AS [Filter1]
            WHERE [Filter1].[row_number] > 2
            ORDER BY [Filter1].[OrderDate] ASC ) AS [Limit1]
      LEFT OUTER JOIN [dbo].[Order Details] AS [Extent3] ON [Limit1].[OrderID] = [Extent3].[OrderID]
) AS [Project1]

ORDER BY [Project1].[OrderDate] ASC, [Project1].[OrderID] ASC, [Project1].[CustomerID1] ASC, [Project1].[C1] ASC

If we comment the Include, the SQL query is more readable :

SELECT TOP (1)
[Filter1].[OrderID] AS [OrderID],
[Filter1].[CustomerID] AS [CustomerID],
[Filter1].[EmployeeID] AS [EmployeeID],
[Filter1].[OrderDate] AS [OrderDate],
[Filter1].[RequiredDate] AS [RequiredDate],
[Filter1].[ShippedDate] AS [ShippedDate],
[Filter1].[ShipVia] AS [ShipVia],
[Filter1].[Freight] AS [Freight],
[Filter1].[ShipName] AS [ShipName],
[Filter1].[ShipAddress] AS [ShipAddress],
[Filter1].[ShipCity] AS [ShipCity],
[Filter1].[ShipRegion] AS [ShipRegion],
[Filter1].[ShipPostalCode] AS [ShipPostalCode],
[Filter1].[ShipCountry] AS [ShipCountry]
FROM ( SELECT [Extent1].[OrderID] AS [OrderID], [Extent1].[CustomerID] AS [CustomerID], [Extent1].[EmployeeID] AS [EmployeeID], [Extent1].[OrderDate] AS [OrderDate], [Extent1].[RequiredDate] AS [RequiredDate], [Extent1].[ShippedDate] AS [ShippedDate], [Extent1].[ShipVia] AS [ShipVia], [Extent1].[Freight] AS [Freight], [Extent1].[ShipName] AS [ShipName], [Extent1].[ShipAddress] AS [ShipAddress], [Extent1].[ShipCity] AS [ShipCity], [Extent1].[ShipRegion] AS [ShipRegion], [Extent1].[ShipPostalCode] AS [ShipPostalCode], [Extent1].[ShipCountry] AS [ShipCountry], row_number() OVER (ORDER BY [Extent1].[OrderDate] ASC) AS [row_number]
FROM [dbo].[Orders] AS [Extent1]
WHERE [Extent1].[ShipCity] = 'PARIS'
) AS [Filter1]
WHERE [Filter1].[row_number] > 2

ORDER BY [Filter1].[OrderDate] ASC

We can find our WHERE City = ‘Paris’, our WHERE row_number > 2 (for the Skip), out ORDER BY OrderDate and our TOP 1 (for the First).

Take care, this “sort of” provider LINQ is a POC. It is not finished. For example, it doesn’t support variables or the new operator (for date for example).

What happens if we integrate methods non supported by ClientLINQ?

It’s ok!

LINQ To Object will be used.

For example, the following LINQ query:

var customerInfos = (from o in new NorthwindClientContext(service).Orders.Include("Customer")
                     where o.ShipCity == "PARIS"
                     orderby o.OrderDate
                     group o by o.Customer into g
                     select new { g.Key.CompanyName, g.Key.ContactName, OrdersCount = g.Count() }).ToList();

will call this method

_service.GetOrders(myQueryable.IncludeValues.ToList(), myQueryable.WhereValue, myQueryable.OrderByValue, myQueryable.SkipValue, myQueryable.TakeValue)

with these parameters:

which will generate the following SQL query:

SELECT
[Extent1].[OrderID] AS [OrderID],
[Extent1].[CustomerID] AS [CustomerID],
[Extent1].[EmployeeID] AS [EmployeeID],
[Extent1].[OrderDate] AS [OrderDate],
[Extent1].[RequiredDate] AS [RequiredDate],
[Extent1].[ShippedDate] AS [ShippedDate],
[Extent1].[ShipVia] AS [ShipVia],
[Extent1].[Freight] AS [Freight],
[Extent1].[ShipName] AS [ShipName],
[Extent1].[ShipAddress] AS [ShipAddress],
[Extent1].[ShipCity] AS [ShipCity],
[Extent1].[ShipRegion] AS [ShipRegion],
[Extent1].[ShipPostalCode] AS [ShipPostalCode],
[Extent1].[ShipCountry] AS [ShipCountry],
[Extent2].[CustomerID] AS [CustomerID1],
[Extent2].[CompanyName] AS [CompanyName],
[Extent2].[ContactName] AS [ContactName],
[Extent2].[ContactTitle] AS [ContactTitle],
[Extent2].[Address] AS [Address],
[Extent2].[City] AS [City],
[Extent2].[Region] AS [Region],
[Extent2].[PostalCode] AS [PostalCode],
[Extent2].[Country] AS [Country],
[Extent2].[Phone] AS [Phone],
[Extent2].[Fax] AS [Fax]
FROM [dbo].[Orders] AS [Extent1]
LEFT OUTER JOIN [dbo].[Customers] AS [Extent2] ON [Extent1].[CustomerID] = [Extent2].[CustomerID]
WHERE [Extent1].[ShipCity] = 'PARIS'
ORDER BY [Extent1].[OrderDate] ASC

As you can see, no trace of group by. However, the result integrates it (with LINQ To Object).

In this post, I wanted to present my “sort of'” LINQ provider but the real goal of this post is to persuade you (and I’m sure you are ) of the EF / T4 couple productivity gain. Indeed,

your templates are reusable in different projects. You just have to update the edmx path and to let Visual Studio regenerate T4 templates code.
If you haven't already written the template you need, the dev time is not proportional to the number of entities which implies very quickly a productivity gain compare to a “classic” dev.

4 in 1: EF won’t fire DBAs, Velocity, SQL CLR and SQL Dependency

Matthieu MEZIL — Sat, 21 Nov 2009 15:42:16 GMT

Imagine that we have a table Cars with a PK Id (nvarchar(8)) based on the following regex pattern [1-9][0-9]{1,2}[A-Z]{2,3}[0-9]{2}. The last two numbers are a region code.

The increasement is done like this: 10AA[region], 11AA[region], …, 999AA[region], 10AB[region], …, 999ZZ[region], 10AAA[region], …, 999ZZZ[region]

This table can be huge.

In a first time, we will write a LINQ To Entities query to get regions with 3 letters:

var q1 = (from c in context.Cars
where c.Id.Contains("AAA")
select c.Id.Substring(c.Id.Length - 2)).Distinct();

The SQL generated query is the following:

SELECT
[Distinct1].[C1] AS [C1]
FROM ( SELECT DISTINCT

      SUBSTRING([Extent1].[ Id ], ((LEN([Extent1].[ Id ])) - 2) + 1, (LEN([Extent1].[ Id ])) - ((LEN([Extent1].[ Id ])) - 2)) AS [C1]
      FROM [dbo].[Cars] AS [Extent1]
      WHERE [Extent1].[ Id ] LIKE N'%AAA%'
) AS [Distinct1]

If I write it myself, I would probably write this:

SELECT DISTINCT
      RIGHT(Id, 2) AS [C1]
      FROM [Cars]
      WHERE [ Id ] LIKE N'%AAA%'

Note that the execution plan is the same!

Imagine that the DBA wants that I use the RIGHT function. With EF4, we can do it.

The Right doesn’t exist in C# but it does in ESQL. So we will use CSDL Functions.

In the CSDL, we will add the following code:

<Function Name="GetRegion" ReturnType="String">
<Parameter Name="car" Type="Self.Car" />
<DefiningExpression>
Right(car.Id, 2)
</DefiningExpression>
</Function>

Then,we will add an extension method to be able to use it in our LINQ To Entities queries:

public static class CarExtension
{
    [EdmFunction("CarsModel", "GetRegion")]
    public static string GetRegion(this Car car)
    {
        throw new NotImplementedException("Only used by LINQ To Entities");
    }
}

I now can write my query like this:

var q1 = (from c in context.Cars
where c.Id.Contains("AAA")
select c.GetRegion()).Distinct();

This LINQ query is translated as the following SQL query:

SELECT
[Distinct1].[C1] AS [C1]
FROM ( SELECT DISTINCT
      RIGHT([Extent1].[ Id ], 2) AS [C1]
      FROM [dbo].[Cars] AS [Extent1]
      WHERE [Extent1].[ Id ] LIKE N'%AAA%'
) AS [Distinct1]

Cool!

Now I want to get the last Id per region. I don’t think that there is a clean way to do it with SQL. Indeed, I don’t think that we can use Regex in SQL Server and we need it to determine which is the last id.

So to do this, I will use a LINQ To Object query. The “normal” way can be to write this:

var qL2E = from c in context.Cars
           orderby c.GetRegion()
           select c.Id;
var qL2O = from id in qL2E.AsEnumerable()
           group id by id.Substring(id.Length - 2) into g
           select new
           {
               Region = g.Key,
               Id = (from id in g
                     let letters = Regex.Match(id, "[A-Z]{2,3}")
                     orderby letters.Length descending, letters descending, Regex.Match(id, "^[0-9]{2,3}") descending
                     select id).FirstOrDefault()
           }.ToDictionary(id => id.Region, id => id.Id);

However, with this way, we will probably have an OutOfMemoryException. Indeed, with this query, all the Cars records are loaded in memory and we can have hundreds millions of rows.

In a first time, we will try a dichotomous approach.

The execution of this query is very slow (26 minutes and 18 seconds in my test with 562 012 347 records).

So what can we do?

The CPU is used by the DB at 100% and the slowly is only because of the DB. So it useless to imagine to parallelize our code.

We can imagine to resize the Data Base server but it is not the goal of this post.

In our case, the DBA (I specify that I am not a DBA) will probably want to change the Cars table schema in order to split into 3 columns the Id. However, in a lot of cases, it can be interesting to keep the Id column, which keeps the PK. Indeed, imagine that we have some existing applications. The idea is to add these columns with no impact on them.

So we will have four columns (all not nullable) :

Id
Number
Letters
Region

To set them, we will use CLR functions:

public class CarsFunction
{
    [SqlFunction]
    public static SqlInt16 GetNumber(SqlString id)
    {
        return new SqlInt16(short.Parse(Regex.Match(id.Value, "^[0-9]{2,3}").Value));
    }

    [SqlFunction]
    public static SqlString GetLetters(SqlString id)
    {
        return new SqlString(Regex.Match(id.Value, "[A-Z]{2,3}").Value);
    }

    [SqlFunction]
    public static SqlInt16 GetRegion(SqlString id)
    {
        return new SqlInt16(short.Parse(Regex.Match(id.Value, "[0-9]{2}$").Value));
    }
}

Then, in the DB, we will register the assembly and we will create some SQL functions from previous ones:

CREATE ASSEMBLY CarsFunctionsAssembly
FROM 'D:\documents\visual studio 2010\Projects\CarsFunctions\CarsFunctions\bin\Debug\CarsFunctions.dll'
GO

CREATE FUNCTION GetNumber(@id AS nvarchar(8)) RETURNS smallint AS EXTERNAL NAME CarsFunctionsAssembly.CarsFunction.GetNumber
GO

CREATE FUNCTION GetLetters(@id AS nvarchar(8)) RETURNS nvarchar(3) AS EXTERNAL NAME CarsFunctionsAssembly.CarsFunction.GetLetters
GO

CREATE FUNCTION GetRegion(@id AS nvarchar(8)) RETURNS smallint AS EXTERNAL NAME CarsFunctionsAssembly.CarsFunction.GetRegion

Ok, we have the functions, so we can be back on the new columns creation. Instead of creating “normal” columns, we will use computed columns. We can do it with the SSMS designer by setting the Computed Column Specification Formula to dbo.GetNumber(Id) (resp dbo.GetLetters(Id), dbo.GetRegion(Id)). In our case, the idea isn’t to calculate them each time but to keep their values. So we will set the “Is Persisted” property to true.

But there is an issue: the function has to be deterministic. Our function is. However, because it is a CLR function, SQL Server can’t know it. So we will tell it explicitly.

To do this, we will change the CLR functions code like this:

[SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static SqlInt16 GetNumber(SqlString id)
{
    return new SqlInt16(short.Parse(Regex.Match(id.Value, "^[0-9]{2,3}").Value));
}

[SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static SqlString GetLetters(SqlString id)
{
    return new SqlString(Regex.Match(id.Value, "[A-Z]{2,3}").Value);
}

[SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static SqlInt16 GetRegion(SqlString id)
{
    return new SqlInt16(short.Parse(Regex.Match(id.Value, "[0-9]{2}$").Value));
}

Now, we can persist the three columns.

Be careful, if we create an edmx from the DB, we will have a StoreGeneratedPattern="Computed" on the columns Number, Letters and Region. It implies that in each update, EF, we reload the properties Number, Letters et Region. In our case, the calculation is only done on the key. With Entity Framework, it is not possible to change the value of the key. So, it is useful to change the StoreGeneratedPattern from Computed to Identity.

Cool, we have fixed the INSERT issue.

Let’s now concentrate on the goal: our query.

We don’t need a dichotomous approach, a simple LINQ To Entities query is enough:

var ids = (from c in context.Cars
           group c by c.Region into g
           select new
           {
               Region = g.Key,
               LastId = (from c in g
                         orderby c.Letters.Length descending, c.Letters descending, c.Number descending
                         select c.Id).FirstOrDefault()
           }).AsEnumerable().ToDictionary(region => region.Region, region => region.LastId);

With this, our query runs in 1 minute and 42 seconds instead of 26 minutes and 18 seconds!

It’s better but it isn’t finished yet.

In the DB, we will set the Cars table cluster on the Region column (instead of the Id column).

In this case, the query execution falls to 12 seconds!

However, be careful! It’s really great in our sample but in the real life, don’t forget to study all the DB query on the table Cars before changing the cluster.

So the DBA role is important in the success of the project.

Now, imagine that we want to keep in cache the last id per region. To do it, we can use a static Dictionary. But there are two issues:

Imagine a N-Tiers scenario. If our application is deployed on more than one frontal server, it’s a shame to have to initialize the cache for each of them. Moreover, we have to maintain each of them with the last values
If another application adds or removes a car in the DB, how to maintain our cache with the last values?

To fix the first issue, we will Velocity. For the second, we will use SQLDependency.

Velocity is a distributed cache which allows a lot of very interesting things like load balancing. This distributed cache will be shared by all the frontal servers. It means that we won”t have to manage one cache per server.

What do we have to do to use Velocity? First point: download it and install it.

When the installation is done, we will add the following references in our project:

CacheBaseLibrary.dll
CASBase.dll
CASMain.dll
ClientLibrary.dll
FabricCommon.dll

Then, we have to start the Velocity cluster. In this CTP, all administration commands are written with the console “Administration Tool - Microsoft Distributed Cache”. Then we write the following command line:

start-cachecluster

Then, in the config file, we wiil add these lines:

Now we can imagine to query the cache in our application (server tier).

return new DataCacheFactory().GetDefaultCache().GetObjectsInRegion("LastImmatPerRegion").ToDictionary(keyValuePair => short.Parse(keyValuePair.Key), keyValuePair => (string)keyValuePair.Value);

With the data in the cache, the query executes in 30 ms!

Now, we have to fix the last point: initializing the cache and maintaining it.

To initialize it, it’s very easy. We just have to use the above query:

var cache = new DataCacheFactory().GetDefaultCache();

try
{
   cache.RemoveRegion(LAST_IMMAT_PER_REGION);
}
catch
{
}
cache.CreateRegion(LAST_IMMAT_PER_REGION, false);
foreach (var car in from c in context.Cars
                    group c by c.Region into g
                    select new
                    {
                        Region = g.Key,
                        LastId = (from c in g
                                  orderby c.Letters.Length descending, c.Letters descending, c.Number descending
                                  select c.Id).FirstOrDefault()
                    })
    cache.Put(car.Region.ToString(), car.LastId, LAST_IMMAT_PER_REGION);

Now to maintain the cache with the last values, it’s harder. My idea is to use a SQLDependency. The issue is the fact that we can’t, a priori, get the modifications with SQLDependency and, of course, I don’t want to refresh all my cache. So my idea is to add a Trigger on the Cars table which will fill a new table (for updates).

To begin, we will create this new table: CarsModificationsTmp with six columns:

TmpId (int Identity(1,1), PK),
CarId (nvarchar(8), not nullable)
Number (smallint, not nullable)
Letters (nvarchar(3), not nullable)
Region (smallint, not nullable),
Deleted (bit, not nullable, default=0)

Then, we have to define triggers on Cars table:

CREATE TRIGGER CarsInserted
ON Cars
FOR INSERT
AS
BEGIN
DECLARE @Id AS nvarchar(8)
DECLARE @Number AS smallint
DECLARE @Letters AS nvarchar(3)
DECLARE @Region AS smallint
SELECT @Id = Id, @Number = Number, @Letters = Letters, @REGION = Region FROM Inserted
INSERT INTO CarsModificationsTmp(CarId, Number, Letters, Region) VALUES(@Id, @Number, @Letters, @Region)
END
GO

CREATE TRIGGER CarsDeleted
ON Cars
FOR DELETE
AS
BEGIN
DECLARE @Region AS smallint
DECLARE @Id AS nvarchar(8)
DECLARE @Number AS smallint
DECLARE @Letters AS nvarchar(3)
SELECT @Id = Id, @Number = Number, @Letters = Letters, @REGION = Region FROM Deleted
INSERT INTO CarsModificationsTmp(CarId, Number, Letters, Region, Deleted) VALUES(@Id, @Number, @Letters, @Region, 1)
END

Last, we will modify our code to use SQL Dependency.

private void Load()
{
    using (var context = new CarsContainer())
    {
        _cache = new DataCacheFactory().GetDefaultCache();
        try
        {
            _cache.RemoveRegion(LAST_IMMAT_PER_REGION);
        }
        catch
        {
        }
        _cache.CreateRegion(LAST_IMMAT_PER_REGION, false);
        foreach (var car in from c in context.Cars
                            group c by c.Region into g
                            select new
                            {
                                Region = g.Key,
                                LastId = (from c in g
                                          orderby c.Letters.Length descending, c.Letters descending, c.Number descending
                                          select c.Id).FirstOrDefault()
                            })
            _cache.Put(car.Region.ToString(), car.LastId, LAST_IMMAT_PER_REGION);

        _connectionString = ((EntityConnection)context.Connection).StoreConnection.ConnectionString;
        SqlDependency.Stop(_connectionString);
        SqlDependency.Start(_connectionString);
        DefineCarsNotification();
    }
}

private void DefineCarsNotification()
{
    var connection = new SqlConnection(_connectionString);
    var command = connection.CreateCommand();
    command.CommandText = "SELECT TmpId, Region, CarId, Deleted FROM CarsModificationsTmp";
    command.CommandType = CommandType.Text;
    var sqlDependency = new SqlDependency(command);
    sqlDependency.OnChange += SqlDependency_OnChange;
    connection.Open();
    command.ExecuteNonQuery();
    connection.Close();
}

private void SqlDependency_OnChange(object sender, SqlNotificationEventArgs e)
{
    using (var contextModifications = new CarsContainer())
    {
        bool any = false;
        foreach (var cm in contextModifications.CarsModificationsTmps)
        {
            any = true;
            var cacheModifications = new DataCacheFactory().GetDefaultCache();
            var cacheItem = cacheModifications.GetCacheItem(cm.Region.ToString(), LAST_IMMAT_PER_REGION);
            if (cm.Deleted)
            {
                if (cacheItem != null && cacheItem.Value.ToString() == cm.CarId)
                {
                    var newLastId = (from c in contextModifications.Cars
                                     where c.Region == cm.Region
                                     orderby c.Letters.Length descending, c.Letters descending, c.Number descending
                                     select c.Id).FirstOrDefault();
                    if (newLastId == null)
                        _cache.Remove(cm.Region.ToString(), LAST_IMMAT_PER_REGION);
                    else
                        _cache.Put(cm.Region.ToString(), newLastId, LAST_IMMAT_PER_REGION);
                }
            }
            else
            {
                string letters;
                int lettersCompare = 0;
                if (cacheItem == null || (letters = Regex.Match(cacheItem.Value.ToString(), "[A-Z]{2,3}").Value).Length < cm.Letters.Length || (letters.Length == cm.Letters.Length && ((lettersCompare = String.Compare(letters, cm.Letters)) < 0 || lettersCompare == 0 && short.Parse(Regex.Match(cacheItem.Value.ToString(), "^[0-9]{2,3}").Value) < cm.Number)))
                    _cache.Put(cm.Region.ToString(), cm.CarId, LAST_IMMAT_PER_REGION);
            }
            contextModifications.DeleteObject(cm);
        }
        if (any)
            contextModifications.SaveChanges();
    }
    DefineCarsNotification();

}

We have a possible bug with this. Indeed, SqlDependency_OnChange is asynchronous and so we can have some parallels calls which implies some concurrent issues. Velocity DataCache is thread-safe. But, with the previous code, the concurrency doesn’t allow us to be sure we have the last id in the cache. To fix this, we will use a lock.

if (cm.Deleted)
{
    lock (_lockObject)
    {
        if (cacheItem != null && cacheItem.Value.ToString() == cm.CarId)
        {
            var newLastId = (from c in contextModifications.Cars
                             where c.Region == cm.Region
                             orderby c.Letters.Length descending, c.Letters descending, c.Number descending
                             select c.Id).FirstOrDefault();
            if (newLastId == null)
                _cache.Remove(cm.Region.ToString(), LAST_IMMAT_PER_REGION);
            else
                _cache.Put(cm.Region.ToString(), newLastId, LAST_IMMAT_PER_REGION);
        }
    }
}
else
{
    string letters;
    int lettersCompare = 0;
    lock (_lockObject)
    {
        if (cacheItem == null || (letters = Regex.Match(cacheItem.Value.ToString(), "[A-Z]{2,3}").Value).Length < cm.Letters.Length || (letters.Length == cm.Letters.Length && ((lettersCompare = String.Compare(letters, cm.Letters)) < 0 || lettersCompare == 0 && short.Parse(Regex.Match(cacheItem.Value.ToString(), "^[0-9]{2,3}").Value) < cm.Number)))
            _cache.Put(cm.Region.ToString(), cm.CarId, LAST_IMMAT_PER_REGION);
    }
}

We have another thing like this with the SaveChanges. Indeed, with concurrency, we can try to delete an already deleted DataRow which will throw an OptimisticConcurrencyException. My idea is: if the SaveChanges throws an exception, we will detach all the entities which implies the exception (ie: which are already deleted).

if (any)
    for (; ; )
        try
        {
            contextModifications.SaveChanges();
            break;
        }
        catch (OptimisticConcurrencyException ex)
        {
            foreach (var ose in ex.StateEntries)
                contextModifications.Detach(ose.Entity);
        }

Our solution is now thread-safe and is (I hope so ) bug free. However, we can improve our code. If we have two update notifications in parallel which aren’t in the same region, it’s a shame to wait that the first ends before treating the second (what we do with our single lock). In fact, my idea is to use a dictionnary of locks instead of a single one. However, the Dictionary class is not thread-safe! We can use another object to lock the Dictionary but with .NET 4, we have a very useful new class: ConcurrentDictionary.

private ConcurrentDictionary<short, object> _concurrentDictionary = new ConcurrentDictionary<short,object>();

Now, we will modify our code to use a lock per region:

object lockRegion = _concurrentDictionary.GetOrAdd(cm.Region, new object());
if (cm.Deleted)
{
    lock (lockRegion)
    {
        if (cacheItem != null && cacheItem.Value.ToString() == cm.CarId)
        {
            var newLastId = (from c in contextModifications.Cars
                             where c.Region == cm.Region
                             orderby c.Letters.Length descending, c.Letters descending, c.Number descending
                             select c.Id).FirstOrDefault();
            if (newLastId == null)
                _cache.Remove(cm.Region.ToString(), LAST_IMMAT_PER_REGION);
            else
                _cache.Put(cm.Region.ToString(), newLastId, LAST_IMMAT_PER_REGION);
        }
    }
}
else
{
    string letters;
    int lettersCompare = 0;
    lock (lockRegion)
    {
        if (cacheItem == null || (letters = Regex.Match(cacheItem.Value.ToString(), "[A-Z]{2,3}").Value).Length < cm.Letters.Length || (letters.Length == cm.Letters.Length && ((lettersCompare = String.Compare(letters, cm.Letters)) < 0 || lettersCompare == 0 && short.Parse(Regex.Match(cacheItem.Value.ToString(), "^[0-9]{2,3}").Value) < cm.Number)))
          _cache.Put(cm.Region.ToString(), cm.CarId, LAST_IMMAT_PER_REGION);
    }

}

It’s now finish. Enjoy

ADO.NET Data Services Client context

Matthieu MEZIL — Wed, 04 Nov 2009 23:40:24 GMT

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

Generate a WCF service from an edmx with T4 v2

Matthieu MEZIL — Mon, 26 Oct 2009 23:13:00 GMT

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.

Entity Framework: the productivity way

Matthieu MEZIL — Tue, 20 Oct 2009 13:39:12 GMT

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

Matthieu MEZIL — Wed, 07 Oct 2009 05:10:06 GMT

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!

Matthieu MEZIL — Sat, 26 Sep 2009 18:17:24 GMT

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

Thanks by advance for your votes

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

Matthieu MEZIL — Wed, 02 Sep 2009 20:49:00 GMT

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

Matthieu MEZIL — Tue, 01 Sep 2009 20:34:06 GMT

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.

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:

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:
 
Only with the AsParallel extension method, It’s hard to do it more easily!  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:

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 have FK in EF v1?

Matthieu MEZIL — Tue, 21 Jul 2009 21:03:08 GMT

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

T4 for View Generation

Matthieu MEZIL — Mon, 29 Jun 2009 23:49:00 GMT

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();
                        }
                    }
                }
            }
        }
    }
#>

POCO T4

Matthieu MEZIL — Fri, 26 Jun 2009 08:23:00 GMT

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

SubObjectSet

Matthieu MEZIL — Tue, 16 Jun 2009 17:22:00 GMT

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
}

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

Matthieu MEZIL — Sat, 13 Jun 2009 01:26:00 GMT

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.