Matthieu MEZIL : T4

EF and N-Tiers

Matthieu MEZIL — Sun, 13 Dec 2009 00:56:00 GMT

Even if it’s really easier with Self-Tracking entities and EF4, a difficult point remains in N-Tiers scenarios.

In a previous post, I explained how with T4, I can generate all my WCF service. Now, what about the client tiers?

I explained how can I use LINQ queries and translate them into WCF service methods.

Now my objective is to have a real context on the client tier. I already did it for ADO .NET Data Services using T4, I now have one (still with T4 of course) for WCF.

One of my customers told me that he didn’t like the fact that with ADO .NET Data Services, when you want to access to the context collection, it implies a REST query by default. He wanted to use the cache by default.

With my context, I have this and I use AsQueryable extension method to generate a query on the server.

[TestClass]
public class UnitTest
{
    [TestMethod]
    public void TestService()
    {
        INorthwindService context = new NorthwindClientContext();
        Assert.AreNotEqual(0, context.GetCustomers().Count);
    }

    [TestMethod]
    public void TestFilter()
    {
        INorthwindService context = new NorthwindClientContext();
        Assert.AreEqual(1, context.GetCustomers(null, "it.CustomerID='ALFKI'", null, null, null).Count);
    }

    [TestMethod]
    public void TestLINQWhere()
    {
        using (var context = new NorthwindClientContext())
        {
            Assert.AreEqual(1, (from c in context.Customers.AsQueryable()
                                where c.CustomerID == "ALFKI"
                                select c).Count());
        }
    }

    [TestMethod]
    public void TestLINQWhere2()
    {
        using (var context = new NorthwindClientContext())
        {
            var customerID = "ALFKI";
            Assert.AreEqual(1, (from c in context.Customers.AsQueryable()
                                where c.CustomerID == customerID
                                select c).Count());
        }
    }

    [TestMethod]
    public void TestLINQWhere3()
    {
        using (var context = new NorthwindClientContext())
        {
            var customer = new Customer { CustomerID = "ALFKI" };
            Assert.AreEqual(1, (from c in context.Customers.AsQueryable()
                                where c.CustomerID == customer.CustomerID
                                select c).Count());
        }
    }

    [TestMethod]
    public void TestLINQWhere4()
    {
        using (var context = new NorthwindClientContext())
        {
            Assert.AreNotEqual(0, (from o in context.Orders.AsQueryable()
                                   where o.OrderDate > new DateTime(1997, 1, 31)
                                   select o).Count());
        }
    }

    [TestMethod]
    public void TestLINQWhere5()
    {
        using (var context = new NorthwindClientContext())
        {
            var d = new DateTime(1997, 1, 31);
            Assert.AreNotEqual(0, (from o in context.Orders.AsQueryable()
                                   where o.OrderDate > d
                                   select o).Count());
        }
    }

    [TestMethod]
    public void TestLINQTake()
    {
        using (var context = new NorthwindClientContext())
        {
            Assert.AreEqual(2, (from c in context.Customers.AsQueryable()
                                select c).Take(2).Count());
        }
    }

    [TestMethod]
    public void TestFirstAndInclude()
    {
        using (var context = new NorthwindClientContext())
        {
            var order = (from o in context.Orders.AsQueryable().Include(Order.CUSTOMER_NAME).Include(Order.ORDERDETAILS_NAME)
                         where o.ShipCity == "PARIS"
                         orderby o.OrderDate
                         select o).Skip(2).First();
            Assert.IsNotNull(order.Customer);
            Assert.AreEqual(1, context.Customers.Count);
            Assert.AreEqual(order.Customer, context.Customers[0]);
            Assert.AreEqual(1, context.Orders.Count);
            Assert.AreEqual(order, context.Orders[0]);
            Assert.AreNotEqual(0, order.OrderDetails);
        }
    }

    [TestMethod]
    public void NoTracking()
    {
        using (var context = new NorthwindClientContext())
        {
            context.MergeOption = MergeOption.NoTracking;
            var l = context.Customers.AsQueryable().ToList();
            Assert.AreEqual(0, context.Customers.Count);
            Assert.AreNotEqual(0, l.Count);
        }
    }

    [TestMethod]
    public void AppendOnly()
    {
        using (var context = new NorthwindClientContext())
        {
            context.MergeOption = MergeOption.AppendOnly;
            var l = context.Customers.AsQueryable().ToList();
            Assert.AreEqual(l.Count, context.Customers.Count);
        }
    }

    [TestMethod]
    public void AppendOnly2()
    {
        using (var context = new NorthwindClientContext())
        {
            context.MergeOption = MergeOption.AppendOnly;
            var c = new Customer { CustomerID = "ALFKI" };
            context.Customers.Attach(c);
            context.Customers.AsQueryable().ToList();
            Assert.IsNull(c.CompanyName);
            Assert.AreEqual(0, c.ChangeTracker.ModifiedProperties.Count);
        }
    }

    [TestMethod]
    public void OverwriteChanges()
    {
        using (var context = new NorthwindClientContext())
        {
            context.MergeOption = MergeOption.OverwriteChanges;
            var l = context.Customers.AsQueryable().ToList();
            Assert.AreEqual(l.Count, context.Customers.Count);
        }
    }

    [TestMethod]
    public void OverwriteChanges2()
    {
        using (var context = new NorthwindClientContext())
        {
            context.MergeOption = MergeOption.OverwriteChanges;
            var c = new Customer { CustomerID = "ALFKI" };
            context.Customers.Attach(c);
            context.Customers.AsQueryable().ToList();
            Assert.IsNotNull(c.CompanyName);
            Assert.AreEqual(0, c.ChangeTracker.ModifiedProperties.Count);
        }
    }

    [TestMethod]
    public void PreserveChanges()
    {
        using (var context = new NorthwindClientContext())
        {
            context.MergeOption = MergeOption.PreserveChanges;
            var l = context.Customers.AsQueryable().ToList();
            Assert.AreEqual(l.Count, context.Customers.Count);
        }
    }

    [TestMethod]
    public void PreserveChanges2()
    {
        using (var context = new NorthwindClientContext())
      {
            context.MergeOption = MergeOption.PreserveChanges;
            var c = new Customer { CustomerID = "ALFKI" };
            context.Customers.Attach(c);
            context.Customers.AsQueryable().ToList();
            Assert.IsNull(c.CompanyName);
            Assert.AreNotEqual(0, c.ChangeTracker.ModifiedProperties.Count);
            Assert.IsTrue(c.ChangeTracker.ModifiedProperties.Contains("CompanyName"));
        }
    }

    [TestMethod]
    public void Save()
    {
        using (var context = new NorthwindClientContext())
        {
            //Add
            var psCount = context.Products.AsQueryable().Count();
            var p = new Product { ProductName = "p" };
            context.Products.Add(p);
            context.SaveChanges();
            Assert.AreNotEqual(0, p.ProductID);
            Assert.AreEqual(ObjectState.Unchanged, p.ChangeTracker.State);
            Assert.AreEqual(psCount + 1, context.Products.AsQueryable().ToList().Count);

            //Update
            p.ProductName = "p2";
            Assert.AreEqual(1, p.ChangeTracker.ModifiedProperties.Count);
            Assert.AreEqual("ProductName", p.ChangeTracker.ModifiedProperties[0]);
            context.SaveChanges();
            context.MergeOption = MergeOption.NoTracking;
            var p2 = context.Products.AsQueryable().Where(pr => pr.ProductID == p.ProductID).First();
            Assert.AreNotEqual(p, p2);
            Assert.AreEqual("p2", p2.ProductName);

            //Remove
            context.Products.Remove(p);
            Assert.AreEqual(ObjectState.Deleted, p.ChangeTracker.State);
            context.SaveChanges();
            Assert.AreEqual(psCount, context.Products.AsQueryable().ToList().Count);
        }
    }

    [TestMethod]
    public void Save2()
    {
        using (var context = new NorthwindClientContext())
        {
            var csCount = context.Categories.AsQueryable().Count();
            var c1 = new Category { CategoryName = "cn" };
            context.Categories.Add(c1);
            var psCount = context.Products.AsQueryable().Count();
            var p1 = new Product { ProductName = "p1" };
            var p2 = new Product { ProductName = "p2" };
            context.Products.Add(p1);
            context.Products.Add(p2);
            context.SaveChanges();
            using (var context2 = new NorthwindClientContext())
            {
                Assert.AreEqual(csCount + 1, context2.Categories.AsQueryable().Count());
                Assert.AreEqual(psCount + 2, context2.Products.AsQueryable().Count());
            }
            context.Categories.Remove(c1);
            context.Products.Remove(p1);
            p2.ProductName = "p2Bis";
            context.SaveChanges();
            using (var context2 = new NorthwindClientContext())
            {
                Assert.AreEqual(csCount, context2.Categories.AsQueryable().Count());
                Assert.AreEqual(psCount + 1, context2.Products.AsQueryable().Count());
                Assert.AreEqual("p2Bis", context2.Products.Last().ProductName);
            }
            context.Products.Remove(p2);
            context.SaveChanges();
            using (var context2 = new NorthwindClientContext())
            {
                Assert.AreEqual(psCount, context2.Products.AsQueryable().Count());
            }
        }
    }

    [TestMethod]
    public void TestSaveManyToMany()
    {
        CustomerDemographic cd;
        using (var context = new NorthwindClientContext())
        {
            cd = new CustomerDemographic { CustomerTypeID = "CD", CustomerDesc = "cd desc" };
            context.CustomerDemographics.Add(cd);
            context.SaveChanges();
        }
        using (var context = new NorthwindClientContext())
        {
            var c = context.Customers.AsQueryable().First();
            c.CustomerDemographics.Add(cd);
            Assert.AreEqual(ObjectState.Unchanged, cd.ChangeTracker.State);
            Assert.AreEqual(1, context.CustomerDemographics.Count);
            Assert.AreEqual(1, cd.Customers.Count);
            context.SaveChanges();
        }
        using (var context = new NorthwindClientContext())
        {
            var c = context.Customers.AsQueryable().Include(Customer.CUSTOMERDEMOGRAPHICS_NAME).First();
            Assert.AreNotEqual(0, context.CustomerDemographics.Count);
            Assert.AreNotEqual(0, cd.Customers.Count);
            context.CustomerDemographics.Remove(context.CustomerDemographics.First(cd2 => cd2.CustomerTypeID.TrimEnd() == "CD"));
            context.SaveChanges();
        }
    }

    [TestMethod]
    public void TestAddViaCollection()
    {
        using (var context = new NorthwindClientContext())
        {
            int psCount;
            using (var context2 = new NorthwindClientContext())
            {
                psCount = context2.Products.AsQueryable().Count();
            }
            var c = context.Categories.AsQueryable().First();
            var p = new Product { ProductName = "pn" };
            c.Products.Add(p);
            Assert.AreEqual(1, c.Products.Count);
            Assert.AreEqual(1, context.Products.Count);
            Assert.AreEqual(context.Products[0], p);
            Assert.AreEqual(p.Category, c);
            Assert.AreEqual(p.CategoryID, c.Id);
            Assert.AreEqual(ObjectState.Added, p.ChangeTracker.State);
            context.SaveChanges();
            using (var context2 = new NorthwindClientContext())
            {
                Assert.AreEqual(psCount + 1, context2.Products.AsQueryable().Count());
            }
            Assert.AreEqual(1, c.Products.Count);
            context.Products.Remove(p);
            Assert.AreEqual(0, c.Products.Count);
            context.SaveChanges();
        }
    }

    [TestMethod]
    public void TestAddViaCollection2()
    {
        using (var context = new NorthwindClientContext())
        {
            int psCount;
            using (var context2 = new NorthwindClientContext())
            {
                psCount = context2.Products.AsQueryable().Count();
            }
            var c = context.Categories.AsQueryable().First();
            var p = new Product { ProductName = "pn", Category = c };
            Assert.AreEqual(1, c.Products.Count);
            Assert.AreEqual(1, context.Products.Count);
            Assert.AreEqual(context.Products[0], p);
            Assert.AreEqual(p.Category, c);
            Assert.AreEqual(p.CategoryID, c.Id);
            Assert.AreEqual(ObjectState.Added, p.ChangeTracker.State);
            context.SaveChanges();
            using (var context2 = new NorthwindClientContext())
            {
                Assert.AreEqual(psCount + 1, context2.Products.AsQueryable().Count());
            }
            Assert.AreEqual(1, c.Products.Count);
            context.Products.Remove(p);
            Assert.AreEqual(0, c.Products.Count);
            context.SaveChanges();
        }
    }

    [TestMethod]
    public void TestAddViaCollection3()
    {
        using (var context = new NorthwindClientContext())
        {
            var c = new Category { CategoryName = "cn" };
            context.Categories.Add(c);
            var p = new Product { ProductName = "pn", Category = c };
            Assert.AreEqual(1, c.Products.Count);
            Assert.AreEqual(1, context.Products.Count);
            Assert.AreEqual(context.Products[0], p);
            Assert.AreEqual(c, p.Category);
            Assert.AreEqual(c.Id, p.CategoryID);
            Assert.AreEqual(1, context.Categories.Count);
            Assert.AreEqual(c, context.Categories[0]);
            context.SaveChanges();
            Assert.AreEqual(c, p.Category);
            Assert.AreEqual(c.Id, p.CategoryID);
            Assert.AreEqual(1, c.Products.Count);
            Assert.AreEqual(p, c.Products[0]);
            context.Categories.Remove(c);
            Assert.IsNull(p.Category);
            Assert.IsNull(p.CategoryID);
            Assert.AreEqual(ObjectState.Modified, p.ChangeTracker.State);
           Assert.AreEqual(1, p.ChangeTracker.ModifiedProperties.Count);
            Assert.AreEqual("CategoryID", p.ChangeTracker.ModifiedProperties[0]);
            context.SaveChanges();
            context.Products.Remove(p);
            context.SaveChanges();
        }
    }

    [TestMethod]
    public void TestAddViaCollection4()
    {
        using (var context = new NorthwindClientContext())
        {
            var c = new Category { CategoryName = "cn" };
            var p = new Product { ProductName = "pn", Category = c };
            context.Categories.Add(c);
            Assert.AreEqual(1, c.Products.Count);
            Assert.AreEqual(1, context.Products.Count);
            Assert.AreEqual(context.Products[0], p);
            Assert.AreEqual(p.Category, c);
            Assert.AreEqual(p.CategoryID, c.Id);
            Assert.AreEqual(1, context.Categories.Count);
            Assert.AreEqual(c, context.Categories[0]);
        }
    }

    [TestMethod]
    public void TestAddViaCollection5()
    {
      using (var context = new NorthwindClientContext())
        {
            var c = new Customer { CustomerID = "A" };
            var cd = new CustomerDemographic();
            c.CustomerDemographics.Add(cd);
            context.Customers.Attach(c);
            Assert.AreEqual(1, context.Customers.Count);
            Assert.AreEqual(1, context.CustomerDemographics.Count);
            Assert.AreEqual(c, context.Customers[0]);
            Assert.AreEqual(cd, context.CustomerDemographics[0]);
           Assert.AreEqual(1, cd.Customers.Count);
        }
    }

    [TestMethod]
    [ExpectedException(typeof(InvalidOperationException))]
    public void TestEntitySetAttachWithSameKey()
    {
        using (var context = new NorthwindClientContext())
        {
            context.Categories.Attach(new Category { Id = 1 });
            context.Categories.Attach(new Category { Id = 1 });
        }
    }

    [TestMethod]
    [ExpectedException(typeof(InvalidOperationException))]
    public void TestEntitySetAddWithSameKey()
    {
        using (var context = new NorthwindClientContext())
        {
            context.Customers.Add(new Customer { CustomerID = "C" });
            context.Customers.Add(new Customer { CustomerID = "C" });
        }
    }

    [TestMethod]
    public void TestEntitySetAddWithSameKeyIdentity()
    {
        using (var context = new NorthwindClientContext())
        {
            context.Categories.Add(new Category { Id = 0 });
            context.Categories.Add(new Category { Id = 0 });
        }
    }

    [TestMethod]
    [ExpectedException(typeof(InvalidOperationException))]
    public void TestNavigationPropertyManyAttachWithSameKey()
    {
        using (var context = new NorthwindClientContext())
        {
            var cd = new CustomerDemographic { CustomerTypeID = "CD" };
            context.CustomerDemographics.Attach(cd);
            context.Customers.Attach(new Customer { CustomerID = "C" });
            cd.Customers.Attach(new Customer { CustomerID = "C" });
        }
    }

    [TestMethod]
    [ExpectedException(typeof(InvalidOperationException))]
    public void TestNavigationPropertyManyAddWithSameKey()
    {
        using (var context = new NorthwindClientContext())
        {
            var cd = new CustomerDemographic { CustomerTypeID = "CD" };
            context.CustomerDemographics.Attach(cd);
            context.Customers.Attach(new Customer { CustomerID = "C" });
            cd.Customers.Add(new Customer { CustomerID = "C" });
        }
    }

    [TestMethod]
    public void MultipleAddAttachOfSameObject()
    {
        using (var context = new NorthwindClientContext())
        {
            var c = new Customer { CustomerID = "C" };
            context.Customers.Add(c);
            context.Customers.Add(c);
            context.Customers.Attach(c);
            new CustomerDemographic { CustomerTypeID = "CD" }.Customers.Add(c);
            Assert.AreEqual(1, context.Customers.Count);
        }
    }

    [TestMethod]
    public void MultipleRelationAddAttachOfSameObject()
    {
        using (var context = new NorthwindClientContext())
        {
            var c = new Customer { CustomerID = "C" };
            var cd = new CustomerDemographic { CustomerTypeID = "CD" };
            c.CustomerDemographics.Add(cd);
            c.CustomerDemographics.Add(cd);
            c.CustomerDemographics.Attach(cd);
            Assert.AreEqual(1, c.CustomerDemographics.Count);
            Assert.AreEqual(1, cd.Customers.Count);
       }
    }

    [TestMethod]
    public void DeleteCascade()
    {
        using (var context = new NorthwindClientContext())
        {
            var o = new Order();
            var od = new OrderDetail { Order = o };
            Assert.AreEqual(ObjectState.Detached, o.ChangeTracker.State);
            Assert.AreEqual(ObjectState.Detached, od.ChangeTracker.State);
            context.Orders.Add(o);
            Assert.AreEqual(ObjectState.Added, o.ChangeTracker.State);
            Assert.AreEqual(ObjectState.Added, od.ChangeTracker.State);
            context.Orders.Remove(o);
            Assert.AreEqual(ObjectState.Detached, o.ChangeTracker.State);
            Assert.AreEqual(ObjectState.Detached, od.ChangeTracker.State);
        }
    }

    [TestMethod]
    public void DeleteCascade2()
    {
        using (var context = new NorthwindClientContext())
        {
            var o = new Order();
            var od = new OrderDetail { Order = o, Product = context.Products.AsQueryable().First(), Discount = 0, Quantity = 1, UnitPrice = 10 };
            context.Orders.Add(o);
            context.SaveChanges();
            var od2 = new OrderDetail { Order = o };
            context.Orders.Remove(o);
            Assert.AreEqual(ObjectState.Deleted, o.ChangeTracker.State);
            Assert.AreEqual(ObjectState.Deleted, od.ChangeTracker.State);
            Assert.AreEqual(ObjectState.Detached, od2.ChangeTracker.State);
            context.SaveChanges();
        }
    }

    [TestMethod]
    public void AttachRelationInLoad()
    {
        using (var context = new NorthwindClientContext())
        {
            var c = new Category { Id = 1 };
            context.Categories.Attach(c);
            var p = new Product { CategoryID = 1 };
            context.Products.Attach(p);
            Assert.AreEqual(c, p.Category);
            Assert.AreEqual(1, c.Products.Count);
            Assert.AreEqual(p, c.Products[0]);
        }
    }

    [TestMethod]
    public void AttachRelationInLoad2()
    {
        using (var context = new NorthwindClientContext())
        {
            var c = new Category { Id = 1 };
            context.Categories.Add(c);
            var p = new Product { CategoryID = 1 };
            context.Products.Add(p);
            Assert.AreEqual(c, p.Category);
            Assert.AreEqual(1, c.Products.Count);
            Assert.AreEqual(p, c.Products[0]);
        }
    }

    [TestMethod]
    public void AttachRelationInLoad3()
    {
        using (var context = new NorthwindClientContext())
        {
            var p = new Product { CategoryID = 1 };
            context.Products.Attach(p);
            var c = new Category { Id = 1 };
            context.Categories.Attach(c);
            Assert.AreEqual(c, p.Category);
            Assert.AreEqual(1, c.Products.Count);
            Assert.AreEqual(p, c.Products[0]);
        }
    }

    [TestMethod]
    public void AttachRelationInLoad4()
    {
        using (var context = new NorthwindClientContext())
        {
            var p = new Product { CategoryID = 1 };
            context.Products.Add(p);
            var c = new Category { Id = 1 };
            context.Categories.Add(c);
            Assert.AreEqual(c, p.Category);
            Assert.AreEqual(1, c.Products.Count);
            Assert.AreEqual(p, c.Products[0]);
        }
    }

    [TestMethod]
    public void AttachRelationInLoad5()
    {
        using (var context = new NorthwindClientContext())
        {
            context.Products.AsQueryable().ToList();
            var c = context.Categories.AsQueryable().First();
            Assert.AreEqual(context.Products.Where(p => p.CategoryID == c.Id).Count(), c.Products.Count);
            foreach (var p in context.Products.Where(p => p.CategoryID == c.Id))
            {
              Assert.AreEqual(c, p.Category);
                Assert.IsTrue(c.Products.Contains(p));
            }
        }
    }

    [TestMethod]
    public void AttachRelationInLoad6()
    {
        using (var context = new NorthwindClientContext())
      {
            var c = context.Categories.AsQueryable().First();
            context.Products.AsQueryable().ToList();
            Assert.AreEqual(context.Products.Where(p => p.CategoryID == c.Id).Count(), c.Products.Count);
            foreach (var p in context.Products.Where(p => p.CategoryID == c.Id))
            {
                Assert.AreEqual(c, p.Category);
                Assert.IsTrue(c.Products.Contains(p));
            }
        }
    }

    [TestMethod]
    public void AttachRelationInLoad7()
    {
        using (var context = new NorthwindClientContext())
        {
            context.Products.AsQueryable().ToList();
            var c = context.Categories.AsQueryable().Include(Category.PRODUCTS_NAME).First();
            Assert.AreEqual(context.Products.Where(p => p.CategoryID == c.Id).Count(), c.Products.Count);
            foreach (var p in context.Products.Where(p => p.CategoryID == c.Id))
            {
                Assert.AreEqual(c, p.Category);
                Assert.IsTrue(c.Products.Contains(p));
            }
        }
    }

    [TestMethod]
    public void AttachRelationInLoad8()
    {
        using (var context = new NorthwindClientContext())
        {
            context.Products.AsQueryable().ToList();
            var c = new Category { Id = 1 };
            context.Categories.Attach(c);
            var c2 = context.Categories.AsQueryable().Include(Category.PRODUCTS_NAME).First();
            Assert.AreEqual(c, c2);
            Assert.AreEqual(context.Products.Where(p => p.CategoryID == c.Id).Count(), c.Products.Count);
            foreach (var p in context.Products.Where(p => p.CategoryID == c.Id))
            {
                Assert.AreEqual(c, p.Category);
                Assert.IsTrue(c.Products.Contains(p));
            }
        }
    }

    [TestMethod]
    public void AttachRelationInLoad9()
    {
        using (var context = new NorthwindClientContext())
        {
            var c = new Category { CategoryName = "cn" };
          var p = new Product { ProductName = "pn", Category = c };
            context.Categories.Add(c);
            Assert.AreEqual(1, context.Products.Count);
            context.SaveChanges();
            Product p2;
            using (var context2 = new NorthwindClientContext())
            {
                var c2 = context2.Categories.AsQueryable().Include(Category.PRODUCTS_NAME).Where(c3 => c3.Id == c.Id).First();
                p2 = new Product { ProductName = "pn2" };
                c2.Products.Add(p2);
                context2.SaveChanges();
            }
            var c4 = context.Categories.AsQueryable().Include(Category.PRODUCTS_NAME).Where(c3 => c3.Id == c.Id).First();
            Assert.AreEqual(c, c4);
            Assert.IsTrue(c.Products.Contains(p));
            Assert.IsFalse(c.Products.Contains(p2));
            var p3 = c.Products.First(p4 => p4.ProductID == p2.ProductID);
            Assert.IsTrue(c.Products.Contains(p3));
            Assert.IsTrue(context.Products.Contains(p3));
            context.Products.Remove(p3);
            context.Products.Remove(p);
            context.Categories.Remove(c);
            Assert.AreEqual(c.Id, p.CategoryID);
            Assert.AreEqual(c.Id, p3.CategoryID);
            context.SaveChanges();
        }
    }

    [TestMethod]
    public void AttachRelationInLoad10()
    {
        using (var context = new NorthwindClientContext())
        {
            var p = context.Products.AsQueryable().First();
            context.Orders.Attach(new Order());
            var od = context.OrderDetails.AsQueryable().Where(od2 => od2.ProductID == p.ProductID).First();
            var p2 = (from p3 in context.Products.AsQueryable().Include(Product.CATEGORY_NAME).Include(string.Concat(Product.ORDERDETAILS_NAME, ".", OrderDetail.ORDER_NAME))
                      where p3.ProductID == p.ProductID
                      select p3).First();
            Assert.AreEqual(p, p2);
            Assert.IsNotNull(p.Category);
            Assert.AreEqual(1, context.Categories.Count);
            Assert.AreEqual(p.OrderDetails.Count, context.OrderDetails.Count);
            foreach (var od2 in p.OrderDetails)
                Assert.IsNotNull(od2.Order);
            Assert.AreEqual(p.OrderDetails.Select(od3 => od3.Order).Distinct().Count() + 1, context.Orders.Count);
            Assert.AreNotEqual(0, context.Orders.Count);
        }
    }

    [TestMethod]
    [ExpectedException(typeof(InvalidOperationException))]
    public void AddSameEntityInDifferentContext()
    {
        var p = new Product();
        using (var context1 = new NorthwindClientContext())
        {
            context1.Products.Add(p);
            using (var context2 = new NorthwindClientContext())
            {
                context2.Products.Add(p);
            }
        }
    }

    [TestMethod]
    [ExpectedException(typeof(InvalidOperationException))]
    public void AddSameEntityFromServiceInDifferentContext()
    {
        Product p;
        using (var context1 = new NorthwindClientContext())
        {
            p = context1.Products.AsQueryable().First();
            using (var context2 = new NorthwindClientContext())
            {
                context2.Products.Add(p);
            }
        }
    }

    [TestMethod]
    public void AddSameEntityInDifferentContextWithDispose()
    {
        var p = new Product();
        using (var context1 = new NorthwindClientContext())
        {
            context1.Products.Add(p);
        }
        using (var context2 = new NorthwindClientContext())
        {
            context2.Products.Add(p);
        }
    }

    [TestMethod]
    [ExpectedException(typeof(InvalidOperationException))]
    public void AddSameEntityInDifferentContextWithDisposeAndConflicts()
    {
        var c = new Category { Id = 1 };
        var p1 = new Product { ProductID = 1, Category = c };
        var p2 = new Product { ProductID = 2 , CategoryID = 1};
        using (var context1 = new NorthwindClientContext())
        {
            context1.Categories.Attach(c);
            context1.Products.Attach(p2);
            Assert.AreEqual(2, context1.Products.Count);
            Assert.AreEqual(2, c.Products.Count);
            Assert.AreEqual(c, p2.Category);
        }
        using (var context2 = new NorthwindClientContext())
        {
            var p3 = new Product { ProductID = 1 , CategoryID = 1};
            context2.Products.Attach(p3);
            context2.Categories.Attach(c);
        }
    }

    [TestMethod]
    public void LoadFromOneToMany()
    {
        using (var context = new NorthwindClientContext())
        {
            var c = context.Categories.AsQueryable().First();
            Assert.AreEqual(0, c.Products.Count);
            Assert.AreEqual(0, context.Products.Count);
            var ps = c.LoadProducts();
            Assert.AreNotEqual(0, ps.Count);
            Assert.AreEqual(ps.Count, c.Products.Count);
            Assert.AreEqual(ps.Count, context.Products.Count);
            foreach (var p in ps)
                Assert.AreEqual(c, p.Category);
        }
    }

    [TestMethod]
    [ExpectedException(typeof(InvalidOperationException))]
    public void LoadFromOneToManyDetached()
    {
        Category c;
        using (var context = new NorthwindClientContext())
        {
            c = context.Categories.AsQueryable().First();
        }
        var ps = c.LoadProducts();
    }

    [TestMethod]
    public void LoadFromManyToOne()
    {
        using (var context = new NorthwindClientContext())
        {
            var p = context.Products.AsQueryable().First();
            Assert.IsNull(p.Category);
            Assert.AreEqual(0, context.Categories.Count);
            var c = p.LoadCategory();
            Assert.IsNotNull(c);
            Assert.AreEqual(c, p.Category);
            Assert.AreEqual(1, context.Categories.Count);
            Assert.AreEqual(1, c.Products.Count);
            Assert.AreEqual(p, c.Products[0]);
        }
    }

    [TestMethod]
    public void LoadFromManyToMany()
    {
        using (var context = new NorthwindClientContext())
        {
            var c = (from cust in context.Customers.AsQueryable()
                     where cust.CustomerID == "VINET"
                     select cust).First();
            Assert.AreEqual(0, c.CustomerDemographics.Count);
            Assert.AreEqual(0, context.CustomerDemographics.Count);
            c.LoadCustomerDemographics();
            Assert.AreNotEqual(0, c.CustomerDemographics.Count);
            Assert.AreEqual(c.CustomerDemographics.Count, context.CustomerDemographics.Count);
        }
    }

    [TestMethod]
    public void LoadFromOneToOne()
    {
        using (var context = new NorthwindClientContext())
        {
            var c = context.Customers.AsQueryable().First();
            Assert.IsNull(c.Member);
            Assert.AreEqual(0, context.Members.Count);
            var m = c.LoadMember();
            Assert.IsNotNull(c.Member);
            Assert.AreEqual(1, context.Members.Count);
            Assert.AreEqual(c, m.Customer);
        }
    }

    [TestMethod]
    public void OfType()
    {
        using (var context = new NorthwindClientContext())
        {
            var employeesInActivity = context.Employees.AsQueryable().OfType<EmployeeInActivity>().ToList();
            Assert.AreEqual(employeesInActivity.Count, context.Employees.Count);
            Assert.AreEqual(employeesInActivity.Count, context.EmployeeInActivities.Count);
            Assert.AreEqual(0, context.OutEmployees.Count);
        }
    }

    [TestMethod]
    public void AddIntoBaseEntitySet()
    {
        using (var context = new NorthwindClientContext())
        {
            var firedEmployee = new FiredEmployee();
            context.Employees.Attach(firedEmployee);
            Assert.AreEqual(1, context.Employees.Count);
            Assert.AreEqual(1, context.OutEmployees.Count);
            Assert.AreEqual(1, context.FiredEmployees.Count);
        }
    }

    [TestMethod]
    public void GetAllEntitiesWithInheritance()
    {
        using (var context = new NorthwindClientContext())
        {
            var l = context.Employees.AsQueryable().ToList();
            Assert.AreEqual(l.Count, context.Employees.Count);
            Assert.AreEqual(l.OfType<OutEmployee>().Count(), context.OutEmployees.Count);
            Assert.AreEqual(l.OfType<FiredEmployee>().Count(), context.FiredEmployees.Count);
            Assert.AreNotEqual(0, context.FiredEmployees.Count);
        }
    }

    [TestMethod]
    public void Detach()
    {
        using (var context = new NorthwindClientContext())
        {
            var c = new Category { Id = 1 };
            context.Categories.Attach(c);
            var p = new Product { CategoryID = 1 };
            context.Products.Attach(p);
            Assert.AreEqual(c, p.Category);
            context.Categories.Detach(c);
            Assert.IsNull(p.Category);
            Assert.AreEqual(c.Id, p.CategoryID);
        }
    }

    [TestMethod]
    public void UpdateFK()
    {
        using (var context = new NorthwindClientContext())
        {
            var c1 = new Category { Id = 1 };
            context.Categories.Attach(c1);
            var c2 = new Category { Id = 2 };
            context.Categories.Attach(c2);
            var p = new Product { CategoryID = 1 };
            context.Products.Attach(p);
            Assert.AreEqual(c1, p.Category);
            Assert.AreEqual(1, c1.Products.Count);
            p.CategoryID = 2;
            Assert.AreEqual(c2, p.Category);
            Assert.AreEqual(0, c1.Products.Count);
            Assert.AreEqual(1, c2.Products.Count);
        }
    }
}

I used this EDM for my tests:

Moreover, with self-tracking default template the generated SQL update includes all columns even if only one property changed. With my implementation the update will contain only the modified column. Note that this is not true for complex types. If one complex type’s property changes, all the columns mapped on this complex type will be included in the update. It is an EF limitation.

To realize it, I add a list of string ModifiedProperties in ObjectChangeTracker class (I update the self-tracking types T4 template). Then I also change the self-tracking context T4 template. I replace:

context.ObjectStateManager.ChangeObjectState(entity, EntityState.Modified);

context.ObjectStateManager.ChangeObjectState(entity, EntityState.Unchanged);
var ose = context.ObjectStateManager.GetObjectStateEntry(entity);
ose.SetModified();
foreach (var propertyName in entity.ChangeTracker.ModifiedProperties)
ose.SetModifiedProperty(propertyName);

If you want to learn more about my templates, I will speak about this for next French Microsoft Tech Days in Paris and for the confoo in Montreal (also in French).

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.

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.

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.