Those who have not referred the related post of this article can check it out : Entity Framework Posts
There are normally 3 ways to perform query against the Entity Data Model, which are Linq to Entities, Entity SQL and last one is Entity Client.
If you have gone thought the background knowledge of Entity Framework, then now you are ready to write queries. It is good to have background knowledge, since you can grasp easily the future posts along with the current post. Ok so now move forward to write query for Entity Framework and to do that we first need a EDM. Once we have EDM then we write code in a specific syntax, and to help in writing code we have several options. These requirements are explained in detail in below.
EDM Generate Approaches
-
Database First Approach
This approach is useful when the project is already developed, and Entity Framework features is needed to insert in the project. This approach creates the Entity Data Model based on the existing database. All the relations, mappings, constraints and data types are generated and derived from the existing database schema. You can also say that EF does reverse engineering from existing database and generates managed code for us.
When you add new ADO.NET Entity Data Model item, the wizard will ask you whether you want to create EDM from existing database, or with blank database. The first option i.e. create from existing database is called the Database first approach. This is shown in below screen. -
Model First Approach
Model first approach is mostly used when you have not yet created database. EF provides designing surface in which developer can create entities, complex types, can manage relationships among them etc. Once you are done with your model schema then you can generated DDL code from existing EDM.
Lets do that. Start Visual studio, add new project ( for example I am going to create new Library project).
Add new item called ADO.NET Entity Data Model. Select start with blank database at this time.
Press Finish, and this ends with a EF Designer screen as shown below:
Now you can create entities, can assign relationships between them using the toolbar. I am going to create two entities called “Product” and “Category” where Category have many Products relation.
Once you are done with your desired model, then you can generate the database script from the model, for that right click in the designer and select “Generate database from model” option.
Data generation wizard will popup, just provide the connection string, i.e. the destination database connection, and press Next. And you get the below screen:
Press finish, and it creates the sql file in the same project. You have to run the script on the database manually. -
Code First Approach
Code First approach is introduced in Entity Framework 4.0, I will cover that in future blog, since it is the POCO (Plain Old CLR Object) feature introduced in EF 4.0
So now you know various ways to create EDM. Next we look at several syntaxes available to write query against the EDM
Type of Syntaxes:
-
Query-Expression Syntax
The most common syntax used with writing LINQ queries (LINQ to Entities, LINQ to SQL, etc.) is the query-expression syntax. This is simply because it is easier to read and understand. With query-expression syntax, queries are written using operators and functions.
[sourcecode language="csharp" firstline="1" padlinenumbers="true" collapse="false" gutter="true" htmlscript="false" light="false" toolbar="true" wraplines="true"]using (NorthwindEntities db = new NorthwindEntities())
Lets reuse our first project which we have built in last post
Now write the below code:
{
var products = from product in db.Products
select product;
foreach (var product in products)
Console.WriteLine("{0}\t{1}", product.ProductName, product.UnitPrice.Value);
Console.ReadLine();
}[/sourcecode]
The above code will result in the below output:
Lets debug our code from the first line:
[sourcecode language="csharp" firstline="1" padlinenumbers="true" collapse="false" gutter="true" htmlscript="false" light="false" toolbar="true" wraplines="true"]NorthwindEntities db = new NorthwindEntities()[/sourcecode]
Over here db is our Context. This Context contains the EntitySets, which intern contains the Entities. So Products are the EntitySet, which contains the Product Entity
var products = from product in db.Products select product;
This is the example of the Query-Expression. They are similar to the SQL and have a rich functions to perform Projection, Filtering etc. They are known as Query Operators.
Let say if we want to find products which have price greater then 100 and we want to sort them by unit price descending, for that we can write something like:
[sourcecode language="csharp" firstline="1" padlinenumbers="true" collapse="false" gutter="true" htmlscript="false" light="false" toolbar="true" wraplines="true"]using (NorthwindEntities db = new NorthwindEntities())
{
var products = from product in db.Products
where product.UnitPrice > 100
orderby product.UnitPrice descending
select product;
foreach (var product in products)
Console.WriteLine("{0}\t{1}", product.ProductName, product.UnitPrice.Value);
Console.ReadLine();
}[/sourcecode]
The above code results in the following output:
What we have added over here the where condition and the orderby keyword to sort the result which we get in the filter based on the unit price. If you look at the code, first of your though may be that “hey the syntax is similar to T-SQL”. It looks like standard T-SQL but it is not. EF engine process this query.
Normally when I have started programing in the Expression-based syntax, I was wondering why its starting with from"? well that seems strange at first moment. Then I did googling
for that and here is what I found from net:
In earlier development stage of LINQ the query statements were infect begin with SELECT. However, the developers at Microsoft quickly realized that identifying the type that is being used up front enabled IntelliSense to provide meaningful suggestions as the rest of the query was constructed.
According to Microsoft's Y. Alan Griver, who was very involved with the LINQ project during its early stages, the Microsoft developers jokingly referred to this syntax as "Yoda speak" when they altered the syntax for the sake of IntelliSense.
Method-based Syntax
The only difference between method based syntax and query-expression syntax is that this one is not that much easily readable. Apart from this there is no other difference like performance or behavior or feature.
To know how Method-based syntax works you should have a bit knowledge of Lambda expressions, which were introduced in .NET Framework 3.0. Lambda expressions are anonymous functions that can contain expressions and statements. Lambda expressions use the operator =>, which is read as “goes to,” meaning that the left side of the operator specifies any input parameters while the right side of the operator holds the expression or statement block. For example, the following is a simple example of a lambda expression:
x => x * x
This statement can be pronounced as “x goes to x into x.” or “x goes to x square”.
In a lambda expression the => operator has the same precedence as the = assignment.
These Lambdas are used in method-based LINQ queries as arguments to query operator methods. For example below is a method based syntax which contains lambda expressions used as arguments to the Where query operator method.
[sourcecode language="csharp" firstline="1" padlinenumbers="true" collapse="false" gutter="true" htmlscript="false" light="false" toolbar="true" wraplines="true"]var products = db.Products.Where(p => p.UnitPrice > 100);
[/sourcecode]
Querying options
Linq to Entities
All the above examples we have seen are based on the LINQ to Entities. Because we have used the Entities generated by the Entity Framework. Linq to Entites is actually one of the Linq implementation. It uses ObjectQuery to construct and process queries.
What happens under the hood of the process is, first the query has been writing by the query syntax (as discussed above) with the use of the Entities generated by EF or the custom entities which were generated by developer using the EF API. The query has been expressed as ObjectQuery, which later on is converted in to the database provider specific query by Entity Framework. After the query is executed by the provider, the result is return back to the EF, then EF uses Object services to do materialization of the result, to construct the entites and return result back to Context.
Entity SQL
This is more complex to in understand and read compared to Linq to Entities. It is a storage-independent syntax and it looks similar to T-SQL. It was the original language designed to work with the Entity Framework to query against the EDM.
Even though it looks similar to T-SQL, there are some differences between them. Entity SQL supports the inheritance and relationships found in an EDM, whereas in T-SQL you must use joins to work with relationships. Databases do not even have the concept of inheritance; therefore, T-SQL doesn't support that either. Entity SQL does not support the * syntax (for example SELECT *, or COUNT(*)). Another example is that T-SQL allows for ORDER BY clauses to be specified only at the topmost SELECT statement, whereas in Entity SQL you can use a nested ORDER BY expression and be placed anywhere in the query.
A LINQ to Entities query implicitly creates an ObjectQuery. EF’s ObjectServices provides several ways to create an ObjectQuery. In case of creating ObjectQuery directly you need to use then Entity SQL to build the query expression. Lets take a look at the code below:
[sourcecode language="csharp" firstline="1" padlinenumbers="true" collapse="false" gutter="true" htmlscript="false" light="false" toolbar="true" wraplines="true"]NorthwindEntities db = new NorthwindEntities();
string query = "SELECT VALUE p FROM NorthwindEntities.Product AS p";
query += " WHERE p.UnitPrice > 100";
var products = db.CreateQuery<Product>(query);
foreach (var product in products)
Console.WriteLine(product.ProductName);[/sourcecode]
As you can see over here that we have used Context ( NorthwindEntities in our case) to create ObjectQuery. The query is also similar to standard T-SQL like. It is also possible to create parameterized query. Checkout the below example.
[sourcecode language="csharp" firstline="1" padlinenumbers="true" collapse="false" gutter="true" htmlscript="false" light="false" toolbar="true" wraplines="true"]string query = "SELECT VALUE p FROM NorthwindEntities.Product AS p";
query += " WHERE p.UnitPrice > @unitPrice";
var products = db.CreateQuery<Product>(query);
products.Parameters.Add(new System.Data.Objects.ObjectParameter("unitPrice",100);
foreach (var product in products)
Console.WriteLine(product.ProductName); [/sourcecode]
In above code we have added parameter for Unit Price.
EntityClient
EntityClient is different from LINQ to Entities and Object Services because it does not materialize objects. Instead, it streams data back to the requesting application as rows and columns in an EntityDataReader, which implements DbDataReader. The EntityClient does not have its own language, so it uses the Entity SQL language to create its syntax, execute commands against an entity model. It has similar methods and properties like ADO.NET including connections, commands, parameters, and transactions. Let check out the example of Entity Client code:
[sourcecode language="csharp" firstline="1" padlinenumbers="true" collapse="false" gutter="true" htmlscript="false" light="false" toolbar="true" wraplines="true"]using (EntityConnection con = new EntityConnection("name=NorthwindEntities"))
{
con.Open();
string query = "SELECT VALUE p FROM NorthwindEntities.Product AS p";
query += " WHERE p.UnitPrice > 100";
EntityCommand cmd = con.CreateCommand();
cmd.CommandText = query;
using (EntityDataReader dr = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
{
while (dr.Read())
{
var firstname = dr.GetString(1);
var lastname = dr.GetString(2);
var title = dr.GetString(3);
Console.WriteLine("{0} {1} {2}",
title.Trim(), firstname.Trim(), lastname);
}
}
con.Close();
Console.ReadKey();
}
[/sourcecode]
EntityConnection can accept EDM connection string or MetaDataWorkSpace.
No comments:
Post a Comment