Julie Lerman The Data Farm jlermanthedatafarmcom DTL312 Julie Lerman MentorConsultant jlermanthedatafarmcom thedatafarmcomblog LearnEntityFrameworkcom 10 Tips amp Tricks Solving Pain Points ID: 617862
Download Presentation The PPT/PDF document "The ADO.NET Entity Framework: Tips &..." is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1Slide2
The ADO.NET Entity Framework: Tips & Tricks
Julie
Lerman
The Data Farm
jlerman@thedatafarm.com
DTL312 Slide3
Julie Lerman
Mentor/Consultant
jlerman@thedatafarm.com
thedatafarm.com/blogLearnEntityFramework.comSlide4
10 Tips & Tricks
Solving Pain Points
Foreign Keys: Reading
: Writing : Set Default ValueImprove Inefficient Store Queries
Use Random Results from Stored Procedures
Use
ObjectQuery
methods with L2E
Using EF More Effectively
Create Generic Queries
Find Exception Handling Details
Encapsulate Query Execution
Improve Query PerformanceSlide5
EF Model Hides Foreign Key
!Slide6
1. Get FK Value
W
hen
No Reference Object Is AvailableSlide7
2. Set Reference
without Ref Object
Order.CustomerReference.EntityKey
=
new EntityKey("
MyEntities.Customers
", "
PersonID
", 1)
from Single EntityKey Property
var
eKeyValues
=
new
KeyValuePair
<string, object>[] {
new KeyValuePair<string, object>(“PropertyA", 12), new KeyValuePair<string, object>(“PropertyB", 103) };EntityKey ekey= new EntityKey(“MyEntities", eKeyValues);instance.EntityRef.EntityKey=ekey;
from Multiple EntityKey PropertiesSlide8
3. Set Default Entity Reference Value
Create Constructor in Entity's Partial Class
C#
Public Customer()
{
CustomerTypeReference.EntityKey
=
new EntityKey("
MyEntities.CustomerTypes
", “
TypeID
", 1)
}
VB
Public Sub New()
CustomerTypeReference.EntityKey = New EntityKey("MyEntities.CustomerTypes", “TypeID", 1)End SubSlide9
Undesirable Store Queries
Resulting T-SQL Query
SELECT [Extent1].[
PersonID
] AS [
PersonID
], ...
WHERE (CAST(CHARINDEX(N'T',
Extent1
].[
LastName
])
AS
int
)) = 1
LINQ to Entities QueryFrom p In context.People Where p.LastName.StartsWith(“K")!Slide10
4. Control Store Query w/ ESQL
Resulting T-SQL
SELECT [Extent1].[
PersonID
] AS [
PersonID
],
WHERE [Extent1].[
LastName
] LIKE 'T%'
Entity SQL
SELECT VALUE p FROM
EFEntities.People
AS p
WHERE
p.LastName
LIKE “T%”
Query Builder Methodscontext.People.Where("it.LastName LIKE 'T%'")Slide11
Bonus Tip! Testing ESQL Expressions
eSql
Blast!
code.msdn.com/
adonetefxSlide12
Stored Procs Return Non-Entity
Requires lots of manual editing of the model, MSL and SSDL to create an entity that matches the return value
!
CREATE PROCEDURE
OrdersBySalesPersonbyDate
AS
SELECT MIN(
Person.FirstName
) as First,
MIN(
Person.LastName
) as Last,
COUNT(
Orderid
) as
OrderCount
FROM …Slide13
5. Use Views in Place of Sprocs
Wizard creates all entity metadata elements
Other benefits
View is composableUse in queries
Change Tracking (use Stored
Procs
for DML)
CREATE VIEW
OrdersBySalesPersonbyDate
AS
SELECT MIN(
Person.FirstName
) as First,
MIN(
Person.LastName
) as Last,
COUNT(
Orderid) as OrderCountFROM …Slide14
ObjectQuery Special Methods in L2E
Include
from p in context.Orders.Include(“Customer”)
select p
OfType
from p in context.People.OfType<Customer>
select p
MergeOption
context.People.MergeOption=MergeOption.NoTracking;
from p in context.People select p;
Execute
???
ToTraceString
???
!Slide15
6. Cast L2E to Get
ObjectQuery
Methods
C#
var
l2eQuery=from c in
context.Customers
select c ;
var
storeSQL
=((ObjectQuery)l2eQuery).ToTraceString();
VB
Dim l2eQuery=From c In
context.Customers
Select c
Dim
storeSQL = CType(l2eQuery, ObjectQuery).ToTraceStringSlide16
Redundant L2E Queries
Country Reference List
List<Country>
GetCountries()
{
return
context.Countries.OrderBy
(c =>
c.Name
)
.ToList(); }
Account Type Reference List
List<
AccountType
>
GetAccountTypes
() { return context.AccountTypes.OrderBy
(a => a.TypeName) .ToList(); }Product Reference List List<Product> GetProducts() { return context.Products.OrderBy(p => p. Name) .ToList(); } !Slide17
7. Build Reusable, Generic Queries
context.GetReferenceList<Country>
context.GetReferenceList(Of AccountType)
context.GetReferenceList<Product>
public static List<
TEntity
>
GetReferenceList
<
TEntity
>
(this ObjectContext context)Slide18
UpdateExceptions Anonymity
Person D
Person C
Person B
Order A
Order D
Order C
Order B
Person A
db Error
?
?
?
!
sSlide19
8.
ObjectStateEntry
& Entity Instance Are
Provided in UpdateExceptionsSlide20
Redundant Code around Queries
!
var
query=from p in
context.People
select p;
try
{
var
people=
query.ToList
(); }
catch (
EntityException
)
{ ... }
Catch (
CommandExecutionException) { ... }var query2=from o in context.Orders select o;try { var orders =query2.ToList(); }catch (EntityException) { ... }Catch (CommandExecutionException
)
{ ... }Slide21
9. Encapsulate Query Execution
Calling Reusable Methods
IEnumerable<Person> L2EQuery = from p in
context.People
select p;
List<Person> people =
dal.ExecuteList
<Person>(L2EQuery);
ObjectQuery
oQuery
=
context.Orders
;
List<Order>
orderList
=
dal.ExecuteList
<Order>(oQuery);Reusable Repository Methodspublic List<TEntity> ExecuteList<TEntity> (ObjectQuery<TEntity> objectQuery)public List<TEntity> ExecuteList<
TEntity
>
(IQueryable<
TEntity
> L2EQuery)
Slide22
Bonus Tip! Pre-Compile L2E Queries
LINQ to Entities
Query
Store
Query
PreCompiled
QuerySlide23
Web
Sites Lose
Pre-Compiled L2E Queries
Short-lived
ObjectContext
used to compile
Pre-Compilation is repeated each time
App process loses benefit of pre-compilation
Big performance loss
!Slide24
10. Cache Pre-Compiled Query Func
Class Level Static Function
static Func<MyEntities, IQueryable<Customer>> customerPCQ;
Class Constructorpublic CustomerProvider()
{
if (customerPCQ == null)
{
customerPCQ = CompiledQuery.Compile<MyEntities,
IQueryable<Customer>>
(ctx => from cust in ctx.Customers
where cust.Orders.Any() select cust
);
}
} Slide25
EF4 Removes Some of the Pain
Solving Pain Points
Foreign Keys: Reading
: Writing : Set Default ValueImprove Inefficient Store Queries
Use Random Results from Stored Procedures
Use
ObjectQuery
methods with L2E
Using EF More Effectively
Create Generic Queries
Find Exception Handling DetailsEncapsulate Query Execution
Improve Query PerformanceSlide26
Contact
Julie Lerman
jlerman@thedatafarm.com
thedatafarm.com/blogLearnEntityFramework.comSlide27
question & answerSlide28
www.microsoft.com/teched
Sessions On-Demand & Community
http://microsoft.com/technet
Resources for IT Professionals
http://microsoft.com/msdn
Resources for Developers
www.microsoft.com/learning
Microsoft Certification and Training
R
esources
www.microsoft.com/learning
Microsoft Certification & Training Resources
Resources
Required Slide
Speakers,
TechEd 2009 is not producing
a DVD. Please announce that
attendees can
access session
recordings at TechEd Online. Slide29
Track Resources
Visit the DPR TLC for a chance to win a copy of Visual Studio Team Suite. Daily drawing occurs every day in the TLC at 4:15pm. Stop by for a raffle ticket
http://www.microsoft.com/visualstudio
http://www.microsoft.com/visualstudio/en-us/products/teamsystem/default.mspx
Please visit us in the TLC blue area Slide30
Complete an evaluation on
CommNet
and enter to win!
Required SlideSlide31
©
2009 Microsoft
Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.
The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation.
MICROSOFT
MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.
Required Slide