[Original] subsonic performs join queries in the same table connection)

Reprinted please indicate the author (think8848) and source (http://think8848.cnblogs.com)

The feeling of using sqlcommand is sometimes cool. Just like that, the control of data is full. However, in this process, I am always worried that I will not be careful and have serious consequences. I used subsonic for N years before I chose Orm. It seems to be quite good in general, but subsonic has always had a hard injury: join cannot be performed on the same table. Although this demand is not available every day, it may take a few days for a month to face it. It is difficult for those people to do so. When the primary subsonic is my primary master, the problem cannot be solved, I will inevitably get a few white eyes. Today, I encountered another problem. The requirement is very simple: A users has three columns: ID, name, and supervisorid, and supervisorid is the user's supervisor. Obviously, this query is very simple:

 
Select [users]. [ID], [users]. [name], [supervisors]. [name] as [supervisorname] from users left join [users] as [supervisors] on [users]. [supervisorid] = [supervisors]. [ID]
 

However, subsonic is not easy to use. The most natural method is:

 
VaR query = new select (/* columns */). From <user> (). leftouterjoin <user> ("supervisorid", "ID ");
 

The SQL statement generated by subsonic is:

Select [users]. [ID], [users]. [name], [users]. [supervisorid] from inner join [users] on [users]. [supervisorid] = [users]. [ID]
 

Two bizarre results: one is that there is no table name behind the from, and the other is that the leftouterjoin method is used to generate an inner join?

So another reload form of leftouterjoin is used:

 
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 );
 

The SQL statement generated by subsonic is:

 
Select [users]. [ID], [users]. [name], [users]. [supervisorid] From left Outer Join [users] on [users]. [supervisorid] = [users]. [ID]
 

This is left outer join, but the table name behind the from statement still does not exist, so first check what happened when the from statement is generated.

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.aspxITable table = TBL; // can't pop a table into the from list if it 'S also in a joinif (! 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 ();}
 

It has been verified. If the from table exists in the table to be joined, an empty string is generated...

In this case, the subsonic does not provide this function yet. The problem now is how costly it is to add this function to subsonic. If it can be lightweight (I like lightweight) to solve this problem, it is still a matter of starting.

First, let's see how join is generated:

Subsonic. sqlgeneration. ansisqlgenerator. CS

 
Public Virtual string generatejoins () {stringbuilder sb = new stringbuilder (); If (query. joins. count> 0) {sb. appendline (); // build up the joinsforeach (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 (integrity); sb. append (J. fromcolumn. qualifiedname) ;}} return sb. tostring ();}
 

Here, we need to pay attention to sb. append (J. fromcolumn. Table. qualifiedname); in this sentence, subsonic uses the qualifiedname of itable to generate the table name after join. The qualifiedname attribute is defined as follows:

Subsonic. schema. databasetable. CS

 
Public String qualifiedname {get {return provider. qualifytablename (this );}}
 

Subsonic uses qualifiedname wherever the table name is needed. In this case, it is wise to modify the value of qualifytablename. What's a bit "hateful" is that, if you define a read-only attribute, the value is still the return value of a method. In this way, reflection is used and there is no way to modify the value. Therefore, the idea of setting a read-only attribute is dispelled. What should I do? manually add a join. The Join Operation is actually an icolumn, And the icolumn is still standing behind an itable. It seems that an itable needs to be generated in the final analysis, and the itable name cannot be the same as the table name in the database (otherwise it will be blocked by the from ), the most unfortunate thing is that the real table name must also appear in SQL statements (a bit nonsense )...

Since qualifiedname appears in multiple places, use qualifiedname as an alias. append (J. fromcolumn. table. qualifiedname); for this line, qualifiedname must be changed to [XXX] As qualifiedname. You only need to move one line. After some viewing, I found that the friendlyname in databasetable is useless, and I did not find any use in any place except the definition.Code:

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, fromC Olumn, type); If (! Query. fromtables. Contains (tmpcol. Table) {query. fromtables. Add (tmpcol. Table);} return query ;}
 

The friendlyname is specified, but subsonic does not know that we use this attribute. There is no way. We only need to reload the generatejoins method and use the friendlyname in it for non-intrusive purposes, define a derived class of sqlserverprovider;

Public class cleversqlserverprovider: sqlserverprovider {public cleversqlserverprovider (string connectionstring, string providername): Base (connectionstring, providername) {} public override isqlgenerator getsqlgenerator (sqlquery query) {return New cleversqlgenerator (query );}}
 

This class does not actually generate specific SQL code, and you have to define a sqlgenerator class:

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); Str Ing 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 (integrity); sb. append (J. fromcolumn. qualifiedname) ;}} return sb. tostring ();}}

Use 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); a row that applies friendlyname. The only question now is: how to use cleversqlserverprovider, new? No no. I don't want to think about it. I cannot tolerate it. What kind of configuration file is used? It seems that I haven't found out how to configure it. Let's look at the providerfactory class and find a useful method:

 
Public static void register (string providername, func <string, String, idataprovider> factorymethod) {If (_ factories. containskey (providername) {_ factories. remove (providername);} _ factories. add (providername, factorymethod );}
 

This is saved :)

Try the sametablejoin method to see if the desired result can be generated;

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 );
 

Check the generated SQL:

 
Select [users]. [ID], [users]. [name], [supervisors]. [name] as [supervisorname] from [users] left Outer Join [users] as [supervisors] on [users]. [supervisorid] = [supervisors]. [ID]
 

OK, finally achieved the effect, did not modify the subsonic source code, but achieved the expected goal.

In my personal sense, do not look down on subsonic because of its flaws. In general, it is quite cool to use it for a few years, and you have also seen it, if you have any problems, you can easily fix them by yourself. I have collected many extension methods to enhance subsonic functions.