slide1 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
The ADO.NET E n tity Framework: Tips & Tricks PowerPoint Presentation
Download Presentation
The ADO.NET E n tity Framework: Tips & Tricks

Loading in 2 Seconds...

play fullscreen
1 / 33

The ADO.NET E n tity Framework: Tips & Tricks - PowerPoint PPT Presentation


  • 208 Views
  • Uploaded on

The ADO.NET E n tity Framework: Tips & Tricks. Shyam Pather (spather@microsoft.com) Development Manager Microsoft Session Code: DTL312. Julie Lerman made these slides. Mentor/Consultant jlerman@thedatafarm.com thedatafarm.com/blog LearnEntityFramework.com. 10 Tips & Tricks .

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'The ADO.NET E n tity Framework: Tips & Tricks' - phuong


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
the ado net e n tity framework tips tricks

The ADO.NET Entity Framework: Tips & Tricks

Shyam Pather (spather@microsoft.com)

Development Manager

Microsoft

Session Code: DTL312

julie lerman made these slides
Julie Lerman made these slides

Mentor/Consultant

jlerman@thedatafarm.com

thedatafarm.com/blog

LearnEntityFramework.com

10 tips tricks
10 Tips & Tricks
  • Solving Pain Points
    • Foreign Keys: Reading
    • : Writing
    • : Set Default Value
    • Improve 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 Performance
2 set reference without ref object
2. Set Reference without Ref Object

from Single EntityKey Property

Order.CustomerReference.EntityKey =

new EntityKey("MyEntities.Customers", "PersonID", 1)

from Multiple EntityKey Properties

vareKeyValues =

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;

3 set default entity reference value
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 Sub
undesirable store queries
Undesirable Store Queries

LINQ to Entities Query

From p In context.PeopleWhere p.LastName.StartsWith(“K")

  • Resulting T-SQL Query
  • SELECT [Extent1].[PersonID] AS [PersonID], ...
  • WHERE (CAST(CHARINDEX(N'T', Extent1].[LastName])
  • AS int)) = 1

!

4 control store query w esql
4. Control Store Query w/ ESQL

Entity SQL

SELECT VALUE p FROM EFEntities.People AS p

WHERE p.LastName LIKE “T%”

Query Builder Methodscontext.People.Where("it.LastName LIKE 'T%'")

  • Resulting T-SQL
  • SELECT [Extent1].[PersonID] AS [PersonID],
  • WHERE [Extent1].[LastName] LIKE 'T%'
bonus tip testing esql expressions
Bonus Tip! Testing ESQL Expressions

eSql Blast!

code.msdn.com/adonetefx

stored procs return non entity
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 …

!

5 use views in place of sprocs
5. Use Views in Place of Sprocs

CREATE VIEW OrdersBySalesPersonbyDate

AS

SELECT MIN(Person.FirstName) as First,

MIN(Person.LastName) as Last,

COUNT(Orderid) as OrderCount

FROM …

  • Wizard creates all entity metadata elements
  • Other benefits
    • View is composable
    • Use in queries
    • Change Tracking (use Stored Procs for DML)
objectquery special methods in l2e
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

???

!

6 cast l2e to get objectquery methods
6. Cast L2E to Get ObjectQuery Methods
  • C#
  • var l2eQuery=from c in context.Customers select c ;
  • varstoreSQL=((ObjectQuery)l2eQuery).ToTraceString();
  • VB
  • Dim l2eQuery=From c In context.Customers Select c
  • Dim storeSQL = CType(l2eQuery, ObjectQuery).ToTraceString
redundant l2e queries
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(); }

!

7 build reusable generic queries
7. Build Reusable, Generic Queries

public static List<TEntity> GetReferenceList<TEntity>

(this ObjectContext context)

context.GetReferenceList<Country>

context.GetReferenceList(Of AccountType)

context.GetReferenceList<Product>

updateexceptions anonymity
UpdateExceptions Anonymity

s

Order B

?

Person A

Person B

Order A

db Error

?

Person D

Order C

?

Order D

Person C

!

redundant code around queries
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)

{ ... }

!

9 encapsulate query execution
9. Encapsulate Query Execution

Reusable Repository Methods

public List<TEntity> ExecuteList<TEntity>

(ObjectQuery<TEntity> objectQuery)

public List<TEntity> ExecuteList<TEntity>

(IQueryable<TEntity> L2EQuery)

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);

bonus tip pre compile l2e queries
Bonus Tip! Pre-Compile L2E Queries

PreCompiled Query

LINQ to Entities

Query

Store

Query

web sites lose pre compiled l2e queries
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

!

10 cache pre compiled q u ery func
10. Cache Pre-Compiled Query Func

Class Level Static Function

static Func<MyEntities, IQueryable<Customer>> customerPCQ;

Class Constructor

public CustomerProvider()

{

if (customerPCQ == null)

{

customerPCQ = CompiledQuery.Compile<MyEntities,

IQueryable<Customer>>

(ctx => from cust in ctx.Customers

where cust.Orders.Any()

select cust

);

}

}

ef4 removes some of the pain
EF4 Removes Some of the Pain
  • Solving Pain Points
    • Foreign Keys: Reading
    • : Writing
    • : Set Default Value
    • Improve 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 Performance
julie lerman s contact info again
Julie Lerman's Contact Info Again

Julie Lerman

jlerman@thedatafarm.com

thedatafarm.com/blog

LearnEntityFramework.com

my contact info
My Contact Info

spather@microsoft.com

resources

Required Slide

Speakers,

TechEd 2009 is not producing

a DVD. Please announce that

attendees can access session

recordings from Tech-Ed website. These will only be available after the event.

Resources

Tech·Ed Africa 2009 sessions will be made available for download the week after the event from: www.tech-ed.co.za

  • www.microsoft.com/teched

International Content & Community

  • www.microsoft.com/learning
  • Microsoft Certification & Training Resources
  • http://microsoft.com/technet
    • Resources for IT Professionals
  • http://microsoft.com/msdn

Resources for Developers

related content

Required Slide

Speakers,

please list the Breakout Sessions, TLC Interactive Theaters and Labs that are related to your session. Any queries, please check with your Track Owner.

Related Content

Sessions:

DTL402: The ADO.NET Entity Framework 4

DTL208: An Introduction to the ADO.NET Data Services Framework v1.5

DTL401: How LINQ Works: A Deep Dive into the Microsoft Visual Basic and C# Implementations

Whiteboard Sessions:

WTB211: A Strategic Comparison of Data Access Technologies from Microsoft

Hotlabs:

DTL20H: Application Development with the ADO.NET Entity Framework in the Microsoft .NET Framework 4

track resources

Required Slide

Track Owners to provide guidance.

Please address any queries to your track owners.

Track Resources

ADO.NET Team Blog: http://blogs.msdn.com/adonet/

Entity Framework Design Blog: http://blogs.msdn.com/efdesign/

Daniel Simmons’s Blog: http://blogs.msdn.com/dsimmons/

Julie Lerman’s Blog: http://thedatafarm.com/blog/

  • Visual Studio Data Blog: http://blogs.msdn.com/vsdata/
slide32

Required Slide

10 pairs of MP3 sunglasses to be won

Complete a session evaluation and enter to win!

slide33

Required Slide

© 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.