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.

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.

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.

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

entityBuilder.ProviderConnectionString = dbBuilder.ToString();

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

Console.WriteLine(entityBuilder.ToString());

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:

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.

And of course, there is a large gray area in the middle where you simply want to take advantage of the programming concepts that are available in EDM/EF and not available in the database space – like inheritance, associations, materialization, and LINQ. Sitting on EDM, your application would focus on the business domain rather on the mechanics of assembling data and materializing it into objects, and reverse-engineering object changes to database rows.
]]>https://blogs.msdn.microsoft.com/esql/2008/03/14/why-should-i-use-entity-framework/feed/2eSqlBlast on Code Galleryhttps://blogs.msdn.microsoft.com/esql/2008/01/31/esqlblast-on-code-gallery/
https://blogs.msdn.microsoft.com/esql/2008/01/31/esqlblast-on-code-gallery/#respondThu, 31 Jan 2008 13:12:00 +0000https://blogs.msdn.microsoft.com/esql/2008/01/31/esqlblast-on-code-gallery/MSDN Code Gallery is now live! It’s a place to download compiled binaries rather than source code. The latest eSqlBlast is available at http://code.msdn.com/esql.
]]>https://blogs.msdn.microsoft.com/esql/2008/01/31/esqlblast-on-code-gallery/feed/0The Edm.Length() Canonical Function over SqlClienthttps://blogs.msdn.microsoft.com/esql/2007/12/20/the-edm-length-canonical-function-over-sqlclient/
https://blogs.msdn.microsoft.com/esql/2007/12/20/the-edm-length-canonical-function-over-sqlclient/#commentsThu, 20 Dec 2007 02:21:00 +0000https://blogs.msdn.microsoft.com/esql/2007/12/20/the-edm-length-canonical-function-over-sqlclient/

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

LEN(x + ‘.’) – LEN(‘.’)

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.

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:

“Returns the number of characters of the specified string expression, excluding trailing blanks.”

(The highlighting is mine.)

Indeed

SELECTLEN(‘abc’), LEN(‘abc‘);

returns: 33, not 312 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.

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.

2007.12.18: Today I posted an update to How to Parse an EntityDataReader – I discovered (and fixed) two bugs in the parsing code. Since the code is taken from eSqlBlast, the same fix should be applied to it too. The code is in project Core, class XmlVisitor, method VisitRecord. I’ll try to push a refresh to CodePlex soon. Sorry for the inconvenience.

The source code of eSqlBlast is available for public download from the Entity Framework samples location:

Microsoft.Samples.Data.eSqlBlast, briefly eSqlBlast, is a suite of tools and libraries that complements Entity Framework. It aids authoring, executing, and visualizing ad-hoc Entity SQL queries against arbitrary EDM models. The tools of the eSqlBlast suite may be used interactively, from the command line, or embedded in other programs. The eSqlBlast suite also contains XSL transformation scripts for rendering CSDL and its own raw XML format.