Zlatko Michailov on Entity SQL

Le Roi Est Mort, Vive Le Roi!

Zlatko Michailov - MSFT — Tue, 09 Sep 2008 09:41:00 GMT

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...

Zlatko Michailov - MSFT — Tue, 27 May 2008 23:38:00 GMT

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

Zlatko Michailov - MSFT — Mon, 12 May 2008 21:45:00 GMT

The .NET Framework SP1 Beta-compatible version of eSqlBlast is available at http://code.msdn.microsoft.com/esql/Release/ProjectReleases.aspx?ReleaseId=991. The release includes a source code zip as well.

The SP1 Beta distributions of .NET Framework 3.5 and Visual Studio 2008 are available through: http://msdn.microsoft.com/en-us/vstudio/cc533448.aspx

Primer to EntityConnectionStringBuilder

Zlatko Michailov - MSFT — Sat, 15 Mar 2008 01:20:00 GMT

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:

EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder();

entityBuilder.Provider = Util.ReadKeyValue("Provider");

entityBuilder.Metadata = Util.ReadKeyValue("Metadata");

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

DbProviderFactory dbFactory = DbProviderFactories.GetFactory(entityBuilder.Provider);

DbConnectionStringBuilder dbBuilder = dbFactory.CreateConnectionStringBuilder();

foreach (string key in dbBuilder.Keys)

{

dbBuilder.Add(key, Util.ReadKeyValue(key));

}

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:

<add name="Northwind"

connectionString="Metadata=..\..\..\..\Northwind; Provider=System.Data.SqlClient; Provider Connection String='Data Source=.; Initial Catalog=Northwind; Integrated Security=true;'"

providerName="System.Data.EntityClient"/>

<add name="Adventureworks"

connectionString="Metadata=..\..\..\..\AdventureWorks; Provider=System.Data.SqlClient; Provider Connection String='Data Source=.; Initial Catalog=AdventureWorks; Integrated Security=true;'"

providerName="System.Data.EntityClient"/>

</connectionStrings>

</configuration>

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

EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder();

entityBuilder.Name = Util.ReadKeyValue("Name");

Look up the attached source code for details.

Enjoy!

Why Should I Use Entity Framework?

Zlatko Michailov - MSFT — Fri, 14 Mar 2008 21:38:00 GMT

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.

eSqlBlast on Code Gallery

Zlatko Michailov - MSFT — Thu, 31 Jan 2008 21:12:00 GMT

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.

The Edm.Length() Canonical Function over SqlClient

Zlatko Michailov - MSFT — Thu, 20 Dec 2007 10:21:00 GMT

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

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.

SQL Server’s LEN() Function

Zlatko Michailov - MSFT — Sat, 15 Dec 2007 03:03:00 GMT

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

SELECT LEN('abc'), LEN('abc ');

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

Zlatko Michailov - MSFT — Sat, 15 Dec 2007 01:11:00 GMT

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

Zlatko Michailov - MSFT — Thu, 13 Dec 2007 03:38:00 GMT

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:

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

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.

Entity Framework Beta 3 Is Available for Download

Zlatko Michailov - MSFT — Thu, 06 Dec 2007 20:44:00 GMT

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

Zlatko Michailov - MSFT — Thu, 06 Dec 2007 04:01:00 GMT

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:

ROW(Edm.Length('abc') AS [Edm.Length],

Length('abc') AS [Length],

SqlServer.LEN('abc') AS [SqlServer.Len])

-- Powered by eSqlBlast

Edm.Length	Length	SqlServer.Len
3	3	3

The T-SQL that SqlClient produces is:

SELECT

1 AS [C1],

CAST(LEN('abc') AS int) AS [C2],

CAST(LEN('abc') AS int) AS [C3],

CAST(LEN('abc') AS int) AS [C4]

FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1]

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

Zlatko Michailov - MSFT — Wed, 05 Dec 2007 04:11:00 GMT

API = syntax + behavior

What Do ObjectQuery’s Builder Methods Build

Zlatko Michailov - MSFT — Wed, 05 Dec 2007 03:26:00 GMT

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:

// Create an ObjectContext

using (Northwind.Northwind northwind = new Northwind.Northwind(NorthwindConnectionString))

{

// Create an ObjectQuery

ObjectQuery<Northwind.Product> products = northwind.Products

.Where("LEFT(it.ProductName, 1) = 'C'")

.OrderBy("it.ProductName");

// Make sure the connection is open

northwind.Connection.Open();

// Display the Entity SQL built for the ObjectQuery

Console.WriteLine("\n\n---------------------------------------------------------");

Console.WriteLine("Entity SQL");

Console.WriteLine("---------------------------------------------------------");

Console.WriteLine(products.CommandText);

// Display the T-SQL generated for the (Entity SQL of the) ObjectQuery

Console.WriteLine("\n\n---------------------------------------------------------");

Console.WriteLine("T-SQL");

Console.WriteLine("---------------------------------------------------------");

Console.WriteLine(products.ToTraceString());

// Render the result to make sure the query is valid

Console.WriteLine("\n\n---------------------------------------------------------");

Console.WriteLine("Result");

Console.WriteLine("---------------------------------------------------------");

foreach (Northwind.Product product in products)

{

Console.WriteLine("{0,2}: {1}", product.ProductID, product.ProductName);

}

How to Get the Native SQL from an ObjectQuery

Zlatko Michailov - MSFT — Wed, 05 Dec 2007 02:51:00 GMT

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:

// Create an ObjectContext

using (Northwind.Northwind northwind = new Northwind.Northwind(NorthwindConnectionString))

{

// Create an ObjectQuery

ObjectQuery<Northwind.Product> products = northwind.Products

.Where("LEFT(it.ProductName, 1) = 'C'")

.OrderBy("it.ProductName");

// Make sure the connection is open

northwind.Connection.Open();

// Display the Entity SQL built for the ObjectQuery

Console.WriteLine("\n\n---------------------------------------------------------");

Console.WriteLine("Entity SQL");

Console.WriteLine("---------------------------------------------------------");

Console.WriteLine(products.CommandText);

// Display the T-SQL generated for the (Entity SQL of the) ObjectQuery

Console.WriteLine("\n\n---------------------------------------------------------");

Console.WriteLine("T-SQL");

Console.WriteLine("---------------------------------------------------------");

Console.WriteLine(products.ToTraceString());

// Render the result to make sure the query is valid

Console.WriteLine("\n\n---------------------------------------------------------");

Console.WriteLine("Result");

Console.WriteLine("---------------------------------------------------------");

foreach (Northwind.Product product in products)

{

Console.WriteLine("{0,2}: {1}", product.ProductID, product.ProductName);

}

How to Get the Native SQL from an EntityCommand

Zlatko Michailov - MSFT — Wed, 05 Dec 2007 02:31:00 GMT

With the Beta 3 release, Entity Framework is introducing an easy way to get the native SQL from an EntityCommand. There was public API to get to the native SQL, but the pattern was too obscure, and I don’t want to go into detail. Let’s discuss the new one, instead. It’s a single method directly exposed off EntityCommand – ToTraceString(), which returns a string.

There is no guarantee the returned string is directly executable by the provider (although as of today it is for SQL Server). What you should keep in mind is that ToTraceString() requires the associated EntityConnection to be open. On the other hand, ToTraceString() does not require the EntityCommand to be prepared.

Here’s a sample code snippet that shows how to print the native SQL generated for an EntityCommand:

// Create an EntityConnection

using (EntityConnection connection = new EntityConnection(NorthwindConnectionString))

{

string esql = "SELECT VALUE product \n" +

"FROM Northwind.Products AS product\n" +

"WHERE LEFT(product.ProductName, 1) = 'C' \n" +

"ORDER BY product.ProductName";

// Create an EntityCommand for this connection.

EntityCommand productsCmd = connection.CreateCommand();

productsCmd.CommandText = esql;

// Open the connection.

connection.Open();

// Display the Entity SQL text using the CommandText property from DbCommand.

Console.WriteLine("\n\n---------------------------------------------------------");

Console.WriteLine("Entity SQL");

Console.WriteLine("---------------------------------------------------------");

Console.WriteLine(productsCmd.CommandText);

// Display the T-SQL text using the new ToTraceString() method.

Console.WriteLine("\n\n---------------------------------------------------------");

Console.WriteLine("T-SQL");

Console.WriteLine("---------------------------------------------------------");

Console.WriteLine(productsCmd.ToTraceString());

// Traverse and display the result to make sure the query is valid.

Console.WriteLine("\n\n---------------------------------------------------------");

Console.WriteLine("Result");

Console.WriteLine("---------------------------------------------------------");

EntityDataReader productsReader = productsCmd.ExecuteReader(CommandBehavior.SequentialAccess);

while (productsReader.Read())

{

Console.WriteLine("{0,2}: {1}", productsReader.GetInt32(productsReader.GetOrdinal("ProductID")),

productsReader.GetString(productsReader.GetOrdinal("ProductName")));

}

productsReader.Close();

connection.Close();

}

How to Do Custom Mapping Using Entity SQL Views

Zlatko Michailov - MSFT — Thu, 22 Nov 2007 00:09:00 GMT

This post was intended to be a reply to this question http://forums.microsoft.com/msdn/showpost.aspx?postid=2440161&siteid=1 on the Entity Framework forum, but its size exceeds the forum server limit. Besides, it’s useful in general. So I decided to post it here. In this post you’ll find an entire repro of the subject scenario from the forum.

Create the following database schema:

create table Organization

(

OrganizationId int primary key,

OrganizationName nvarchar(100)

);

create table OrganizationRelationship

(

OrganizationA int foreign key references Organization(OrganizationId),

OrganizationB int foreign key references Organization(OrganizationId),

RelationshipId int

primary key (OrganizationA, OrganizationB, RelationshipId)

);

insert into Organization values(1, 'One');

insert into Organization values(2, 'Two');

insert into Organization values(3, 'Three');

insert into Organization values(4, 'Four');

-- 1: A partner B

-- 2: A client B

-- 3: A subsidiary B

insert into OrganizationRelationship values(1, 2, 1);

insert into OrganizationRelationship values(3, 4, 1);

insert into OrganizationRelationship values(2, 1, 1);

insert into OrganizationRelationship values(4, 3, 1);

insert into OrganizationRelationship values(1, 3, 2);

insert into OrganizationRelationship values(2, 4, 2);

insert into OrganizationRelationship values(1, 4, 3);

insert into OrganizationRelationship values(2, 3, 3);

I ran EdmGen.exe to do a first cut of a model, which I hacked manually afterwards.

CSDL remains virtually unchanged (except some renaming). Notice that it represents the way things are in the database, i.e. the associations match the foreign keys between the two tables:

<?xml version="1.0" encoding="utf-8"?>

</AssociationSet>

</AssociationSet>

</EntityContainer>

<Key>

</Key>

</EntityType>

<Key>

</Key>

</EntityType>

</Principal>

</Dependent>

</ReferentialConstraint>

</Association>

</Principal>

</Dependent>

</ReferentialConstraint>

</Association>

</Schema>

What you have to notice in the MSL is that if you provide a QueryView for one set, all related sets must be given a QueryView. Those are easy to write once you have one. Yesterday I wrote a more complex view than it was need. Also notice that the original associations from SSDL are missing here. That’s because they don’t make sense in the model. Instead, we introduce new associations – Partner, Client, etc. The OrganizationRelationship entity set has also disappeared for the same reason.

<?xml version="1.0" encoding="utf-8"?>

SELECT VALUE Organizations.Organization(org.OrganizationId, org.OrganizationName)

FROM dbo.Organization AS org;

</QueryView>

</EntitySetMapping>

SELECT VALUE Organizations.Partner(

CREATEREF(Organizations.Organization, ROW(rel.OrganizationA)),

CREATEREF(Organizations.Organization, ROW(rel.OrganizationB)) )

FROM dbo.OrganizationRelationship AS rel

WHERE rel.RelationshipId = 1;

</QueryView>

</AssociationSetMapping>

SELECT VALUE Organizations.Client(

CREATEREF(Organizations.Organization, ROW(rel.OrganizationA)),

CREATEREF(Organizations.Organization, ROW(rel.OrganizationB)) )

FROM dbo.OrganizationRelationship AS rel

WHERE rel.RelationshipId = 2;

</QueryView>

</AssociationSetMapping>

</EntityContainerMapping>

</Mapping>

In CSDL we model the sets and types that we already saw in MSL. Additionally, we create navigation properties for each kind of relationship:

<?xml version="1.0" encoding="utf-8"?>

</AssociationSet>

</AssociationSet>

</EntityContainer>

<Key>

</Key>

</EntityType>

</Association>

</Association>

</Schema>

To verify the model I ran the following Entity SQL query:

SELECT org, org.Partners, org.Clients

FROM Organizations.Organization AS org;

-- Powered by eSqlBlast

Which produced the following correct result:

org

Partners

Clients

OrganizationId	OrganizationName
1	One

OrganizationId	OrganizationName
2	Two

OrganizationId	OrganizationName
3	Three

OrganizationId	OrganizationName
2	Two

OrganizationId	OrganizationName
1	One

OrganizationId	OrganizationName
4	Four

OrganizationId	OrganizationName
3	Three

OrganizationId	OrganizationName
4	Four

OrganizationId	OrganizationName
4	Four

OrganizationId	OrganizationName
3	Three

I’m leaving it up to you to expose the Subsidiaries navigation property for homework.

Enjoy!

LINQ to Entities: Compiled Queries

Zlatko Michailov - MSFT — Tue, 20 Nov 2007 01:08:00 GMT

Last Friday I blogged about making local variables behave consistently in LINQ to Entities queries. And the solution I recommended was compiled queries. But just today I realized that I haven’t blogged about compiled queries in general yet.

The main purpose of compiled queries to improve performance. While regular LINQ to Entities queries are re-compiled before every execution, compiled queries are compiled only once (subject to Query Plan Cache availability) and cached. Subsequent executions pull the query plan from the Query Plan Cache. Additionally, compiled queries improve the experience with using local variables and parameters.

The “query” part of compiled queries is a regular LINQ to entities query with explicit parameters. A compiled query has at least one parameter – an ObjectContext. That way a query instance can be compiled before the ObjectContext instance is constructed. That also means that a single compiled query may be executed against different ObjectContext instances as long as those two instance have loaded the same entity model.

Usage of parameters is explicit with compiled queries - a compiled query may still reference local variables, but those values are taken as constants.

Following is an example of a compiled query that retrieves the products whose names start with a user-provided text string in the Northwind model:

var products = CompiledQuery.Compile((Northwind northwind, string startsWith) =>

from product in northwind.Products

where product.ProductName.StartsWith(startsWith)

orderby product.ProductName

select product);

using (Northwind northwind = new Northwind(Program.NorthwindConnectionString))

{

foreach (var product in products(northwind, "C"))

{

Console.WriteLine(product.ProductName);

}

Behavior of Variables in LINQ to Entities Queries

Zlatko Michailov - MSFT — Sat, 17 Nov 2007 03:21:00 GMT

LINQ is very nice about referencing local variables without any additional declaration. When I ran my first such LINQ to Entities query, I was ecstatic. The next moment I was puzzled – are the values of those variables picked at compile-time and later used as constants, or are they picked at each execution, so they are real parameters?

I did some investigation, and unfortunately there is no single answer. First of all, it’s implementation-dependent. That means the behavior of variables in LINQ to Entities may be different from the behavior of variables in LINQ to SQL. This post is all about LINQ to Entities.

I’ll be showing some examples over the Products entity set of the Northwind model. The set contains 77 items with consecutive ID’s from 1 through 77:

ProductID	ProductName	QuantityPerUnit	UnitPrice	UnitsInStock	UnitsOnOrder	ReorderLevel	Discontinued
1	Chai	10 boxes x 20 bags	18.0000	39	0	10	False
2	Chang	24 - 12 oz bottles	19.0000	17	40	25	False
3	Aniseed Syrup	12 - 550 ml bottles	10.0000	13	70	25	False
4	Chef Anton's Cajun Seasoning	48 - 6 oz jars	22.0000	53	0	0	False
5	Chef Anton's Gumbo Mix	36 boxes	21.3500	0	0	0	True
6	Grandma's Boysenberry Spread	12 - 8 oz jars	25.0000	120	0	25	False
7	Uncle Bob's Organic Dried Pears	12 - 1 lb pkgs.	30.0000	15	0	10	False
8	Northwoods Cranberry Sauce	12 - 12 oz jars	40.0000	6	0	0	False
9	Mishi Kobe Niku	18 - 500 g pkgs.	97.0000	29	0	0	True
10	Ikura	12 - 200 ml jars	31.0000	31	0	0	False
11	Queso Cabrales	1 kg pkg.	21.0000	22	30	30	False
12	Queso Manchego La Pastora	10 - 500 g pkgs.	38.0000	86	0	0	False
13	Konbu	2 kg box	6.0000	24	0	5	False
14	Tofu	40 - 100 g pkgs.	23.2500	35	0	0	False
15	Genen Shouyu	24 - 250 ml bottles	15.5000	39	0	5	False
16	Pavlova	32 - 500 g boxes	17.4500	29	0	10	False
17	Alice Mutton	20 - 1 kg tins	39.0000	0	0	0	True
18	Carnarvon Tigers	16 kg pkg.	62.5000	42	0	0	False
19	Teatime Chocolate Biscuits	10 boxes x 12 pieces	9.2000	25	0	5	False
20	Sir Rodney's Marmalade	30 gift boxes	81.0000	40	0	0	False
21	Sir Rodney's Scones	24 pkgs. x 4 pieces	10.0000	3	40	5	False
22	Gustaf's Knäckebröd	24 - 500 g pkgs.	21.0000	104	0	25	False
23	Tunnbröd	12 - 250 g pkgs.	9.0000	61	0	25	False
24	Guaraná Fantástica	12 - 355 ml cans	4.5000	20	0	0	True
25	NuNuCa Nuß-Nougat-Creme	20 - 450 g glasses	14.0000	76	0	30	False
…	…	...	...	...	...	...	...

I expected the following example to retrieve either item 11 twice, or item 11 and item 25. However it returned neither – it returned item 11 and item 21.

using (Northwind northwind = new Northwind(Program.NorthwindConnectionString))

{

int bottomProductID = 10;

int skipProducts = 1;

var productByVars = (from product in northwind.Products

where product.ProductID >= bottomProductID // Lambda expression

orderby product.ProductID

select product)

.Skip(skipProducts) // Static expression

.Take(1);

// Execute and render results (1)

Console.WriteLine("bottomProductID={0}, skipProducts={1}", bottomProductID, skipProducts);

Product theProduct = productByVars.First();

Console.WriteLine("{0}: {1}", theProduct.ProductID, theProduct.ProductName);

// Modify the parameter variables

bottomProductID = 20;

skipProducts = 5;

// Execute and render results (2)

Console.WriteLine("\n\nbottomProductID={0}, skipProducts={1}", bottomProductID, skipProducts);

theProduct = productByVars.First();

Console.WriteLine("{0}: {1}", theProduct.ProductID, theProduct.ProductName);

}

The explanation for that behavior is this: when a local variable is used in a lambda expression it behaves as a parameter, i.e. it’s value is re-evaluated each time the query is executed; when a variable is used in a static expression, it’s value is picked at compile-time, and then it’s used as a constant. Even if you know that rule (and now that you do), unless you use the method-based style of writing LINQ to Entities queries, it’s not very clear what expression is static and what is lambda.

So is there a way to guarantee a consistent behavior of local variables? We’ve gotten to the “good news” part of the post – yes, there is. In Beta 3 we are introducing the concept of “compiled LINQ to Entities queries”. Compiled queries are explicit about what their parameters are. If local variables are still referenced, they are used as constants. The following example retrieves items 11 and 25 as it should be expected:

int bottomProductID = 10;

int skipProducts = 1;

var productByVars = CompiledQuery.Compile(

(Northwind northwind, int bottomProductIDArg, int skipProductsArg) =>

(from product in northwind.Products

where product.ProductID >= bottomProductIDArg

orderby product.ProductID

select product)

.Skip(skipProductsArg)

.Take(1));

using (Northwind northwind = new Northwind(Program.NorthwindConnectionString))

{

// Execute and render results (1)

Console.WriteLine("bottomProductID={0}, skipProducts={1}", bottomProductID, skipProducts);

Product theProduct = productByVars(northwind, bottomProductID, skipProducts).First();

Console.WriteLine("{0}: {1}", theProduct.ProductID, theProduct.ProductName);

// Modify the parameter variables

bottomProductID = 20;

skipProducts = 5;

// Execute and render results (2)

Console.WriteLine("\n\nbottomProductID={0}, skipProducts={1}", bottomProductID, skipProducts);

theProduct = productByVars(northwind, bottomProductID, skipProducts).First();

Console.WriteLine("{0}: {1}", theProduct.ProductID, theProduct.ProductName);

}

If you replace the usage of the parameters bottomProductIDArg and skipProductsArg inside the query with the local variables bottomProductID and skipProducts respectively, the query will return item 11 twice.

How to Parse an EntityDataReader

Zlatko Michailov - MSFT — Sat, 17 Nov 2007 01:31:00 GMT

2007.12.18: Please accept my apologies – today I discovered (and fixed) two bugs in the parsing code. One is a silly error. The other is that collections should be treated as primitive types and refs with regard to the number of fields. The code bellow includes the changes.

2007.12.04: Last week I discovered that the pattern I had listed in this post did not catch unwrapped primitive types and ref’s. So I’m fixing it now. This is the pattern for Beta 3. I see that it could be simplified (in the highlighted sections) if the FieldMetadata collection was populated for non-structural types. I will propose the change for RTM, but there is no guarantee it will be accepted.

Although you can successfully parse an EntityDataReader by only reasoning over its physical shape using record.GetType(i), you cannot reconstruct the entire entity-level structure without using EDM metadata. For instance, you can easily figure that something is coming as a nested DbDataRecord, but you cannot know whether that is an entity type, a complex type, or a row type. To get the exact entity-level structure, EDM metadata must be used.

Following is an extraction from a class that “visits” an arbitrary EntityDataReader. The highlighted portion is the place where the EDM metadata is obtained.

// We want to make sure the passed DbDataReader

// is actually an EntityDataReader.

public void VisitReader(EntityDataReader reader)

{

VisitReader_(reader);

}

// Nested collections are assembled as internal BridgeDataReader’s.

// That’s why we can’t reuse the public method.

private void VisitReader_(DbDataReader reader)

{

while (reader.Read())

{

// We’ll call this method with internal BridgeDataReader’s

// that implement IExtendedDataRecord.

VisitRecord_(reader as IExtendedDataRecord);

}

// The metadata is exposed through IExtendedDataRecord.

private void VisitRecord_(IExtendedDataRecord record)

{

// First get the type kind of the record itself.

// A record is not necessarily a strucutral type.

BuiltInTypeKind recordTypeKind = record.DataRecordInfo.RecordType.EdmType.BuiltInTypeKind;

int fieldCount;

// For RefType, PrimitiveType, and CollectionType the FieldMetadata collection is not populated.

// In those cases the record contains exactly one field.

if (recordTypeKind == BuiltInTypeKind.RefType ||

recordTypeKind == BuiltInTypeKind.PrimitiveType ||

recordTypeKind == BuiltInTypeKind.CollectionType)

{

fieldCount = 1;

}

else

{

fieldCount = record.DataRecordInfo.FieldMetadata.Count;

}

for (int i = 0; i < fieldCount; i++)

{

// If the field is flagged as DbNull, the shape of the value is undetermined.

// An attempt to get such a value may trigger an exception.

if (record.IsDBNull(i))

{

// ... (Render NULL)

}

else

{

BuiltInTypeKind fieldTypeKind = recordTypeKind;

// If the record is a structural type, examine the field with the given index.

if (fieldTypeKind == BuiltInTypeKind.EntityType ||

fieldTypeKind == BuiltInTypeKind.ComplexType ||

fieldTypeKind == BuiltInTypeKind.RowType)

{

fieldTypeKind = record.DataRecordInfo.FieldMetadata[i].FieldType.TypeUsage.EdmType.BuiltInTypeKind;

}

switch (fieldTypeKind)

{

case BuiltInTypeKind.CollectionType:

// Collections are surfaced as nested BridgeDataReader’s.

VisitReader_(record.GetData(i));

break;

case BuiltInTypeKind.EntityType:

// Entities are wrapped in BridgeDataRecord’s.

VisitRecord_(record.GetDataRecord(i) as IExtendedDataRecord);

break;

case BuiltInTypeKind.ComplexType:

// Complex type instances are wrapped in BridgeDataRecord’s.

VisitRecord_(record.GetDataRecord(i) as IExtendedDataRecord);

break;

case BuiltInTypeKind.RowType:

// Row instances are wrapped in BridgeDataRecord’s.

VisitRecord_(record.GetDataRecord(i) as IExtendedDataRecord);

break;

case BuiltInTypeKind.RefType:

// Ref’s are surfaced as EntityKey instances. The containing record sees them as atomic.

VisitKey_(record.GetValue(i) as EntityKey);

break;

case BuiltInTypeKind.PrimitiveType:

// Primitive types are surfaced as plain objects.

// ... (Render record.GetValue(i).ToString())

break;

default:

// Unhandled type kind. We shouldn't end up here.

// ... (Render fieldTypecand.ToString())

break;

}

private void VisitKey_(EntityKey key)

{

// ... (Render key.EntitySetName)

foreach (EntityKeyMember keyMember in key.EntityKeyValues)

{

// ... (Render keyMember.Key)

// ... (Render keyMember.Value)

}

The code above uses the following namespaces:

using System.Data;

using System.Data.Common;

using System.Data.EntityClient;

using System.Data.Metadata;

using System.Data.Metadata.Edm;

XSL Transformation of CSDL into HTML

Zlatko Michailov - MSFT — Fri, 16 Nov 2007 23:18:00 GMT

The attached XSL script neatly renders an Entity Framework CSDL file into HTML.

Below is the NorthwindEF model that we’ve been using for our samples, rendered using that XSL script:

Northwind

Entity Sets
Entity Types
Complex Types
Associations

Entity Sets

Northwind
Employees	Northwind.Employee
Territories	Northwind.Territory
Regions	Northwind.Region
Suppliers	Northwind.Supplier
Products	Northwind.Product
Categories	Northwind.Category
Customers	Northwind.Customer
Orders	Northwind.Order
OrderDetails	Northwind.OrderDetail

Entity Types

Order
OrderID	Int32
EmployeeID	Int32
OrderDate	DateTime
RequiredDate	DateTime
ShippedDate	DateTime
Freight	Decimal
ShipName	String
ShipAddress	String
ShipCity	String
ShipRegion	String
ShipPostalCode	String
ShipCountry	String
Customer	Northwind.CustomerOrders
OrderDetails	Northwind.Order_Details_Order

InternationalOrder
Northwind.Order
CustomsDescription	String
ExciseTax	Decimal

Employee
EmployeeID	Int32
LastName	String
FirstName	String
Title	String
TitleOfCourtesy	String
BirthDate	DateTime
HireDate	DateTime
Address	Northwind.CommonAddress
HomePhone	String
Extension	String
Photo	Binary
Notes	String
PhotoPath	String
Territories	Northwind.EmployeesTerritories

CurrentEmployee

Northwind.Employee

PreviousEmployee

Northwind.Employee

Product
ProductID	Int32
ProductName	String
QuantityPerUnit	String
UnitPrice	Decimal
UnitsInStock	Int16
UnitsOnOrder	Int16
ReorderLevel	Int16
Category	Northwind.Products_Category
OrderDetails	Northwind.Order_Details_Product
Supplier	Northwind.Products_Supplier

DiscontinuedProduct
Northwind.Product
DiscontinuedDate	DateTime

Customer
CustomerID	String
CompanyName	String
ContactName	String
ContactTitle	String
Address	Northwind.CommonAddress
Phone	String
Fax	String
Orders	Northwind.CustomerOrders

Category
CategoryID	Int32
CategoryName	String
Description	String
Picture	Binary
Products	Northwind.Products_Category

Supplier
SupplierID	Int32
CompanyName	String
ContactName	String
ContactTitle	String
Address	Northwind.CommonAddress
Phone	String
Fax	String
HomePage	String
Products	Northwind.Products_Supplier

OrderDetail
OrderID	Int32
ProductID	Int32
UnitPrice	Decimal
Quantity	Int16
Discount	Single
Order	Northwind.Order_Details_Order
Product	Northwind.Order_Details_Product

Territory
TerritoryID	Int32
TerritoryDescription	String

Region
RegionID	Int32
RegionDescription	String

Complex Types

CommonAddress
Address	String
City	String
Region	String
PostalCode	String
Country	String

Associations

CustomerOrders
Customer	Northwind.Customer	0..1
Orders	Northwind.Order	*

Products_Category
Category	Northwind.Category	0..1
Products	Northwind.Product	*

Products_Supplier
Supplier	Northwind.Supplier	0..1
Products	Northwind.Product	*

Order_Details_Product
Product	Northwind.Product	1
OrderDetails	Northwind.OrderDetail	*

Order_Details_Order
Order	Northwind.Order	1
OrderDetails	Northwind.OrderDetail	*

EmployeesTerritories
Employees	Northwind.Employee	*
Territories	Northwind.Territory	*

TerritoriesRegion
Region	Northwind.Region	1
Territories	Northwind.Territory	*

Entity SQL Tip #1

Zlatko Michailov - MSFT — Thu, 01 Nov 2007 21:36:00 GMT

A well defined query against a well defined entity data model does not need JOIN.

Navigation properties in combination with nesting sub-queries should be used instead. These latter constructs represent task requirements much more closely than JOIN does. That makes it easier to build and maintain correct Entity SQL queries.

Live Appearance: Nov 27, 2007 in Vancouver, BC

Zlatko Michailov - MSFT — Wed, 24 Oct 2007 22:40:00 GMT

On Nov 27, 2007 I'll speak about Query Options for Entity Framework at DevTeach in Vancouver, BC. Following is a preliminary outline of the talk:

An overview of the Query Pipeline and the processes that take place there.
Shapes of results that Entity Framework can produce.
Interesting queries using Entity SQL.
Similar queries using LINQ to Entities.
Interesting mapping scenarios:

Retrieve data through stored procedures
Native SQL views
EntitySQL views
Inheritance

Query Complexity Factors

Zlatko Michailov - MSFT — Wed, 24 Oct 2007 20:04:00 GMT

A query issued through Entity Framework undergoes three main transformations:

1. Compilation

2. Query view expansion

3. Native SQL generation

1. Compilation

Compilation is the process of producing a Command Tree.

1.1. Entity SQL

1.1.1. Navigation Properties

Navigation is not natively supported by database servers. It is implemented using APPLY operators to bring in the related data.

1.1.2. Queries Nested in Projection

Entity SQL supports nesting full-blown queries in the projection clause. However, that is not commonly supported by data stores. Instead, it is implemented via a pattern of JOIN’ing the nested query with the main source.

1.2. ObjectQuery

1.2.1. Entity Span

Entity Span (Include) in ObjectQuery is the equivalent of navigation. Similarly ObjectQuery needs to compile one or more APPLY operators to bring in the spanned over entities.

1.2.2. Relationship Span

When one end of a relationship is referenced, the other end is implicitly brought in using a APPLY operator.

1.3. LINQ To Entities

LINQ has a variety of operators that don’t exist in Command Trees and that are compiled to patterns of Command Tree constructs:

· Aggregates and GroupBy

· Anonymous types

· Entity collections

2. Query View Expansion

The complexity of the expanded view depends solely on the subject model. The following mapping scenarios by definition produce complex queries:

2.1. Inheritance (Table-Per-Type)

In order to construct a whole entity, rows that represent the ancestor types must be JOIN’ed together.

2.2. Inheritance (Table-Per-Concrete Type)

In order to construct a whole entity UNION ALL operators are used to bring in the rows for derived types.

2.3. Entity Splitting

Similarly, the parts of split entities must be JOIN’ed together.

2.4. Many-To-Many Relationships

Navigating a many-to-many relationship requires at least 2 JOIN operators since it can only be modeled with a separate, “link”, table.

3. Native SQL Generation

3.1. Store Capabilities

Command Trees are designed with the capabilities of SQL Server 2005 in mind. And their capabilities go even further. Other stores, like SQL Server 2000, have to translate some Command Tree operations to complex patterns. Such operations may be:

· SKIP (paging)

· APPLY

· Query nesting

· Navigation

· Canonical functions

4. Other Aspects of Complexity

The data store may not be able to carry on with a relatively simple native SQL query if the query requires an intermediate temporary storage and that intermediate projection exceeds the store’s capabilities. That may happen as a result of too many JOIN operators as well as of few JOINs of large projections.

Zlatko Michailov

Program Manager, Data Programmability Runtime

Microsoft Corp.

Entity SQL - Quick Reference

Zlatko Michailov - MSFT — Wed, 24 Oct 2007 19:33:00 GMT

Literals

“abc”

Value

abc

‘abc’

Value

abc

Value

{2}

Value

{3, 4, 5}

Value

Rows

ROW(1 AS i, ‘abc’ AS s)

i	s
1	abc

SELECT ROW(1 AS i, ‘abc’ AS s) AS Row

FROM {11, 12, 13};

Row

i	s
1	abc

i	s
1	abc

i	s
1	abc

{ROW(1 AS i, ‘abc’ AS s)}

UNION ALL

{ROW(2, ‘xyz’)};

i	s
1	abc
2	xyz

Entities

SELECT c

FROM AdventureWorks.Culture AS c

WHERE c.CultureID IN {'en', 'es', 'fr'};

CultureID	Name	ModifiedDate
en	English	6/1/1998 12:00:00 AM

CultureID	Name	ModifiedDate
es	Spanish	6/1/1998 12:00:00 AM

CultureID	Name	ModifiedDate
fr	French	6/1/1998 12:00:00 AM

SELECT VALUE c

FROM AdventureWorks.Culture AS c

WHERE c.CultureID IN {'en', 'es', 'fr'};

CultureID	Name	ModifiedDate
en	English	6/1/1998 12:00:00 AM
es	Spanish	6/1/1998 12:00:00 AM
fr	French	6/1/1998 12:00:00 AM

AdventureWorks.Department(

CAST(100 AS Edm.Int16), 'Dyn. Dept',

'Dyn. Group', Edm.GetDate());

DepartmentID	Name	GroupName	ModifiedDate
100	Dyn. Dept	Dyn. Group	6/20/2007 3:52:47 PM

SELECT VALUE AdventureWorks.Department(

rs.ID, rs.Name, rs.[Group], rs.[Date])

FROM

({ROW(CAST(101 AS Edm.Int16) AS ID,

'Dyn. Dept 1' AS Name,

'Dyn. Group' AS [Group],

Edm.GetDate() AS [Date])}

UNION ALL

{ROW(CAST(102 AS Edm.Int16),

'Dyn. Dept 2', 'Dyn. Group',

Edm.GetDate())}

UNION ALL

{ROW(CAST(103 AS Edm.Int16),

'Dyn. Dept 3', 'Dyn. Group',

Edm.GetDate())}

) AS rs;

DepartmentID	Name	GroupName	ModifiedDate
101	Dyn. Dept 1	Dyn. Group	6/20/2007 3:59:43 PM
102	Dyn. Dept 2	Dyn. Group	6/20/2007 3:59:43 PM
103	Dyn. Dept 3	Dyn. Group	6/20/2007 3:59:43 PM

Functions

SELECT c.ContactID,

-- Canonical:

Length(c.FirstName) AS FirstNameLength,

-- Canonical:

Edm.Length(c.LastName) AS LastNameLength,

-- Provider-specific:

SqlServer.LEN(c.EmailAddress) AS EmailAddressLength

FROM AdventureWorks.Contact AS c

WHERE c.ContactID BETWEEN 10 AND 12;

ContactID	FirstNameLength	LastNameLength	EmailAddressLength
10	6	5	27
11	6	8	27
12	5	7	26

Keys/References

SELECT VALUE KEY(c)

FROM AdventureWorks.Culture AS c

WHERE c.CultureID IN {'en', 'es', 'fr'};

CultureID

SELECT VALUE REF(c)

FROM AdventureWorks.Culture AS c

WHERE c.CultureID IN {'en', 'es', 'fr'};

C1	CultureID
0	en
0	es
0	fr

SELECT VALUE DEREF(REF(c))

FROM AdventureWorks.Culture AS c

WHERE c.CultureID IN {'en', 'es', 'fr'};

CultureID	Name	ModifiedDate
en	English	6/1/1998 12:00:00 AM
es	Spanish	6/1/1998 12:00:00 AM
fr	French	6/1/1998 12:00:00 AM

Navigation + Nesting

SELECT e.EmployeeID,

-- to 1:

e.Contact.FirstName, e.Contact.LastName,

-- to many:

(SELECT eph.RateChangeDate, eph.Rate

FROM e.EmployeePayHistory AS eph) AS

PayHistory

FROM AdventureWorks.Employee AS e

WHERE e.EmployeeID IN {4, 6};

EmployeeID

FirstName

LastName

EmployeePayHistory

Rob

Walters

RateChangeDate	Rate
1/5/1998 12:00:00 AM	8.6200
7/1/2000 12:00:00 AM	23.7200
1/15/2002 12:00:00 AM	29.8462

David

Bradley

RateChangeDate	Rate
1/20/1998 12:00:00 AM	24.0000
8/16/1999 12:00:00 AM	28.7500
6/1/2002 12:00:00 AM	37.5000

Paging/TOP

SELECT TOP(3) c.ContactID, c.FirstName, c.LastName

FROM AdventureWorks.Contact AS c

WHERE c.ContactID >= 10;

ContactID	FirstName	LastName
10	Ronald	Adina
11	Samuel	Agcaoili
12	James	Aguilar

SELECT c.ContactID, c.FirstName, c.LastName

FROM AdventureWorks.Contact AS c

ORDER BY c.ContactID

SKIP 9 LIMIT 3;

ContactID	FirstName	LastName
10	Ronald	Adina
11	Samuel	Agcaoili
12	James	Aguilar

Grouping

SELECT c.FirstName, c.LastName, epc.PayChanges

FROM

(SELECT eph.EmployeeID,

Count(eph.EmployeeID) AS PayChanges

FROM AdventureWorks.EmployeePayHistory AS eph

GROUP BY eph.EmployeeID

HAVING Count(eph.EmployeeID)

>= 3) AS epc

JOIN AdventureWorks.Contact AS c

ON epc.EmployeeID = c.ContactID;

FirstName	LastName	PayChanges
Humberto	Acevedo	3
Frances	Adams	3
Sean	Jacobson	3
Adam	Barr	3
Mary	Billstrom	3
Cornelius	Brandon	3
Shirley	Bruner	3
Megan	Burke	3
Stephen	Burton	3
Jovita	Carmody	3
Matthew	Cavallari	3
Charles	Christensen	3
Bart	Duncan	3