When working with stateless sessions in NHibernate we need some way to see generated SQL but we cannot use simple tricks as custom NHibernate interceptor to log SQL queries. As I’m using mostly NHibernate LINQ these days I was interested in how to get SQL out from LINQ query without actually executing it. Here’s my solution.
ToSql() extension method
I wrote simple ToSql() extension method for IQueryable generated by NHibernate LINQ. It also writes out parameters.
public static class NHibernateExtensions
{
public static string ToSql(this IQueryable query)
{
var provider = query.Provider as DefaultQueryProvider;
if(provider == null)
{
return null;
}
var sessionImpl = provider.Session;
var factory = sessionImpl.Factory;
var linqExpression = new NhLinqExpression(query.Expression, factory);
var translatorFactory = new ASTQueryTranslatorFactory();
var translator = translatorFactory.CreateQueryTranslators(linqExpression, null, false, sessionImpl.EnabledFilters, factory).First();
var parameters = linqExpression.ParameterValuesByName.ToDictionary(x => x.Key, x => x.Value.Item1);
var result = translator.SQLString;
foreach (var parameter in parameters)
{
result += "\r\n" + parameter.Key + ": " + parameter.Value;
}
return result.Trim();
}
}
ToSql() extension method uses only classes needed to translate LINQ expression tree to SQL.
Using ToSql() extension method
The following piece of code shows how to use ToSql() extension method. To find out more about my IDataMapper interface that mimics DbContext of Entity Framework Core, please read my blog post NHibernate on ASP.NET Core.
var projectQuery = _dataMapper.AsNoTracking()
.Projects
.Fetch(c => c.Customer)
.Fetch(c => c.Mediator)
.Where(p => p.Id == id);
var projectSql = projectQuery.ToSql();
var projects = await projectQuery.FirstOrDefaultAsync();
When projectSql variable is evaluated the query is not executed. It is only translated to SQL. Query is executed when projects variable is evaluated as then data is actually asked from projectQuery.
Here’s the result of ToSql() extension method.
select
project0_.Id as id1_2_0_,
customer1_.Id as id1_1_1_,
customer2_.Id as id1_1_2_,
project0_.Title as title2_2_0_,
project0_.ProjectKey as projectkey3_2_0_,
project0_.IsActive as isactive4_2_0_,
project0_.CustomerId as customerid5_2_0_,
project0_.MediatorId as mediatorid6_2_0_,
project0_.Deadline as deadline7_2_0_,
project0_.Description as description8_2_0_,
customer1_.Name as name2_1_1_,
customer2_.Name as name2_1_2_
from
Projects project0_
left outer join Customers customer1_ on
project0_.CustomerId=customer1_.Id
left outer join Customers customer2_ on
project0_.MediatorId=customer2_.Id
where
project0_.Id=?
p1: d9571e6b-fa73-4553-9c90-57aa7c050298
NB! ToSql() extension method doesn’t give formatted SQL. It gives it like it is for executing. You can use Poor Man’s T-SQL Formatter service to format queries returned by ToSql() extension method.
Wrapping up
Translating NHibernate LINQ expression tree to SQL without executing query against database server is trick we can use to investigate generated SQL when writing complex queries. I’m using ToSql() extension method also when optimizing slow queries to find out what was actually generated by NHibernate. For more serious work I recomment using advanced tools to monitor NHibernate.
View Comments (145)
It's not the final SQL and parameters are not really easy to check. You can get the actual DBCommand using the following code: https://stackoverflow.com/a/55517893/9667775
Your article helped me a lot, is there any more related content? Thanks!
Thanks for sharing. I read many of your blog posts, cool, your blog is very good.
먹튀검증 완료! 안전 토토사이트 추천
s34ryj
xo5xzr
o1ka48
토토사이트 추천
Your article helped me a lot, is there any more related content? Thanks!
Your article helped me a lot, is there any more related content? Thanks!
Your point of view caught my eye and was very interesting. Thanks. I have a question for you.
Can you be more specific about the content of your article? After reading it, I still have some doubts. Hope you can help me.
j3f3pg
Thank you for your sharing. I am worried that I lack creative ideas. It is your article that makes me full of hope. Thank you. But, I have a question, can you help me?
an15a8
8v7diw
Your writing has a way of making even the most complex topics accessible and engaging. I'm constantly impressed by your ability to distill complicated concepts into easy-to-understand language.
Can you be more specific about the content of your article? After reading it, I still have some doubts. Hope you can help me.
ptwenr
8lpzex
8vjsw3
I don't think the title of your article matches the content lol. Just kidding, mainly because I had some doubts after reading the article. https://accounts.binance.com/el/register-person?ref=IQY5TET4
I don't think the title of your article matches the content lol. Just kidding, mainly because I had some doubts after reading the article.
Can you be more specific about the content of your article? After reading it, I still have some doubts. Hope you can help me. https://accounts.binance.com/sl/register-person?ref=OMM3XK51
Can you be more specific about the content of your article? After reading it, I still have some doubts. Hope you can help me. https://accounts.binance.info/en/register?ref=JHQQKNKN
I don't think the title of your article matches the content lol. Just kidding, mainly because I had some doubts after reading the article.
xhi1bx
I don't think the title of your article matches the content lol. Just kidding, mainly because I had some doubts after reading the article.
Thank you very much for sharing, I learned a lot from your article. Very cool. Thanks.
Thank you very much for sharing, I learned a lot from your article. Very cool. Thanks.
Thanks for sharing. I read many of your blog posts, cool, your blog is very good.
I don't think the title of your article matches the content lol. Just kidding, mainly because I had some doubts after reading the article.
g942xy
Their strain selector could be an evolved version of ancient Ayurvedic typologies.
15f8vx
탑플레이어포커 머니상: https://www.pokertopplayer.com/
토토사이트 토토천국
Your article helped me a lot, is there any more related content? Thanks!
Can you be more specific about the content of your article? After reading it, I still have some doubts. Hope you can help me.
5ld175
I don't think the title of your article matches the content lol. Just kidding, mainly because I had some doubts after reading the article.
테더환전: https://www.tetherexchange.net/
I don't think the title of your article matches the content lol. Just kidding, mainly because I had some doubts after reading the article.
Your article helped me a lot, is there any more related content? Thanks!
Thanks for sharing. I read many of your blog posts, cool, your blog is very good. https://accounts.binance.com/en-ZA/register-person?ref=JHQQKNKN
wifi speed test windows 10
aaid5d
Hey There. I found your blog using msn. This is an extremely well written article. I’ll make sure to bookmark it and come back to read more of your useful information. Thanks for the post. I will certainly comeback.
Thanks for sharing. I read many of your blog posts, cool, your blog is very good.
Can you be more specific about the content of your article? After reading it, I still have some doubts. Hope you can help me.
Thank you, your article surprised me, there is such an excellent point of view. Thank you for sharing, I learned a lot.
xsykhj
Thank you for your sharing. I am worried that I lack creative ideas. It is your article that makes me full of hope. Thank you. But, I have a question, can you help me?
Thank you, your article surprised me, there is such an excellent point of view. Thank you for sharing, I learned a lot.
Thank you, your article surprised me, there is such an excellent point of view. Thank you for sharing, I learned a lot.
b9ybul
Can you be more specific about the content of your article? After reading it, I still have some doubts. Hope you can help me.
m0jh50
Can you be more specific about the content of your article? After reading it, I still have some doubts. Hope you can help me.
rytnzj
Can you be more specific about the content of your article? After reading it, I still have some doubts. Hope you can help me.
Can you be more specific about the content of your article? After reading it, I still have some doubts. Hope you can help me.
Hello there! This post couldn't be written any better! Reading through this post reminds me of my previous room mate! He always kept talking about this. I will forward this page to him. Pretty sure he will have a good read. Thanks for sharing!
Thanks for sharing. I read many of your blog posts, cool, your blog is very good.
Your point of view caught my eye and was very interesting. Thanks. I have a question for you.
6on606
I do not even know how I ended up here but I thought this post was great I do not know who you are but certainly youre going to a famous blogger if you are not already Cheers
Your point of view caught my eye and was very interesting. Thanks. I have a question for you.
2viuot
Thanks for sharing. I read many of your blog posts, cool, your blog is very good.
Thanks for sharing. I read many of your blog posts, cool, your blog is very good.
Thank you for your sharing. I am worried that I lack creative ideas. It is your article that makes me full of hope. Thank you. But, I have a question, can you help me?
rxxugv
0sr0tu
I don't think the title of your article matches the content lol. Just kidding, mainly because I had some doubts after reading the article.
I don't think the title of your article matches the content lol. Just kidding, mainly because I had some doubts after reading the article. https://www.binance.info/cs/register?ref=S5H7X3LP
Thank you for your sharing. I am worried that I lack creative ideas. It is your article that makes me full of hope. Thank you. But, I have a question, can you help me?
certainly like your website but you need to take a look at the spelling on quite a few of your posts Many of them are rife with spelling problems and I find it very troublesome to inform the reality nevertheless I will definitely come back again
Somebody essentially lend a hand to make significantly posts I might state That is the very first time I frequented your web page and up to now I surprised with the research you made to create this particular put up amazing Excellent job
Your point of view caught my eye and was very interesting. Thanks. I have a question for you. https://www.binance.info/fr/register?ref=GJY4VW8W
Can you be more specific about the content of your article? After reading it, I still have some doubts. Hope you can help me.
Your writing is like a breath of fresh air in the often stale world of online content. Your unique perspective and engaging style set you apart from the crowd. Thank you for sharing your talents with us.
I have been surfing online more than 3 hours today yet I never found any interesting article like yours It is pretty worth enough for me In my opinion if all web owners and bloggers made good content as you did the web will be much more useful than ever before
mfxc0s
Can you be more specific about the content of your article? After reading it, I still have some doubts. Hope you can help me.
I don't think the title of your article matches the content lol. Just kidding, mainly because I had some doubts after reading the article. https://www.binance.info/cs/register?ref=IJFGOAID
This is such a valuable article! 👏 I really like how you’ve managed to explain the topic in a clear and practical way—it feels authentic and easy to relate to. Reading it gave me some new perspectives that I can actually apply. I’m especially interested in content like this because at meinestadtkleinanzeigen.de we’re running a classifieds and directory platform in Germany that connects people with services, businesses, and opportunities across many categories. Insights like yours remind me how powerful it is when knowledge and connections come together. Thanks for sharing—looking forward to more of your work! 🚀
https://www.xn--9i1bo3h90bi5k6sg1yc3ttuwds2ig4c.xyz/
Really well-written article! 👏 I enjoyed the way you broke down the topic—it feels very genuine and helpful, not just theory. The practical tips make it easy for readers like me to connect and actually take something useful away.At meinestadtkleinanzeigen.de , we’re building a directory and classifieds platform in Germany where people can discover businesses, services, and opportunities across many categories. That’s why I especially value content like yours, because it shows how sharing knowledge online can really create connections.Keep up the great work—I’ll definitely be following along for more insights! 🚀
Really well-written article! 👏 I enjoyed the way you broke down the topic—it feels very genuine and helpful, not just theory. The practical tips make it easy for readers like me to connect and actually take something useful away.At meinestadtkleinanzeigen.de , we’re building a directory and classifieds platform in Germany where people can discover businesses, services, and opportunities across many categories. That’s why I especially value content like yours, because it shows how sharing knowledge online can really create connections.Keep up the great work—I’ll definitely be following along for more insights! 🚀
Really well-written article! 👏 I enjoyed the way you broke down the topic—it feels very genuine and helpful, not just theory. The practical tips make it easy for readers like me to connect and actually take something useful away.At meinestadtkleinanzeigen.de , we’re building a directory and classifieds platform in Germany where people can discover businesses, services, and opportunities across many categories. That’s why I especially value content like yours, because it shows how sharing knowledge online can really create connections.Keep up the great work—I’ll definitely be following along for more insights! 🚀
Fantastic read! 👏 I really appreciate how clearly you explained the topic—your writing not only shows expertise but also makes the subject approachable for a wide audience. It’s rare to come across content that feels both insightful and practical at the same time. At explodingbrands.de we run a growing directory site in Germany that features businesses from many different categories. That’s why I truly value articles like yours, because they highlight how knowledge and visibility can create stronger connections between people, services, and opportunities.Keep up the great work—I’ll definitely be checking back for more of your insights! 🚀
Really well-written article! 👏 I enjoyed the way you broke down the topic—it feels very genuine and helpful, not just theory. The practical tips make it easy for readers like me to connect and actually take something useful away.At meinestadtkleinanzeigen.de , we’re building a directory and classifieds platform in Germany where people can discover businesses, services, and opportunities across many categories. That’s why I especially value content like yours, because it shows how sharing knowledge online can really create connections.Keep up the great work—I’ll definitely be following along for more insights! 🚀
Really well-written article! 👏 I enjoyed the way you broke down the topic—it feels very genuine and helpful, not just theory. The practical tips make it easy for readers like me to connect and actually take something useful away.At meinestadtkleinanzeigen.de , we’re building a directory and classifieds platform in Germany where people can discover businesses, services, and opportunities across many categories. That’s why I especially value content like yours, because it shows how sharing knowledge online can really create connections.Keep up the great work—I’ll definitely be following along for more insights! 🚀
Fantastic read! 👏 I really appreciate how clearly you explained the topic—your writing not only shows expertise but also makes the subject approachable for a wide audience. It’s rare to come across content that feels both insightful and practical at the same time. At explodingbrands.de we run a growing directory site in Germany that features businesses from many different categories. That’s why I truly value articles like yours, because they highlight how knowledge and visibility can create stronger connections between people, services, and opportunities.Keep up the great work—I’ll definitely be checking back for more of your insights! 🚀
Fantastic read! 👏 I really appreciate how clearly you explained the topic—your writing not only shows expertise but also makes the subject approachable for a wide audience. It’s rare to come across content that feels both insightful and practical at the same time. At explodingbrands.de we run a growing directory site in Germany that features businesses from many different categories. That’s why I truly value articles like yours, because they highlight how knowledge and visibility can create stronger connections between people, services, and opportunities.Keep up the great work—I’ll definitely be checking back for more of your insights! 🚀
This is such a valuable article! 👏 I really like how you’ve managed to explain the topic in a clear and practical way—it feels authentic and easy to relate to. Reading it gave me some new perspectives that I can actually apply. I’m especially interested in content like this because at meinestadtkleinanzeigen.de we’re running a classifieds and directory platform in Germany that connects people with services, businesses, and opportunities across many categories. Insights like yours remind me how powerful it is when knowledge and connections come together. Thanks for sharing—looking forward to more of your work! 🚀
💡 Excellent work on this ultimate guide! every paragraph is packed with value. It’s obvious a lot of research and love went into this piece. If your readers want to put these 7 steps into action immediately, we’d be honoured to help: 👉 https://meinestadtkleinanzeigen.de/ – Germany’s fastest-growing kleinanzeigen & directory hub. • 100 % free listings • Auto-sync to 50+ local citation partners • Instant push to Google Maps data layer Drop your company profile today and watch the local calls start rolling in. Keep inspiring, and thanks again for raising the bar for German SEO content!
Hi my family member I want to say that this post is awesome nice written and come with approximately all significant infos I would like to peer extra posts like this
Your article helped me a lot, is there any more related content? Thanks!