Looking for New Opportunities

Looking for New Opportunities

Today it’s not going to be my ordinary published posts.

I quit my job at SRL Group.

I’m not going to write the reasons for such a dramatic
decision, but I can tell that I made it even though a
financial crisis is occurring and I don’t
have any alternative currently.

I want to thank the following people for being there
in good times and bad times:
Maor David, Ran Wahle, Yaron Shkop, Yael Cohen, 
David Halimi, Rivka Gugig, Alon Moshe, Tal Tzur,
Leon Langleyben and everyone I worked with during
the last two years and I forgot to mention here. 
The friends word is sometimes underestimated.
In my case, you were and will still be my friends even though I’m leaving.  
I’m leaving a home with a very bad taste but that’s life – you sometimes need
to decide very hard decisions.

If you have any job suggestions I’ll be glad to hear from you.
You can read my Linkedin profile here.
You can contact me through the Contact Form of my blog.

Mapping Stored Procedure Results to a Custom Entity in Entity Framework

Mapping Stored Procedure Results to a Custom Entity in Entity Framework

In the post I’m going
to explain how to map
results of a stored procedure
to a custom created entity
which we have created with the
Entity Framework designer.

Map Stored Procedures to Custom Entity

Sometimes we have stored procedures in our database which don’t
map to any table or view of our database. The problem with that is
that if we want to use those stored procedures and map them to
a custom entity that we have created we will get an error while
compiling the project. Entity Framework’s entities cannot be left
without a mapping to a table or a view. The workaround for such
situations is to use a dummy DefiningQuery element and to map the
entity to that element. Hopefully that in the next release of
Entity Framework we will get the ability to map stored procedures
to custom entities instead of using the hack I’m going to show.

How to do the hack?

In the following example I’ll use the following stored procedure:

CREATE PROCEDURE dbo.GetCourseIDAndCredits

AS

BEGIN

    SET NOCOUNT ON 

    SELECT CourseID, Credits

    FROM Course

END

Pay attention that this stored procedure is simple and it’s only
to show how to perform the mapping to a custom entity. The
stored procedure returns the ID of a course with its credits.

Step 1
Import the stored procedure to the store model using Entity Framework
Wizard.

Step 2
Create CourseCredits entity that matches the columns which the stored
procedure returns. Make CourseID property as the entity primary key.
The following figure shows the result of step 2:

Step 3
Use the designer’s Function Import feature and on Add Function
Import dialog, set the return type of the stored procedure to our new
CourseCredits entity (if you are not familiar with Function Import you can
read my previous post on this subject). The following figure shows the
dialog:
 

Step 4
Create an entity type on the SSDL which will be the definition of
the entity type that we are going to map to the CourseCredits entity.
Open the model in Xml editor and define CourseCredits entity type like:

<EntityType Name="CourseCredits"> 

  <Key>

    <PropertyRef Name="CourseID" />

  </Key> 

  <Property Name="CourseID" Type="int" Nullable="false"/> 

  <Property Name="Credits" Type="int"/>

</EntityType>

Step 5
Since Entity Framework restrict us to map every entity to a table or view,
we need to use an hack and create a DefiningQuery on SSDL. Define the
CourseCredits entity set as follow in the SSDL:

<EntitySet Name="CourseCreditsSet" EntityType="SchoolModel.Store.CourseCredits">

  <DefiningQuery>

    SELECT cast(0 as int)  CourseID, cast(0 as int) Credits

    WHERE 1 = 2

  </DefiningQuery>

</EntitySet> 

Pay attention that the DefiningQuery returns nothing. The where clause
will never happen. This is for the sake of not enabling using the custom
entity in other situations.

Step 6
Map the CourseCredits entity in the designer to the created dummy
DefiningQuery in the Mapping Details View:
 

Step 7
Test the solution. The following test code will print to the output
the mapped objects:

using (SchoolEntities context = new SchoolEntities())

{

    var courses = context.GetCourseIDAndCredits();

    foreach (var course in courses)

    {

        Console.WriteLine("{0} {1}", course.CourseID, course.Credits);

    }

    Console.ReadLine();

} 

And the result of running the code:

Summary

Lets sum up, I showed how to map stored procedure to a custom
created entity. In Entity Framework V1 this means that we need to
create a dummy DefiningQuery and to map the new entity to it in order
to enable that functionality. Hopefully that in V2 it will be resolved.

QueryView Element in Entity Framework

QueryView Element in Entity Framework

In a very old post that I
wrote I explained what
is the DefiningQuery
element and how to use it.
In this post I’m going to
explain what is the
QueryView element, how to use it and the difference between
using a DefiningQuery and using a QueryView.

What is the QueryView Element?

QueryView element is defined inside an EntitySetMapping in the
MSL part of the EDM schema and it is a read-only mapping between an
entity in the CSDL and an entity/ies in the SSDL. You define the QueryView
mapping with an E-SQL query inside the EntitySetMapping. That query is
evaluated against the storage model and the result set can be used as an
entity in the conceptual model.

How to Use a QueryView?

In the following example I’m going to use the following database definition:
 
When we use the Entity Framework Wizard the following conceptual
model is created:

We want to use a QueryView element and to have an entity that
for each company has its country name inside of it.
How we can achieve it?

Step 1
Add the CountryName from the Country entity to the Company entity
and then remove the Country entity from the conceptual model.
The result should look like:
 

Step 2
Open the model in Xml editor and in the EntitySetMapping of
Company replace the mapping with a QueryView element like in
the following example:

<EntitySetMapping Name="CompanySet">

  <QueryView>

    SELECT VALUE EntityFrameworkExampleModel.Company(comp.CompanyID, comp.CompanyName, coun.CountryName)

    FROM EntityFrameworkExampleModelStoreContainer.Companies AS comp

    INNER JOIN EntityFrameworkExampleModelStoreContainer.Countries AS coun

    ON comp.CountryID = coun.CountryID

  </QueryView>

</EntitySetMapping>

Step 3
Test the result. I used the following code to fetch all the
companies and print them to the console:

using (EntityFrameworkExampleEntities context = new EntityFrameworkExampleEntities())

{

    foreach (var comp in context.CompanySet)

    {

        Console.WriteLine("{0} {1}", comp.CompanyName, comp.CountryName);

    }

    Console.ReadLine();

}

The result is show in the next figure:
 

QueryView Restrictions

Once you have created one QueryView in your MSL, every related EntitySet
and AssociationSet needs to be mapped using QueryView too.
This means that you need to use QueryViews a lot if you have a very
connected entity in your model which makes it hard to use the QueryView 
feature.

The Difference Between DefiningQuery and QueryView

A QueryView element lives inside the mapping definition and provides a
way to use E-SQL to get a read only view on top of the storage model. A very
practical use for it is to limit access to particular data from the database. On
the other hand, DefiningQuery is defined in the storage definition and it’s 
only a read only database view. When you import database views to your model
using the Entity Framework Wizard they are generated as a DefiningQuery.

Summary

Lets sum up, in the post I explained what is the QueryView element and
how to use it. Also, I explained the difference between QueryViews and
DefiningQueries. Like DefiningQueries, QueryViews are very useful
for implementing a conceptual model which apply to our needs but they
are harder to use.

Enabling Xml Schema Validation in Visual Studio

Enabling Xml Schema Validation in Visual Studio

The post is a simple tip
which will help you to
enable Xml schema
validation and intellisense
while writing an Xml file
inside Visual Studio.

The Problem

Writing Xml files according to a specific Xml schema definition is
sometime very hard and can be a very “painful” task for a developer.
You need to remember all the small details and elements in order to do it
properly. So how can we validate the Xml files while writing them?

The Solution

Having the Xsd file which can validate your schema during the writing
of the Xml file can be very helpful. Also, getting hints and intellisense
about Xml properties, elements and errors while writing the Xml file
can increase the writing speed of the file.
Visual Studio include this feature you just need to know how to enable
it and use it.

Step 1

Add a namespace to your schema file. For example the following
schema fragment has a “urn:schema-namespace” namespace:

<xs:schema targetNamespace="urn:schema-namespace" xmlns="urn:schema-namespace" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:NS="urn:schema-namespace">

    <!-- Your schema definitions goes here -->

</xs:schema>

Having the namespace is a must because in Visual Studio you’ll
have to give the schema namespace to the Xml file you write.

Step 2

Put the Xsd file in the following directory while Visual Studio is
closed:
$Visual Studio Directory$\Xml\Schemas
(Replace the $Visual Studio Directory$ with the location of your
Visual Studio)

Step 3

Open Visual Studio and start writing your Xml file. Remember that you
have to give the namespace of the schema in order to have schema
validations and intellisense inside Visual Studio.
The following example is using the schema namespace from step 1:

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

<root xmlns="urn:schema-namespace">

  <!-- write here your Xml -->

</root>

Summary

Lets sum up, I showed a very helpful tip of how to enable Xml
schema validation and intellisense through Visual Studio. I use this
feature a lot when I write Xml files which have schema files.
I hope this tip will help you too.

Project “Astoria” Went Offline

Project “Astoria” Went Offline

As I said at the end of the
session I had this week,
the “Astoria" team is working 
on an offline feature for
ADO.NET Data Services.
Today I saw in Guy Burstein’s
blog that an alpha preview of the offline feature was
released as a stand alone preview.

What it’s all about?

“Astoria Offline” is an end-to-end solution for the building of offline
applications that use data from data services, either within an intranet
or across the internet. It’s based upon the collaboration of
ADO.NET Data Services, Entity Framework and the Microsoft Sync
Framework. By using this feature you’ll be able to build the client and
server sides of the system, to create sync-capable databases or adjust
existing database schemas with sync capabilities. Using “Astoria Offline”
allows the consumers of the data service to synchronize against it,
while maintaining the decoupling a service provides, and while allowing
the service to enforce business rules before the data makes it into the
database.

Where to get it?

Adding Many to Many Relationship between Entities

Adding Many to Many Relationship between Entities

A friend of mine asked me
this week a question regarding
link tables in Entity Framework.
In their system they have a
link table of many to many
relationship between more then
two tables.

The Problem

We have a link table with more then two reference keys but
Entity Framework map this table into an entity instead of an
entity association of many to many. For example I have the following
hypothetic database which has a relation of many to many between
persons, companies and countries:

The result of the mapping after using the Entity Framework Wizard will
look like:

The problem with this mapping is when we want to query the entities,
we use extra joins with CountriesToPersonsToCompanies table which we
would like to spare. For the propose of inserts and updates it will give a
better performance using the two one to many relationship way but for
queries we will pay in performance.

Modeling two one to many relationship as Many to Many relationship

We would like to keep the two one to many relationship for the CUD
operations but for queries we would like to build a many to many
relationship.
How to do that?
We will build a new EntityType for every new association that we need that
will hold the primary keys of the table we want to use. Also we will need a
new EntitySet for the created EntityType which will use a DefiningQuery to
retrieve the data. Then in the designer we will build a new association of
many to many and map it to the new created EntitySet.

Step 1 – Create the EntityType 

In order to create a new EntityType we will need to edit the SSDL manually.
Open the SSDL in Xml editor mode and create a new EntityType for each
many to many relation that you need. The following Xml fragment is
an example of an EntityType for the given problem:

<EntityType Name="CompaniesToCountries">

  <Key>

    <PropertyRef Name="CompanyID" />

    <PropertyRef Name="CountryID" />

  </Key>

  <Property Name="CompanyID" Type="int" Nullable="false" />

  <Property Name="CountryID" Type="int" Nullable="false" />

</EntityType>

Step 2 – Create the EntitySet

After we defined the EntityType we will create a new EntitySet for that
type. The EntitySet will be created using a DefiningQuery element which
is a read only view definition for that set. The following Xml fragment is
an example of an EntitySet for the given problem:

<EntitySet Name="CompaniesToCountriesSet" EntityType="Self.CompaniesToCountries">

  <DefiningQuery>

    SELECT CompanyID, CountryID FROM CountriesToPersonsToCompanies

  </DefiningQuery>

</EntitySet>

Step 3 – Adding Many to Many Association in the Designer

When we have the EntityType and EntitySet we can go to the designer
and create a new many to many association to map to the created
EntitySet.
From the designer surface press right mouse key and choose
Add –> Association.

In the Add Association form create the many to many relation between
the two entities.

Then in the Mapping Details View map the new association to the
new EntitySet that we created earlier.

After that we will get the following model which has a new many to
many relationship between Country entity and Company entity.

Step 4 – Check the relation

After building the new relationship we need to check it.
The following code will use the new many to many relationship
that we created:

using (EntityFrameworkExampleEntities context = new EntityFrameworkExampleEntities())

{

    var company = context.Companies.Include("Country").First();

    foreach (var country in company.Country)

    {

        Console.WriteLine(country.CountryName);

    }

    Console.ReadLine();

}

Step 5 – Do steps 1-4 for each many to many relationship that you want to add

Summary

Lets sum up, I showed how to add a new many to many relationship
in order to retrieve data when Entity Framework didn’t mapped my
relationship to a many to many relationship. One drawback of this
solution that using the Update Model from Database feature will
erase all the changes that we did because we edited the schema manually.

Mapping Stored Procedures to ObjectContext Methods

Mapping Stored Procedures to ObjectContext Methods

In the session I had on
Sunday I showed a simple
scenario of how to map stored
procedures to ObjectContext
methods in Entity Framework. In this
post I’ll show the same example that I used in the session.

The Stored Procedure

In the example I’m going to use a simple stored procedure which
returns all the courses from the database ordered by their title.
The stored procedure code:

CREATE PROCEDURE dbo.GetCoursesOrderByTitle

AS

BEGIN

    SET NOCOUNT ON 

    SELECT CourseID, Title, Days, [Time], Location, Credits

    FROM Course

    ORDER BY Title ASC

END

How to Map a Stored Procedure to a ObjectContext Method?

Step 1

The first thing to do is to choose the stored procedure in the
Entity Framework Wizard. You can do that whenever you create the
model or when you use the Update Model from Database feature of the
designer. After that the stored procedure will appear in the Model Browser
like in the following figure:
 
Step 2

When we have the stored procedure mapped in the SSDL (step 1)
we can use the designer Add -> Function Import in order to map
it to our conceptual model. The following figure shows the designer
Add –> Function Import:

Step 3

Pressing the Function Import button will open the Function Import
form. In that form you will choose the stored procedure name, the
name to import to the ObjectContext (method name) and the return type:

As you can see you can map the method to return entities from the
conceptual model. One drawback is that the Scalars mapping isn’t working
in V1 of Entity Framework. Pressing OK will create the method on the
ObjectContext and you’ll be able to see it in the Model Browser like in the
next figure:

Using Mapped Stored Procedure

The following code shows how to use the mapped stored
procedure through the custom ObjectContext that I use:

using (SchoolEntities context = new SchoolEntities())

{

    var courses = context.GetCoursesOrderByTitle();

    foreach (var course in courses)

    {

        Console.WriteLine(course.Title);

    }

    Console.ReadLine();

}

and the output:
 

Pay attention!
When you are using a stored procedure from the ObjectContext it
return an ObjectResult<T> which can be read only once (behind
the scenes a DataReader is returned). If you would like to use it more
then once you can transform the result to a list for example.

Summary

Lets sum up, I showed how to map a simple stored procedure to a method
on the ObjectContext of Entity Framework. Entity Framework support even
more sophisticated stored procedures then the one I showed. You can also map
stored procedures to replace the default CUD operations of entities which isn’t
shown in the post.

Entity Splitting in Entity Framework

Entity Splitting in Entity Framework

One of the mapping scenarios
that I talked about in the
session at WDC this week
but I didn’t show an example
is entity splitting in Entity
Framework. This post will explain
what is entity splitting and how to implement this mapping scenario
in Entity Framework.

What is Entity Splitting?

Entity splitting is a scenario that happen when our entity is constructed
from many tables in the database. This happens for example when we don’t
want duplications in our database and use lookup tables instead which our
table has a reference key to those tables. More scenarios can be that we
decide to split our entity representation in the database.
When we have such scenarios the wizard of Entity Framework don’t know
that we split our entity and we have to map the entity to more then
one table.

Splitting to Two Tables Example

In the example I have a database that looks like this:

As you can see I split the employee entity to two tables: Employees and
Address. The two tables have the same primary key which is EmployeeID.
When I use the Entity Framework wizard and construct my model
the result will be:

I want to have a single employee entity which contains its address.
How can I do that?
The answer is easy. I’ll map Employee entity to two tables instead of
one and drop the Address entity.

Step 1

Cut and paste the address details (City, Street and ZipCode) to the
Employee entity.

Step 2

Add a second mapping to the Employee entity. The mapping should be
to the Address table. When you do that Entity Framework is smart enough
to map the properties to their database fields.
The result should look like:

Step 3

Remove the Address entity from the model.

Step 4

Test the result.
I use the following code to test the model:

using (EntityFrameworkExampleEntities context = new EntityFrameworkExampleEntities())

{

    var employee = new Employee

    {

        EmployeeType = "Developer",

        City = "Tel Aviv",

        EmployeeFirstName = "Dan",

        EmployeeLastName = "Ronen"

    };

    context.AddToEmployeeSet(employee);

    context.SaveChanges();

    var newEmployee = context.EmployeeSet.First(e => e.City.Equals("Tel Aviv"));

    Console.WriteLine("{0} {1}", newEmployee.EmployeeFirstName, newEmployee.EmployeeLastName);

}

Pay Attention!
In situations such as more then one table entity, we have table joins
whenever we retrieve the data and multiple updates whenever we use
CUD operations. If in your situation you don’t need the address properties
every time you retrieve an employee you should use other mapping scenarios
such as the navigation property that was constructed in the model in the
first place.

Summary

Lets sum up, in the post I showed how to map a split entity into a
single Entity Framework entity. Also you should always consider whether
to use entity splitting or to use other mapping scenarios because of
the performance impact of multiple joins.

Start Thinking with ADO.NET 3.5 Slide Deck

Start Thinking with ADO.NET 3.5 Slide Deck

Yesterday I had an ADO.NET 
3.5 session in the
The Israeli Web Development
Community (WDC). The session agenda:

• Entity Framework
• ADO.NET Data Services
• Datasets Enhancements 

As promised, the slide deck and demos can be downloaded from here.
I want to thank Noam King for the opportunity to lecture in WDC. it was a
pleasure. Also, I want to thank all the participants of the session.
I really had a good time presenting the frameworks. Also, there were a lot of good
questions in the session that made it even more enjoyable.

P.S. – a backup of the database that I used in the demos is located in the
solution root.
Enjoy.