This article discusses:
- C # And LINQ
- Evolution of LINQ
- Execute SQL query from code
|
This article uses the following technologies: LINQ, C # |
DirectoryLambda expressions Extension Method Anonymous type Implicitly typed Partial Variables Object Initial Value Query expression |
Me
He was a super fan of the connections series, hosted by James Burke in the Discovery Channel. The basic assumption is: how seemingly unrelated discoveries affect other discoveries, which ultimately facilitate modern life. The implication is that if you want to make progress, no progress is achieved in isolation. The same is true for language integration query (LINQ.
To put it simply, LINQ supports a series of language extensions for querying data in a type-safe manner. It will be released in the next version of Visual Studio codenamed "orcas. The data to be queried can be in the form of XML (LINQ to XML), Database (ADO with LINQ enabled.. net, including LINQ to SQL, LINQ to dataset, and LINQ to entities) and objects (LINQ to objects. As shown in figure 1.
Figure 1 LINQ architecture (click the image to get a small view)
Figure 1 LINQ architecture (click the image to get a larger view)
Let's look at some code. In the "orcas" Version C # to be released, the LINQ query may be as follows:
var overdrawnQuery = from account in db.Accounts where account.Balance < 0 select new { account.Name, account.Address };
When you use foreach to traverse the results of this query, each element returned will contain the name and address of an account whose balance is less than 0.
The preceding example shows that the syntax is similar to SQL. Several years ago, Anders hejlsberg (chief designer of C #) and Peter Golde considered extending C # to better integrate data queries. Peter was then C # compiler development director and was studying the possibility of extending the C # compiler, especially the add-ons that support domain-specific language syntaxes such as verifiable SQL. On the other hand, Anders is imagining more in-depth and specific integration. He was developing a set of "sequence operators" that can run on any set of ienumerable implementations and remote type queries that implement iqueryable. Finally, the idea of sequence operators was most supported, and Anders submitted a document on this idea to thinkweek of Bill Gates in early 2004. The feedback fully affirmed this. At the initial stage of design, the simple query syntax is as follows:
sequence<Customer> locals = customers.where(ZipCode == 98112);
In this example, sequence is the alias of ienumerable <t>. The word "where" is a special operator that the compiler can understand. The where operator is a common C # static method that accepts the delegate in the form of predicate (namely, bool Pred <t> (T item. This idea aims to give the editor special knowledge about operators. This will allow the compiler to call the static method correctly and create code to associate the delegate with the expression.
Assume that the preceding example is the ideal query syntax of C. What does the query look like in C #2.0 without any language extensions?
IEnumerable<Customer> locals = EnumerableExtensions.Where(customers, delegate(Customer c) { return c.ZipCode == 98112; });
This code is incredibly lengthy and, worse, requires a very careful study to find the relevant filter (zipcode = 98112 ). This is just a simple example. Imagine how difficult it is to read the code if several filters and projections are used. The lengthy syntax is rooted in the syntax required by the anonymous method. In an ideal query, except for the expressions to be calculated, the expressions do not require any processing. Then, the compiler will try to deduce the context. For example, zipcode actually references zipcode defined on customer. How can this problem be solved? Hard coding of knowledge about specific operators into a language does not satisfy the language design team, so they began to seek alternative syntaxes for anonymous methods. They require that the syntax be extremely concise, but do not require more knowledge than the compiler currently required for anonymous methods. Finally, they invented the lambda expression.
Lambda expressions
Lambda expressions are a language function that is similar to anonymous methods in many aspects. In fact, if Lambda expressions are first introduced into the language, there will be no need for anonymous methods. The basic concept here is that code can be considered as data. In C #1.0, strings, integers, and reference types can usually be passed to methods for operations on those values. Anonymous methods and lambda expressions extend the value range to include code blocks. This concept is common in functional programming.
We can use the above example and use a Lambda expression to replace the anonymous method:
IEnumerable<Customer> locals = EnumerableExtensions.Where(customers, c => c.ZipCode == 91822);
There are several notes. There are many reasons why lambda expressions are concise and concise for beginners. First, the constructor is not introduced using the delegate keyword. Instead, a new operator => is used to notify the compiler that this is not a regular expression. Secondly, the customer type is inferred from usage. In this example, the signature of the where method is as follows:
public static IEnumerable<T> Where<T>( IEnumerable<T> items, Func<T, bool> predicate)
The compiler can infer that "C" refers to the customer. Because the first parameter of the where method is ienumerable <customer>, T must actually be the customer. With this knowledge, the compiler can also verify that customer has a zipcode member. Finally, no specified keyword is returned. In syntax form, the returned Members are omitted, but this is only for syntax convenience. The result of the expression is still treated as the return value.
Like Anonymous methods, lambda expressions also support variable capturing. For example, for a method that contains a Lambda expression within the lambda expression body, you can reference its parameters or local variables:
public IEnumerable<Customer> LocalCusts( IEnumerable<Customer> customers, int zipCode){ return EnumerableExtensions.Where(customers, c => c.ZipCode == zipCode);}
Finally, lambda expressions support more lengthy syntax, allowing you to explicitly specify the type and execute multiple statements. For example:
return EnumerableExtensions.Where(customers, (Customer c) => { int zip = zipCode; return c.ZipCode == zip; });
The good news is that we have taken a big step towards the ideal syntax proposed in the original article, and we can achieve this by leveraging a language feature that normally works beyond the query operator. Let's take a look at our current stage again:
IEnumerable<Customer> locals = EnumerableExtensions.Where(customers, c => c.ZipCode == 91822);
There is an obvious problem here. Currently, the customer must understand this enumerableextensions class, rather than considering the operations that can be performed on the customer. In addition, in the case of multiple operators, users must reverse their thinking to write the correct syntax. For example:
IEnumerable<string> locals = EnumerableExtensions.Select( EnumerableExtensions.Where(customers, c => c.ZipCode == 91822), c => c.Name);
Note that select is an external method, although it runs on the basis of the where method result. The ideal syntax should be similar to the following code:
sequence<Customer> locals = customers.where(ZipCode == 98112).select(Name);
Therefore, is it possible to use the functions of another language to further approach implementing the ideal syntax?
Extension Method
It turns out that a better syntax will appear in the form of a language function called an extension method. The extension method is basically a static method that can be called through the instance syntax. The root cause of the above query problem is that we tried to add the method to ienumerable <t>. However, if we want to add operators such as where and select, all existing and future implementers must implement those methods. Although most of those implementations are the same. In C #, the only way to share the "interface implementation" is to use the static method, which is a successful method for processing the previously used enumerableextensions class.
Suppose we write the where method as an extension method instead. You can rewrite the query as follows:
IEnumerable<Customer> locals = customers.Where(c => c.ZipCode == 91822);
This syntax is almost perfect for this simple query. But what is the true meaning of writing the where method as an extension method? It is actually very simple. Basically, because the signature of the static method is changed, the "this" modifier is added to the first parameter:
public static IEnumerable<T> Where<T>( this IEnumerable<T> items, Func<T, bool> predicate)
In addition, the method must be declared in the static class. A static class can only contain static members and is represented by a static modifier in the class declaration. This is all about its meaning. This statement instructs the compiler to allow where calls with the same syntax as the instance method in any type that implements ienumerable <t>. However, you must be able to access the where method from the current scope. When the include type is in the scope, the method is also in the scope. Therefore, you can use the using command to introduce the extension method into the scope. (For more information, see "extension methods" on the sidebar ".)
Extension Method
Obviously, extension methods help to simplify our query examples, but are these methods a widely used language feature? It turns out that the extension method has multiple purposes. One of the most common purposes is to provide shared interface implementation. For example, assume that you have the following interfaces:
interface IDog{ // Barks for 2 seconds void Bark(); void Bark(int seconds);}
This interface requires that each Implementer should write an implementation that applies to two kinds of overloading. With "orcas" C #, the interface becomes very simple:
interface IDog{ void Bark(int seconds);}
The extension method can be added to another class:
static class DogExtensions{ // Barks for 2 seconds public static void Bark(this IDog dog) { dog.Bark(2); }}
The interface implementer now only needs to implement a single method, but the interface client can freely call any overload.
Close [x]
We now have a very close-to-ideal syntax for writing filter clauses, but is "orcas" C # limited to this? Not totally. Let's make a little extension to the example. Compared with the entire customer object, we only project the customer name. As I mentioned earlier, the ideal syntax should be in the following format:
sequence<string> locals = customers.where(ZipCode == 98112).select(Name);
This code can be rewritten as follows:
IEnumerable<string> locals = customers.Where(c => c.ZipCode == 91822).Select(c => c.Name);
Note that the return type of this query is different. It is ienumerable <string> rather than ienumerable <customer>. This is because only the customer name is returned from the SELECT statement.
This method is indeed effective when projection is just a single field. However, assume that we not only need to return the customer name, but also the customer address. The ideal syntax should be as follows:
locals = customers.where(ZipCode == 98112).select(Name, Address);
Anonymous type
If we want to continue using our existing syntax to return names and addresses, we will soon be faced with problems, that is, there is no type that only contains name and address. Although we can still write this query, this type must be introduced:
class CustomerTuple{ public string Name; public string Address; public CustomerTuple(string name, string address) { this.Name = name; this.Address = address; }}
Then we can use this type, that is, customertuple, to generate the query results.
IEnumerable<CustomerTuple> locals = customers.Where(c => c.ZipCode == 91822) .Select(c => new CustomerTuple(c.Name, c.Address));
It is indeed like a lot of sample code used to project a subset of fields. It is often unclear how to name this type. Is customertuple a good name? What should I do if the name and age are projected? It can also be called mermertuple. Therefore, the problem is that we have sample code and it seems that we cannot find any proper name for the type we created. In addition, many different types may be required, and how to manage these types may soon become a tricky problem.
This is the problem to be solved by the anonymous type. This function allows you to create a structural type without specifying a name. If we re-compile the above query using the anonymous type, the Code is as follows:
locals = customers.Where(c => c.ZipCode == 91822) .Select(c => new { c.Name, c.Address });
This Code implicitly creates a type with the name and address fields:
class { public string Name; public string Address;}
This type cannot be referenced by name because it has no name. When creating an anonymous type, you can explicitly declare the field name. For example, if the field being created is derived from a complex expression, or you do not need a name, you can change the name:
locals = customers.Where(c => c.ZipCode == 91822) .Select(c => new { FullName = c.FirstName + “ “ + c.LastName, HomeAddress = c.Address });
In this case, the generated type has fields named fullname and homeaddress.
In this way, we have taken another step towards the ideal world, but there is still a problem. You will find that the type of the local variable is strategically omitted in any location where the anonymous type is used. Obviously, we cannot declare anonymous names. How do we use them?
Implicitly typed Partial Variables
Another language is called implicit typed local variables (or var for short), which instruct the compiler to deduce the type of local variables. For example:
var integer = 1;
In this example, the integer type is int. Be sure to understand that this is still a strong type. In dynamic languages, the integer type can be changed later. To illustrate this, the following code will not be compiled successfully:
var integer = 1;integer = “hello”;
C # The Compiler reports errors in the second line, indicating that the string cannot be implicitly converted to int.
In the preceding query example, we can write a complete value assignment, as shown below:
var locals = customers .Where(c => c.ZipCode == 91822) .Select(c => new { FullName = c.FirstName + “ “ + c.LastName, HomeAddress = c.Address });
The local variable type eventually becomes ienumerable <?>, "?" Is a type name that cannot be written (because it is anonymous ).
Implicitly typed local variables are only local variables in the method. They cannot exceed the boundary of methods, attributes, indexers, or other blocks because the type cannot be explicitly declared and "Var" is invalid for fields or parameter types.
It turns out that the implicitly typed local variables are very convenient outside the query environment. For example, it helps simplify complex General instantiation:
var customerListLookup = new Dictionary<string, List<Customer>>();
Now we have made good progress in our queries. We are close to the ideal syntax, and we achieved it using the universal language function.
Interestingly, we found that as more and more people use this syntax, there is often a need to allow projection to go beyond the boundary of the method. As we have seen before, this is possible. You only need to call the constructor of the object within the SELECT statement to build the object. However, what happens if the constructor is not used to accurately accept the values you need to set?
Object Initial Value
To solve this problem, the forthcoming "orcas" version provides a C # language function called the initial value of an object. Object initial values can be assigned to multiple attributes or fields in a single expression. For example, the common mode for creating an object is:
Customer customer = new Customer();customer.Name = “Roger”;customer.Address = “1 Wilco Way”;
At this time, the customer does not have constructors that can accept the name and address. However, there are two attributes: Name and address. You can set them after creating an instance. Object initial values allow the following syntax to create the same results:
Customer customer = new Customer() { Name = “Roger”, Address = “1 Wilco Way” };
In our previous customertuple example, we created the customertuple class by calling its constructor. We can also get the same result through the initial object value:
var locals = customers .Where(c => c.ZipCode == 91822) .Select(c => new CustomerTuple { Name = c.Name, Address = c.Address });
Note that the brackets of the constructor can be omitted from the object's initial values. In addition, fields and configurable attributes can be assigned values within the subject of the object's initial value.
We now have a concise syntax for creating a query in C. However, we also have a scalable way to add new operators (distinct, orderby, sum, etc.) by using the extension method and a group of language functions that are very useful ).
The language design team now has several prototype for feedback. Therefore, we have organized an availability study with many participants with C # and SQL experience. Almost all feedback is positive, but something is neglected. Specifically, it is difficult for developers to apply their SQL knowledge, because we believe that the ideal syntax is not very consistent with the specialized technologies they are good.
Query expression
Therefore, the language design team designed a syntax that is more similar to SQL, called a query expression. For example, the query expression for our example can be as follows:
var locals = from c in customers where c.ZipCode == 91822 select new { FullName = c.FirstName + “ “ + c.LastName, HomeAddress = c.Address };
The query expression is based on the functions of the preceding language. In terms of syntax, they are fully converted to the basic syntax we have seen. For example, the preceding query can be directly converted:
var locals = customers .Where(c => c.ZipCode == 91822) .Select(c => new { FullName = c.FirstName + “ “ + c.LastName, HomeAddress = c.Address });
The query expression supports many different "clauses", such as from, where, select, orderby, group by, let, and join. These clauses are first converted to equal operator calls, which are then implemented through extension methods. If the query syntax does not support clauses of necessary operators, the query clause is closely related to the extension method that implements the operators, which facilitates the combination of the two. For example:
var locals = (from c in customers where c.ZipCode == 91822 select new { FullName = c.FirstName + “ “ + c.LastName, HomeAddress = c.Address}) .Count();
In this example, the number of customers living in the 91822 zip code area is queried.
Through this method, we have managed to reach the goal at the beginning at the end (I am always very satisfied with this ). After several years of development, the C # syntax of the next version has tried many new language functions to reach the realm of the original syntax proposed in the winter of 2004. The addition of Query expressions is based on the functions of other languages in the C # version to be released, and facilitates the reading and understanding of many query conditions.