[Programming Entity Framework] Chapter 3rd querying the Entity Data Model (EDM) (ii)

ArticleDirectory

• Why is another query used?
• Entity SQL)
• Parameterized Object Query)
• Query using the LINQ Method
• Use the query constructor and Entity SQL query

Programming Entity FrameworkTranslation index for version 2 Query using object service and Entity SQL

Another method that can be created to replace LINQ to entities is to directly use the EF Object Service (ojbect services), which is in the system. Data. Objects namespace. You can directly create an objectquery that combines the query language EF is similar to the T-SQL to construct a query expression, this query language is Entity SQL.

To understand how it works, follow these steps to modify your example:

1. In Example 3-3CodeReplace the code lines that contain the query by LINQ to entities, or comment out the previous code.

Example 3-3. querying with Entity SQL

VaRQuerystring ="Select value C"+

"From sampleentities. Contacts as C"+

"Where C. firstname = 'rebort '";

Objectquery<Contact> Contacts = context. createquery <Contact> (Querystring );

 

1. Run againProgramThe result is the same as the previous one.

In the first line of the Code, an Entity SQL expression is created. In the second row, an objectquery is created and passed to it to query the expressions used. In this example, the existing code is executed and the query result is returned. If you have organized SQL queries in this way, the Entity SQL syntax used in Example 3-3 is very similar, but not exactly the same.

The returned type of this query is objectquery <contact>, which implements the iqueryable interface. However, as you will learn later in this book, you can convert the iqueryable of LINQ to entities into objectquery and then access these attributes and methods. This means that you can still benefit from these attributes and Methods even if you choose to use LINQ to entities.

Why is another query used?

Why is it necessary to query EDM in other ways than LINQ to entities? Microsoft has no plans to use these two methods to bother you. In fact, Entity SQL has been created before the existence of LINQ, but now they have their own purposes. LINQ is obviously easier to use, because it is a query language that you can use in. net, not just in EF. Its powerful spelling also makes it easier to organize. However, LINQ cannot be used in every scenario. It is part of C # and VB, but it is not part of other. NET languages. In addition, you will learn how to query stream results in datareaders when you do not need specific objects. Only Entity SQL expressions can achieve this. As you will see in Chapter 5th and later, it is more advantageous to create Entity SQL strings in some scenarios. Therefore, even if you use LINQ to entities to complete most queries, you will be prepared when you encounter these rare cases.

Entity SQL)

Entity SQL (esql) is indeed the first syntax used to query objects. LINQ was developed by the VB and C # language groups as language extensions. In the end, it is obvious that LINQ is an excellent addition to EF, which is also the origin of LINQ to entities.

Entity SQL originated from SQL, because it makes sense from what we know. However, because entities are different from Relational Data, Entity SQL is separated from SQL and provides necessary functions for querying EDM.

What is the difference between Entity SQL and T-SQL? The topic in EF is "what is the difference between real SQL and transact-SQL ". It provides a list of differences and explanations for each difference. For example, Entity SQL supports inheritance and relationships in EDM, while T-SQL you must use joins to implement relationships. Relational databases do not even have the concept of inheritance; therefore, T-SQL does not support inheritance.

Take a closer look at the SQL query string written in the previous section. You will notice that like LINQ to entities, it defines the variable C used for query. In LINQ, it is called a control variable, but in Entity SQL, it is only a variable.

Figure 3-6 analyzes query strings except the WHERE clause. Variables are defined using the as keyword and referenced only in the select clause. The value keyword specifies the set of individual items you want to return. Here it is the contact entity.

If you want to select a single type value clause, a single type can be an object, an attribute, a base object set, and a strongly typed object that you want to return. This is shown in the following code snippet:

Select value c from sampleentities. Contacts...

Select value c. firstname from sampleeneities. Contacts...

Select value c. addresses from sampleeneities. Contacts...

If you want to select multiple items, you cannot use value, such:

Select C, C. addresses from sampleentities. contacts ....

Select C. lastname, C. Title from sampleentities. contacts...

If you forget to use value, a strongly typed object will be thanks to a wrapper, which we will discuss immediately. You need to convert the result to the desired type. Otherwise, you will encounter an invalidoperationexception during runtime.

If you include value in multiple items, the entitysqlexception exception will be thrown, telling you the following prompt:

"Select value can have only one expression in the projection list ."

You can select only one expression in the rule list.

It even tells you the row number and column number of the problem. However, unfortunately, because the entitysql string is compiled until it is run, you can only know this problem until then.

Note: Chapter 1 will further investigate EF exceptions.

If there is no value clause, the result is loaded into rows in the table format. You must go deep into rows and columns to obtain data. Similar to LINQ query, you can select from the set. In this query, the set is the entity set contacts, but it is also necessary to specify entitycontainer in the Entity SQL. Again, C is the name of any variable used in the query, representing the contact item in the contacts object set.

The where clause in the SQL entity uses the SQL-like syntax as follows:

Where C. firstname = 'rebort'

Standard Functions of Entity SQL

The Entity SQL language is very strong and provides many functions. Although it cannot overwrite all operators and functions supported by the language. You will see many of them used in this book. You can find all the lists in the Entity SQL document of the msdn library.

Entity SQL supports a very large set of standard functions, which are also required by all data providers. It also allows data providers to include their own special functions. The SQL Server provider of the. NET Framework compiled by Microsoft provides about 75 special functions. When the target database is an SQL Server, you can use it in the real SQL query. Some of them overlap with standard functions. The provider also provides native types and their aspects for specific providers, as well as the internal logic used to map EDM and SQL Server. Other providers written for EDM will have their own list of additional functions and features they support.

Note: The biggest benefit of using EF for queries is that it does not rely on databases. Therefore, you should consider this before using the unique elements of the provider in the Entity SQL query.

Note: If you are familiar with T-SQL, you will be very happy to have a standard function that is trim (), which means you don't have to be silly to use ltrim (rtrim () everywhere.

Parameterized Object Query)

Object Query allows you to create parameterized queries. Similar to other query languages, you can use the @ placeholder in the character and define its value in the parameter.

To use parameterized query, you can add parameters created using the createquery method in objectcontext to the object query, or add them to the object you explicitly instantiate. For example, 3-4. You also need to pass objectcontext as a parameter when querying the instantiated object.

Then you add parameters to the object query before execution. To understand how this works, you can rewrite the previous query to allow dynamic changes to the query. See example 3-4.

Example 3-4. Adding an objectparameter to an objectquery

VaRQstr ="Select value c from sampleentities. Contacts as C"+

"Where C. firstname = @ firstname";

Objectquery<Contact> Contacts =New Objectquery<Contact> (Qstr, context );

Contacts. Parameters. Add (New Objectparameter("Firstname","Robert"));

Note: Many namespaces in the example are not mentioned in the class. Make sure that the correct namespace is added to the top of your code file. For example, for the objectquery class, you need the system. Data. Objects namespace.

Although this looks attractive, you cannot replace attribute names with parameters in query characters. In other words, if you try to create an Entity SQL string select @ myproperty from sampleentities. Contacts as C, then you set the parameter @ myproperty to C. lastname, and esql looks like this:

"Select 'C. lastname' from sampleentities. Contacts as C"

This is invalid esql and an error will be reported. You need to construct esql using String concatenation:

"Select" + _ propname + "from sampleentities. Contacts as C"

Note: For security considerations, you should be very careful about the source of the attribute name. You should not splice it in user input. Think about it if someone executes a query such as "select login, password from contacts.

Usage Query

Up to now, you have seen the LINQ to entities and object service queries written as standard Query expressions. Both LINQ to entitiest and object service provide methods for writing queries, rather than running operators and functions, or strings (in Entity SQL.

Both query languages have the method syntax you can use, but they have different causes. C # and VB implement LINQ at the top layer of the query method. Your LINQ expressions are converted into these query methods, but you can use them directly if you want.

EF directly processes Entity SQL statements. However, the method-based syntax for organizing Entity SQL expressions is available.

Query using the LINQ Method

Although VB and C # understand the LINQ syntax, CLR does not understand it. The first thing that occurs when the compiler compiles a LINQ query is that it converts the query into a set of methods to access the queried set.All standard query operators (where, select, join, etc.) are associated with methods in. net.

You can use these methods to directly write queries, if you prefer. Many developers prefer this, although others prefer the query expression syntax. The msdn document says, "in general, we recommend the query syntax because it is generally simpler and readable. However, the method syntax and query syntax are not essentially different ". Therefore, which type and personal choice is used.

Note: msdn provides a list of LINQ methods and whether they are supported by LINQ to entities. Its topic is "supported and not supported by the LINQ method (LINQ to entities), and its address is the http://msdn.microsoft.com/en-us/library/bb738550.aspx

To compile a method-based query, you must first know the features referenced in. Net 3.5, called the Lambdas expression. Lambda expressions have special syntaxes for inline methods. If you are a beginner in the expressions of LINQ and Lambda and have used anonymous delegation, you will understand some examples.

Let's use the WHERE clause to explore usage methods rather than operators. The standard where clause is like this, where lastname = "Hesse ". The where () method requires lastname = 'hesse' as the parameter. You will write lambda expressions in C # and VB.

Let lambda expressions occupy your mind Lambda expressions are a bit confusing at first, but once you understand them, they will get a perfect understanding and help you write very efficiently and code. It is undeniable that my VB background makes me have little understanding of Lambda, and it would be nice if I have been using C ++ or C # frequently. Some great articles can help you learn more about lambda expressions. For C # developers, Anson Horton has an excellent MSN magazine article "The revolution of LINQ and its impact on C # design" (http://msdn.microsoft.com/en-us/magazine/cc163400.aspx ), it provides a good explanation of Lambda. For VB developers, timonthy ng has a great msdn magazine article "Basic Instinct: lambda expressions" (http://msdn.microsoft.com/en-us/magazine/cc163362.aspx) that parses lambda.

Here we will take a look at the query you used in the previous example. Now you can write it using method-based query. In VB, the expression starts with function, indicating that you are executing the function on the control variable. Then, it assumes the condition. Control variable. In this example, It is C and busy again.

Dim contacts = context. Contacts _

. Where (function (c) C. firstname = "Robert ")

The usage of method-based LINQ to entities query syntax in C # looks very different:

VaR contacts = context. Contacts

. Where (C => C. firstname = "Robert ");

The Lambda expression of C # starts with the specified control variable, followed by => (lambda), followed by the expression, [control variable]. firstname = "Robert ".

Note: In the WHERE clause, a Boolean expression is returned, which is called a predicate ). Yes. All contacts whose expression value is true will be returned for the query.

Try this:

1. Replace an existing query with one method. You will see that smart sensing is very helpful when writing lambda.
2. Press F5 to run the application. The result is the same as the previous one.

Link Method

You can join the LINQ query method to build a more practical expression. This is called a link. To try this, add an orderby method for the previous query. Note that the lambda expression of orderby does not require conditions to determine whether the value is true or false as the where method does. It only needs to return an attribute. See example 3-5.

Example 3-5. Chaining LINQ Methods

VaR contacts = context. Contacts

. Where (c) => C. firstname = "Robert ")

. Orderby (FOO) => Foo. lastname );

Note: When the method signature requires a predicate, like the where method, the expression must return a Boolean value. Otherwise, the lambda expression only needs to be a function, such as the orderby method. In VB, the signatures of all methods are called functions. The C # method specifically mentions the predicates that require the expression to return boolean values. You can view the signatures of various methods of LINQ to entities in the topic "supported and unsupported methods (LINQ to entities)" in the msdn document.

Although you can easily use a variable with the same name in a hybrid method, this variable does not represent the same instance. In the previous LINQ query, I gave different names of variables to highlight how the compiler evaluates the query.

Only one method in the query can be evaluated at a time by LINQ. First, it evaluates context. Contacts. Then it applies the where Method to the result, and finally it applies the orderby Method to the result of the where method. In the where method, C refers to the items returned by context. Contacts. The variable in the orderby method refers to context. Contacts. Where (...) The returned iqueryable.

One evaluation method does not mean that one query is executed at a time. LINQ to entities evaluates a method each time, and then creates an SQL query based on the entire method, unless you use the method to indicate that it must be executed on the client. It does not separate each method.

Here is a T-SQL generated from the previous query:

Select

[Extent1]. [contactid] as [contactid],

[Extent1]. [firstname] as [firstname],

[Extent1]. [lastname] as [lastname],

[Extent1]. [title] as [title],

[Extent1]. [adddate] as [adddate],

[Extent1]. [modifieddate] as [modifieddate]

From [DBO]. [contact] as [extent1]

Where n 'Robert '= [extent1]. [firstname]

Order by [extent1]. [lastname] ASC

 

Use the query constructor and Entity SQL query

Method syntax can also be used in Entity SQL, although there are only a few limited Methods: 13, in fact, there are also where and select. These methods are called query constructor methods. The query construction method is as recommended by their names: use the correct Entity SQL expression to create an object query (objectquery ).

Although the query constructor may look like some LINQ methods, they are definitely different. When you use a query constructor based on a parameter expression (which will include a Lambda expression or an Entity SQL expression that is queried by or using a LINQ), the compiler can identify it.

Note: When learning different query methods, since we have only explored where and select so far, we do not list methods and operators for the moment, in the subsequent sections, We will list more query methods.

Example 3-6 shows the latest query, which uses the Entity SQL as the method parameter.

Example 3-6. Entity SQL Query Builder Method

VaR contacts = context. Contacts

. Where ("it. firstname = 'Robert '")

. Orderby ("it. lastname ");

The most common question when considering these expressions is "where does it come from? ". It is the default alias of the control variable. Because you have to work with other queries, there is no chance to define control variables, although you can define variables for Embedded queries, as shown in the example 3-8.

During debugging, you can check the commandtext attribute of the contacts object query to see that the query constructor has indeed created an Entity SQL statement for you, as shown in Example 3-7. It may be slightly more complex than the query you have written. This is the result that the query constructor must be fluent. In addition, it does not specify the entitycontainer name in the expression. It is unavoidable in constructing an Entity SQL statement.

Example 3-7. the Entity SQL built by the Query Builder Methods

Select value it

From (select value it

From ([contacts]) as it

Where it. firstname = 'Robert ')

As it

Order by it. lastname

There is an interesting difference between creating a query constructor using an object SQL statement and a method using a Lambda expression, that is, the entity expression removes the concerns about syntax differences between VB and C.

Whether you are using a LINQ predicate or an Entity SQL predicate, during the compilation period, EF determines which query compilation path can be selected by checking the predicate.

Control Variable

As you can see in Example 3-8, you can also join the query constructor. EF control variables are the same for all new ojbectquery instances by default. However, when you have an objectquery instance, you can change the name of the control variable by designing the attributes of the name. You can continuously combine the queries shown in Example 3-8.

Example 3-8. Naming a control variable

VaR contactsquery = context. contacts;

Contactsquery. Name = "con ";

VaR contacts = contactsquery. Where ("con. firstname = 'Robert '")

. Orderby ("con. lastname ");

The preceding example demonstrates another feature called composite query. Define a query, and then write the other query based on it. The first query is not executed independently. It will be compiled into the second query contacts. When the contacts query is executed, the Combined Query is compiled by EF and sent to the database.

You can also use a combination of LINQ to entities queries.