May 2009 - Posts
Entity SQL User-Defined Functions in Entity Framework 4
Continuing the tour about
the new features in EF4, the
next stop is the new user-defined
function capability of Entity SQL.
Entity SQL User-Defined Functions
There are two kinds of user-defined functions in EF4:
- Functions that are being defined as part of the conceptual model
- Functions that are being defined inline within a query
You should use the Entity SQL user-defined functions when you
want to encapsulate commonly used Entity SQL inside a function.
Entity SQL User-Defined Functions – Conceptual Model
This approach is called Model Defined Functions. The functions
are declared in the conceptual model and not in the storage model
and are written in Entity SQL. In the Schema element of the CSDL
you insert a Function element with the relevant details like:
<Function Name="GetCourseTime" ReturnType="Edm.Int32">
<Parameter Name="Course" Type="SchoolModel.Course" />
<DefiningExpression>
Edm.Hour(Course.CourseDetails.Time)
</DefiningExpression>
</Function>
This function returns the hour which the course is occurring.
DefiningExpression element can hold any valid Entity SQL expression.
You can use the declared function in Entity SQL queries like in the
following code snippet which get the courses that occur after 12:00:
using (var context = new SchoolEntities())
{
var qSql = "SELECT VALUE(c) FROM SchoolEntities.Courses " +
"AS c WHERE SchoolModel.GetCourseTime(c) > 12 ";
var afternoonCourse = context.CreateQuery<Course>(qSql);
foreach (var course in afternoonCourse)
{
Console.WriteLine(course.Title);
}
Console.ReadLine();
}
If you want to use this function in LINQ to Entities you need
to provide a corresponding CLR method. That method needs
to be in the same signature like the declared function. Use the
EdmFunction attribute to “tell” Entity Framework that a mapping exists
between this function and your Model Defined Function. The following
code shows an example of how to do that:
public partial class SchoolEntities
{
[EdmFunction("SchoolModel", "GetCourseTime")]
public int GetCourseTime(Course course)
{
throw new NotSupportedException();
}
}
Pay attention, that you don’t need to implement the function.
Here is the same query from earlier but in LINQ to Entities:
using (var context = new SchoolEntities())
{
var afternoonCourse = from c in context.Courses
where context.GetCourseTime(c) > 12
select c;
foreach (var course in afternoonCourse)
{
Console.WriteLine(course.Title);
}
Console.ReadLine();
}
Entity SQL User-Defined Functions – Inline Functions
You can also declare inline functions inside your queries.
Using this approach is less flexible because you declare the function
to be used inside the current Entity SQL expression you create.
The following code demonstrate the use of user-defined inline functions:
using (var context = new SchoolEntities())
{
string qStr = "USING EF4; " +
"FUNCTION GetCourseCreditBiggerThenThree(c Course) AS " +
"(c.CourseDetails.Credits > 3) " +
"SELECT c.Title " +
"FROM SchoolEntities.Courses AS c " +
"WHERE GetCourseCreditBiggerThenThree(c)";
var query = new ObjectQuery<DbDataRecord>(qStr, context);
foreach (var record in query)
{
Console.WriteLine("{0}", record[0]);
}
Console.ReadLine();
}
What you see is a query that is constructed from a declaration of a
function which returns true if the course has more than 3 credits.
After the declaration I call another Entity SQL statement that uses
the declared function. This is a simple and not so useful function but
you can see how inline functions are built.
Summary
Lets sum up, one of the new features of EF4 is the Entity SQL
user-defined functions. As in databases, we can define our own
functions inside the conceptual model or as inline functions inside
an Entity SQL query. This new feature enable more flexibility to
Entity Framework.
Calling Database Functions in LINQ to Entities in Entity Framework 4
Another new feature in EF4 is
the new SqlFunctions class.
In this post I’ll explain what is
SqlFunctions class and how to use
it in LINQ to Entities queries.
The SqlFunctions Class
The SqlFunctions is a new class in EF4. It contains methods
that expose SQL Server functions to use inside your
LINQ to Entities queries. When you use SqlFunctions methods
the corresponding database functions are being executed. This
feature is specific to SQL Server provider and is located in the
System.Data.Objects.SqlClient. As written in MSDN
in a note about SqlFunctions – “Similar classes that expose
database functions may be available through other providers.”
There are many functions that you can call through the
SqlFunctions such as Average, CharIndex, Count, DateDiff,
Replace and many more. You should explore the members of
SqlFunctions for more details.
Examples
The following code shows how to get the year of a StartDate
field in a Department class:
using (var context = new SchoolEntities())
{
var departments = from d in context.Departments
where SqlFunctions.Year(d.StartDate) == 2009
select d;
Console.WriteLine(((ObjectQuery)departments).ToTraceString());
Console.ReadLine();
foreach (var department in departments)
{
Console.WriteLine(department.StartDate);
}
Console.ReadLine();
}
As you can see I’m calling the ToTraceString in order to see the
query that will be executed in the database. This is the query:
SELECT
[Extent1].[DepartmentID] AS [DepartmentID],
[Extent1].[Name] AS [Name],
[Extent1].[Budget] AS [Budget],
[Extent1].[StartDate] AS [StartDate],
[Extent1].[Administrator] AS [Administrator
FROM [dbo].[Department] AS [Extent1]
WHERE 2009 = (YEAR([Extent1].[StartDate]))
Another example is calculating the budget of the school:
using (var context = new SchoolEntities())
{
var schoolBudget = SqlFunctions.Sum(from d in context.Departments
select d.Budget);
Console.WriteLine(schoolBudget);
Console.ReadLine();
}
This function is also created in the database and this is the
query:
SELECT
[GroupBy1].[A1] AS [C1]
FROM ( SELECT
SUM([Extent1].[Budget]) AS [A1]
FROM [dbo].[Department] AS [Extent1]
) AS [GroupBy1]
Summary
Lets sum up, in the new EF4 you can use the SqlFunctions class
in order to call database functions in LINQ to Entities. This class
is part of SQL Server provider but you should expect similar classes
that expose database functions for other providers in the future.
This is a very helpful tool in the arsenal of tools provided by EF4.
Model First in Entity Framework 4
One of the new features of
EF4 is the ability to generate a
database and the SSDL and MSL that
correspond to a provided model.
In this feature we can start building
our conceptual model first and then generate
all the other parts of the EDM according to it.
In this post I’ll introduce the model first feature.
Generating a Database from a Given Conceptual Model
One thing that was missing (in my opinion) in the first release
of EF was the ability to start from a conceptual model and then
to generate the other parts of the EDM and the database. In the
new Visual Studio 2010 Beta 1 that feature was given. Now we have
a Create Database Wizard to help us generate a database from an
Entity Data Model. We can start in building our model first and then
use that feature to do all the other work. A new menu item – Generate
Database Script from Model was added to the EDM Designer start
the Generate Database Script Wizard. The next figure shows the new
menu item:
How to Generate a Database from a Given Conceptual Model?
The following steps will help you to understand the process of
generating a database from a given Entity Data Model. In the
example I’m using the following model that was created as an
empty model without the EDM Wizard:
Step 1
On the EDM Designer surface click the right mouse button and select
Generate Database Script from Model.
Step 2
In the Generate Database Script Wizard, click the New Connection button
or select an existing connection from the drop-down list. Pay attention that
providing a connection string wouldn’t not initiate the generation of the
database.
Step 3
After choosing a connection string the next step will be to get the
generated data definition language (DDL) script and to save it in a
proper SQL script file.
You can customize the DDL generated to your database of choice.
Pay attention that the default inheritance mapping strategy is
table per type. This can also be overwritten if you choose to.
Step 4
Pressing the Finish button will raise a question whether to overwrite
your existing SSDL and MSL. Choosing Yes will overwrite them and
choosing No will keep your previous data.
When choosing the Yes button the following things happen:
- Generate the store schema definition language (SSDL).
- Generate the mapping specification language (MSL).
- Update the .edmx file with the generated SSDL and MSL.
- Saves the generated data definition language (DDL) in the
location you specified in the Save DDL As text box of the
wizard.
- Add a connection string to your configuration file.
Pay attention that you have to manually run the generated script
file in order to create the database. The wizard process doesn’t do
it for you.
Summary
Lets sum up, in today’s post I showed the new model first feature
of EF4. That feature enables us to start with a model and then to
generate its database. The process of generating the database can
be customized and you are able to create your database of choice.
CodeProject
Pluralize or Singularize Generated Object Names Checkbox in Entity Framework 4
Creating entities with pluralize
or singular
object names was one
of the missing features in EF V1.
Like LINQ to SQL now EF4 also
has a feature that enables to
pluralize or singularize the created objects automatically.
You have the choice whether to enable it or not by a new
checkbox in the EDM Wizard.
The Pluralize or Singularize Generated Object Names Checkbox
The first thing that you notice when you create a new Entity Data
Model is the new Pluralize or Singularize Generated Object Names
checkbox in the EDM Wizard. This checkbox tells EF4 EDM Wizard to
generate the object names with pluralize or singular names.
Now you won’t need to rename most of your entity or entity sets in the
model. Even though, I suggest to check the names that were created
automatically but as you will see in the next figures the new feature has
a nice dictionary. These are some of the entities that were generated by
the wizard:
Very nice!
Summary
Lets sum up, I showed another nice feature of the new EF4 in Visual
studio 2010 beta 1 version. This is a helpful feature but I didn’t fell
out of my feet when I saw it because other frameworks like
LINQ to SQL had it before. It was only a matter of time for EF team to add
that feature.
Complex Type Support in the EDM Designer in Entity Framework 4
Yesterday I installed the new 
Visual Studio 2010 Beta 1. Since
then I’m learning the new features
of .NET 4 and in particularly Entity
Framework 4 (or EF4). In the near
future I’m going to write about the new
features and enhancement made in EF4. In this post I’m revisiting a post
I wrote in the past – Creating Complex Types in Entity Framework – and
show how to use the EDM designer in EF4 to achieve the same
functionality.
What are Complex Types?
As I wrote in the previous post, complex types are a way to encapsulate a
set of entity’s properties inside a structure which isn’t an entity.
You use them to organize properties into structures that make your design
more understandable. For more details about complex types in EF V1 read
my previous post.
Complex Type Support in the EDM Designer
In EF4, complex types creation was made easier. If in EF V1 you needed to edit
the CSDL and MSL manually and after that you couldn’t work with the designer,
in EF4 they are part of the EDM Designer. Moreover, the new and improved
Update Model from Database wouldn’t erase your changes if you’ll decide to
do it manually.
Example
In the example I’m going to build a CourseDetails complex type inside
a Course entity. The following figure is the model before the
complex type changes:
Step 1 – Create Complex Type
In the designer click right mouse button and in the Add menu item select the
new Complex Type menu item to create the complex type:
Another way to do it is from the Model Browser to use the new menu
item – Create Complex Type.
Step 2 – Rename Complex Type
In the Model Browser, rename the new created complex type to the
desired name. I rename it to CourseDetails:
Step 3 – Add Properties
Add the relevant properties to the complex type. You can copy & paste
the properties from your entities to the Model Browser or in the Model
Browser use the Add menu item to add your properties manually. The
following figure shows the Model Browser after the change:
Step 4 – Add Complex Type to Entity
After you have the new complex type now it is the time to add it to your
entity. In the designer use the Add –> Complex Property on the entity
to add a new complex property to the entity.
Give that property a meaningful name and in it’s properties menu attach
it to the new created complex type’s type.
Step 5 – Map the Complex Type’s Properties
The last step in the process of creating a new complex type is to
map the complex type’s properties. In the Mapping Details menu
map every complex type property to the relevant field.
Step 6 – Testing
After finishing the previous steps you should test the new complex
type. The following code shows a small program I wrote to check that
I can add and retrieve the data of the new created complex type:
class Program
{
static void Main(string[] args)
{
using (var context = new SchoolEntities())
{
var course = new Course
{
Department = context.Departments.FirstOrDefault(),
Title = "My New Course",
CourseDetails = new CourseDetails
{
Credits = 4,
Days = "MF",
Location = "Class A",
Time = DateTime.Now
}
};
context.AddToCourses(course);
context.SaveChanges();
var newCourse = (from c in context.Courses
where c.Title.Equals("My New Course")
select c).FirstOrDefault();
Console.WriteLine("{0} {1}", newCourse.Title, newCourse.CourseDetails.Time);
Console.ReadLine();
}
}
}
and the output of the test run:
Summary
Lets sum up, in today’s post I revisited an older post about EF V1’s complex type
support. I showed the new designer enhancement for complex types in EF4 and
showed an example of how to use it. You can expect more posts about EF4 in the
near future.
Making Cross-Domain Ajax Requests for a Data Service Revisited
A month ago I released the post
Making Cross-Domain Ajax
Requests for a Data Service.
The post is about a better
solution to the problem.
Problem Revisited
In my previous post, I mentioned the problem of making a cross-domain Ajax
requests to a service that isn’t located in the application domain. I also offered a
solution of creating a cross-domain proxy service that will act as an
intermediary between your application and the data service. A main problem in
that solution was the using a traditional SOAP-based WCF service as an interface
to a data service.
More Elegant Solution
Meanwhile, I found a post written by Tom Laird-McConnell about
creating an ADO.NET data service proxy as workaround for
Silverlight/ADO.NET cross domain issue which gives a solution to the
cross-domain problem of Silverlight with a chaining of data services.
I tried to impose the same solution with Ajax calls as well and it works
perfectly.
Summary
Lets sum up, I encourage you to read Tom’s post and to use his solution also
with Ajax calls to a cross-domain data services. I uploaded my previous
solution with Tom’s suggested solution. You can download it from here.
Setting an EntityReference Using an EntityKey in Entity Framework
Yesterday I got a question
in my blog concerning the
ability to set a reference to
an entity in Entity Framework
by using a foreign key that exists
in my hand. The answer is described
in this post.
Setting an EntityReference Using an EntityKey
Sometimes you want to set an EntityReference but you don’t have
that entity in the ObjectContext but you have its key. One solution
for this problem is to load the entity and then to set the EntityReference.
This will make an unwanted roundtrip to the database which we would
like to avoid. The second solution is to create the EntityReference
using only its EntityKey. This solution will allow to create a foreign key
for an entity without having the related data in the ObjectContext.
One drawback for this solution is that you must know the entity key or else
you’ll get an exception.
The Solution
The following example shows how to set an EntityReference using
an EntityKey:
Course course = new Course();
course.DepartmentReference.EntityKey =
new EntityKey("SchoolEntities.Departments", "DepartmentID", 1);
In the example I create a new course and I know that the engineering
department has a DepartmentID of 1. I make a entity reference to that
department through the created DepartmentReference property using
its EntityKey.
Summary
Let sum up, in the post I showed a simple solution that enables to set a
navigation property by using an entity key. You must pay attention that
setting an un-existing EntityKey will raise an exception. This solution
can decrease roundtrips to the database and increase the performance
for your application.
CodeProject
What to be Expecting of Entity Framework in .NET 4
The ADO.NET team started 
to release a series of posts
that describe the primary
scenarios and patterns they have
been working on throughout the
new release of Entity Framework
in .NET 4.0. The primary scenarios and
patterns you should be expecting:
Development Approaches
- Model First development –
Enables the developer to start develop the model and then
have T-SQL and customized code generated from that model. - Enable testing applications with Entity Framework –
A new interface was added, along with guidance, that enables better
testability of applications that use the Entity Framework.
Architectural Concerns
- Persistence Ignorance –
Enabling developers to use their own classes without the need to introduce
interfaces or other elements specific to the Entity Framework. - Applications Patterns –
Discussing patterns like the Repository and UnitOfWork patterns with
guidance on how to use them with the Entity Framework. - N-Tier applications with the Entity Framework –
Adding API’s and templates that make building N-Tier applications with
the Entity Framework much easier.
Entity Framework Improvements
- Customization of Code Generation –
Integration with the ADO.NET Entity Framework Designer and T4 Templates
in Visual Studio to provide developer controlled code generation. - Small things that make development of applications simpler –
Add things like Pluralization and Singularlization in the model,
lazy loading, and more stored procedure mapping make building
applications that use the Entity Framework much easier. - Customizing Queries –
Support for existing LINQ operators, recognizing a larger set of
patterns with LINQ, writing model defined functions along with the ability
to use these in LINQ, and a number of other ways to create and customize queries. - SQL Generation Readability Improvements –
Improve the readability, along with T-SQL performance optimizations, of the
generated queries. - Bug fixes and more.
Summary
Lets sum up, a lot can be expected of the new Entity Framework release.
The ADO.NET team try to approach a lot of things that raised while developing
with Entity Framework V1. You can read the following posts for more details:
My Next Month Courses Schedule
Next month I’m scheduled for
the following courses:
If you want to participate in one of those courses or more details, you can
contact E4D in the following ways:
- Contact Form in the this link
- Call Michal - 054-5612259
- Call Oranit - 03-6325707
See you there.
The Benefits of Building a Layered Application
During the last two weeks I’m consulting at a customer that built
a very simple application very badly. One of the problems that I found was
the lack of layers separation which made the application very tangled.
One example for that is the calling of stored procedures from UI user
controls. I talked to the managers about that issue and explained the
benefits of building a layered application. This post will explore those
benefits.
Layered Application Pattern
Before talking about the benefits first we need to understand what is
layered application pattern. The pattern is very easy. You separate the
application components to layers. The components in each layer should
be cohesive and should have very close related in their functionality. Each
layer should be loosely coupled to the layers that are below it.
The following figure shows layers:
(the figure is taken form the Enterprise Solution Patterns Using Microsoft .NET
book)
The main subject in layers is dependency management. Every component
in one layer can interact with components in the same layer or components
from lower layers. There are many approaches to layers building but it won’t
be covered in the post. For more details you can read about the subject in
the following books:
- Enterprise Solution Patterns Using Microsoft .NET
- Patterns of Enterprise Application Architecture
The Benefits of Building a Layered Application
- Other applications will be able to reuse the functionality exposed
by your layers.
- You will be able to distribute your layers over multiple physical
tiers. This can make a very good impact on your application by
improving performance (sometimes), scalability and fault
tolerance.
- The maintenance of your application is easier because of the low
coupling between layers.
- Adding more functionality to your application is made easier.
- Layers make your application more testable.
- Building a well formed layers makes the orientation in your
application more easier.
Summary
Lets sum up, the post explained what is the layered application pattern.
It also explored the benefits of building a layered application. Building
layered application can make your life easier. I encourage you to use the pattern.
E4D Learning
In the last two weeks I
’m
working in E4D Solutions
as a senior consultant and
as a .NET instructor.
As part of my position
I’m instructing the following
courses:
If you are interested in one of those courses you can contact
E4D in the following link.
I’ll be happy to see you in the next course.
Why You have to use Coding Conventions
Tomorrow I’m going to 
a client in order to help
in constructing a coding
conventions document for
their project. One question
that I sometimes hear is why
to use coding conventions in
projects? This post will try
to answer the question.
What are Coding Conventions?
Taken from Wikipedia –
“Coding conventions are
a set of guidelines for a
specific programming
language that recommend
programming style, practices and methods for each aspect of a piece
program written in this language. These conventions usually cover
file organization, indentation, comments, declarations, statements,
white space, naming conventions, programming practices and etc.”
Why to use Coding Conventions?
The main reasons to use coding conventions:
- Comprehensible code – Coding conventions enforce coding
standards on the development teams and therefore makes
the code comprehensible to read and understand.
- Avoiding pitfalls – the coding conventions dictate
programming practices that can help to reduce pitfalls that
someone else fell into in the past.
- Help to spread knowledge easily – coding conventions help to
spread the ideas you write in your code more easily to other
developers in the team.
How to Enforce Coding Conventions?
- Code Review Process – during code review you can enforce
the conventions of your company on the developers.
- Code Conventions Tools – there are a lot of tools that can
help you to integrate the coding conventions into the
development process. For example, you can use Microsoft FxCop
to check conventions by creating rules for those conventions or
You can use Code Style Enforcer to checks the code against
a configurable code standard and best practices.
There are more tools out there that can help you to enforce
coding conventions.
- Coding Conventions Documents – you should create your
company’s coding conventions document which every developer
should know. There is a great document that was written by
Juval Lowy that you can download from here which is very
recommend to use. That document can be your building block
for creating your own coding conventions in your company.
Summary
Lets sum up, I think that coding conventions are essential
tool for a quality and successful projects. In the post I wrote
about coding conventions and why you should use them.
I also wrote how you can enforce coding conventions.
What do you think about coding conventions? I’m very interested
to know.