Sunday, September 14, 2014

LEFT JOIN GROUP BY HAVING COUNT on Entity Framework

Objective, create a Linq query that shows all persons with pay history count of zero or three on entities


This is how our Linq's query should be like in SQL:

select p.BusinessEntityID, p.FirstName, TheCount = count(ph.BusinessEntityId)
from Person.Person p
left join HumanResources.EmployeePayHistory ph on p.BusinessEntityID = ph.BusinessEntityID
group by p.BusinessEntityID, p.FirstName
having count(ph.BusinessEntityId) = 0 or count(ph.BusinessEntityId) = 3
order by TheCount



The two domain entities however doesn't belong in same aggregate..

namespace Domain
{
    public static class ThePerson
    {
        public class Person
        {
            public int    BusinessEntityId { get; set; }

            public string Title            { get; set; }
            public string FirstName        { get; set; }
            public string MiddleName       { get; set; }
            public string LastName         { get; set; }

            // public virtual ICollection PayHistories { get; set; }
        }
    }
}


namespace Domain
{
    public static class TheHumanResources
    {
        public class EmployeePayHistory
        {
            public int                       BusinessEntityId { get; set; }
            public DateTime                  RateChangeDate   { get; set; }

            public virtual ThePerson.Person  Person           { get; set; }
            public decimal                   Rate             { get; set; } 
        }
    }
}


namespace DomainMapping
{
    public class TheDbContext : DbContext
    {
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            modelBuilder.Entity<ThePerson.Person>().ToTable("Person.Person")
                .HasKey(x => x.BusinessEntityId);
                // .HasMany(x => x.PayHistories).WithRequired().HasForeignKey(x => x.BusinessEntityId);

                
               


            modelBuilder.Entity<TheHumanResources.EmployeePayHistory>()
                .ToTable("HumanResources.EmployeePayHistory")
                .HasKey(x => new { x.BusinessEntityId, x.RateChangeDate } )
                .HasRequired(x => x.Person)
                .WithRequiredDependent()
                .Map(m => m.MapKey("BusinessEntityID"));

        }
    }
}


..hence we can't make a simpler query like this:
var query =
    from person in ctx.Set<ThePerson.Person>().SelectMany(x => x.PayHistories.DefaultIfEmpty(), (person, payHistory) => new { person, payHistory })
    
    group person by new { person.person.BusinessEntityId, person.person.FirstName } into persons
    
    let TheCount = persons.Sum(x => x.payHistory != null ? 1 : 0)
    
    where TheCount == 0 || TheCount == 3
    
    orderby TheCount
    
    select new { persons.Key.BusinessEntityId, persons.Key.FirstName, TheCount };


Output:
SELECT
    [GroupBy1].[K1] AS [BusinessEntityId],
    [GroupBy1].[K2] AS [FirstName],
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT
        [Join1].[K1] AS [K1],
        [Join1].[K2] AS [K2],
        SUM([Join1].[A1]) AS [A1]
        FROM ( SELECT
            [Extent1].[BusinessEntityId] AS [K1],
            [Extent1].[FirstName] AS [K2],
            CASE WHEN ( NOT (([Extent2].[BusinessEntityId] IS NULL) AND ([Extent2].[RateChangeDate] IS NULL))) THEN 1 ELSE 0 END AS [A1]
            FROM  [Person].[Person] AS [Extent1]
            LEFT OUTER JOIN [HumanResources].[EmployeePayHistory] AS [Extent2] ON [Extent1].[BusinessEntityId] = [Extent2].[Person_BusinessEntityId1]
        )  AS [Join1]
        GROUP BY [K1], [K2]
    )  AS [GroupBy1]
    WHERE [GroupBy1].[A1] IN (0,3)
    ORDER BY [GroupBy1].[A1] ASC




Following is easy to read Linq with left join group by and having, but it's not efficient, this creates a subquery in SELECT:

var query = from person in ctx.Set<ThePerson.Person>()

            join payHistory in ctx.Set<TheHumanResources.EmployeePayHistory>() on person.BusinessEntityId equals payHistory.BusinessEntityId into payHistories
            from payHistoryNullable in payHistories.DefaultIfEmpty()

            let TheCount = payHistories.Count()

            where TheCount == 0 || TheCount == 3

            orderby TheCount ascending

            select new { person.BusinessEntityId, person.FirstName, TheCount };


Output:
SELECT
    [Project1].[BusinessEntityId] AS [BusinessEntityId],
    [Project1].[FirstName] AS [FirstName],
    [Project1].[C1] AS [C1]
    FROM ( SELECT
        [Extent1].[BusinessEntityId] AS [BusinessEntityId],
        [Extent1].[FirstName] AS [FirstName],
        -- subquery        
        (SELECT
            COUNT(1) AS [A1]
            FROM [HumanResources].[EmployeePayHistory] AS [Extent3]
            WHERE [Extent1].[BusinessEntityId] = [Extent3].[BusinessEntityId]) AS [C1]
        FROM  [Person].[Person] AS [Extent1]
        LEFT OUTER JOIN [HumanResources].[EmployeePayHistory] AS [Extent2] ON [Extent1].[BusinessEntityId] = [Extent2].[BusinessEntityId]
    )  AS [Project1]
    WHERE [Project1].[C1] IN (0,3)
    ORDER BY [Project1].[C1] ASC



Not as readable as the Linq query above, but this is better as this uses GROUP BY:

var query = from person in ctx.Set<ThePerson.Person>()

            join payHistory in ctx.Set<TheHumanResources.EmployeePayHistory>() on person.BusinessEntityId equals payHistory.BusinessEntityId into payHistories
            from payHistoryNullable in payHistories.DefaultIfEmpty()

            group payHistoryNullable by new { person.BusinessEntityId, person.FirstName } into payHistoriesNullable

            let TheCount = payHistoriesNullable.Sum(eachPayHistoryNullable => eachPayHistoryNullable != null ? 1 : 0)

            where TheCount == 0 || TheCount == 3

            orderby TheCount ascending
            select new { payHistoriesNullable.Key.BusinessEntityId, payHistoriesNullable.Key.FirstName, TheCount };


Output:

SELECT
    [GroupBy1].[K1] AS [BusinessEntityId],
    [GroupBy1].[K2] AS [FirstName],
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT
        [Join1].[K1] AS [K1],
        [Join1].[K2] AS [K2],
        SUM([Join1].[A1]) AS [A1]
        FROM ( SELECT
            [Extent1].[BusinessEntityId] AS [K1],
            [Extent1].[FirstName] AS [K2],
            CASE WHEN ( NOT (([Extent2].[BusinessEntityId] IS NULL) AND ([Extent2].[RateChangeDate] IS NULL))) THEN 1 ELSE 0 END AS [A1]
            FROM  [Person].[Person] AS [Extent1]
            LEFT OUTER JOIN [HumanResources].[EmployeePayHistory] AS [Extent2] ON [Extent1].[BusinessEntityId] = [Extent2].[BusinessEntityId]
        )  AS [Join1]
        -- efficient, uses GROUP BY 
        GROUP BY [K1], [K2] 
    )  AS [GroupBy1]
    WHERE [GroupBy1].[A1] IN (0,3)
    ORDER BY [GroupBy1].[A1] ASC            



Happy Coding!

Saturday, September 13, 2014

Interface Magic (at least to me)

Didn't know that interface could intercept the method of the base class even that base class is not implementing the interface

using System;
                    
public class Program
{
    public static void Main()
    {
        IRobot r = new Robot();

        r.SaySomething();
    }
}


public class Machine
{
    public void SaySomething()
    {
        Console.WriteLine("Hello");
    }
}

public interface IRobot
{
    void SaySomething();
}


public class Robot : Machine, IRobot
{
// and we don't have to implement IRobot here    
}




Live Code: https://dotnetfiddle.net/cqdt8z


Output:
Hello

I still have to find a use for that code found somewhere



Happy Coding!

Friday, September 12, 2014

WPF Command

Got lost on the new Command here:

public ICommand ShowEmailAddress
{
    get
    {
        return new Command(() => true, () => DialogService.Show(this.EmailAddress));
    }
}


Command is not available on base class library of .NET, we have to implement ICommand ourselves.

Found a sample implementation of ICommand here: http://www.markwithall.com/programming/2013/03/01/worlds-simplest-csharp-wpf-mvvm-example.html


Modified according to the need of code above:
public class Command : ICommand
{
    readonly Action _action;

    readonly Func<bool> _canExecute;

    
    public Command(Func<bool> canExecute, Action action)
    {
        _canExecute = canExecute;
        _action = action;
    }

    void ICommand.Execute(object parameter)
    {
        _action();
    }

    bool ICommand.CanExecute(object parameter)
    {
        // return true; // It's advisable to make this always true: http://www.markwithall.com/programming/2013/03/01/worlds-simplest-csharp-wpf-mvvm-example.html

        return _canExecute();
    }

#pragma warning disable 67
    public event EventHandler CanExecuteChanged;
#pragma warning restore 67
}


Happy Coding!

Thursday, September 11, 2014

Property changed event handler

If you want the implementation of the INotifyPropertyChanged interface..
public event PropertyChangedEventHandler PropertyChanged;

void OnPropertyChanged(string property)
{
 PropertyChangedEventHandler handler = this.PropertyChanged;
 if (handler != null)
  handler(this, new PropertyChangedEventArgs(property));
}



..more explicit, you can't just change the PropertyChanged implementation to this:
event PropertyChangedEventHandler INotifyPropertyChanged.PropertyChanged;

void OnPropertyChanged(string property)
{
 PropertyChangedEventHandler handler = this.PropertyChanged;
 if (handler != null)
  handler(this, new PropertyChangedEventArgs(property));
}


It will have this error:
An explicit interface implementation of an event must use event accessor syntax


Must do this:
PropertyChangedEventHandler _propertyChanged;
event PropertyChangedEventHandler INotifyPropertyChanged.PropertyChanged
{
 add { _propertyChanged += value; }
 remove { _propertyChanged -= value; }
}

void OnPropertyChanged(string property)
{
 PropertyChangedEventHandler handler = this._propertyChanged;
 if (handler != null)
  handler(this, new PropertyChangedEventArgs(property));
}  


Happy Coding!

Trusted connection string is not allowed on IIS?

Trusted connection string format doesn't work when in IIS..


cfg.DataBaseIntegration(c =>
    {
        c.Driver<NHibernate.Driver.SqlClientDriver>();
        c.Dialect<NHibernate.Dialect.MsSql2008Dialect>();
        c.ConnectionString = "Server=.;Database=RideOfYourLife;Trusted_Connection=True";

        c.LogSqlInConsole = true;
        c.LogFormattedSql = true;                    
    });


.., the error cascades to ServiceStack as:
response Status
error CodeExceptionmessageError trying to resolve Service 'Marshaller.TheServices.ReservationResourceService' or one of its autowired dependencies (see inner exception for details)



The error is not helpful at all, so I tried to wire a simple IoC-injected object, that is an object with no database connection, and voila, the error gone!


Tracked the error in connection string, must change it to standard format:

cfg.DataBaseIntegration(c =>
    {
        c.Driver<NHibernate.Driver.SqlClientDriver>();
        c.Dialect<NHibernate.Dialect.MsSql2008Dialect>();
        c.ConnectionString = "Server=.;Database=RideOfYourLife;User Id=sa; Password=opensesame;";

        c.LogSqlInConsole = true;
        c.LogFormattedSql = true;                    
    });


I have yet to find out how to make trusted connection string work on IIS


Happy Coding!

Sunday, September 7, 2014

Intercepting SQL in NHibernate for Test Uses

Found an implementation for interceptor:

public class NHSQLInterceptor : EmptyInterceptor, IInterceptor
{
    NHibernate.SqlCommand.SqlString IInterceptor.OnPrepareStatement(NHibernate.SqlCommand.SqlString sql)
    {
        // SQL.NHibernateSQL is static property
        SQL.NHibernateSQL = sql.ToString();
        return sql;
    }
}



On configuration:

var config = new Configuration();
...
...
config.SetInterceptor(new SQLInterceptor());
...
...
ISessionFactory factory = config.BuildSessionFactory();
...
...
factory.OpenSession(config.Interceptor); // turns out passing the interceptor to session is not needed, interceptors still gets called despite removing this



Then I found something simpler on stackoverflow:
public class SqlStatementInterceptor : EmptyInterceptor
{
    public override NHibernate.SqlCommand.SqlString OnPrepareStatement(NHibernate.SqlCommand.SqlString sql)
    {
        SQL.NHibernateSQL = sql.ToString();
        return sql;
    }
}



Happy Coding!

Unit Testing NHibernate Caching

I read somewher that says HashtableCacheProvider is more ok on unit testing, somehow true, have to use it for unit testing for now

cfg.Cache(x =>
            {

                // This is more stable on unit test
                x.Provider<NHibernate.Cache.HashtableCacheProvider>();       

                // I don't know why SysCacheProvider has problem on simultaneous unit testing, 
                // might be SysCacheProvider is just giving one session factory, so simultaneous test see each other caches
                // This solution doesn't work: http://stackoverflow.com/questions/700043/mstest-executing-all-my-tests-simultaneously-breaks-tests-what-to-do

                // x.Provider<NHibernate.Caches.SysCache.SysCacheProvider>();