Entity Framework 4 in action Reading Notes-Chapter 4: querying using LINQ to entities: filter data

ArticleDirectory

• 4.1.1 Association Filtering
• 4.1.2 paging results
• 4.1.3 retrieve an object
• 4.14 dynamically create a query

All examples in this chapter are based on the ordeit structure in Chapter 2.

4.1 filter data

Basic Requirement: query the order based on the delivery city.

Solution: Use the where method of LINQ to add the filter function. BelowCodeQuery all orders delivered to New York.

 
VaROrders =FromOInCTX. OrdersWhereO. shippingaddress. City ="New York"SelectO;
 

New demand: Query orders in multiple delivery cities.

The following code queries all orders delivered to New York and Seattle.

VaROrders =FromOInCTX. OrdersWhereO. shippingaddress. City ="New York"| O. shippingaddress. City ="Seattle"SelectO;
 

It is not appropriate to query orders in multiple delivery cities according to the above method, because you do not know how many cities to query orders in advance, A better solution is to use the LINQ ins method of LINQ (ef1.0 does not support the contains method, and ef4.0 supports the contains method ).

VaRCities =New[] {"New York","Seattle"};VaROrders =FromOInCTX. OrdersWhereCities. Contains (O. shippingaddress. City)SelectO;
 

4.1.1 Association Filtering

When queries involving more than one object are involved, you must use the navigation property to navigate to the model.

Single Association Filtering

Basic Requirement: Query orders with the customer's bill address city New York.

VaROrders =FromOInCTX. OrdersWhereO. Customer. billingaddress. City ="New York"SelectO;
 

Set Association Filtering

Basic Requirement: Query all orders that contain the specified product brand.

Solution: the details of each order must be aggregated. A boolean value is returned to indicate whether at least one product is a specified brand. In this case, use the any method to obtain the result. This method belongs to the Collection family. It accepts a Lambda expression to indicate that the condition meets at least once.

 
VaROrders =FromOInCTX. OrdersWhereO. orderdetails. Any (D => D. Product. Brand ="Mybrand")SelectO;
 

Basic Requirement: Query all orders without discounts.

Solution: there is another set of methods: All. It allows you to specify a condition to ensure that all phases in the query set meet this condition. In this example, the condition is that the discount attribute of each detail is0.

 
VaROrders =FromOInCTX. OrdersWhereO. orderdetails. All (D => D. Discount = 0)SelectO;
 

Basic Requirement: query the order with the total discount price exceeding a certain amount ($5 in this example. When sum is used to execute the computation, the query becomes very simple.

 
VaROrders =FromOInCTX. OrdersWhereO. orderdetails. sum (D => D. Discount * D. Quantity)> 5SelectO;
 

Sum is a family of aggregate methods that sum the returned values of input lambda expressions.

In addition, you can write a chain for the LINQ method. first look at the code.

 
VaROrders =FromOInCTX. OrdersWhereO. orderdetails. Select (D => D. Product. productid). Distinct (). Count ()> 2SelectO;
 

Let's see what this query has done. The first method, select, is a projection method used to set the query output. In this example, you only need to extract the productid attribute, so this method returns a list of productid integers. Next, we use the distinct method to remove duplicate productid, and finally use the count method to sum the list of non-repeated integers. The following is a flowchart of the process.

4.1.2 paging results

Basic Requirement: query the customer's latest 15 orders.

Solution: The method to implement this filtering function is take. This method accepts an integer that specifies the number of objects to be retrieved.

VaROrders = (FromOInCTX. OrdersOrderbyO. orderdateSelectO). Take (15 );
 

New Requirement: Although filtering is implemented, users need to obtain so many records each time they query orders, reducing the performance and availability of webpages. In addition, it is unfriendly to process hundreds of orders on a single page, and users need to display them by page.

Solution: LINQ to entities provides a method to work with take: Skip. This method ignores the first N records. Implement paging, use skip to skip the first N records, and use take to retrieve the N records after skipping. The following code snippet skips the first 10 records and retrieves the last 10 records.

 
VaROrders = (FromOInCTX. OrdersOrderbyO. orderidSelectO). Skip (10). Take (10 );
 

4.1.3 retrieve an object

Basic Requirement: After you select an order, you need a form to view details or even modify them. In the orderit model, you must give the order ID to locate the order.

Solution: Return ienumerable <t> for a LINQ query, even if there is only one object. If you know in advance that you want to obtain an object, it is more convenient to directly return it instead of processing the list. First and single can achieve this function.

 
VaROrders = (FromOInCTX. OrdersWhereO. orderid = 1SelectO). First ();
 

 
VaROrders = (FromOInCTX. OrdersWhereO. orderid = 1SelectO). Single ();
 

The result of the above query is the order object. The first method is converted to the top 1 (top is valid in SQL Server, and the provider generates an appropriate equivalent expression for other databases.

If no record is returned for query, the first method generates an invalidoperationexception. You can use try-catch to handle this exception, but it is expensive to handle this exception at runtime. The best choice is to use the firstordefault method, which has the same behavior as the first method, but there is a significant difference if no record is returned. It returns the default value of the query object. Because order is a reference type, if no record is returned, null is returned.

Single can achieve the same goal. The slight difference between first and single forces the database to query only one record. If two records are returned, an exception is thrown. If zero records are returned, an exception is thrown unless singleordefault is used. singleordefault and firstordefault have the same behavior.

4.14 dynamically create a query

Because you want to query bills based on multiple parameters, You need to dynamically create a query based on the parameters you enter. LINQ to entities is simple again, just like normal SQL. First, create an objectquery <t> class instance. Then, the traversal filter determines which filter to apply. If you must apply the filter to the query, connect a new linq method to the objectquery <t> instance and reassign the result to the same instance. Finally, you will get the following dynamic query.

VaRDate =Datetime. Today;StringCity =Null;VaRResult = CTX. Orders. asqueryable ();If(Date! =Datetime. Minvalue) {result = result. Where (O => O. orderdate <date );}If(!String. Isnullorempty (city) {result = result. Where (O => O. shippingaddress. City = City );}
 

Since the query is executed only when you know the request data, you can connect to many methods you need. Of course, this technology applies to all the LINQ to entities methods, but it may be used only when the filter is applied. Until now, all created queries have returned the complete object. If the database is used, it is similar to the write select * From statement. According to our practice, we usually do not need an entity to disclose all the data, but only a small part. In the next article, we will discuss how to retrieve only the required data.