Entity Framework 4.0 Recipes (eBook)

Larry Tenny has more than 20 years experience developing applications using a broad range of development tools primarily targeting the Microsoft platform. He has extensive .NET development experience. He has a Ph.D. in computer science from Indiana University.

Title Page 1
Copyright Page 2
Contents at a Glance 4
Table of Contents 5
About the Authors 30
About the Technical Reviewers 31
Acknowledgments 32
Preface 33
Who This Book Is For 33
What’s in This Book 34
About the Recipes 35
Stuff You Need to Get Started 36
Code Examples 36
The Database 36
Apress Website 37
CHAPTER 1 Getting Started With Entity Framework 38
A Brief Tour of the Entity Framework World 39
Models 39
Terminology 40
Code 41
Visual Studio 2010 41
Using Entity Framework 42
CHAPTER 2 Entity Data Modeling Fundamentals 46
2-1. Creating a Simple Model 46
Problem 46
Solution 46
How It Works 51
2-2. Creating a Model from an Existing Database 53
Problem 53
Solution 53
How It Works 56
2-3. Modeling a Many-to-Many Relationship with No Payload 59
Problem 59
Solution 59
How It Works 60
2-4. Modeling a Many-to-Many Relationship with a Payload 63
Problem 63
Solution 63
How It Works 64
2-5. Modeling a Self-Referencing Relationship 66
Problem 66
Solution 66
How It Works 67
2-6. Splitting an Entity Across Multiple Tables 70
Problem 70
Solution 70
How It Works 72
2-7. Splitting a Table Across Multiple Entities 74
Problem 74
Solution 74
How It Works 77
2-8. Modeling Table per Type Inheritance 79
Problem 79
Solution 79
How It Works 81
2-9. Using Conditions to Filter an ObjectSet 83
Problem 83
Solution 83
How It Works 85
2-10. Modeling Table per Hierarchy Inheritance 86
Problem 86
Solution 86
How It Works 89
2-11. Modeling Is-a and Has-a Relationships Between Two Entities 91
Problem 91
Solution 92
How It Works 93
2-12. Creating, Modifying, and Mapping Complex Types 94
Problem 94
Solution 95
How It Works 96
CHAPTER 3 Querying an Entity Data Model 99
3-1. Executing an SQL Statement 99
Problem 99
Solution 99
How It Works 101
3-2. Returning Objects from a SQL Statement 102
Problem 102
Solution 102
How It Works 103
3-3. Returning Objects from an Entity SQL Statement 104
Problem 104
Solution 104
How It Works 106
3-4. Specifying Fully Qualified Names in Entity SQL 108
Problem 108
Solution 108
How It Works 110
3-5. Finding a Master that Has Detail in a Master-Detail Relationship 110
Problem 110
Solution 110
How It Works 112
3-6. Setting Default Values in a Query 113
Problem 113
Solution 113
How It Works 115
3-7. Returning Multiple Result Sets From a Stored Procedure 116
Problem 116
Solution 116
How It Works 117
3-8. Comparing Against a List of Values 118
Problem 118
Solution 118
How It Works 120
3-9. Building and Executing a Query Against an ObjectSet< T>
Problem 121
Solution 121
How It Works 123
3-10. Returning a Primitive Type From a Query 123
Problem 123
Solution 123
How It Works 125
3-11. Filtering Related Entities 125
Problem 125
Solution 125
How It Works 128
3-12. Applying a Left Outer Join 129
Problem 129
Solution 129
How It Works 131
3-13. Ordering by Derived Types 132
Problem 132
Solution 132
How It Works 133
3-14. Paging and Filtering 134
Problem 134
Solution 134
How It Works 136
3-15. Grouping by Date 137
Problem 137
Solution 137
How It Works 138
3-16. Flattening Query Results 139
Problem 139
Solution 139
How It Works 141
3-17. Grouping by Multiple Properties 141
Problem 141
Solution 142
How It Works 144
3-18. Using Bitwise Operators in a Filter 144
Problem 144
Solution 144
How It Works 147
3-19. Joining on Multiple Columns 147
Problem 147
Solution 147
How It Works 149
CHAPTER 4 Using Entity Framework in ASP.NET 150
4-1. Building a Search Query 150
Problem 150
Solution 150
How It Works 153
4.2. Building CRUD Operations in an ASP.NET Web Page 154
Problem 154
Solution 154
How It Works 159
4-3. Executing Business Logic When Changes Are Saved 159
Problem 159
Solution 159
How It Works 161
4-4. Loading Related Entities 162
Problem 162
Solution 162
How It Works 164
4-5. Searching with QueryExtender 164
Problem 164
Solution 164
How It Works 170
4-6. Retrieving a Derived Type Using an EntityDataSource Control 171
Problem 171
Solution 171
How It Works 174
4-7. Filtering with ASP.NET’s URL Routing 174
Problem 174
Solution 174
How It Works 177
4-8. Building CRUD Operations with an ObjectDataSource Control 178
Problem 178
Solution 178
How It Works 183
4-9. Using Entity Framework With MVC 184
Problem 184
Solution 184
How It Works 189
CHAPTER 5 Loading Entities and Navigation Properties 190
5-1. Loading Related Entities 190
Problem 190
Solution 190
How It Works 193
5-2. Loading a Complete Object Graph 195
Problem 195
Solution 195
How It Works 197
5-3. Loading Navigation Properties on Derived Types 197
Problem 197
Solution 198
How It Works 199
5-4. Using Include() with Other LINQ Query Operators 200
Problem 200
Solution 200
How It Works 201
5-5. Deferred Loading of Related Entities 202
Problem 202
Solution 202
How It Works 204
5-6. Filtering and Ordering Related Entities 204
Problem 204
Solution 205
How It Works 206
5-7. Executing Aggregate Operations on Related Entities 207
Problem 207
Solution 207
How It Works 209
5-8. Testing Whether an Entity Reference or Entity Collection Is Loaded 209
Problem 209
Solution 209
How It Works 211
5-9. Loading Related Entities Explicitly 211
Problem 211
Solution 211
How It Works 213
5-10. Filtering an Eagerly Loaded Entity Collection 215
Problem 215
Solution 215
How It Works 216
5-11. Using Relationship Span 217
Problem 217
Solution 217
How It Works 219
5-12. Modifying Foreign Key Associations 219
Problem 219
Solution 219
How It Works 222
CHAPTER 6 Beyond the Basics with Modeling and Inheritance 224
6-1. Retrieving the Link Table in a Many-to-Many Association 224
Problem 224
Solution 224
How It Works 226
6-2. Exposing a Link Table as an Entity 227
Problem 227
Solution 227
How It Works 230
6-3. Modeling a Many-to-Many, Self-Referencing Relationship 231
Problem 231
Solution 231
How It Works 232
6-4. Modeling a Self-Referencing Relationship Using Table per Hierarchy Inheritance 235
Problem 235
Solution 235
How It Works 237
6-5. Modeling a Self-Referencing Relationship and Retrieving a Complete Hierarchy 239
Problem 239
Solution 239
How It Works 242
6-6. Mapping Null Conditions in Derived Entities 243
Problem 243
Solution 243
How It Works 244
6-7. Modeling Table per Type Inheritance Using a Non-Primary Key Column 246
Problem 246
Solution 246
How It Works 250
6-8. Modeling Nested Table per Hierarchy Inheritance 251
Problem 251
Solution 251
How It Works 253
6-9. Limiting the Values Assigned to a Foreign Key 255
Problem 255
Solution 255
How It Works 257
6-10. Applying Conditions in Table per Type Inheritance 259
Problem 259
Solution 259
How It Works 261
6-11. Creating a Filter on Multiple Criteria 262
Problem 262
Solution 262
How It Works 264
6-12. Using Complex Conditions with Table per Hierarchy Inheritance 267
Problem 267
Solution 268
How It Works 270
6-13. Modeling Table per Concrete Type Inheritance 273
Problem 273
Solution 273
How It Works 275
6-14. Applying Conditions on a Base Entity 277
Problem 277
Solution 277
How It Works 279
6-15. Creating Independent and Foreign Key Associations 281
Problem 281
Solution 281
How It Works 282
6-16. Changing an Independent Association into a Foreign Key Association 282
Problem 282
Solution 283
How It Works 284
CHAPTER 7 Working with Object Services 286
7-1. Dynamically Building a Connection String 286
Problem 286
Solution 286
How It Works 287
7-2. Reading a Model from a Database 288
Problem 288
Solution 288
How It Works 291
7-3. Deploying a Model 292
Problem 292
Solution 292
How It Works 292
7-4. Using the Pluralization Service 293
Problem 293
Solution 293
How It Works 295
7-5. Retrieving Entities from the Object State Manager 296
Problem 296
Solution 296
How It Works 298
7-6. Generating a Model from the Command Line 298
Problem 298
Solution 298
How It Works 299
7-7. Working with Dependent Entities in an Identifying Relationship 299
Problem 299
Solution 299
How It Works 302
7-8. Inserting Entities Using an Object Context 302
Problem 302
Solution 302
How It Works 304
CHAPTER 8 Plain Old CLR Objects 305
8-1. Using POCO 305
Problem 305
Solution 305
How It Works 310
8-2. Loading Related Entities With POCO 310
Problem 310
Solution 310
How It Works 313
8-3. Lazy Loading With POCO 313
Problem 313
Solution 314
How It Works 316
8-4. POCO With Complex Type Properties 317
Problem 317
Solution 317
How It Works 319
8-5. Notifying Entity Framework About Object Changes 320
Problem 320
Solution 320
How It Works 322
8-6. Retrieving the Original (POCO) Object 323
Problem 323
Solution 323
How It Works 325
8-7. Manually Synchronizing the Object Graph and the Object State Manager 326
Problem 326
Solution 326
How It Works 329
8-8. Testing Domain Objects 330
Problem 330
Solution 330
How It Works 338
8-9. Testing a Repository Against a Database 339
Problem 339
Solution 339
How It Works 342
CHAPTER 9 Using the Entity Framework in N-Tier Applications 344
9-1. Deleting an Entity When Disconnected 344
Problem 344
Solution 344
How It Works 347
9-2. Managing Concurrency When Disconnected 348
Problem 348
Solution 348
How It Works 351
9-3. Finding Out What Has Changed 352
Problem 352
Solution 352
How It Works 355
9-4. Using POCO With WCF 356
Problem 356
Solution 356
How It Works 361
9-5. Using Self-Tracking Entities With WCF 362
Problem 362
Solution 362
How It Works 366
9-6. Validating Self-Tracking Entities 367
Problem 367
Solution 367
How It Works 371
9-7. Using Self-Tracking Entities on the Server Side 371
Problem 371
Solution 371
How It Works 377
9-8. Serializing Proxies in a WCF Service 378
Problem 378
Solution 378
How It Works 382
9-9. Serializing Self-Tracking Entities in the ViewState 382
Problem 382
Solution 382
How It Works 386
9-10. Fixing Duplicate References on a WCF Client 387
Problem 387
Solution 387
How It Works 390
CHAPTER 10 Stored Procedures 392
10-1. Returning an Entity Collection 392
Problem 392
Solution 392
How It Works 394
10-2. Returning Output Parameters 395
Problem 395
Solution 395
How It Works 398
10-3. Returning a Scalar Value Result Set 398
Problem 398
Solution 398
How It Works 400
10-4. Returning a Complex Type from a Stored Procedure 400
Problem 400
Solution 400
How It Works 402
10-5. Defining a Custom Function in the Storage Model 403
Problem 403
Solution 403
How It Works 405
10-6. Populating Entities in a Table per Type Inheritance Model 406
Problem 406
Solution 406
How It Works 408
10-7. Populating Entities in a Table per Hierarchy Inheritance Model 409
Problem 409
Solution 409
How It Works 411
10-8. Mapping the Insert, Update, and Delete Actions to Stored Procedures 412
Problem 412
Solution 412
How It Works 414
10-9. Using Stored Procedures for the Insert and Delete Actions in a Many-to-Many Association 415
Problem 415
Solution 416
How It Works 420
10-10. Mapping the Insert, Update, and Delete Actions to Stored Procedures for Table per Hierarchy Inheritance 420
Problems 420
Solution 420
How It Works 424
CHAPTER 11 Functions 426
11-1. Returning a Scalar Value from a Model Defined Function 426
Problem 426
Solution 426
How It Works 429
11-2. Filtering an Entity Collection Using a Model Defined Function 430
Problem 430
Solution 430
How It Works 433
11-3. Returning a Computed Column from a Model Defined Function 434
Problem 434
Solution 434
How It Works 437
11-4. Calling a Model Defined Function from a Model Defined Function 437
Problem 437
Solution 437
How It Works 441
11-5. Returning an Anonymous Type From a Model Difined Function 441
Problem 441
Solution 441
How It Works 444
11-6. Returning a Complex Type From a Model Defined Function 445
Problem 445
Solution 445
How It Works 448
11-7. Returning a Collection of Entity References From a Model Defined Function 448
Problem 448
Solution 448
How It Works 450
11-8. Using Canonical Functions in eSQL 451
Problem 451
Solution 451
How It Works 452
11-9. Using Canonical Functions in LINQ 452
Problem 452
Solution 452
How It Works 454
11-10. Calling Database Functions in eSQL 455
Problem 455
Solution 455
How It Works 457
11-11. Calling Database Functions in LINQ 457
Problem 457
Solution 457
How It Works 458
11-12. Defining Built-in Functions 458
Problem 458
Solution 459
How It Works 461
CHAPTER 12 Customizing Entity Framework Objects 462
12-1. Executing Code When SaveChanges() Is Called 462
Problem 462
Solution 462
How It Works 464
12-2. Validating Property Changes 465
Problem 465
Solution 465
How It Works 467
12-3. Logging Database Connections 468
Problem 468
Solution 468
How It Works 470
12-4. Recalculating a Property Value When an Entity Collection Changes 470
Problem 470
Solution 470
How It Works 472
12-5. Automatically Deleting Related Entities 473
Problem 473
Solution 473
How It Works 476
12-6. Deleting All Related Entities 476
Problem 476
Solution 477
How It Works 480
12-7. Assigning Default Values 480
Problem 480
Solution 480
How It Works 483
12-8. Retrieving the Original Value of a Property 484
Problem 484
Solution 484
How It Works 486
12-9. Retrieving the Original Association for Independent Associations 487
Problem 487
Solution 487
How It Works 490
12-10. Retrieving XML 490
Problem 490
Solution 490
How It Works 493
12-11. Applying Server-Generated Values to Properties 493
Problem 493
Solution 493
How It Works 497
12-12. Validating Entities on SavingChanges 497
Problem 497
Solution 497
How It Works 502
CHAPTER 13 Improving Performance 504
13-1. Optimizing Queries in a Table per Type Inheritance Model 504
Problem 504
Solution 504
How It Works 505
13-2. Retrieving a Single Entity Using an Entity Key 506
Problem 506
Solution 506
How It Works 507
13-3. Retrieving Entities for Read Only 508
Problem 508
Solution 508
How It Works 509
13-4. Improving the Startup Time 510
Problem 510
Solution 510
How It Works 511
13-5. Efficiently Building a Search Query 512
Problem 512
Solution 513
How It Works 514
13-6. Making Change Tracking with POCO Faster 515
Problem 515
Solution 515
How It Works 518
13-7. Compiling LINQ Queries 518
Problem 518
Solution 518
How It Works 521
13-8. Returning Partially Filled Entities 522
Problem 522
Solution 522
How It Works 524
13-9. Moving an Expensive Property to Another Entity 524
Problem 524
Solution 524
How It Works 527
13-10. Avoiding Include 528
Problem 528
Solution 528
How It Works 530
13-11. Improving QueryView Performance 530
Problem 530
Solution 530
How It Works 532
13-12. Generating Proxies Explicitly 533
Problem 533
Solution 533
How It Works 535
13-13. Preventing the Update of All Columns in Self-Tracking Entities 536
Problem 536
Solution 536
How It Works 540
CHAPTER 14 Concurrency 541
14-1. Applying Optimistic Concurrency 541
Problem 541
Solution 541
How It Works 543
14-2. Managing Concurrency When Using Stored Procedures 544
Problem 544
Solution 544
How It Works 548
14-3. Reading Uncommitted Data 548
Problem 548
Solution 548
How It Works 550
14-4. Implementing the “Last Record Wins” Strategy 550
Problem 550
Solution 550
How It Works 552
14-5. Getting Affected Rows from a Stored Procedure 552
Problem 552
Solution 553
How It Works 556
14-6. Optimistic Concurrency with Table Per Type Inheritance 556
Problem 556
Solution 556
How It Works 559
14-7. Generating a Timestamp Column with Model First 559
Problem 559
Solution 559
How It Works 560
CHAPTER 15 Advanced Modeling 561
15-1. Creating an Association on a Derived Entity 561
Problem 561
Solution 561
How It Works 563
15-2. Mapping an Entity to Customized Parts of One or More Tables 564
Problem 564
Solution 564
How It Works 566
15-3. Creating Conditional Associations 568
Problem 568
Solution 568
How It Works 573
15-4. Fabricating Additional Inheritance Hierarchies 574
Problem 574
Solution 574
How It Works 577
15-5. Sharing Audit Fields Across Multiple Entities 579
Problem 579
Solution 579
How It Works 583
15-6. Modeling a Many-to-Many Relationship with Payload 585
Problem 585
Solution 585
How It Works 587
15-7. Mapping a Foreign Key Column to Multiple Associations 588
Problem 588
Solution 589
How It Works 594
15-8. Using Inheritance to Map a Foreign Key Column to Multiple Associations 596
Problem 596
Solution 596
How It Works 599
15-9. Creating Read-only and Computed Properties 600
Problem 600
Solution 600
How It Works 606
15-10. Mapping an Entity to Multiple Tables 608
Problem 608
Solution 608
How It Works 609
15-11. Mapping an Entity to Multiple Entity Sets (MEST) 610
Problem 610
Solution 610
How It Works 615
15-12. Extending Table per Type with Table per Hierarchy 617
Problem 617
Solution 617
How It Works 620
Index 622