• [原]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的功能了。

      

      

      

  • 相关阅读:
    Treap 树堆 容易实现的平衡树
    (转)Maven实战(二)构建简单Maven项目
    (转)Maven实战(一)安装与配置
    根据请求头跳转判断Android&iOS
    (转)苹果消息推送服务器 php 证书生成
    (转)How to renew your Apple Push Notification Push SSL Certificate
    (转)How to build an Apple Push Notification provider server (tutorial)
    (转)pem, cer, p12 and the pains of iOS Push Notifications encryption
    (转)Apple Push Notification Services in iOS 6 Tutorial: Part 2/2
    (转)Apple Push Notification Services in iOS 6 Tutorial: Part 1/2
  • 原文地址:https://www.cnblogs.com/think8848/p/2180539.html
Copyright © 2020-2023  润新知