mysql 构造 linq语句_[转]查看LINQ生成SQL语句的几种方法

记录LINQ生成的SQL语句是常用的调试方式,而且能根据需要来优化LINQ生成的SQL语句,更能了深入的了解LINQ.

DataContext的Log属性来将LINQ to SQL生成的SQL语句格式化.

一.控制台程序(Console)dataContext.Log = Console.Out;二.利用GetCommand方法

dataContext.GetCommand(query).CommandText;

三.使用LINQPad (官方网站)

LINQPad支持C# 3.0 和 Framework 3.5的全部功能：LINQ to SQL

LINQ to Objects

LINQ to XML

四.LINQ to SQL Debug Visualizer

ScottGu的LINQ to SQL Debug Visualizer可以在Debug过程中查看SQL语句.

安装方法

1. 关闭 VS2008。

2. 将压缩包中的 SqlServerQueryVisualizer.dll 拷贝到 \Program Files\Microsoft Visual Studio 9.0\Common7\Packages\Debugger\Visualizers。

3. 重启 VS2008 即可。

五.DebuggerWriter工具类

由于Console.Out方法在ASP.NET程序不起作用.

Kris Vandermotten已经创建好了一个这个工具类, 你只要使用这样的语法:

MyDataContext db = new MyDataContext();db.Log = new DebuggerWriter();

asp.net可以选择将Log信息直接发送到Debug的输出窗口.

源码:

usingSystem;

usingSystem.Diagnostics;

usingSystem.Globalization;

usingSystem.IO;

usingSystem.Text;

namespaceVandermotten.Diagnostics

{

/**///Implements afor writing information to the debugger log.

//////

publicclassDebuggerWriter : TextWriter

{

privateboolisOpen;

privatestaticUnicodeEncoding encoding;

privatereadonlyintlevel;

privatereadonlystringcategory;

/**///Initializes a new instance of theclass.

///

publicDebuggerWriter()

:this(0, Debugger.DefaultCategory)

{

}

/**///Initializes a new instance of theclass with the specified level and category.

//////A description of the importance of the messages.///The category of the messages.

publicDebuggerWriter(intlevel,stringcategory)

:this(level, category, CultureInfo.CurrentCulture)

{

}

/**///Initializes a new instance of theclass with the specified level, category and format provider.

//////A description of the importance of the messages.///The category of the messages.///Anobject that controls formatting.

publicDebuggerWriter(intlevel,stringcategory, IFormatProvider formatProvider)

:base(formatProvider)

{

this.level=level;

this.category=category;

this.isOpen=true;

}

protectedoverridevoidDispose(booldisposing)

{

isOpen=false;

base.Dispose(disposing);

}

publicoverridevoidWrite(charvalue)

{

if(!isOpen)

{

thrownewObjectDisposedException(null);

} Debugger.Log(level, category, value.ToString());

}

publicoverridevoidWrite(stringvalue)

{

if(!isOpen)

{

thrownewObjectDisposedException(null);

}if(value!=null)

{

Debugger.Log(level, category, value);

} }

publicoverridevoidWrite(char[] buffer,intindex,intcount)

{

if(!isOpen)

{

thrownewObjectDisposedException(null);

}if(buffer==null||index<0||count<0||buffer.Length-index

{

base.Write(buffer, index, count);//delegate throw exception to base class} Debugger.Log(level, category,newstring(buffer, index, count));

}

publicoverrideEncoding Encoding

{

get

{

if(encoding==null)

{

encoding=newUnicodeEncoding(false,false);

}returnencoding;

} }

publicintLevel

{

get{returnlevel; } }

publicstringCategory

{

get{returncategory; } } }}

六.将LINQ to SQL生成的SQL语句写入日志文件

DataContext.Log是System.IO.TextWriter类型,所以你可以用以下的方法来做.

StreamWriter sw = new StreamWriter(

Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "log.txt"));dBLinqDataContext.Log = sw;var query = dataContext.Customers.Single(c=>c.CustomerID.Contains("s"))

.Skip(0).Take(10).ToList();sw.Flush();sw.Close();

但以上方法有个缺点,就是需要在每个实现的方法中都写这么多代码.使用起来太不方便.参照dataContext.Log = Console.Out的表现形式

由是有了FileLog类.(当然,FileLog类除了此功能还有一些基本的记录日志的方法)

使用时直接dataContext.Log = Yaosansi.IO.FileLog.Out;即可. 默认会在桌面上生成一个名叫UnNameFile.txt的文件.

当然如果你不想使用默认的文件名和路径也可以使用dataContext.Log =new Yaosansi.IO.FileLog("FileName")的方式.

下面是FileLog类的源码:

//原文:http://www.yaosansi.com/post/1380.htmlusingSystem;

usingSystem.Collections.Generic;

usingSystem.Text;

usingSystem.IO;

namespaceYaosansi.IO

{

/**///文件操作

///

publicclassFileLog : TextWriter

{

构造函数#region构造函数

     

/**/////////文件名[文件路径使用默认路径]

publicFileLog(stringfileName)

            :this(fileName,string.Empty,false)

{

        }

/**/////////文件名///文件路径///是否删除已经存在的文件

publicFileLog(stringfileName,stringfilePath,booldeleteExistingFile):this(fileName,filePath,long.MaxValue)

{

if(deleteExistingFile)

{

                DeleteFile();

            }        }

/**/////////文件名///文件路径///文件大小［单位：bytes］超出此大小将自动删除文件(使用longong.MaxValue例外)///

publicFileLog(stringfileName,stringfilePath,longfileSize)

{

if(!string.IsNullOrEmpty(fileName))

{

                FileName=fileName;

            }else

{

                FileName="UnNameFile.txt";

            }if(!string.IsNullOrEmpty(filePath))

{

                FilePath=filePath;

            }else

{

                FilePath=Environment.GetFolderPath(Environment.SpecialFolder.Desktop);

            }            FileSize=fileSize;

        }#endregion

重写TextWriter#region重写TextWriter

publicoverrideEncoding Encoding

{

get

{

returnnewUnicodeEncoding(false,false);

            }        }

publicoverridevoidWrite(charvalue)

{

            WriteFile(value.ToString());

        }

publicoverridevoidWrite(stringvalue)

{

if(value!=null)

{

                WriteFile(value);

            }        }

publicoverridevoidWrite(char[] buffer,intindex,intcount)

{

if(buffer==null||index<0||count<0||buffer.Length-index

{

base.Write(buffer, index, count);

            }            WriteFile(newstring(buffer, index, count));

        }

#endregion

属性#region属性

/**///文件名

///

publicstringFileName{set;get; }

/**///获取文件全名［包含路径］

///

publicstringFullFileName

{

get

{

returnFilePath+"\\"+FileName;

            }        }

/**///定义文件大小

///

publiclongFileSize{set;get; }

/**///文件路径;

///

publicstringFilePath

{

set;

get;

        }

/**///FileLog Factory

///

publicstaticFileLog Out

{

get

{

returnnewFileLog("");

            }        }

#endregion

方法#region方法

/**///删除文件

//////

publicboolDeleteFile()

{

boolsucceed=false;

try

{

if(File.Exists(FullFileName))

{

                    File.Delete(FullFileName);

                    succeed=true;

                }            }

catch{ }returnsucceed;

        }

/**///仅记录当前时间及分隔符

///

publicvoidWirteTime()

{

stringmessage="\r\n-------------------------------------------------------\r\n"+"时间:"+DateTime.Now.ToString()+"\r\n"+"-------------------------------------------------------";

            WriteFile(message);

        }

/**///写文件

//////

publicvoidWriteFile(stringMessage)

{

            StreamWriter sw=null;

try

{

//如果文件目录不存在,则创建if(!Directory.Exists(FilePath))

{

                    Directory.CreateDirectory(FilePath);

                }//超过一定大小自动删除文件if(FileSize!=long.MaxValue)

{

                    FileInfo finfo=newFileInfo(FullFileName);

if(finfo.Exists&&finfo.Length>FileSize)

{

                        finfo.Delete();

                    }                }if(!File.Exists(FullFileName))

{

                    sw=File.CreateText(FullFileName);

                }else

{

                    sw=File.AppendText(FullFileName);

                }                sw.WriteLine(Message);

                sw.Flush();

            }catch(Exception ee)

{

                Console.WriteLine("文件没有打开,详细信息如下:"+ee.ToString());

throw(newException("文件没有打开,详细信息如下:"+ee.ToString()));

            }finally

{

                sw.Close();

                sw.Dispose();

            }        }#endregion

    }}