Zlatko Michailov

Le Roi Est Mort, Vive Le Roi!

Now that SQL Server 2008 is out the door, I’m full throttle on my new job. I’m totally excited about the improvement I’m working on, and I can’t wait to start blogging about it (once it’s out of secrecy.)

Today I’m only changing the outlook of my blog to reflect my new emploi. Yes, I’ve decided to keep this blog. I actually like the ESQL acronym. It seems to fit anywhere. I realize my blog now looks just like Matt Neerincx’, but I really like the sunny picture – we don’t have much of that up here in Seattle.

I’ve added links to the SQL Server Protocols team blog and Matt Neerincx’ blog. Those are the people with whom I work now. Their knowledge on SQL Server connectivity by far exceeds mine. There is a lot to learn from digging into those blogs.

While I’m not blogging, I answer questions (a couple per day) at the SQL Server Data Access forum. If something’s bugging you, post it there, we have plenty of smart people watching the forum.

Moving On...

I have to admit publically that I’ve decided to take on new challenges unrelated to EDM and Entity Framework. Starting today I’ll be working on the lower levels of the SQL Server Connectivity stack.

I’d like to thank all of you who’ve been reading my Entity SQL blog and who’ve been using Entity Framework. I’m especially grateful to Julie Lerman who’s been tirelessly digging into Entity SQL and who’s provided priceless feedback.

I haven’t decided yet whether I should try to pass the ownership of this blog to someone else or whether I should continue using it for non-Entity SQL-related stuff. I prefer to keep it coherent for now. Either way, I’ll make sure the links to it remain intact.

New version of eSqlBlast - compatible with .NET Framework 3.5 SP1 Beta

Primer to EntityConnectionStringBuilder

Following the spirit of Primer to ConnectionStringBuilder from ADO.NET 2.0, let’s see how the pattern has evolved in Entity Framework.

Building an Entity Connection String from Scratch

What’s important to keep in mind before we dive into code samples, is that an entity connection string consists of two separate connection strings – one for the EntityClient itself and one for the underlying store provider. Therefore we’ll be using two connection string builders to build a full entity connection string.

We need to start with the part specific to EntityClient:

Then we dynamically construct a DbConnectionStringBuilder for the given provider (using DbProviderFactory), and we allow the user to enter values for its keywords:

What’s left is to set the provider-specific connection string on the EntityConnectionStringBuilder:

To get the connection string out of the connection string builder, you need to call the ToString() method:

The ReadKeyValue utility method that reads a line of text from the console. (Attached is the complete source code.)

Loading an Entity Connection String

EntityConnectionStringBuilder implements the concept of “named” connections. This has been a long time ask. Connection strings are built through VisualStudio wizard, or third-party tools, or by hand, and placed into the app.config/web.config file:

Then the application no longer needs to worry about “building” the connection string. It only needs to “load”. Like this:

Look up the attached source code for details.

Enjoy!

Why Should I Use Entity Framework?

If your app is small enough, i.e. you write your own database schema (for SQL Server) and you write the only app over that schema, you don’t really need Entity Framework. In that case, you may consider LINQ to SQL.

Now let’s think big. Let’s say you are an app/service vendor but your are not a database vendor, i.e. you re-sell the data. That means data may come from different store brands and eventually from different schemas. You don’t want to implement a different adapter for each schema/store. You create an EDM and mappings for each variation that become the contract between apps and data vendors.

From the other perspective – you are a data vendor – you write a gigantic database schema and you want different kinds of applications to consume it, while you still have the freedom to evolve it. A table/column renaming would cause some significant disturbance in the app space. Therefore, you shield your implementation behind an EDM, and don’t let apps make any assumption about your implementation.

eSqlBlast on Code Gallery

The Edm.Length() Canonical Function over SqlClient

I recently blogged about canonical functions in Entity Framework and SQL Server’s LEN() function. The purpose of those two posts was to lay out the foundation for this one, and to allow it to be brief and coherent. Please read those two posts before proceeding.

If you’ve been using the Edm.Length() canonical function from the Beta 2 and Beta 3 releases, you may have noticed that it’s behavior (and the T-SQL generated for it), when used against the SqlClient provider, have changed.

In Beta 2 SqlClient used to generate the following T-SQL for Edm.Length(x):

The purpose of generating a non-trivial expression was to compensate for LEN()’s behavior of ignoring trailing spaces by appending a non-blank character. Unfortunately, that approach had one big deficiency – it was returning a wrong result for string values at the 8KB boundary.

In Beta 3 we investigated the adoption of a different compensating formula, based on the DATALENGTH() function, but the formula was too complex, and it was returning wrong results over Unicode strings with surrogate characters. Thus we concluded that the provider should not try to alter its server’s behavior, and we mapped Edm.Length(x) to a plain LEN(x). You should be aware of that behavior, and design workarounds that suite your specific scenarios.

Going forward, we’ve requested a new string length function from the T-SQL team – one that accounts for all characters. When, whether, and in what form it will be implemented is still unknown, but we are convinced that’s the right approach to provide a canonical string length behavior in both Entity Framework and SQL Server.

SQL Server’s LEN() Function

There is a peculiarity around the LEN() function in SQL Server that not many people have realized or paid attention to. It’s the treatment of trailing spaces. The documentation says:

(The highlighting is mine.)

Indeed

returns: 3   3, not 3   12 as most people would expect.

I tried to deduce the rationale behind this behavior even though that doesn’t change the behavior at all: It must have appeared as a remedy for fixed-length character strings – char and nchar. Since those are always padded with blank spaces to fill up the entire slot, LEN() over values of those types would always return the size of the type, regardless of the value. That would be pretty useless. Unfortunately this behavior has been extended over to variable-length character strings where all characters, including trailing blanks, have a meaning.

Is SQL Server alone in this? No, there are other database servers with the same behavior. You can try the above query on your server, and see how it treats trailing spaces. What can we do?

a.    If strings like 'abc' and 'abc         '  are equivalent in your application, this behavior is perfect for you. You may eventually consider trimming the trailing spaces explicitly using RTIM() to be more explicit.

b.    If you do want to distinguish between those two strings, you should consider appending a special, non-blank, character at the end that you can strip off when you load strings from the database. You should also subtract the number of such special characters from what LEN() returns. If you have strings with corner-case lengths – 4,000 for nvarchar and 8,000 for varchar, you should consider casting values to nvarchar(max) or varchar(max) respectively before appending anything. Otherwise, the characters that exceed the type size limit will be silently lost.

Entity Framework and Object/Relational Mapping

Often, people categorize Entity Framework as an Object/Relational Mapper and try to compare it to other O/R Mappers. I dare say that’s an apples-to-oranges kind of comparison. While Entity Framework does have ORM capabilities, that is only a fraction of its functionality, and more importantly, those ORM capabilities are achieved through a fundamentally different approach compared to typical O/R Mappers.

What the database world offers is the notion of persistence plus a way to statically model flat entities and relationships between entities expressed as constraints on the entities themselves. What it lacks is the ability to model runtime type hierarchies and explicit, navigatable, relationships. The object world, on the other hand, has excellent capabilities to model type hierarchies with inheritance and containment and navigatable references. But it has no notion of persistence and static modeling. Still, the object world may be considered a better option, because most of the business logic is written in object code. What O/R Mappers do is providing a mapping bridge form object- to database space. That compensates for the lack of persistence, but static modeling is still very limited.

Entity Framework takes a completely different approach – it creates a development platform in the space between database and objects. That is the Conceptual space. It’s a virtual space, unconstrained from objects- and database legacy. That allows Entity Framework to support static modeling capabilities with type hierarchies and containment as well as explicit, navigatable, relationships. What’s similar to O/R Mappers, is the mapping. However, it’s not a single, object-to-store mapping bridge, but two separate bridges – object-to-conceptual and conceptual-to-store. (The object-to-conceptual mapping is implicit for version 1.)

Entity Framework shifts the center of gravity for enterprise applications to conceptual space. That enables full-featured business domain modeling with persistence. As a result, enterprise applications can be written against conceptual models, not only against legacy databases. That further allows applications to consume data from different sources – compatible schemas in different database server brands/versions. Some applications may perform tasks over models alone, without any data consumption, e.g. higher-level programming languages and frameworks.

The role of the ORM capabilities in Entity Framework is to provide applications with seamless access to conceptual space. It’s even possible that third-party object-level development platforms may implement their own mapping bridges to Entity Framework conceptual models in future. (That doesn’t mean that Entity Framework will not continue enhancing its object-level support.)

To close on the loop – Entity Framework is much bigger than a mere O/R Mapper. It is a conceptual-level development platform, and its ORM capabilities are an interface for applications to interact with conceptual models.

For more information on Entity Framework, visit the ADO.NET team blog at: http://blogs.msdn.com/adonet.

Information on downloading Entity Framework Beta 3 is available at this blog post: http://blogs.msdn.com/esql/archive/2007/12/06/EntityFramework_5F00_Beta3.aspx

eSqlBlast – The Ultimate Tool for Writing Entity SQL Queries

http://www.codeplex.com/Release/ProjectReleases.aspx?ProjectName=adonetsamples&ReleaseId=9063

Entity Framework Beta 3 Is Available for Download

Entity Framework Beta 3 and related packages are available for public download:

·         Entity Framework Runtime:         http://go.microsoft.com/fwlink/?LinkId=104981

·         Entity Framework Tools:                 http://go.microsoft.com/fwlink/?LinkId=104983

·         Entity Framework Samples:            http://go.microsoft.com/fwlink/?LinkID=104849

·         Entity Framework Documentation: http://go.microsoft.com/fwlink/?LinkID=104984

Entity SQL: Canonical Functions

Canonical functions were introduced in the Beta 2 release of Entity Framework. Their purpose is to expose a [virtually] canonical API beyond the core language constructs. Consumers of canonical functions should be aware that Entity Framework defines only the “syntax” part of the API. The behavior is entirely up to the store provider. Not the store, but the ADO.NET provider used to connect to it. That means different providers for the same store may map functions differently. A decent store provider would document how exactly each canonical function is mapped to a store function. For SqlClient that reference is placed under:

ADO.NET Entity Framework > Feature Reference > .NET Framework Data Provider for SQL Server

in the Entity Framework documentation.

Canonical functions are contained in the Edm namespace. Specifying the Edm namespace is optional. However, specifying a store namespace is not. For instance, all three function calls in the following Entity SQL query do exactly the same:

The T-SQL that SqlClient produces is:

Canonical functions are the preferred way to access functionality outside the core language, because they keep the queries [virtually] portable. The set of canonical functions defined by Entity Framework for version 1 is a tiny subset of the set of functions any major database server exposes, but it will grow over time.

Definition: API

API = syntax + behavior

What Do ObjectQuery’s Builder Methods Build

Until Beta 2 ObjectQuery’s builder methods used to build a Command Tree. There were multiple problems with that:

·         Duplicate code - there is already a component that builds Command Trees from Entity SQL.

·         Caching – Command Trees are expensive to hash and thus – difficult to cache.

·         Tracing – getting a meaningful canonical description of an ObjectQuery required a converting the Command Tree back to text which was a step back.

Therefore, in Beta 2 ObjectQuery was refactored to build Entity SQL internally instead of a Command Tree. When the ObjectQuery is about to be executed, the internal Entity SQL representation is sent to the Entity SQL Parser to build a Command Tree for it. Thus ObjectQuery now leverages the same Query Plan Cache as EntityCommand – queries are compiled only the first time they are executed; subsequent executions reuse the already compiled query plan (as long as the cache is not full).

Reusing the Entity SQL Parser, however, means that ObjectQuery may now surface exceptions from it. That’s not bad as long as there is an API to get to the internal Entity SQL command text. In Beta 2 such an API was missing, but that’s fixed in Beta 3. Similarly to EntityCommand, a CommandText property is exposed now off ObjectQuery.

The same example that showed how to get the native SQL, also shows how to get the Entity SQL for a given ObjectQuery:

How to Get the Native SQL from an ObjectQuery

The way to get the native SQL generated for an ObjectQuery is exactly the same as for EntityCommand – using the new ToTraceString() method introduced in Beta 3. Previously that would require a similar (even more obscure) pattern that involved ObjectQuery.CreateCommandTree() and IServiceProvider.CreateCommandDefinition(). Now that there is a new pattern, both of the above methods have been removed from public surface. Yes, that means if you’ve discovered that pattern and you’ve been using it, you have to switch to ToTraceString(). Same rules and restrictions as for EntityCommand apply.

And, here is a sample that shows how to print the native SQL generated for an ObjectQuery: