Some refactoring using E4 POCO with generics

Here is a story around refactoring some switch statements using polymorphism and some repetitive code using some generics to produce DRY code.

We has a smell in our EarthquakeSettingsLegacy class that had a switch statements that needs some refactoring to use a polymorphic approach

   private void GetVulnerability(ModelRun modelRun)
        {

            ModelType modelType;
            modelType = (ModelType)modelRun.ModelId;

            List<Ccp.Entities.VulnerabilityAggregate> vulnerabilityAggregates = databaseContext
                .Query<VulnerabilityAggregate>()
                .Where(v => v.ModelRunId.HasValue && v.ModelRunId.Value.Equals(modelRun.Id)).ToList();

            switch (modelType)
            {
                case ModelType.EarthquakeCanada:
                    GetCanadaVulnerability(modelRun, vulnerabilityAggregates);

                    break;
                case ModelType.EarthquakeMexico:
                    GetMexicoVulnerability(modelRun, vulnerabilityAggregates);
                    break;

                case ModelType.EarthquakeAfricaIndia:
                    GetAfricaToIndiaVulnerability(modelRun, vulnerabilityAggregates);
                    break;

                case ModelType.EarthquakeJapan:
                    GetJapanVulnerability(modelRun, vulnerabilityAggregates);
                    break;

                case ModelType.EarthquakeSouthAmerica:
                    GetSouthAmericaVulnerability(modelRun, vulnerabilityAggregates);
                    break;

                default:
                    return;
            }

The first step was to make the following more generic. The challenge is that we are using auto generate POCO classes, in other words we cannot simply make the classes inherit from a base class that we could use as a compile time type with generics. Another complication was that we need to use a compile time type as apposed to a run time type. Because Generics are place holders for types that are compiled at compile time.

        private void GetSouthAmericaVulnerability(ModelRun modelRun, List<Ccp.Entities.VulnerabilityAggregate> vulnerabilityAggregates)
        {
            List<Ccp.Entities.Earthquake.SouthAmericaVulnerabilityCatalogue> vaList = vulnerabilityAggregates
                .Select<Ccp.Entities.VulnerabilityAggregate, Ccp.Entities.Earthquake.SouthAmericaVulnerabilityCatalogue>(
                va => EntityMapper.Map<Ccp.Entities.VulnerabilityAggregate, Ccp.Entities.Earthquake.SouthAmericaVulnerabilityCatalogue>(va))
                .ToList();

            //Earthquake handles vulnerability functions as strings where as Tropical Cyclone handles them as integers. Therefore we need to
            //have some special code to map these together

            var result = from av in this.databaseContext.GetAll<VulnerabilityAggregate>().Where(z => z.ModelRunId.Value.Equals(modelRun.Id))
                         join vf in this.databaseContext.GetAll<VulnerabilityFunction>()
                         on av.VulnerabilityFunctionId equals vf.Id
                         select new { av.VulnerabilityFunctionId, vf.VulnerabilityFunctionString };

            var dic = result.Select(p => new { p.VulnerabilityFunctionId, p.VulnerabilityFunctionString })
                            .Distinct()
                            .AsEnumerable()
                            .ToDictionary(k => k.VulnerabilityFunctionId, v => v.VulnerabilityFunctionString);

            for (int i = 0; i < vulnerabilityAggregates.Count; i++)
            {
                String Vulnerability;
                int? VulnerabilityFunctionId = vulnerabilityAggregates[i].VulnerabilityFunctionId;
                if (dic.TryGetValue(VulnerabilityFunctionId.Value, out Vulnerability))
                {
                    List<string> vulnerabilityCoeficients;
                    vulnerabilityCoeficients = Vulnerability.Split(' ').ToList<string>();

                    vaList[i].A = Convert.ToDouble(vulnerabilityCoeficients[0]);
                    vaList[i].B = Convert.ToDouble(vulnerabilityCoeficients[1]);
                    vaList[i].C = Convert.ToDouble(vulnerabilityCoeficients[2]);
                    vaList[i].VulnerabilityFunction = Vulnerability;
                }
            }

            if (vulnerabilityAggregates.Count > 0)
            {
                using (this.databaseContextEarthquake.BeginTransaction())
                {
                    this.databaseContextEarthquake.DeleteAll<Ccp.Entities.Earthquake.SouthAmericaVulnerabilityCatalogue>();
                    this.databaseContextEarthquake.Add(vaList.ToArray());
                    this.databaseContextEarthquake.Save();

                    this.databaseContextEarthquake.CommitTransaction();
                }
            }
        }

We translated the above into the following generic function. To make the POCO DAL entities work TDestination needs to inherit from class and implement a IVulnerabilityParameter interface

        public void GetVulnerability<TDestination>(ModelRun modelRun, List<Ccp.Entities.VulnerabilityAggregate> vulnerabilityAggregates)
            where TDestination : class, IVulnerabilityParameters
        {
            List<TDestination> vaList = vulnerabilityAggregates
                .Select<Ccp.Entities.VulnerabilityAggregate, TDestination>(
                va => EntityMapper.Map<Ccp.Entities.VulnerabilityAggregate, TDestination>(va))
                .ToList();

            //Earthquake handles vulnerability functions as strings where as Tropical Cyclone handles them as integers. Therefore we need to
            //have some special code to map these together

            var result = from av in this.databaseContext.GetAll<VulnerabilityAggregate>().Where(z => z.ModelRunId.Value.Equals(modelRun.Id))
                         join vf in this.databaseContext.GetAll<VulnerabilityFunction>()
                         on av.VulnerabilityFunctionId equals vf.Id
                         select new { av.VulnerabilityFunctionId, vf.VulnerabilityFunctionString };

            var dic = result.Select(p => new { p.VulnerabilityFunctionId, p.VulnerabilityFunctionString })
                            .Distinct()
                            .AsEnumerable()
                            .ToDictionary(k => k.VulnerabilityFunctionId, v => v.VulnerabilityFunctionString);

            for (int i = 0; i < vulnerabilityAggregates.Count; i++)
            {
                String Vulnerability;
                int? VulnerabilityFunctionId = vulnerabilityAggregates[i].VulnerabilityFunctionId;
                if (dic.TryGetValue(VulnerabilityFunctionId.Value, out Vulnerability))
                {
                    List<string> vulnerabilityCoeficients;
                    vulnerabilityCoeficients = Vulnerability.Split(' ').ToList<string>();

                    vaList[i].A = Convert.ToDouble(vulnerabilityCoeficients[0]);
                    vaList[i].B = Convert.ToDouble(vulnerabilityCoeficients[1]);
                    vaList[i].C = Convert.ToDouble(vulnerabilityCoeficients[2]);
                    vaList[i].VulnerabilityFunction = Vulnerability;
                }
            }

            if (vulnerabilityAggregates.Count > 0)
            {
                using (this.databaseContextEarthquake.BeginTransaction())
                {
                    this.databaseContextEarthquake.DeleteAll<TDestination>();
                    this.databaseContextEarthquake.Add(vaList.ToArray());
                    this.databaseContextEarthquake.Save();

                    this.databaseContextEarthquake.CommitTransaction();
                }
            }
        }

The interface looks like:
namespace Ccp.Entities.Earthquake
{
    /// <summary>
    /// All the earthquake vulnerability catalogues need to implement this interface for the settings.
    /// </summary>
    public interface IVulnerabilityParameters
    {
        string VulnerabilityFunction { get; set; }
        Nullable<double> A { get; set; }
        Nullable<double> B { get; set; }
        Nullable<double> C { get; set; }
    }
}

Next we created to partial classes for each entity that we need to process.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Ccp.Entities.Earthquake
{
    public partial class SouthAmericaVulnerabilityCatalogue : IVulnerabilityParameters
    {
    }
}

This looks a little strange but the implementation of IVulnerabilityParameters is in the partial class of the POCO DAL.

We can continue to refactor the 4 link statements into one using AsEnumarble. The difference between Linq and sql is that the where clause is not used in the same way. In SQL the where clause would explicitly join a foreign key to a primary key. In Linq we want to associate to another entity the relationship v.VulnerabilityFunction, in which case you need to include any property from the child entity. In the case below this is a VulnerabilityFunctionString (as apposed to Id). The emitted sql includes an outer join. In this case the relationship is 0-1 to many which means the outer join is not so bad

            List<Ccp.Entities.TropicalCyclone.VulnerabilityAggregate> vaList =
                databaseContext
                    .Query<VulnerabilityAggregate>()
                    .Where(v => v.ModelRunId.HasValue && v.ModelRunId.Value.Equals(modelRun.Id) && v.VulnerabilityFunction.VulnerabilityFunctionString != "")
                    .AsEnumerable()
                    .Select<Ccp.Entities.VulnerabilityAggregate, Ccp.Entities.TropicalCyclone.VulnerabilityAggregate>(
                        va =>
                            {
                                var vae = EntityMapper.Map<Ccp.Entities.VulnerabilityAggregate, Ccp.Entities.TropicalCyclone.VulnerabilityAggregate>(va);
                                vae.VfId = Convert.ToInt32(va.VulnerabilityFunction.VulnerabilityFunctionString);
                                return vae;
                            })
                    .ToList();

Here’s a book recommendations for creating linq queries:
http://www.amazon.de/LINQ-Pocket-Reference-OReilly/dp/0596519249/ref=sr_1_4?ie=UTF8&s=books-intl-de&qid=1276066598&sr=1-4

Using Moq in unit tests

The unit test below demonstrates how we can use interfaces to moq implementations. In this case we want to make a unit test that tests a mail notification. Naturally we don't want to send a real mail every time we run the unit test, therefore it makes sense to create some interfaces in our application that enables us to test whether our send warning notification system works. It works by creating a property that implements an interface. Within our unit test we overwrite the property with a moq object that implements an interface hence the need for an interface. We then can configure the moq to return some predefined responses and assert whether the send mail method is called with the correct parameters

        [TestMethod]
        public void SendWarning_ToSubscribers_MessageIsSent()
        {
            //Arrange
            string Message =  "Test Message";
            NotifySubscribers target = new NotifySubscribers();
            Mock<ISubscribers> subscribersMoq = new Mock<ISubscribers>();
            target.Subscribers = subscribersMoq.Object;
            subscribersMoq.Setup(i => i.Get()).Returns(() => new List<string> { "nigel.findlater@partnerre.com" });
            Mock<INotify> notifyMoq = new Mock<INotify>();
            target.EmailNotifier = notifyMoq.Object;
            notifyMoq.Setup(n => n.SendMail(
                It.IsAny<string>(),
                It.IsAny<string>(),
                It.IsAny<string>(),
                It.Is<string>(s => s.Equals(Message)),
                Message
                ));

            //Act
            target.SendWarning(Message);
            //Assert
            subscribersMoq.VerifyAll();
            notifyMoq.VerifyAll();

        }

Here is the class that we are testing. Notice that we have made 2 public properties that are of the interface type INotify and ISubscribers. These properties are instantiated in the constructor but can be overwritten by moq thus making it possible to test this class without sending out any mails

namespace Ccp.Notification
{
    public class NotifySubscribers : INotifySubscribers
    {
        public INotify EmailNotifier { get; set; }
        public ISubscribers Subscribers { get; set; }

        public NotifySubscribers()
        {
            this.Subscribers = new Subscribers();
            this.EmailNotifier = new NotifyEmail();
        }

        public void SendWarning(string WarningMessage)
        {
            if (Subscribers == null)
            {
                throw new InvalidOperationException("Subscriber implementation not injected");
            }

            foreach(var email in Subscribers.Get())
            {
                this.EmailNotifier.SendMail(email, email, "", WarningMessage, WarningMessage);
            }
        }
    }
}

namespace Ccp.Infrastructure
{
    public interface ISubscribers
    {
        List<string> Get();
    }
}

namespace Ccp.Infrastructure
{
    public interface INotifySubscribers
    {
        void SendWarning(string warningMessage);
    }
}

Here is the implementation of ISubscribers that is not a part of this test

namespace Ccp.Infrastructure
{
    public class Subscribers: ISubscribers
    {
        public List<string> Get()
        {
            ObjectContext objectContext = new CatFocusEntities(Ccp.Infrastructure.Configuration.CatFocusEntitiesConnectionString);
            IDatabaseContext databaseContext = new DatabaseContext(objectContext);
            var Emails = from no in databaseContext.Query<SystemMonitorNotification>()
                         join u in databaseContext.Query<User>()
                         on no.UserId equals u.Id
                         select u.EMail ;

            return Emails.ToList();
        }
    }
}

Practical application of Linq

In one database we have 2 tables that are joined with a foreign key. In a separate database we have a table that needs to be filled with the result of an inner join from the first database. Here is the first attempt at doing this:

       public void Join_TwoTablesWithLinqQuery_IsMappedToTargetEntity()
        {
            using (this.databaseContext.BeginTransaction())
            {
                int modelRunId = 18406;

                //Earthquake handles vulnerability functions as strings where as Tropical Cyclone handles them as integers. Therefore we need to
                //have some special code to map these together
                var result = from av in this.databaseContext.GetAll<VulnerabilityAggregate>().Where(z => z.ModelRunId.Value.Equals(modelRunId))
                             join vf in this.databaseContext.GetAll<VulnerabilityFunction>()
                             on av.VulnerabilityFunctionId equals vf.Id
                             select new Ccp.Entities.TropicalCyclone.VulnerabilityAggregate
                             { object initializer
                                 Id = av.Id,
                                 VfId = int.Parse(vf.VulnerabilityFunctionString),      // not safe yet
                                 BuildingQuality = av.BuildingQuality,
                                 BuildingType = av.BuildingType,
                                 CoveredRisk = av.CoveredRisk,
                                 MainOccupancyType = av.MainOccupancyType,
                                 OccupancyModifier = av.OccupancyModifier
                             };

                Assert.IsTrue(result.Count() > 0, "this ModelRun should have VAs");
            }
        }

We want to make the conversion of the VulnerabilityFunctionString to integer safer. So we refactored this into a Func

                Func<string, int> convertToInt = (vfString) =>
                {
                    int value = 0;
                    int.TryParse(vfString, out value);
                    return value;
                };

                var result = from av in this.databaseContext.GetAll<VulnerabilityAggregate>().Where(z => z.ModelRunId.Value.Equals(modelRunId))
                             join vf in this.databaseContext.GetAll<VulnerabilityFunction>()
                             on av.VulnerabilityFunctionId equals vf.Id
                             select new Ccp.Entities.TropicalCyclone.VulnerabilityAggregate
                             {
                                 Id = av.Id,
                                 VfId = convertToInt(vf),
                                 BuildingQuality = av.BuildingQuality,
                                 BuildingType = av.BuildingType,
                                 CoveredRisk = av.CoveredRisk,
                                 MainOccupancyType = av.MainOccupancyType,
                                 OccupancyModifier = av.OccupancyModifier
                             };

An alternative would be to make this in line:

                var result = from av in this.databaseContext.GetAll<VulnerabilityAggregate>().Where(z => z.ModelRunId.Value.Equals(modelRunId))
                             join vf in this.databaseContext.GetAll<VulnerabilityFunction>()
                             on av.VulnerabilityFunctionId equals vf.Id
                             select new Ccp.Entities.TropicalCyclone.VulnerabilityAggregate
                             {
                                 Id = av.Id,
                                 VfId = (vfString) =>
                                 {
                                     int value = 0;
                                     int.TryParse(vfString, out value);
                                     return value;
                                 },
                                 BuildingQuality = av.BuildingQuality,
                                 BuildingType = av.BuildingType,
                                 CoveredRisk = av.CoveredRisk,
                                 MainOccupancyType = av.MainOccupancyType,
                                 OccupancyModifier = av.OccupancyModifier
                             };

Next we refactored the Func into a static class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Ccp.Infrastructure
{
    /// <summary>
    /// Helper functions and actions to convert data types.
    /// </summary>
    public static class Conversions
    {
        /// <summary>
        /// Safely convert int to string, with default to 0.
        /// </summary>
        public static Func<string, int> ConvertToInt = (vfString) =>
        {
            int value = 0;
            int.TryParse(vfString, out value);
            return value;
        };

    }
}

     [TestMethod]
        public void Join_TwoTablesWithLinqQuery_IsMappedToTargetEntity()
        {
            using (this.databaseContext.BeginTransaction())
            {
                // need a model run identifier first, that has corresponding vulnerability aggregates
                int modelRunId = 18406;

                //Earthquake handles vulnerability functions as strings where as Tropical Cyclone handles them as integers. Therefore we need to
                //have some special code to map these together
                var result = from av in this.databaseContext.GetAll<VulnerabilityAggregate>().Where(z => z.ModelRunId.Value.Equals(modelRunId))
                             join vf in this.databaseContext.GetAll<VulnerabilityFunction>()
                             on av.VulnerabilityFunctionId equals vf.Id
                             select new Ccp.Entities.TropicalCyclone.VulnerabilityAggregate
                             {
                                 Id = av.Id,
                                 VfId = Conversions.ConvertToInt(vf.VulnerabilityFunctionString),
                                 BuildingQuality = av.BuildingQuality,
                                 BuildingType = av.BuildingType,
                                 CoveredRisk = av.CoveredRisk,
                                 MainOccupancyType = av.MainOccupancyType,
                                 OccupancyModifier = av.OccupancyModifier
                             };

                Assert.IsTrue(result.Count() > 0, "this ModelRun should have VAs");
            }
        }
    }

This worked well but the GetAll function first loads everything to the client and then the client makes the join. We want the join to be made on the sql server so we changed GetAll to Query as shown below:

               var result = from av in this.databaseContext.Query<VulnerabilityAggregate>().Where(z => z.ModelRunId.Value.Equals(modelRunId))
                             join vf in this.databaseContext.Query<VulnerabilityFunction>()
                             on av.VulnerabilityFunctionId equals vf.Id
                             select new Ccp.Entities.TropicalCyclone.VulnerabilityAggregate
                             {
                                 Id = av.Id,
                                 VfId = Conversions.ConvertToInt(vf.VulnerabilityFunctionString),
                                 BuildingQuality = av.BuildingQuality,
                                 BuildingType = av.BuildingType,
                                 CoveredRisk = av.CoveredRisk,
                                 MainOccupancyType = av.MainOccupancyType,
                                 OccupancyModifier = av.OccupancyModifier
                             };

But we got the following exception:

Test method Ccp.DataAccessLayerTest.DatabaseContextTests.Join_TwoTablesWithLinqQuery_IsMappedToTargetEntity threw exception:
System.NotSupportedException: The LINQ expression node type 'Invoke' is not supported in LINQ to Entities.

So we tried to split the function into first the query and then the convert.

                var result = from av in this.databaseContext.Query<VulnerabilityAggregate>().Where(z => z.ModelRunId.Value.Equals(modelRunId))
                             join vf in this.databaseContext.Query<VulnerabilityFunction>()
                             on av.VulnerabilityFunctionId equals vf.Id
                             select new
                             {
                                 av.Id,
                                 vf.VulnerabilityFunctionString,
                                 av.BuildingQuality,
                                 av.BuildingType,
                                 av.CoveredRisk,
                                 av.MainOccupancyType,
                                 av.OccupancyModifier
                             };

                            IList<Ccp.Entities.TropicalCyclone.VulnerabilityAggregate> vaList = result.Select((av) => new Ccp.Entities.TropicalCyclone.VulnerabilityAggregate
                             {
                                 Id = av.Id,
                                 VfId = Conversions.ConvertToInt(av.VulnerabilityFunctionString),
                                 BuildingQuality = av.BuildingQuality,
                                 BuildingType = av.BuildingType,
                                 CoveredRisk = av.CoveredRisk,
                                 MainOccupancyType = av.MainOccupancyType,
                                 OccupancyModifier = av.OccupancyModifier
                             }).ToList();

The problem with the above code is that var result is only loaded when the select is called. This means that we are sill trying to execute a Func within an SQL query. The underlying problem is that we have built up a query tree in the business domain using Linq which includes a function (Func) that there is no corresponding mapping to in the Storage Domain

So the solution was:

[TestMethod]
        public void Join_TwoTablesWithLinqQuery_IsMappedToTargetEntity()
        {
            using (this.databaseContext.BeginTransaction())
            {
                // need a model run identifier first, that has corresponding vulnerability aggregates
                int modelRunId = 18406;

                //Earthquake handles vulnerability functions as strings where as Tropical Cyclone handles them as integers. Therefore we need to
                //have some special code to map these together
                var result = from av in this.databaseContext.Query<VulnerabilityAggregate>().Where(z => z.ModelRunId.Value.Equals(modelRunId))
                             join vf in this.databaseContext.Query<VulnerabilityFunction>()
                             on av.VulnerabilityFunctionId equals vf.Id
                             select new
                             {
                                 av.Id,
                                 vf.VulnerabilityFunctionString,
                                 av.BuildingQuality,
                                 av.BuildingType,
                                 av.CoveredRisk,
                                 av.MainOccupancyType,
                                 av.OccupancyModifier
                             };

                var vaList = result.ToList().Select((av) => new Ccp.Entities.TropicalCyclone.VulnerabilityAggregate
                    {
                        Id = av.Id,
                        VfId = Conversions.ConvertToInt(av.VulnerabilityFunctionString),
                        BuildingQuality = av.BuildingQuality,
                        BuildingType = av.BuildingType,
                        CoveredRisk = av.CoveredRisk,
                        MainOccupancyType = av.MainOccupancyType,
                        OccupancyModifier = av.OccupancyModifier
                    });

                Assert.IsTrue(vaList.Count() > 0, "this ModelRun should have VAs");
            }

This emitted the following SQL code:

exec sp_executesql N'SELECT
[Extent1].[VULNERABILITY_AGGREGATE_ID] AS [VULNERABILITY_AGGREGATE_ID],
[Extent2].[VULNERABILITY_FUNCTION] AS [VULNERABILITY_FUNCTION],
[Extent1].[BUILDING_QUALITY] AS [BUILDING_QUALITY],
[Extent1].[BUILDING_TYPE] AS [BUILDING_TYPE],
[Extent1].[COVERED_RISK] AS [COVERED_RISK],
[Extent1].[MAIN_OCCUPANCY_TYPE] AS [MAIN_OCCUPANCY_TYPE],
[Extent1].[OCCUPANCY_MODIFIER] AS [OCCUPANCY_MODIFIER]
FROM  [dbo].[MOD_VULNERABILITY_AGGREGATE] AS [Extent1]
INNER JOIN [dbo].[MOD_VULNERABILITY_FUNCTION] AS [Extent2] ON [Extent1].[VULNERABILITY_FUNCTION_ID] = [Extent2].[VULNERABILITY_FUNCTION_ID]
WHERE [Extent1].[MODEL_RUN_ID] = @p__linq__0',N'@p__linq__0 int',@p__linq__0=18406

Some Lessons Learned
When we try to make complex select statements in Linq we struggle with the Linq syntax. Since knowledge of Linq at the complex end of the query spectrum is sparse we recommend that complex select statements be made as views. The reason is that the knowledge of building complex SQL statements is wide spread where are the same operation in Linq takes a long time to figure out and the knowledge out there is sparse.

Another thing is Update statements such as "Update table set field= 111 where filed2=2" cannot be easily made within EF. We recommended when this is necesary for performance reasons that stored procedures be used

Recomended book http://www.amazon.de/Programming-Microsoft%C2%AE-PRO-Developer-Paolo-Pialorsi/dp/0735624003/ref=sr_1_3?ie=UTF8&s=books-intl-de&qid=1274251357&sr=1-3
Programming Microsoft® LINQ (PRO-Developer) (Taschenbuch)
von Paolo Pialorsi (Autor), Marco Russo (Autor)

Looking at some more complex SQL statements such as Group by
First a quick tutorial from http://www.sql-tutorial.net/SQL-HAVING.asp

The SQL HAVING clause is used to restrict conditionally the output of a SQL statement, by a SQL aggregate function used in your SELECT list of columns.

You can't specify criteria in a SQL WHERE clause against a column in the SELECT list for which SQL aggregate function is used. For example the following SQL statement will generate an error:

SELECT Employee, SUM (Hours)
FROM EmployeeHours
WHERE SUM (Hours) > 24
GROUP BY Employee

The SQL HAVING clause is used to do exactly this, to specify a condition for an aggregate function which is used in your query:

SELECT Employee, SUM (Hours)
FROM EmployeeHours
GROUP BY Employee
HAVING SUM (Hours) > 24

The above SQL statement will select all employees and the sum of their respective hours, as long as this sum is greater than 24. The result of the SQL HAVING clause can be seen below:

Employee Hours
John Smith 25
Tina Crown 27

Here are some examples of how this gets translated into Linq together with the emitted sql:

        [TestInitialize]
        public void TestInitialize()
        {
            objectContext = new HotelModelContainer();
            target = new DatabaseContext(objectContext);
            target.DeleteAll<Customer>();
            target.DeleteAll<Reservation>();
            target.DeleteAll<Hotel>();
            target.DeleteAll<Price>();
            target.DeleteAll<Chain>();
            target.DeleteAll<Room>();
            target.DeleteAll<City>();
            target.DeleteAll<Currency>();
            Currency chf = new Currency { Symbol = "CHF", Name = "Schweizer Franken" };
            City city1 = new City { Code = "LON", Name = "London", Currency = chf };
            City city2 = new City { Code = "LON2", Name = "London", Currency = chf };
            City city3 = new City { Code = "ZRH", Name = "Zürich", Currency = chf };
            Price price = new Price { Currency = chf, Value = 60.0M };
            this.target.Add(city1, city2, city3);
            target.Save();
        }
        [TestMethod]
        public void GroupBy_HavingCountBiggerThanOne_Returns()
        {
            using (this.target.BeginTransaction())
            {
                var result = from c in this.target.Query<City>()
                             group c by c.Name into cg
                             where cg.Count() > 1
                             select cg;
                var res = result.ToList();
                Assert.AreNotEqual<int>(0, res.Count);
                //SELECT
                //[Project1].[C1] AS [C1],
                //[Project1].[Name] AS [Name],
                //[Project1].[C2] AS [C2],
                //[Project1].[Id] AS [Id],
                //[Project1].[Name1] AS [Name1],
                //[Project1].[Code] AS [Code],
                //[Project1].[Currency_Id] AS [Currency_Id]
                //FROM ( SELECT
                //    [GroupBy1].[K1] AS [Name],
                //    1 AS [C1],
                //    [Extent2].[Id] AS [Id],
                //    [Extent2].[Name] AS [Name1],
                //    [Extent2].[Code] AS [Code],
                //    [Extent2].[Currency_Id] AS [Currency_Id],
                //    CASE WHEN ([Extent2].[Id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C2]
                //    FROM   (SELECT
                //        [Extent1].[Name] AS [K1],
                //        COUNT(1) AS [A1]
                //        FROM [dbo].[Cities] AS [Extent1]
                //        GROUP BY [Extent1].[Name] ) AS [GroupBy1]
                //    LEFT OUTER JOIN [dbo].[Cities] AS [Extent2] ON [GroupBy1].[K1] = [Extent2].[Name]
                //    WHERE [GroupBy1].[A1] > 1
                //)  AS [Project1]
                //ORDER BY [Project1].[Name] ASC, [Project1].[C2] ASC
            }
        }

Here is an example with Group by and having

        [TestMethod]
        public void GroupBy_Having_Returns()
        {
            using (this.target.BeginTransaction())
            {
                int count = this.target.Query<City>().GroupBy<City, string>(c => c.Name).Where(g => g.Count() > 1).Count();
                Assert.IsTrue(count > 0);
                //SELECT
                //[GroupBy2].[A1] AS [C1]
                //FROM ( SELECT
                //    COUNT(1) AS [A1]
                //    FROM ( SELECT
                //        [Extent1].[Name] AS [K1],
                //        COUNT(1) AS [A1]
                //        FROM [dbo].[Cities] AS [Extent1]
                //        GROUP BY [Extent1].[Name]
                //    )  AS [GroupBy1]
                //    WHERE [GroupBy1].[A1] > 1
                //)  AS [GroupBy2]
            }
        }

I encountered one last problem. I made a change to the database and wanted this change to be reflected in the data model within the edm. Unfortunately the only way to get this to work is to erase the entity from within the edm and drag int back in. This is quite annoying because all the mappings to that table are lost in the processed. Apparently this is by design http://www.itweb.co.za/visualstudio/index.php?option=com_ninjaboard&view=topic&topic=4531&Itemid=2
8<---
Update model from database does not check for allow nulls?
on 04/16/2010 04:30
After setting up a vs2010 EF model for a simple sql server 2008 db, I changed a column in the db so that it would not allow nulls and saved the db. I then ran 'Update model from database' in vs expecting the model to reflect the change. But the model property for that column remained (None) for Nullable. Surely evaluation of allow nulls is included during a refresh from the db?

Update model from database does not check for allow nulls?
on 04/12/2010 06:40
Hi, It is a designed behavior. &ldquo;Update model from database&rdquo; will only refresh the modifications in the SSDL (Storage Metadata Schema of the EDM. If we open the .edmx via XML editor (right click the .edmx &agrave; Open With&hellip; &agrave; Select &ldquo;XML Editor&rdquo;). In the SSDL section, we can see that certain property has marked as Nullable=&rdquo;false&rdquo;. However, the CSDL (Conceptual Schema) is designed to be maintained by the users. Generally, &ldquo;Update model from database&rdquo; won&rsquo;t refresh the CSDL section unless no mapping properties is defined at the conceptual model, e.g. we add a new column in the database and then use &ldquo;Update model from database&rdquo;, EF will add a new property at the CSDL in this case. Also, imaging we have many inheritance, merging and splitting tables in the EDM, if &ldquo;Update model from database&rdquo; refreshes the CSDL, our manual modification of the EDM will be lost. In your scenario, we need to set the certain property&rsquo;s Nullable to False at the EDM designer. If you have any questions, please feel free to let me know. Have a nice day! Best Regards, Lingzhi Sun MSDN Subscriber Support in Forum If you have any feedback on our support, please contact msdnmg@microsoft.com Please remember to mark the replies as answers if they help and unmark them if they provide no help. Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
8<---

Dealing with NUMERIC with Automapper
We start by configuring the mapper as follows

            Mapper.CreateMap<Ccp.Entities.TESTCS, Ccp.Entities.EuropeanWind.TESTWS>()
                .ForMember(d => d.TestFloat, c => c.MapFrom<double?>(s => s.test1))
                .ForMember(d => d.TestNumeric, c => c.MapFrom<double?>(s => s.test2));

Then we make a unit test that reproduces the error:

        [TestMethod]
        public void Mapping_TableWithFloatsToTableWithNumeric_DataTransfered()
        {
            // arrange stuff
            this.objectContextEuropeanWind = new EuropeanWindEntities(ConfigurationManager.ConnectionStrings["EuropeanWindEntities"].ConnectionString);
            this.databaseContextEuropeanWind = new DatabaseContext(objectContextEuropeanWind);
            IList<Ccp.Entities.TESTCS> input = this.databaseContext.GetAll<Ccp.Entities.TESTCS>().ToList();
            Mappings.Initialize();

            // act
            Ccp.Entities.EuropeanWind.TESTWS destination = Mapper.Map<Ccp.Entities.TESTCS, Ccp.Entities.EuropeanWind.TESTWS>(input.FirstOrDefault());
            // assert
            Assert.IsNotNull(destination, "mapping did not work");

            using (this.databaseContextEuropeanWind.BeginTransaction())
            {
                this.databaseContextEuropeanWind.Add(destination);
                this.databaseContextEuropeanWind.CommitTransaction();
            }
        }

This through the following error:

Trying to map Ccp.Entities.TESTCS to Ccp.Entities.EuropeanWind.TESTWS.
Using mapping configuration for Ccp.Entities.TESTCS to Ccp.Entities.EuropeanWind.TESTWS
Exception of type 'AutoMapper.AutoMapperMappingException' was thrown.

The solution was to change the mapping as follows:

            Mapper.CreateMap<Ccp.Entities.TESTCS, Ccp.Entities.EuropeanWind.TESTWS>()
                .ForMember(d => d.TestFloat, c => c.MapFrom<double?>(s => s.test1))
                .ForMember(d => d.TestNumeric, c => c.MapFrom<decimal?>(s => (decimal?)s.test2));