The Allure of Rewriting an Application

7304468436_ee00db57d5_zMost Software Engineers have at sometime in their careers advocated for a rewrite. This is a Software Engineer’s utopia. I admit, there was a time I was that software engineer. Thankfully those days are behind me.

Joel Spolsky explains the merits of rewriting software:

…the single worst strategic mistake that any software company can make.

That seems pretty harsh. Is it really that bad to toss the old and write anew?

Joel Spolsky has a thought on why software engineers want to rewrite code:

It’s harder to read code than to write it.

How many times have you read code and thought “What the hell were they thinking?” Worse yet, you’ve said it aloud. To understand code, you must mentally compile it. This is really hard. The author might be a novice, speak a different language or be an experienced coder. Heck the author could be you!

Have you ever read code and wondered why something was written a particular way? You rewrite it, only to discover why it was written in that particular way. Each line of code holds bit of knowledge. Sometimes this knowledge is hard fought. Have you ever chased a bug for weeks?

To be fair a rewrite may be the way to go, but most of the time it’s not.

.NET Vs MEAN: A Rebuttal

In a recent post on Airpair’s blog by Michael Perrenoud, Michael discusses his reasons for leaving .NET and moving to the MEAN stack. After reading his post, I do not feel he gave .NET a fair shake.  This was my response to  him:

Michael,

I can relate with you on your move to the MEAN Stack. I am also a .NET software engineer with 15 plus years of experience. Like you, I have become concerned with .NET and its eco-system. I started exploring other Stacks. I tried the MEAN stack and found it enticing.

When I read your reasons for leaving .NET, I was disappointed. .NET is not perfect by no stretch of the imagination, but I don’t see it on it’s way out. In fact, C# is the 5th most popular language according to Tiobe software. And in the next version of .NET, Microsoft is simplifying choices by revamping core web technologies, reworking it’s packaging framework and effectively sun-setting some older parts of the framework. If anything .NET might be just starting an upswing.

To give .NET a fair shake, I provide counterpoints to your 4 points:

In your first point it is stated: “The stack is heavily server-side dependent.” This could be true for just about every technology stack: Ruby on Rails, Java, .NET and NodeJS. Yes, I said NodeJS. You can just as easily render html on the server and send it to the client en masse from NodeJS. This point seems to be more of a comparison between Single Page Applications (AngularJS, EmberJs) and Server-Side HTML than a specific technology stack.

In your second point it is stated: The server-side technology is very heavy and relies on an even clunkier web server for its exposure. Even the lightest server-side technology, ASP.NET Web API, is still heavier than NodeJS.” Internet Information Server (IIS), Microsoft’s web server provides many out of the box features. At first it might seem overwhelming, but like some of the best technologies you have to get past the learning curve.

Web API is a lightweight API framework that sits on top of the .NET framework. NodeJs a server-side javascript engine, that allows you to write high performing javascript outside of the browser… I don’t know how you can compare the two.

In your third point it is stated: “The market has grown weary of the high cost of entry due to licensing fees. To get into Visual Studio Ultimate (necessary for any very large projects) you need to invest $13K; that’s insane!” I’ve never been on a project that NEEDED Visual Studio Ultimate. There is a free version of Visual Studio called Visual Studio Express that is fine for most smaller projects.

Your post does not mentioned what the $13,000 gets you: It gets you Visual Studio Ultimate Edition, arguably the most powerful IDE, it also gets you access to MSDN, which includes licenses to virtually all of Microsoft’s products, including a generous amount of hours per month on Microsoft’s cloud platform, Azure.

Microsoft is a for profit company. It’s their business. Just like you are a software engineer, you charge for your time. They charge for their software.

In your fourth and final point it is stated: The typical .NET stack is not homogeneous in nature. The data alone, and the transitions it must go through from database engine, to data model, to view model, to JSON, and back again tells the tale. It doesn’t matter how much syntactic sugar you wrap it in. It doesn’t matter how many frameworks. The stack is not homogeneous.” This seems to be more of an argument between statically typed languages and dynamically typed languages. You could just as well be talking about Java rather than .NET. While you don’t explicitly call it out, I can’t help but feel there is a hint of comparison between Relational and NoSql databases.

All the transitions (which are really separations of concerns (SOC)) you mention serve a purpose. The same transitions exist when using the MEAN Stack. It’s just they are handled differently in Javascript and in MongoDB. Switching to the MEAN stack won’t cause these transitions (SOC) to go away.

“They will be in demand for a long time; like COBOL developers. Believe me, you don’t “just rewrite” 4 decades of COBOL; the same holds true for the .NET stack. However, I did draw a conclusion that the .NET stack is on its way out.”

Youch! Really man? That’s just harsh!

.Net and the MEAN Stack both have a large following and have delivered great software. Each has advantages and drawbacks. In my humble opinion the technology stack is a person choice and an academic discussion. Delivering successful inspiring software is what’s important.

Questions to Ask During an Interview

When I waQuestionslk out of a interview, I want to know the position’s responsibilities, I want to know the environment and I want to know what I am expected to accomplish during my first week. Most of all I want to know if the company is a fit for me. More often than not companies will hire the best among the candidate pool. This does not mean they are the best for the position. Simply they are the best in the given candidate pool. Very few companies recognize this difference. It’s your job as the interviewee to vet the company.

I have developed the following questions to ask during an interview:

What will be my first task?
Is there a project plan? How much thought has gone into this position?

What will determine success or failure?
If project success can’t be articulated, how can they measure success in the position?

How do I get my tasks?
Is an issue tracking system used?

Do you use source control?
A company without source control in 2014 is almost always a deal breaker. If a company can’t provide the most basic need of software engineers there are bound to be other issues.

Do you allow remote work?
Telecommuting is a nice perk. It affords you flexibility to do errands or have appointments during lunch.

Describe the computer/environment I am provide.
What type of machine is given to software engineers? Two monitors or one? Is the work area low traffic and quiet — Getting stuck in a loud high traffic area sucks.

What are the hours?
Are the hours flexible? What are the core hours?

Am I on call?
Are you expected to support production issues during off hours? Do software engineers answer customer support calls?

Automated builds and Deployments?
How evolved is the build process? Do developers manually build or is it automated?

Do you have testers?
Am I responsible for testing?

What technologies do you use?
There are some technologies that are no longer interesting.
SCRUM, Lean, Agile or Waterfall. Does the team do Code Reviews? What about Unit Testing?

Most forget that an interview is a two way street. You, as the interviewee, are interviewing the company and your future co-workers for a good fit in the company and in the position.

Missing Management Delegation Icon in IIS

It’s critical this is done first. Web deploy may not install correctly if it’s installed with the Management Service icon missing. Check IIS for the Management Delegation icon, it’ll be under the Management section.

If it’s missing run the following commands.

Windows 2012


dism /online /enable-feature /featurename:IIS-WebServerRole
dism /online /enable-feature /featurename:IIS-WebServerManagementTools
dism /online /enable-feature /featurename:IIS-ManagementService
Reg Add HKLM\Software\Microsoft\WebManagement\Server /V EnableRemoteManagement /T REG_DWORD /D 1
net start wmsvc
sc config wmsvc start= auto

Run Web Deploy.

Check to see if the icon is there. If it’s not, run web deploy again. It should be there.

Calling Stored Procedures with Code First

codeOne of the weaknesses of Entity Framework 6 Code First is the lack of support for natively calling database constructs (views, stored procedures… etc). For those who have not heard of or used Code-First in Entity Framework (EF), Code-First is simply a Fluent mapping API. The idea is to create all your database mappings in code (i.e. C#) and the framework then creates and track the changes in the database schema.

In traditional Entity Framework to call a stored procedure you’d map it in your EDMX file. This is a multi-step process. Once the process is completed a method is created, which hangs off the DataContext.

I sought to making a calling stored procedure easier. At the heart of a stored procedure you have a procedure name, N number of parameters and a results set. I’ve written a small extension method that takes a procedure name, parameters and a return type. It just works. No mapping the procedure and it’s parameters.


public static List<TReturn> CallStoredProcedure<TParameters, TReturn>(this DataContext context, string storedProcedure, TParameters parameters) where TParameters : class where TReturn : class, new()
{
IDictionary<string,object> procedureParameters = new Dictionary<string, object>();
PropertyInfo[] properties = parameters.GetType().GetProperties();

var ps = new List<object>();

foreach (var property in properties)
{
object value = property.GetValue(parameters);
string name = property.Name;

procedureParameters.Add(name, value);

ps.Add(new SqlParameter(name, value));
}

var keys = procedureParameters.Select(p => string.Format("@{0}", p.Key)).ToList();
var parms = string.Join(", ", keys.ToArray());

return context.Database.SqlQuery<TReturn>(storedProcedure + " " + parms, ps.ToArray()).ToList();
}

 

usage


var context = new DataContext();

List<User> users = context.CallStoredProcedure<object,User>("User_GetUserById", new{userId = 3});

Id’s, The Great Debate

Using ‘Id’ or NameId.3549285383_11de3317a6_z

I had a conversation with Rob Toyias on Id’s. The impetus was an existing product we were working on that did not identify primary and foreign keys consistantly. We spent a lot of time chasing down primary and foreign keys.

I was of the mindset that a table should have a primary key called “Id” and all foreign keys should be their respective tablenames. For example a foreign key from the User Table should be called UserId.

Rob disagreed. He thought that each id should be named consistently, even in the table where it originated. For example, in the user table, the primary key would be UserId.

My argument was if you have a table with 3 Id’s how do you know which one is the primary? He countered saying that the Id’s would be consistent across the database.

The conversation ended in a stalemate.

Fast-forward 6 months. I am on a different project. I’m working with Id’s in Javascript. I applied my same Id naming scheme I use in the database to my javascript. On the employee page the variable id refers to the employee id. All other id’s are their respective names of their pages (i.e. userId). This worked great. Until today. I won’t go too deep into the details, but what it came down to was sharing code between pages. The shared code assumes id is the id of the page, which it is, but Employee Id and User Id are not interchangeable.

After spending a number of hours fixing the Id debacle, I conceded to Rob’s point.

 

* photo reference

Git Cheat Sheet

Below are git commands I find myself using over and over.

clone repository
git clone https://github.com/tinymce/tinymce-dist.git

Add existing git files to remote git repo
cd /path/to/my/repo
git remote add origin https://lostinkali@bitbucket.org/lostinkali/flightstats.git
git push -u origin –all # pushes up the repo and its refs for the first time
git push -u origin –tags # pushes up any tags

Create a repository in existing folder
git init

Change current branch to master
git checkout better_branch
git merge –strategy=ours master # keep the content of this branch, but record a merge
git checkout master
git merge better_branch

Delete Branch
git branch -D bugfix

Revert to previous commit
git checkout master
git reset –hard e3f1e37
git push –force origin master
# Then to prove it (it won’t print any diff)
git diff master..origin/master

git add [filename]
adds the file to git.

git clean -fd
kills off the untracked files since the more recent commit in the log.

git commit -m “enter message here”
commits the added files to git.

git rm file1.txt
Remove the file git. Use -f to force the file to removed even when there are changes.

git tag -a v1.4 -m ‘my version 1.4′
Tagging a specific point in time.

 

ASP.NET Identity with Repository and Unit of Work

I have found working with ASP.Net Identity 2.0 and EF6 a bit challenging. The biggest drawback is the lack of documentation or conflicting documentation.

I am using WebApi 2.0, EF6 and ASP.Net Identity 2.0. At first it was tough to get going but once it’s working, it’s been good.

I created my own Identity classes. At the moment I don’t care about extending the identity classes I just want to generate the tables and log into the system.

CustomRole

public class CustomRole : IdentityRole<int, CustomUserRole>
{
    ///
<summary>
    /// Initializes a new instance of the <see cref="CustomRole"/> class.
    /// </summary>
    public CustomRole() { }

    ///
<summary>
    /// Initializes a new instance of the <see cref="CustomRole"/> class.
    /// </summary>
    ///
<param name="name">The name.</param>
    public CustomRole(string name) { Name = name; }
}

CustomUserClaim

public class CustomUserClaim : IdentityUserClaim<int> { }

CustomUserLogin

public class CustomUserLogin : IdentityUserLogin<int> { }

CustomUserRole

public class CustomUserRole : IdentityUserRole<int> {}

User

public class User : IdentityUser<int, CustomUserLogin, CustomUserRole, CustomUserClaim>
{

    ///
<summary>
    /// Gets or sets the first name.
    /// </summary>
    /// <value>The first name.</value>
    public string FirstName { get; set; }

    ///
<summary>
    /// Gets or sets the last name.
    /// </summary>
    /// <value>The last name.</value>
    public string LastName { get; set; }

    ///
<summary>
    /// Gets or sets a value indicating whether this <see cref="User"/> is active.
    /// </summary>
    /// <value><c>true</c> if active; otherwise, <c>false</c>.</value>
    public bool Active { get; set; }

}

I don’t like the naming of the Identity tables, so I changed the names.

DataContext

public class DataContext : IdentityDbContext<User, CustomRole, int, CustomUserLogin, CustomUserRole, CustomUserClaim>
{
    public DataContext() : base("DefaultConnection"){}

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<CustomUserRole>().ToTable("UserRoles", "Security");
        modelBuilder.Entity<CustomUserLogin>().ToTable("UserLogins", "Security");
        modelBuilder.Entity<CustomUserClaim>().ToTable("UserClaims", "Security");
        modelBuilder.Entity<CustomRole>().ToTable("Roles", "Security");
        modelBuilder.Entity<User>().ToTable("Users", "Security");

    }
}

I found getting the UserManager a bit of a pain.

I created a static class to handle it. The UserStore does handle the lifecycle of the DataContext, but you’ll have to call dispose for this to happen. This could cause problems if you are using this DataContext reference elsewhere. I’ll eventually wire it into my DI container, but for now this is what I have:

Identity Manager

public class Identity
{
    ///
<summary>
    /// Gets the user manager.
    /// </summary>
    /// <returns>UserManager<User, System.Int32>.</returns>
    public static UserManager<User, int> GetUserManager()
    {
        var store = new UserStore<User, CustomRole, int, CustomUserLogin, CustomUserRole, CustomUserClaim>(new DataContext());
        var userManager = new UserManager<User, int>(store);

        return userManager;
    }
}

I use the Unit of Work pattern for most my data access. It works good. There are some cases where I have data that needs more control than the unit of work exposes for these cases I exposed the DataContext. If that still does not work for me, I’ll fallback to using a repository.

Unit of Work

public class UnitOfWork : IUnitOfWork
{
    private readonly IContainer _container;

    public UnitOfWork(IContainer container) :this()
    {
        _container = container;
    }

    //private readonly List<CommitInterception> _postInterceptions = new List<CommitInterception>(); 

    public DataContext Context { get; set; }

    ///
<summary>
    /// Initializes a new instance of the <see cref="UnitOfWork"/> class.
    /// </summary>
    public UnitOfWork()
    {
        Context = new DataContext();
    }

    ///
<summary>
    /// Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
    /// </summary>
    /// <exception cref="System.NotImplementedException"></exception>
    public void Dispose()
    {
        try
        {
            Commit();
        }
        finally
        {
            Context.Dispose();
        }
    }

    ///
<summary>
    /// Begins the transaction.
    /// </summary>
    /// <returns>IUnitOfWorkTransaction.</returns>
    public IUnitOfWorkTransaction BeginTransaction()
    {
        return new UnitOfWorkTransaction(this);
    }

    ///
<summary>
    /// Commits this instance.
    /// </summary>
    public void Commit()
    {
        Commit(null);
    }

    ///
<summary>
    /// Commits transaction.
    /// </summary>
    public void Commit(DbContextTransaction transaction)
    {
        //Lee was here.
        try
        {
            Context.SaveChanges();

            if (transaction != null)
            {
                transaction.Commit();
            }

            //foreach (var interception in _postInterceptions)
            //{
            //    interception.PostCommit(interception.Instance, this);
            //}

        }
        catch (DbEntityValidationException ex)
        {
            var errors = FormatError(ex);
            throw new Exception(errors, ex);
        }
        catch
        {
            if (transaction != null)
            {
                transaction.Rollback();
            }
            throw;
        }
        finally
        {
           // _postInterceptions.Clear();
        }
    }

    ///
<summary>
    /// Formats the error.
    /// </summary>
    ///
<param name="ex">The ex.</param>
    /// <returns>System.String.</returns>
    private static string FormatError(DbEntityValidationException ex)
    {
        var build = new StringBuilder();
        foreach (var error in ex.EntityValidationErrors)
        {
            var errorBuilder = new StringBuilder();

            foreach (var validationError in error.ValidationErrors)
            {
                errorBuilder.AppendLine(string.Format("Property '{0}' errored:{1}", validationError.PropertyName, validationError.ErrorMessage));
            }

            build.AppendLine(errorBuilder.ToString());
        }
        return build.ToString();
    }

    ///
<summary>
    /// Inserts the specified entity.
    /// </summary>
    /// <typeparam name="T"></typeparam>
    ///
<param name="entity">The entity.</param>
    /// <returns>``0.</returns>
    public T Insert<T>(T entity) where T: class
    {
        var instance = _container.TryGetInstance<IUnitOfWorkInterception<T>>();

        if (instance != null)
        {
            instance.Intercept(entity, this);
           // _postInterceptions.Add(new CommitInterception() { Instance = entity, PostCommit = (d,f) => instance.PostCommit(d as T, f) });
        }

        var set = Context.Set<T>();
        var item = set.Add(entity);

        return item;
    }

    public T Update<T>(T entity) where T : class
    {
        var set = Context.Set<T>();
        set.Attach(entity);
        Context.Entry(entity).State = EntityState.Modified;

        return entity;
    }

    ///
<summary>
    /// Deletes the specified entity.
    /// </summary>
    /// <typeparam name="T"></typeparam>
    ///
<param name="entity">The entity.</param>
    public void Delete<T>(T entity) where T : class
    {
        var set = Context.Set<T>();
        set.Remove(entity);
    }

    ///
<summary>
    /// Finds the specified predicate.
    /// </summary>
    /// <typeparam name="T"></typeparam>
    ///
<param name="predicate">The predicate.</param>
    /// <returns>IQueryable{``0}.</returns>
    public IQueryable<T> Find<T>(Expression<Func<T, bool>> predicate) where T : class
    {
        var set = Context.Set<T>();
       return set.Where(predicate);
    }

    ///
<summary>
    /// Gets all.
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <returns>IQueryable{``0}.</returns>
    public IQueryable<T> GetAll<T>() where T : class
    {
        return Context.Set<T>();
    }

    ///
<summary>
    /// Gets the by identifier.
    /// </summary>
    /// <typeparam name="T"></typeparam>
    ///
<param name="id">The identifier.</param>
    /// <returns>``0.</returns>
    public T GetById<T>(int id) where T : class
    {
        var set = Context.Set<T>();
        return set.Find(id);
    }

    ///
<summary>
    /// Executes the query command.
    /// </summary>
    /// <typeparam name="T"></typeparam>
    ///
<param name="sql">The SQL.</param>
    /// <returns>DbSqlQuery{``0}.</returns>
    public DbSqlQuery<T> ExecuteQueryCommand<T>(string sql) where T : class
    {
        var set = Context.Set<T>();
        return set.SqlQuery(sql);
    }

    private class CommitInterception
    {
        public object Instance { get; set; }

        public Action<object, IUnitOfWork> PostCommit { get; set; }
    }
}

public class UnitOfWorkTransaction : IUnitOfWorkTransaction
{
    private readonly UnitOfWork _unitOfWork;
    private readonly DbContextTransaction _transaction;

    ///
<summary>
    /// Initializes a new instance of the <see cref="UnitOfWorkTransaction"/> class.
    /// </summary>
    ///
<param name="unitOfWork">The unit of work.</param>
    public UnitOfWorkTransaction(UnitOfWork unitOfWork)
    {
        _unitOfWork = unitOfWork;
        _transaction = _unitOfWork.Context.Database.BeginTransaction();
        Context = unitOfWork.Context;
    }

    ///
<summary>
    /// Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
    /// </summary>
    public void Dispose()
    {
        _unitOfWork.Commit(_transaction);
    }

    public DataContext Context { get; set; }

    ///
<summary>
    /// Commits this instance.
    /// </summary>
    public void Commit()
    {
        _unitOfWork.Commit();
    }

    ///
<summary>
    /// Rollbacks this instance.
    /// </summary>
    public void Rollback()
    {
        _transaction.Rollback();
    }

    ///
<summary>
    /// Inserts the specified entity.
    /// </summary>
    /// <typeparam name="T"></typeparam>
    ///
<param name="entity">The entity.</param>
    /// <returns>T.</returns>
    public T Insert<T>(T entity) where T : class
    {
        return _unitOfWork.Insert(entity);
    }

    ///
<summary>
    /// Updates the specified entity.
    /// </summary>
    /// <typeparam name="T"></typeparam>
    ///
<param name="entity">The entity.</param>
    /// <returns>T.</returns>
    public T Update<T>(T entity) where T : class
    {
        return _unitOfWork.Update(entity);
    }

    ///
<summary>
    /// Deletes the specified entity.
    /// </summary>
    /// <typeparam name="T"></typeparam>
    ///
<param name="entity">The entity.</param>
    public void Delete<T>(T entity) where T : class
    {
        _unitOfWork.Delete(entity);
    }

    ///
<summary>
    /// Finds the specified predicate.
    /// </summary>
    /// <typeparam name="T"></typeparam>
    ///
<param name="predicate">The predicate.</param>
    /// <returns>IQueryable<T>.</returns>
    public IQueryable<T> Find<T>(Expression<Func<T, bool>> predicate) where T : class
    {
       return _unitOfWork.Find(predicate);
    }

    ///
<summary>
    /// Gets all.
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <returns>IQueryable<T>.</returns>
    public IQueryable<T> GetAll<T>() where T : class
    {
        return _unitOfWork.GetAll<T>();
    }

    ///
<summary>
    /// Gets the by identifier.
    /// </summary>
    /// <typeparam name="T"></typeparam>
    ///
<param name="id">The identifier.</param>
    /// <returns>T.</returns>
    public T GetById<T>(int id) where T : class
    {
       return _unitOfWork.GetById<T>(id);
    }

    ///
<summary>
    /// Executes the query command.
    /// </summary>
    /// <typeparam name="T"></typeparam>
    ///
<param name="sql">The SQL.</param>
    /// <returns>DbSqlQuery<T>.</returns>
    public DbSqlQuery<T> ExecuteQueryCommand<T>(string sql) where T : class
    {
       return _unitOfWork.ExecuteQueryCommand<T>(sql);
    }
}

Here are a few examples of it in action. I have an nHibernate background and like defining a transaction in the scope of a using so I implemented in my unit of work.

        using (var trans = _unitOfWork.BeginTransaction())
        {
            var newAgency = trans.Insert(new Database.Schema.Agency() { Name = agency.Name, TaxId = agency.TaxId });

        }

Another example of using the “Find” off of the Unit of Work:

        var users = _unitOfWork.Find<Database.Schema.User>(s => s.Active && s.Agency_Id == agencyId)
            .Select(u=> new {Label = u.FirstName + " " + u.LastName, Value = u.Id})
            .ToList();

User Creation and User Sign-In

I use ASP.NET Identity for the sign-In and user creation and my Unit of Work for everything else.

Testing

I would not try to test ASP.NET Identity. For one I’m sure Microsoft did a pretty good job testing it. I’m sure they did a better job than you or I could do. If you really want to test around the ASP.NET Identity code put it behind an interface and mock out the interface.

Conditional Sql parameters with nHibernate

The problem is a the nHibernate’s CreateSqlQuery needs a complete sql string to be created, but you can’t create a string until you’ve evaluated the parameters. The only work around is to evaluate the conditional parameters to create the sql string to create the nHibernate session and then revaluate the parameters again to add them to the nHibernate query object. The problem with this, is the same evaluation logic is written twice. What is needed is a simple fluent api that will do eveythign for you and spit out the ISQLQuery when it’s done.

Before

        public IList<AppointmentScheduleSummary> FillQuantityOld(DateTime? fromDate, DateTime? toDate, string CompanyID)
        {
            string sql = "select VA.Userid as ID, E.FirstName + ' ' + E.LastName as Name,VA.Userid,count(*) as Total, getdate() as Date  from V_AppointmentScheduleStat VA, Appointment A, Employee E, Office O where";

            if (fromDate.HasValue)
            {
                sql += "  VA.Date >= '" + fromDate.Value.ToShortDateString() + "' and";

            }

            if (toDate.HasValue)
            {
                sql += "  VA.Date <= '" + toDate.Value.AddDays(1).ToShortDateString() + "' and";
            }

            sql += "  VA.date = A.date  and VA.UserId = E.UserId and O.OfficeNum = A.OfficeNum ";
            sql += " and A.appttypeid is not null";
            sql += " and O.CompanyID='" + CompanyID + "'";
            sql += " group by E.FirstName + ' ' + E.LastName ,VA.Userid  ";

            ISQLQuery query = _NHibernateSessionManager.GetSession().CreateSQLQuery(sql)
             .AddEntity("K", typeof(AppointmentScheduleSummary));
            return query.List<AppointmentScheduleSummary>();
        }

After

        public IList<AppointmentScheduleSummary> FillQuantity(DateTime? fromDate, DateTime? toDate,string CompanyID)
        {
           var query = _NHibernateSessionManager.GetSession()
                .SQLQuery("select VA.Userid as ID, E.FirstName + ' ' + E.LastName as Name,VA.Userid,count(*) as Total, getdate() as Date  from V_AppointmentScheduleStat VA, Appointment A, Employee E, Office O where")

                .If(fromDate.HasValue, "VA.Date >= :fromDate and", parameters =>
                {
                    parameters.SetParameter("fromDate", fromDate.Value.ToShortDateString());
                })

                .If(toDate.HasValue, "VA.Date <=:toDate and ", parameters =>
                {
                    parameters.SetParameter("toDate", toDate.Value.AddDays(1).ToShortDateString());
                    parameters.SetParameterList("", new[] {2, 3, 4,});

                })

                .Sql(" VA.date = A.date and VA.UserId = E.UserId and O.OfficeNum = A.OfficeNum and A.appttypeid is not null and O.CompanyID = :companyId" +
                     " group by E.FirstName + ' ' + E.LastName ,VA.Userid")
                    .SetParameter("companyId", CompanyID)
                .ToQuery();

             query.AddEntity("K", typeof(AppointmentScheduleSummary));
             return query.List<AppointmentScheduleSummary>();
        }

SqlStringBuilder

using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using NHibernate;

namespace IT2.DataAccess.Extensions
{
    public class SqlStringBuilder
    {
        private readonly ISession _session;
        private readonly Action<string> _logging;
        readonly StringBuilder _builder = new StringBuilder();
        readonly ParameterBuilder _parameterBuilder;

        ///
<summary>
        /// Initializes a new instance of the <see cref="SqlStringBuilder" /> class.
        /// </summary>
        ///
<param name="session">The session.</param>
        ///
<param name="sql">The SQL.</param>
        ///
<param name="logging"></param>
        public SqlStringBuilder(ISession session, string sql, Action<string> logging)
        {
            _session = session;
            _logging = logging;
            _builder.Append(sql);
            Parameters = new Dictionary<string, object>();
            ParameterList = new Dictionary<string, IEnumerable>();
            _parameterBuilder = new ParameterBuilder(this);
        }

        ///
<summary>
        /// Gets or sets the parameters.
        /// </summary>
        /// <value>The parameters.</value>
        public IDictionary<string, object> Parameters { get; set; }

        ///
<summary>
        /// Gets or sets the parameters.
        /// </summary>
        /// <value>The parameters.</value>
        public IDictionary<string, IEnumerable> ParameterList { get; set; }

        ///
<summary>
        /// To the query.
        /// </summary>
        /// <returns>IQuery.</returns>
        public ISQLQuery ToSqlQuery()
        {
            string sql = _builder.ToString();

            if (_logging != null)
            {
                _logging(sql);
            }

            var query = _session.CreateSQLQuery(sql);

            foreach (var parameter in Parameters)
            {
                query.SetParameter(parameter.Key, parameter.Value);
            }

            foreach (var parameter in ParameterList)
            {
                query.SetParameterList(parameter.Key, parameter.Value);
            }

            return query;
        }

        ///
<summary>
        /// To the query.
        /// </summary>
        /// <returns>IQuery.</returns>
        public IQuery ToQuery()
        {
            string sql = _builder.ToString();

            if (_logging != null)
            {
                _logging(sql);
            }

            var query = _session.CreateQuery(sql);

            foreach (var parameter in Parameters)
            {
                query.SetParameter(parameter.Key, parameter.Value);
            }

            foreach (var parameter in ParameterList)
            {
                query.SetParameterList(parameter.Key, parameter.Value);
            }

            return query;
        }

        ///
<summary>
        /// Ifs the specified evaluation.
        /// </summary>
        ///
<param name="evaluation">if set to <c>true</c> [evaluation].</param>
        ///
<param name="sql">The SQL.</param>
        /// <returns>ParameterBuilder.</returns>
        public ParameterBuilder If(bool evaluation, string sql)
        {
            return If(evaluation, sql, null);
        }

        ///
<summary>
        /// Conditionals the specified evaluation.
        /// </summary>
        ///
<param name="evaluation">if set to <c>true</c> [evaluation].</param>
        ///
<param name="sql">The SQL.</param>
        ///
<param name="parameters">The parameters.</param>
        /// <returns>SqlStringBuilder.</returns>
        public ParameterBuilder If(bool evaluation, string sql, Action<ParameterBuilder> parameters)
        {
            if (evaluation)
            {
                _builder.Append(string.Format(" {0} ", sql));

                if (parameters != null)
                {
                    parameters(_parameterBuilder);
                }
            }

            return _parameterBuilder;
        }

        ///
<summary>
        /// Sets the parameters.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        ///
<param name="key">The key.</param>
        ///
<param name="value">The value.</param>
        /// <returns>ParameterBuilder.</returns>
        public ParameterBuilder SetParameter<T>(string key, T value)
        {
            _parameterBuilder.SetParameter(key, value);
            return _parameterBuilder;
        }

        ///
<summary>
        /// Sets the parameter list.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        ///
<param name="key">The key.</param>
        ///
<param name="value">The value.</param>
        /// <returns>ParameterBuilder.</returns>
        public ParameterBuilder SetParameterList<T>(string key, T value) where T : IEnumerable
        {
            _parameterBuilder.SetParameterList(key, value);
            return _parameterBuilder;
        }

        ///
<summary>
        /// SQLs the specified SQL.
        /// </summary>
        ///
<param name="sql">The SQL.</param>
        /// <returns>IT2.DataAccess.SqlStringBuilder.</returns>
        public SqlStringBuilder Sql(string sql)
        {
            _builder.Append(string.Format(" {0} ", sql));
            return this;
        }
    }

    public class ParameterBuilder
    {
        private readonly SqlStringBuilder _builder;

        ///
<summary>
        /// Initializes a new instance of the <see cref="ParameterBuilder" /> class.
        /// </summary>
        ///
<param name="builder">The builder.</param>
        public ParameterBuilder(SqlStringBuilder builder)
        {
            _builder = builder;
        }

        ///
<summary>
        /// Parameters the specified key.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        ///
<param name="key">The key.</param>
        ///
<param name="value">The value.</param>
        /// <returns>ParameterBuilder.</returns>
        public ParameterBuilder SetParameter<T>(string key, T value)
        {
            _builder.Parameters.Add(key, value);
            return this;
        }

        ///
<summary>
        /// Parameters the specified key.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        ///
<param name="key">The key.</param>
        ///
<param name="value">The value.</param>
        /// <returns>ParameterBuilder.</returns>
        public ParameterBuilder SetParameterList<T>(string key, T value) where T : IEnumerable
        {
            _builder.ParameterList.Add(key, value);
            return this;
        }

        ///
<summary>
        /// Ifs the specified evaluation.
        /// </summary>
        ///
<param name="evaluation">if set to <c>true</c> [evaluation].</param>
        ///
<param name="sql">The SQL.</param>
        /// <returns>ParameterBuilder.</returns>
        public ParameterBuilder If(bool evaluation, string sql)
        {
            return _builder.If(evaluation, sql);
        }

        ///
<summary>
        /// Conditions the specified evaluation.
        /// </summary>
        ///
<param name="evaluation">if set to <c>true</c> [evaluation].</param>
        ///
<param name="sql">The SQL.</param>
        ///
<param name="parameters">The parameters.</param>
        /// <returns>ParameterBuilder.</returns>
        public ParameterBuilder If(bool evaluation, string sql, Action<ParameterBuilder> parameters)
        {
            return _builder.If(evaluation, sql, parameters);
        }

        ///
<summary>
        /// SQLs the specified SQL.
        /// </summary>
        ///
<param name="sql">The SQL.</param>
        /// <returns>SqlStringBuilder.</returns>
        public SqlStringBuilder Sql(string sql)
        {
            _builder.Sql(sql);
            return _builder;
        }

        ///
<summary>
        /// To the query.
        /// </summary>
        /// <returns>ISQLQuery.</returns>
        public IQuery ToQuery()
        {
            return _builder.ToQuery();
        }

        ///
<summary>
        /// To the query.
        /// </summary>
        /// <returns>ISQLQuery.</returns>
        public ISQLQuery ToSqlQuery()
        {
            return _builder.ToSqlQuery();
        }
    }
}