tabs ↹ over ␣ ␣ ␣ spaces

by Jiří {x2} Činčura

Shorter Firebird queries from Entity Framework

29 Mar 2012 2 mins Entity Framework, Entity SQL, Firebird, LINQ

Yesterday I released ADO.NET provider for Firebird version 2.7.5 and there’s one feature I’d like to blog about. As you might know Firebird has a pretty old limitation on “size” of query. It’s both the string itself, but also the column datatypes and parameters used. No need to dive to details, because I’ll only talk about the first item.

When you’re using Entity Framework (or any other ORM) the query is generated by machine. And the machine (or maybe better to say algorithm) needs to be sure, that in every case the query is correct. That’s the reason why you see a lot braces even if it’s not needed in your case. A lot of table aliases, escaping, quoting and so on. It’s not a simple task. But all these extra characters are making the query bigger. Consider that the the generated query isn’t always the shortest (you could write it shorter because either you have some out-of-band knowledge about you database or simply because there’s room for improvement) and all this “character noise” and you might hit the query length limit.

In the above mentioned version I made some improvements to table aliases. Instead of using full names from Entity Framework, I’m trying to use one character names (or +1, +2, +3, … if I run out of 1, 2, 3, … character names). So instead of (example)

select Extent1.Foo, Extent1.Bar, Extent2.Baz from SomeTable as Extent1 join AnotherTable as Extent2 ...

you’ll get

select A.Foo, A.Bar, B.Baz from SomeTable as A join AnotherTable as B ...

Nothing super-cool, but because somebody suggested this in tracker, it’s there. On the other hand to be honest I’ve never hit the query length limit myself. Maybe I’m lucky, maybe I’m writing too simple queries. But still, shorter query means also less data to be transfered. Hope to help to use Entity Framework and Firebird for big stuff. 😃

Profile Picture Jiří Činčura is .NET, C# and Firebird expert. He focuses on data and business layers, language constructs, parallelism, databases and performance. For almost two decades he contributes to open-source, i.e. FirebirdClient. He works as a senior software engineer for Microsoft. Frequent speaker and blogger at