[原]SubSonic在同一个表连内实现接查询(JOIN)

时间：2021-04-04 15:08:07

转载请注明作者(think8848)和出处(http://think8848.cnblogs.com)

使用SqlCommand的感觉有时侯很爽，就跟那啥一样，对于数据的控制酣畅淋漓，但在这程中总是很担心一不小心打个颤，出现严重后果。之前在选择ORM时，选择了SubSonic，不觉已用了n年了，总的感觉来说还是非常不错的，但是SubSonic一直有一个硬伤：不能对同一个表进行JOIN连接。这个需求虽说不是天天有，但一个月总有那么几天需要去面对，搞的那几天人心情都不爽，当初选SubSonic是我力主的，解决不了问题，我难免得挨几下白眼。今天点低，又遇到了，需求很简单：一个Users中有ID,Name,SupervisorID三个列，SupervisorID为这个User的主管，很明显，这个查询很简单：

SELECT [Users].[ID],[Users].[Name],[Supervisors].[Name] AS [SupervisorName] FROM Users LEFT JOIN [Users] AS [Supervisors] ON [Users].[SupervisorID] = [Supervisors].[ID]

然而在SubSonic里不太简单了，最自然的写法为：

    var query = new Select(/*Columns*/).From<User>()
        .LeftOuterJoin<User>("SupervisorID","ID");

SubSonic生成的SQL为：

SELECT [Users].[ID], [Users].[Name], [Users].[SupervisorID] FROM  INNER JOIN [Users] ON [Users].[SupervisorID] = [Users].[ID]

两个很离奇的结果，一个是在FROM后面居然没有表名，另一个是使用LeftOuterJoin方法，生成的居然是INNER JOIN?

于是使用LeftOuterJoin的另一个重载形式：

var provider = ProviderFactory.GetProvider();
var tbUsers = provider.FindOrCreateTable<User>();
var tbSupervisors = provider.FindOrCreateTable<User>();
var colSupervisorID = tbUsers.GetColumn("SupervisorID");
var colID = tbSupervisors.GetColumn("ID");

var query = new Select(/*Columns*/).From(tbUsers).LeftOuterJoin(colSupervisorID,colID);

这时SubSonic生成的SQL为：

SELECT [Users].[ID], [Users].[Name], [Users].[SupervisorID] FROM  LEFT OUTER JOIN [Users] ON [Users].[SupervisorID] = [Users].[ID]

这次是LEFT OUTER JOIN了，但是FROM后面的表名还是没有，于是先查查在生成FROM时到底发生了什么　　

SubSonic.SqlGeneration.ANSISqlGenerator.cs

		public virtual string GenerateFromList()
		{
			StringBuilder sb = new StringBuilder();
			sb.AppendLine();
			sb.Append(this.sqlFragment.FROM);

			bool isFirst = true;
			foreach (ITable tbl in query.FromTables)
			{
				// EK: The line below is intentional. See: http://weblogs.asp.net/fbouma/archive/2009/06/25/linq-beware-of-the-access-to-modified-closure-demon.aspx
				ITable table = tbl;

				//Can't pop a table into the FROM list if it's also in a JOIN
				if (!query.Joins.Any(x => x.FromColumn.Table.Name.Equals(table.Name, StringComparison.InvariantCultureIgnoreCase)))
				{
					if (!isFirst)
						sb.Append(", ");
					sb.Append(tbl.QualifiedName);
					isFirst = false;
				}
			}
			return sb.ToString();
		} 

原来它做了验证，如果FROM的表存在于将要JOIN的表中，则成生一个空字符串...　　

这样看来，SubSonic还真是没有提供这个功能了，现在的问题是，在SubSonic中添加这个功能的代价有多大呢，如果能轻量级(我喜欢轻量级)的解决这个问题还是值的动下手的。

先看看JOIN到底是怎么生成的：

SubSonic.SqlGeneration.ANSISqlGenerator.cs

		public virtual string GenerateJoins()
		{
			StringBuilder sb = new StringBuilder();

			if (query.Joins.Count > 0)
			{
				sb.AppendLine();
				//build up the joins
				foreach (Join j in query.Joins)
				{
					string joinType = Join.GetJoinTypeValue(this, j.Type);
					string equality = " = ";
					if (j.Type == Join.JoinType.NotEqual)
						equality = " <> ";

					sb.Append(joinType);
					sb.Append(j.FromColumn.Table.QualifiedName);
					if (j.Type != Join.JoinType.Cross)
					{
						sb.Append(" ON ");
						sb.Append(j.ToColumn.QualifiedName);
						sb.Append(equality);
						sb.Append(j.FromColumn.QualifiedName);
					}
				}
			}
			return sb.ToString();
		}

其中需要关注的是sb.Append(j.FromColumn.Table.QualifiedName);这句，SubSonic使用ITable的QualifiedName来生成JOIN后面的表名的，QualifiedName属性定义如下：

SubSonic.Schema.DatabaseTable.cs

        public string QualifiedName
        {
            get { return Provider.QualifyTableName(this); }
        }

SubSonic在需要表名的地方均使用了QualifiedName，在这种情况下，去修改QualifyTableName的值本身也不明智，而且这个有点“可恶”的是，定义个只读属性也就算了，居然值还是个方法的返回值，这种方式即使用反射也没有办法修改其值了，因此也就打消了在该属性动手脚的想法。到底该怎么办呢，手动添加一个Join吧，Join的实际上是一个IColumn，而IColumn的背后还站着一个ITable，看起来归根到底是需要生成一个ITable，而且这个ITable的名字不能和数据库中的表名相同(不然又被FROM给挡住了)，最悲摧的是真实的表名还必须出现在SQL语句(有点废话)...

鉴于QualifiedName出现在多个地方，因此就使用QualifiedName作为别名吧，那么在sb.Append(j.FromColumn.Table.QualifiedName);这一行，QualifiedName肯定得换成诸如[XXX] AS QualifiedName之类的，只要动一行，就可以了。经过一番查看，发现DatabaseTable中的FriendlyName没有啥用，除了定义外没有发现任何地方有什么用，于是想出来一段代码：

        public static SqlQuery SameTableJoin(this SqlQuery query, IColumn fromColumn, string toTableQualifiedName, Join.JoinType type, string toColumnName = "ID")
        {
            var provider = fromColumn.Provider;

            var tmpTable = new DatabaseTable(toTableQualifiedName, provider);
            tmpTable.FriendlyName = fromColumn.Table.Name;
            var tmpCol = new DatabaseColumn(toColumnName, tmpTable);

            query.Joins.Add(new Join(tmpCol, fromColumn, type));

            if (!query.FromTables.Contains(tmpCol.Table))
            {
                query.FromTables.Add(tmpCol.Table);
            }

            return query;
        }

FriendlyName是指定了，但是SubSonic并不知道我们用了这个属性啊，没办法，只有重载GenerateJoins方法了，在它里面使用FriendlyName，要达到非侵入目的，定义一个SqlServerProvider的派生类吧;

    public class CleverSqlServerProvider : SqlServerProvider
    {
        public CleverSqlServerProvider(string connectionString, string providerName)
            : base(connectionString, providerName)
        { }

        public override ISqlGenerator GetSqlGenerator(SqlQuery query)
        {
            return new CleverSqlGenerator(query);
        }
    }

这个类其实还是没有做具体的SQL代码生成工作，还得定义一个SqlGenerator类:

    public class CleverSqlGenerator : Sql2005Generator
    {
        public CleverSqlGenerator(SqlQuery query)
            : base(query)
        {
            ClientName = "System.Data.SqlClient";
        }
        public override string GenerateJoins()
        {
            StringBuilder sb = new StringBuilder();

            if (base.Query.Joins.Count > 0)
            {
                sb.AppendLine();
                //build up the joins
                foreach (Join j in base.Query.Joins)
                {
                    string joinType = Join.GetJoinTypeValue(this, j.Type);
                    string equality = " = ";
                    if (j.Type == Join.JoinType.NotEqual)
                        equality = " <> ";

                    sb.Append(joinType);
                    sb.Append(string.IsNullOrEmpty(j.FromColumn.Table.FriendlyName) ? j.FromColumn.Table.QualifiedName : string.Format("[{0}] AS {1}", j.FromColumn.Table.FriendlyName, j.FromColumn.Table.QualifiedName));
                    if (j.Type != Join.JoinType.Cross)
                    {
                        sb.Append(" ON ");
                        sb.Append(j.ToColumn.QualifiedName);
                        sb.Append(equality);
                        sb.Append(j.FromColumn.QualifiedName);
                    }
                }
            }

            return sb.ToString();
        }
    }

使用sb.Append(string.IsNullOrEmpty(j.FromColumn.Table.FriendlyName) ? j.FromColumn.Table.QualifiedName : string.Format("[{0}] AS {1}", j.FromColumn.Table.FriendlyName, j.FromColumn.Table.QualifiedName));一行，将FriendlyName应用了进去，现在唯一的问题是：如何使用CleverSqlServerProvider了，new一个吗？no no no，这个想都不要想，这是我不能容忍的，那种使用配置文件？好像还真没有发现该怎么配，再看看ProviderFactory类，发现一个有用的方法：

        public static void Register(string providerName, Func<string, string, IDataProvider> factoryMethod)
        {
            if (_factories.ContainsKey(providerName))
            {
                _factories.Remove(providerName);
            }

            _factories.Add(providerName, factoryMethod);
        }

这下有救了吧:)

使用SameTableJoin方法试试，看能不能生成想要的结果;

            var provider = ProviderFactory.GetProvider();
            var tbUser = provider.FindOrCreateTable<User>();
            var colSupervisorID = tbUser.GetColumn("SupervisorID");

            var query = new Select(/*Columns*/).From<User>()
                .SameTableJoin(colSupervisorID, "Supervisors", Join.JoinType.LeftOuter);

看看生成的SQL:

SELECT [Users].[ID], [Users].[Name], [Supervisors].[Name] AS [SupervisorName] FROM [Users] LEFT OUTER JOIN [Users] AS [Supervisors] ON [Users].[SupervisorID] = [Supervisors].[ID]

OK,终于达到效果了，没有修改SubSonic的源码，但是达到了预期的目的。

写在后面的一句话，个人感觉，千万不要因为SubSonic这点瑕疵看不起它，总的来说，几年用下来还是觉得非常爽的，而且你也看到了，有了问题也很容易自已动手修补，我已经攒了不少这种扩展方法增强SubSonic的功能了。

　　

　　

　　

标签：

相关文章