Making SQL Queries statement act on the same database across multiple servers

Here’s a useful way to apply queries across a number of sql 2008 servers

1. Open Microsoft SQL Server Management Studio
2. Under Database Engine select Central Management Servers
   (It is on the tab "Registered Servers" next to the tab "Object Explorer"
3. Right Mouse click "Central Management Servers" and select Register Central Management Server...
4. Choose the Server that you want to add in the "New Server Registration dialog"
   This has the effect of adding a node directly under "Central Management Server". This is used to host the Server groups
5. Right Mouse click on the new node and select "New Server Group..."
6. Give a group name
7. Right mouse click on the new group and select "New Server Registration..."
8. Select the servers that you want to be part of the group

This completes the setup.

To create queries that act across the registered servers
1. Right mouse click on a server group
2. "New Query"

When you execute the query you will receive the results with an additional "Server Name" column giving the result of that query on that server

Setting up Transaction log shipping

We have a high performance physical sql server that we want to backup via netApp shares. To get this to work we setup a second virtual sql server that uses netapp shares which are backup using the Snap manager tool.

Data is sent to the virtual sql server using Transaction log shipping. Here is how to setup transaction log shipping

1. Create a Backup of the database making shure that:
   - Recovery model = Full
   - Backup type = Full
2. Restore this backup onto the virtual sql server choosing the second option:
   - Leave the database non-operational, and do not roll back transactions. Additional transaction logs can be restored (RESTORE WITH NORECOVERY)
3. After the backup is complete there is a green upward pointing arrow on the database on the virtual server and the database has (restoring..) after it.
4. Goto the physical server, right mouse click the database and select properties
5. In the select page choose "Transaction Log Shipping"
6. tick the check box "Enable this as primary database in a log shipping configuration"
7. Click the button Backup Settings
   This will open "Transaction Log Backup Settings" dialog
8. In the Network path to backup folder give the path on the physical server where the tranaction logs are to be found eg
   \\Myserver\LOGSHIPPING$
   In the field "If the backup folder is located on the primary server, type the path to the folder"
   G:\LOGSHIPPING
   OK
9. In the Secondary server instances and databases press the Add button
10. Press the connect button and select the secondary database
11. In "Destination folder for copied files"
    G:\LOGSHIPPING
12. OK
13. Check that the backup and restore jobs created on both servers run successfully

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));

Notes from a chat “What is SOA about?”

Here are some notes that I made from a conversation with a good friend Robert Hogg an MVP for BizTalk who has the talent of being able to explain a complex thing simply. In this case it was on getting a better understanding on what is SOA. Here is my understanding of the conversation…

The key thing about Service Orientated Architecture is the term Architecture. This implies that the use of services should be the foundation of the architecture. Services can be functional or data services. Leaving SOA to one side for a moment most applications made can be broken down to 3 tiers; UI, Business Logic and Database. The Business logic can be built on a number of patterns MVC, VMMV etc. SOA is orientated in the business logic tier. In fact SOA could be made even with old technology like MTS.

The thing that differentiates SOA in this case is both Business and IT understand the same thing when it comes down to the word “Service”. A business man would like robust reliable software that is also completely dynamic so as to be able to adapt to changing business needs. Dynamic and robust are like 2 opposing forces which implies a compromise is needed.

Whatever architecture is chosen software must be well written or in other words analyzable, testable, maintainable and re-compassable. SOA does that but where the individual components are arranged as services. New technology such as WCF, BizTalk, ESB, WF make it easier to implement an architecture based on services. These technologies makes Services a natural point for an interface because for example WCF the interface can be easily configured to run with things like reliable messaging, msmq etc. just by modification of a configuration file. Tasks that take several days to completed can be processed via tools like BizTalk can that can reliably processed long running tasks even if the server where to be rebuilt in the mean time. Dynamic tasks such as rearranging business processes can be make over ESB with a small coding overhead. Another aspect of the BizTalk server is that it provides a Hub-Spoke connectivity with out of the box connectors to systems like SAP, PeopleSoft etc. The idea that you make just one interface to the hub as opposed to one per system that you need to connect to.

After some time of programming with SOA you may find that certain services may need to be broken down into smaller ones. When doing this just remember to keep a façade to the old implementation, in this way you will not break any code that is using the old implementation.

I found this a good introduction to SOA from a more down to earth perspective. The subject is quite extensive as there are a lot of example of companies that have tried and failed to implement SOA.

How to delete objects in EF4 without loading them first

In LinqForSQL it was possible to make the delete commands in the following style

    Public Sub DeleteRecord(ByVal COUNTRY_ID As Integer)
        iDataClasses1DataContext.CED_COUNTRies.DeleteOnSubmit( _
              (From Data In iDataClasses1DataContext.CED_COUNTRies _
               Where Data.COUNTRY_ID = COUNTRY_ID).Single)
        iDataClasses1DataContext.SubmitChanges()
    End Sub

In EF4 this is not possible in this way see http://stackoverflow.com/questions/1895455/get-database-table-name-from-entity-framework-metadata. There was a design decision that in order to maintain constraints properly within the entities must be loaded into an object graph, in this way the constraints and dependencies can be maintained and transactions made within the business logic before it is persisted into the storage model. The problem is making direct commands like DELETE FROM means that the constraints and dependencies may be broken. But for performance reasons such a delete operation is a handy thing to have.

The first thing we looked at was how to delete the entire contents of a table. To do this we would like to emit SQL such as DELETE FROM TABLE. So we need to determine the table name of the entity. We would like to implement an interface looking like:
        void DeleteAll<TEntity>() where TEntity : class;

Our first attempt at determining the table name looked like:

ItemCollection itemCollection;
context.MetadataWorkspace.TryGetItemCollection(DataSpace.SSpace, out itemCollection);

This did not work because the ORM to the persistence layer is not accessible (The C-Space and the S-Space are separated by design in EF4). Our next attempt was to look at the ToTraceString of an ObjectSet. This is the emitted native SQL statement which can then be passed to return the table name. The following piece of code successfully retrieves the table name of a given entity:

        private string GetTableName<TEntity>() where TEntity : class
        {
            string snippet = "FROM [dbo].[";

            string sql = this.context.CreateObjectSet<TEntity>().ToTraceString();
            string sqlFirstPart = sql.Substring(sql.IndexOf(snippet) + snippet.Length);
            string tableName = sqlFirstPart.Substring(0, sqlFirstPart.IndexOf("]"));
            return tableName;
        }

We can the implement the interface for DeleteAll

        public void DeleteAll<TEntity>() where TEntity : class
        {
            string tableName = GetTableName<TEntity>();
            this.context.ExecuteStoreCommand(string.Format(CultureInfo.InvariantCulture, "DELETE FROM {0}", tableName));
        }

By the way, for massive tables it is faster to drop and recreate the table.
Next we want to make a delete statement with a where clause. The problem is that Linq is designed for queries and not for commands. Our ideal world is to create a Linq expression that looks like

            this.databaseContext
                .Query<VulnerabilityAggregate>()
                .Where<VulnerabilityAggregate>(v => v.Id == 1
                       && v.MainOccupancyType.Equals("OccType"))
                .Delete();

To get to this we need to get to the database field names so that we can build a where clause like DELETE FROM TABLE WHERE X=1. Again it is not possible to access the ORM to the table fields by design. So we play again with ObjectQueries and ToTraceString.
The following code will return the sql clause from the FROM sql statement

        private string GetClause<TEntity>(IQueryable<TEntity> clause) where TEntity : class
        {
            string snippet = "FROM [dbo].[";

            string sql = ((ObjectQuery<TEntity>)clause).ToTraceString();
            string sqlFirstPart = sql.Substring(sql.IndexOf(snippet));

            sqlFirstPart = sqlFirstPart.Replace("AS [Extent1]", "");
            sqlFirstPart = sqlFirstPart.Replace("[Extent1].", "");

            return sqlFirstPart;
        }

We needed to replace the alias of the SQL statement because this will cause a syntax error in when appended to a DELETE statement. The next step is to implement the interface

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1004:GenericMethodsShouldProvideTypeParameter")]
        void DeleteAll<TEntity>(IQueryable<TEntity> clause) where TEntity : class;

The implementation looks like:

        public void DeleteAll<TEntity>(IQueryable<TEntity> clause) where TEntity : class
        {
            string sqlClause = GetClause<TEntity>(clause);
            this.context.ExecuteStoreCommand(string.Format(CultureInfo.InvariantCulture, "DELETE {0}", sqlClause));
        }

This worked very well. The next step was to make an extension method so that we can include the where clause within a Linq statement. To do this we need to include the ObjectContext as follows: 

        public static IQueryable<T> Delete<T>(this IQueryable<T> instance, ObjectContext context) where T: class
        {
            string sqlClause = GetClause<T>(instance);
            context.ExecuteStoreCommand(string.Format(CultureInfo.InvariantCulture, "DELETE {0}", sqlClause));

            return instance;
        }
This made the Linq query look like:
            this.databaseContext
                .Query<VulnerabilityAggregate>()
                .Where<VulnerabilityAggregate>(v => v.Id == 1
                           && v.MainOccupancyType.Equals("OccType"))
                .Delete<VulnerabilityAggregate>(this.objectContext);

Looking at the SQL Profiler we could see that the emitted SQL was well formed. We also carried out some performance tests that showed an initial cost of 300ms followed by subsequent deletes taking 100ms. In visual studio 2010 select Test menu/Options Under Test Tools/Test Execution there is an option to “Keep test execution engine running between test runs”. If this is enabled you will get connection pooling and hence the previously observed initially long execution times followed by subsequent shorter times. 100 ms is a good time because using the SQL manager you will get times of 40-60 ms.

Conclusion: This is an efficient way to delete data from tables but you need to take care of the dependencies and constraints outside of the EF component model because we are directly interfacing with the database. In other words use with care.