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


    转载请注明作者(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的功能了。

      

      

      

  • 相关阅读:
    开课博客
    今天干了啥
    今天干了啥
    今天干了啥
    今天干了啥
    今天干了啥
    四则运算
    冲刺二(2)
    用户体验评价
    冲刺二(1)
  • 原文地址:https://www.cnblogs.com/think8848/p/2180539.html
Copyright © 2020-2023  润新知