Entity Framework 6 Recipes Chinese translation series (19), entityframework

Entity Framework 6 Recipes Chinese translation series (19), entityframework

For the original intention of translation and why I chose Entity Framework 6 Recipes, see the beginning of this series.

3-16 bitwise operations in Filtering

Problem

　　You want to use bitwise operations in the filter conditions of the query.

Solution

Suppose you have an object type, which has an integer attribute that you want to use as a bit identifier. You will use the bit in this attribute to indicate whether a special attribute exists in an object ). For example, if you have a patrons entity that represents the local Gallery, some sponsors donate directly (contribute money), and some volunteers (volunteer) in the gallery ), some serve the board of directors ). Some sponsors provide more than one way to sponsor galleries. A model that contains this entity, as shown in Figure 3-17.

 

Figure 3-17 the object type Patron has a sort sortype attribute, which is used as a set of bitwise identifiers to indicate the Patron sponsorship type

 

We want to filter the query by the sponsorship type provided by the sponsor (patron. We have implemented our requirements by listing 3-34 of the Code.

Code List 3-34.Bitwise operations in queries

1 static void Main (string [] args) 2 {3 RunExample (); 4} 5 6 [Flags] 7 public enum sort sortypes 8 {9 None = 413 ContributesMoney = Volunteers = IsABoardMember =}; 14 15 static void RunExample () 16 {17 using (var context = new EFRecipesEntities () 18 {19 // Delete the previous test data 20 context. database. executeSqlCommand ("delete from chapter3.patron"); 21 // Add the new test data 22 context. patrons. add (new Patron23 {24 Name = "Jill Robert ts", 25 bytes sortype = (int) unsupported sortypes. contributesMoney26}); 27 context. patrons. add (new Patron28 {29 Name = "Ryan Keyes", 30 // note that the OR operator '|' in the bitwise operator uses 31 sort sortype = (int) (sort sortypes. contributesMoney | 32 types sortypes. isABoardMember) 33}); 34 context. patrons. add (new Patron35 {36 Name = "Karen Rosen", 37 sort sortype = (int) Sort sortypes. volunteers38}); 39 context. patrons. add (new Patr On40 {41 Name = "Steven King", 42 sort sortype = (int) (sort sortypes. contributesMoney | 43 types sortypes. volunteers) 44}); 45 context. saveChanges (); 46} 47 48 using (var context = new EFRecipesEntities () 49 {50 Console. writeLine ("Using LINQ... "); 51 var extends SORS = from p in context. patrons52 // note the use of the AND operator '&' in the bitwise operator 53 where (p. unsupported sortype & 54 (int) unsupported sortypes. contributesMoney )! = 055 select p; 56 Console. writeLine ("Patrons who contribute money"); 57 foreach (var partition sor in partition SORS) 58 {59 Console. writeLine ("\ t {0}", using sor. name); 60} 61} 62 63 using (var context = new EFRecipesEntities () 64 {65 Console. writeLine ("\ nUsing Entity SQL... "); 66 var esql = @" select value p from Patrons as p67 where BitWiseAnd (p. extends sortype, @ type) <> 0 "; 68 var extends SORS = (IObjectContextAdapter) context ). objectContext. createQuery <Patron> (esql, 69 new ObjectParameter ("type", (int) unsupported sortypes. contributesMoney); 70 Console. writeLine ("Patrons who contrisponmoney"); 71 foreach (var partition sor in partition SORS) 72 {73 Console. writeLine ("\ t {0}", using sor. name); 74} 75} 76 Console. writeLine ("\ nPress <enter> to continue... "); 77 Console. readLine (); 78}

The output of code list 3-34 is as follows:

Using LINQ...Patrons who contribute moneyJill RobertsRyan KeyesSteven KingUsing Entity SQL...Patrons who contribute moneyJill RobertsRyan KeyesSteven King

Principle

In our model, the entity type Patron packages multiple single-digit identifiers in a single integer attribute. A sponsor can sponsor a gallery in multiple ways. Each sponsorship type is represented by different bits in the sortype attribute. We can create an enum type to represent each sponsorship method. We assign an integer power of 2 to each type as its value. This means that each type has a specific bit in the sortype attribute. An integer occupies 32 bits in C #. The binary value of 2 is 00000000000000000000000000000010. In this example, it indicates Volunteers (Volunteers), and the binary value of 4 is 00000000000000000000000000000100, in this example, it indicates a board member (IsABoardMember )).

When inserting patrons, we assign the sponsorship type to the sponsorship sortype attribute. For sponsors of more than one method (type) Sponsoring gallery, we simply use the OR operator (|) combine different methods.

We use the AND operator (&) to extract the bit of the ContributesMoney (donate money) sponsorship method from the sortype attribute value. If the result is non-zero, the sponsor will have the ContributesMoney identifier. If we want to query sponsors of more than one sponsorship method (type), before we use the AND (&) operator to extract the identification space, use OR to connect all sortypes we are interested in.

The second method demonstrates how to use Entity SQL. We use the BitWiseAnd () function to extract the identification space. Entity SQL supports complete bitwise operation functions.

 

 

3-17 Join)

Problem

　　You want to join two entities with multiple attributes.

Solution

Suppose you have a model 3-18. The Account entity type is one to multiple associated with the Order entity type. Each account may have multiple orders. However, an order can only be associated with one exact account. You want to find all the parcels to the order with the same state as the account's city.

Figure 3-18 a model that contains the Account entity type and the Order entity associated with it

 

This example uses the Code-First method. In Code list 3-35, we create an object type.

Code List 3-35.Entity type Account and Order

 public class Account    {        public Account()        {            Orders = new HashSet<Order>();        }                public int AccountId { get; set; }        public string City { get; set; }        public string State { get; set; }        public virtual ICollection<Order> Orders { get; set; }    } public class Order    {        public int OrderId { get; set; }        public Decimal Amount { get; set; }        public int AccountId { get; set; }        public string ShipCity { get; set; }        public string ShipState { get; set; }        public virtual Account Account { get; set; }    }

Next, the context object is created in Code listing 3-36, which is the entry to access the object framework function using the Code-First method.

Code List 3-36.Context object

 public class EFRecipesEntities : DbContext    {        public EFRecipesEntities()            : base("ConnectionString") {}        public DbSet<Order> Orders { get; set; }        public DbSet<Account> Accounts { get; set; }        protected override void OnModelCreating(DbModelBuilder modelBuilder)        {            modelBuilder.Entity<Account>().ToTable("Chapter3.Account");            modelBuilder.Entity<Order>().ToTable("Chapter3.Order");            base.OnModelCreating(modelBuilder);        }    }

Use code listing 3-37 to search for orders.

Code List 3-37.Use Join to find all the orders delivered to the same City and State as the account.

1 using (var context = new EFRecipesEntities () 2 {3 // Delete the previous test data 4 context. database. executeSqlCommand ("delete from chapter3. [order]"); 5 context. database. executeSqlCommand ("delete from chapter3.account"); 6 // Add new test data 7 var account1 = new Account {City = "Raytown", State = "MO"}; 8 account1.Orders. add (new Order 9 {10 Amount = 223.09 M, 11 ShipCity = "Raytown", 12 ShipState = "MO" 13}); 14 account1.Orders. add (new Order15 {16 Amount = 189.32 M, 17 ShipCity = "Olathe", 18 ShipState = "KS" 19 }); 20 21 var account2 = new Account {City = "Kansas City", State = "MO"}; 22 account2.Orders. add (new Order23 {24 Amount = 99.29 M, 25 ShipCity = "Kansas City", 26 ShipState = "MO" 27 }); 28 29 var account3 = new Account {City = "North Kansas City", State = "MO"}; 30 account3.Orders. add (new Order31 {32 Amount = 102.29 M, 33 ShipCity = "Overland Park", 34 ShipState = "KS" 35}); 36 context. accounts. add (account1); 37 context. accounts. add (account2); 38 context. accounts. add (account3); 39 context. saveChanges (); 40} 41 42 using (var context = new EFRecipesEntities () 43 {44 var orders = from o in context. orders45 join a in context. accounts on46 // use the anonymous type to construct a composite query expression 47 new {Id = o. accountId, City = o. shipCity, State = o. shipState} 48 bytes s49 new {Id =. accountId, City =. city, State =. state} 50 select o; 51 52 Console. writeLine ("Orders shipped to the account's city, state... "); 53 foreach (var order in orders) 54 {55 Console. writeLine ("\ tOrder {0} for {1}", order. accountId. toString (), 56 order. amount. toString (); 57} 58} 59 60 Console. writeLine ("\ nPress <enter> to continue... "); 61 Console. readLine ();

The output of code listing 3-37 is as follows:

Orders shipped to the account's city, state...Order 31 for $223.09Order 32 for $99.29

 

Principle

To solve this problem, you can first find out all accounts, then compare each order in the Orders set, and find the order with the account's state and city. This may be a reasonable solution when there are only a few accounts. However, in general, the best solution is to place such processing on the storage layer because it is more efficient at the storage layer.

At first, the Account and Order are connected through the AccountId attribute. However, in this solution, we create an anonymous type on both sides of the equals clause to form a Join ).When the Join object has more than one attribute, an anonymous structure is required. We need to ensure that the anonymous types on both sides are the same and must have the same attributes and define the order of the attributes.Here, we explicitly create an inner-join between the two tables in the database, which means that because of the connection condition, orders sent to other cities and states will not be included in the results.

 

So far, Chapter 3 ends. In the next article, we will study chapter 4. Thank you for reading and learning.

 

Entity Framework exchange QQ group: 458326058. You are welcome to join us.

Thank you for your continued attention, my blog address: http://www.cnblogs.com/VolcanoCloud/