Geeks With Blogs
Rohit Gupta Engaging talk on Microsoft Technologies ....My Resume

Eager loading with EF

To Do Eager loading use Projections (for e.g. :

   1: var custWithAddresses = from c in context.Contacts
   2:                         where c.LastName == "Gupta"
   3:                         select new {c, c.Addresses}

or use Include Query Builder Methods (Include(“Addresses”))

   1: from c in context.Contacts.Include("Addresses")
   2: select c;
If there is multi-level hierarchical Data then to eager load all the relationships use Include Query Builder methods like customers.Include("Order.OrderDetail") to include Order and OrderDetail collections or use customers.Include("Order.OrderDetail.Location") to include all Order, OrderDetail and location collections with a single include statement

===========================================================================
Constraints on using Query Builder methods with LINQToEntities:
If the LINQ query uses Joins then Include() Query Builder method will be ignored, use Nested LINQ Queries instead.
If the query does projections then Include() Query Builder method will be ignored

Use Address.ContactReference.Load() OR Contact.Addresses.Load() if you need to Deferred Load Specific Entity – This will result in extra round trips to the database 

Only LINQToEntities queries can return anonymous types (during projection)... when using EntitySQL to return projections they always return a IEnumerable<DBDataRecord>
QueryBuilder methods like Include(), Where(“it.LastName == “Gupta”) etc can only be added to ObjectQuery<>. you cannot append QueryBuilder methods to IQueryable<>. Conversely Any Linq Query method can be added to Query Builder methods (since ObjectQuery Implements IQueryable<T> interface).

If you need to append a Query Builder Method (other than Include) after a LINQ method  then cast the IQueryable<Contact> to ObjectQuery<Contact> and then append the Query Builder method to it

===========================================================================

Query Builder methods are Select, Where, Include Methods which use Entity SQL as parameters e.g. "it.StartDate, it.EndDate"
When Query Builder methods do projection then they return ObjectQuery<DBDataRecord>, thus to iterate over this collection use contact.Item[“Name”].ToString()
When Linq To Entities methods do projection, they return collection of anonymous types --- thus the collection is strongly typed and supports Intellisense
EF Object Context can track changes only on Entities, not on Anonymous types.

If you use a Defining Query for a EntitySet then the EntitySet becomes readonly since a Defining Query is the same as a View (which is treated as a ReadOnly by default). However if you want to use this EntitySet for insert/update/deletes then we need to map stored procs (as created in the DB) to the insert/update/delete functions of the Entity in the Designer

You can use either Execute method or ToList() method to bind data to datasources/bindingsources
If you use the Execute() Method then remember that you can traverse through the ObjectResult<> collection (returned by Execute) only ONCE.
In WPF use ObservableCollection to bind to data sources , for keeping track of changes and letting EF send updates to the DB automatically.

Use Extension Methods to add logic to Entities. For e.g. create extension methods for the EntityObject class.
Create a method in ObjectContext Partial class and pass the entity as a parameter, then call this method as desired from within each entity.
================================================================
DefiningQueries and Stored Procedures:

For Custom Entities, one can use DefiningQuery (option1) or Stored Procedures (option2). Thus the Custom Entity Collection will be populated using the DefiningQuery (of the EntitySet) or the Sproc. If you use Sproc to populate the EntityCollection then the query execution is immediate and this execution happens on the Server side and any filters applied will be applied in the Client App.
If we use a DefiningQuery then these queries are composable, meaning the filters (if applied to the entityset) will all be sent together as a single query to the DB, returning only filtered results.

Steps for mapping sproc results to a non-existing Entity :

If the sproc returns results that cannot be mapped to existing entity, then we first create the Entity/EntitySet in the CSDL using Designer, then create a dummy Entity/EntitySet using XML in the SSDL. When creating a EntitySet in the SSDL for this dummy entity, use a TSQL that does not return any results, but does return the relevant columns
e.g. select ContactID, FirstName, LastName from dbo.Contact where 1=2
Also insure that the Entity created in the SSDL uses the SQL DataTypes and not .NET DataTypes.
If you are unable to open the EDMX file in the designer then note the Errors ... they will give precise info on what is wrong.

The Third option is to simply create a Native Query in the SSDL using

   1: <Function Name="PaymentsforContact" IsComposable="false">
   2:   <CommandText>
   3:     SELECT   Payments.PaymentDate, Payments.Amount,
   4:              Reservations.ReservationDate, Contact.FirstName,
   5:              Contact.LastName, Events.StartDate, Events.EndDate,
   6:              Locations.LocationName
   7:     FROM     Payments INNER JOIN
   8:              Reservations ON Payments.ReservationID =
   9:              Reservations.ReservationID INNER JOIN
  10:              Contact ON Reservations.ContactID = Contact.ContactID
  11:              INNER JOIN Events ON Reservations.EventID = Events.EventID
  12:              INNER JOIN Locations ON Events.LocationID =
  13:              Locations.LocationID
  14:     WHERE Contact.ContactID=@ContactID
  15:   </CommandText>
  16:   <Parameter Name="ContactID" Type="int" Mode="In"/>
  17: </Function>

Now you can create a FunctionImport for the PaymentsforContact function and map it to a existing Entity. This is a quick way to get a custom Entity which is regular Entity with renamed columns or additional columns (which are computed columns). The disadvantage to using this is that It will return all the rows from the Defining query and any filter (if defined) will be applied only at the Client side (after getting all the rows from DB). If you use DefiningQuery instead then we can use that as a Composable Query.

The Fourth option (for mapping a READ stored proc results to a non-existent Entity) is to create a View in the Database which returns all the fields that the sproc also returns, then update the Model so that the model contains this View as a Entity. Then map the Read Sproc to this View Entity. The other option would be to simply create the View and remove the sproc altogether.
================================================================
To Execute a SProc that does not return a entity, use a EntityCommand to execute that proc. You cannot call a sproc FunctionImport that does not return Entities From Code, the only way is to use SSDL function calls using EntityCommand.  This changes with EntityFramework Version 4 where you can return Scalar Types, Complex Types, Entities or NonQuery

================================================================
Using SQL UDF’s with EntityFrameworkv1:
UDF when created as a Function in SSDL, we need to set the Name & IsComposable properties for the Function element.
IsComposable is always false for Sprocs, for UDF's set this to true.
You cannot call UDF "Function" from within code since you cannot import a UDF Function into the CSDL Model (with Version 1 of EF). only stored procedures can be imported (since IsComposable for sprocs is “false”) and then mapped to a entity.
These Functions (created using TSQL UDFs) can be called using EntitySQL like the following:

   1: var esql = "select TREAT(c as BAGA.Customer).WeightPounds," +
   2:            "BAModel.Store.ufnLBtoKG(TREAT(c as BAGA.Customer).WeightPounds) " +
   3:            "from BAEntities.Contacts AS c where c is of(BAGA.Customer)"
   4: Dim query == context.CreateQuery<DbDataRecord>(esql);
   5: var weightList = query.ToList();
================================================================
Entity Framework requires properties (foreign keys) that are involved in association mappings to be mapped in all of the function mappings for the entity (Insert, Update and Delete). Because Payment has an association to Reservation (ReservationID is a foreign key in Payment table)... hence we need to pass both the paymentId and reservationId to the Delete sproc even though just the paymentId is the PK on the Payment Table.
================================================================
When mapping insert, update and delete procs to a Entity, insure that all the three or none are mapped.
Further if you have a base class and derived class in the CSDL, then you must map (ins, upd, del) sprocs to all parent and child entities in the inheritance relationship.
Note that this limitation that base and derived entity methods must all must be mapped does not apply when you are mapping Read Stored Procedures....
================================================================
You can write stored procedures SQL directly into the SSDL by creating a Function element in the SSDL and then once created, you can map this Function to a CSDL Entity directly in the designer during Function Import
================================================================
You can do Entity Splitting such that One Entity maps to multiple tables in the DB. For e.g. the Customer Entity currently derives from Contact Entity...in addition it also references the ContactPersonalInfo Entity. One can copy all properties from the ContactPersonalInfo Entity into the Customer Entity and then Delete the CustomerPersonalInfo entity, finally one needs to map the copied properties to the ContactPersonalInfo Table in Table Mapping (by adding another table (ContactPersonalInfo) to the Table Mapping... this is called Entity Splitting.
Thus now when you insert a Customer record, it will automatically create SQL to insert records into the Contact, Customers and ContactPersonalInfo tables even though you have a Single Entity called Customer in the CSDL
===================================================================
There is Table per Type Inheritance where another EDM Entity can derive from another EDM entity and absorb the inherted entities properties, for example in the Break Away Geek Adventures EDM, the Customer entity derives (inherits) from the Contact Entity and absorbs all the properties of Contact entity. Thus when you create a Customer Entity in Code and then call context.SaveChanges the Object Context will first create the TSQL to insert into the Contact Table followed by a TSQL to insert into the Customer table

===================================================================
Then there is the Table per Hierarchy Inheritance..... where different types are created based on a condition (similar applying a condition to filter a Entity to contain filtered records)... the difference being that the filter condition populates a new Entity Type (derived from the base Entity). In the BreakAway sample the example is Lodging Entity which is a Abstract Entity and Then Resort and NonResort Entities which derive from Lodging Entity and records are filtered based on the value of the Resort Boolean field
===================================================================
Then there is Table per Concrete Type Hierarchy where we create a concrete Entity for each table in the database. In the BreakAway sample there is a entity for the Reservation table and another Entity for the OldReservation table even though both the table contain the same number of fields. The OldReservation Entity can then inherit from the Reservation Entity and configure the OldReservation Entity to remove all Scalar Properties from the Entity (since it inherits the properties from Reservation and filters based on ReservationDate field)
===================================================================
Complex Types (Complex Properties)
Entities in EF can also contain Complex Properties (in addition to Scalar Properties) and these Complex Properties reference a ComplexType (not a EntityType)

DropdownList, ListBox, RadioButtonList, CheckboxList, Bulletedlist are examples of List server controls (not data bound controls) these controls cannot use Complex properties during databinding, they need Scalar Properties.
So if a Entity contains Complex properties and you need to bind those to list server controls then use projections to return Scalar properties and bind them to the control (the disadvantage is that projected collections are not tracked by the Object Context and hence cannot persist changes to the projected collections bound to controls)

ObjectDataSource and EntityDataSource do account for Complex properties and one can bind entities with Complex Properties to Data Source controls and they will be tracked for changes... with no additional plumbing needed to persist changes to these collections bound to controls

So DataBound controls like GridView, FormView need to use EntityDataSource or ObjectDataSource as a datasource for entities that contain Complex properties so that changes to the datasource done using the GridView can be persisted to the DB (enabling the controls for updates)....if you cannot use the EntityDataSource you need to flatten the ComplexType Properties using projections

With EF Version 4 ComplexTypes are supported by the Designer and can add/remove/compose Complex Types directly using the Designer
===================================================================
Conditional Mapping ... is like Table per Hierarchy Inheritance where Entities inherit from a base class and then used conditions to populate the EntitySet (called conditional Mapping). Conditional Mapping has limitations since you can only use =, is null and IS NOT NULL Conditions to do conditional mapping.
If you need more operators for filtering/mapping conditionally then use QueryView(or possibly Defining Query) to create a readonly entity.
QueryView are readonly by default... the EntitySet created by the QueryView is enabled for change tracking by the ObjectContext, however the ObjectContext cannot create insert/update/delete TSQL statements for these Entities when SaveChanges is called since it is QueryView. One way to get around this limitation is to map stored procedures for the insert/update/delete operations in the Designer.
===================================================================
Difference between QueryView and Defining Query :
QueryView is defined in the (MSL) Mapping File/section of the EDM XML, whereas the DefiningQuery is defined in the store schema (SSDL). QueryView is written using Entity SQL and is this database agnostic and can be used against any database/Data Layer. DefiningQuery is written using Database Lanaguage i.e. TSQL or PSQL thus you have more control.
Model Functions that are defined in the Conceptual layer are also written using ESQL. This ESQL however runs the query against the Conceptual Model EntitySets and returns Conceptual Model Entity, whereas the QueryView ESQL runs the query against the Store Model EntitySets and returns Conceptual Model Entity.
An Example of Query View is :

   1: <EntitySetMapping Name="Staffs">
   2: <QueryView>
   3: select value
   4: case
   5: when (i.StaffId is not null) then
   6: EFRecipesModel.Instructor(s.StaffId,s.Name,i.InstructorId,i.Salary)
   7: when (p.StaffId is not null) then
   8: EFRecipesModel.Principal(s.StaffId,s.Name,p.PrincipalId,p.Salary,p.Bonus)
   9: END
  10: from EFRecipesModelStoreContainer.Staff as s
  11: left join EFRecipesModelStoreContainer.Instructor as i
  12: on s.StaffId = i.StaffId
  13: left join EFRecipesModelStoreContainer.Principal as p
  14: on s.StaffId = p.StaffId
  15: </QueryView>
  16: </EntitySetMapping>

Note here that in the “SELECT Entity FROM TABLE” the table is “EFRecipesModelStoreContainer.Staff” which is a SSDL based EntitySet and the ESQL returns “EFRecipesModel.Instructor” which is a Conceptual Model Entity. (Here EFRecipesModelStoreContainer is the SSDL EntityContainer)
An Example of Model Function is :

   1: <Function Name="AverageUnitPrice" ReturnType="Edm.Decimal">
   2:   <Parameter Name="category" Type="EFRecipesModel.Category" />
   3:   <DefiningExpression>
   4:     ANYELEMENT(Select VALUE Avg(p.UnitPrice)
   5:     from EFRecipesEntities.Products as p where p.Category == category
   6:     group by p.Category.CategoryName
   7:     )
   8:   </DefiningExpression>
   9: </Function>

Note here that in the pattern “SELECT Entity FROM TABLE”, the table is a Conceptual Model EntitySet “EFRecipesEntities.Products“ and that the ESQL returns a Conceptual Model Entity “EFRecipesModel.Product”. (Here EFRecipesEntities is the CSDL EntityContainer) .

Using this Model function from C# code is straight forward. You can call the Model function using EntitySQL using the following for e.g :

   1: string eSQL = "SELECT c.CategoryName, 
   2: EFRecipesModel.AverageUnitPrice(c) as AveragePrice 
   3: FROM EFRecipesEntities.Categories as c";
   4:  
   5: var cats = context.CreateQuery<DbDataRecord>(eSQL);
   6: foreach (var cat in cats)
   7: {
   8:     Console.WriteLine("{0}...{1}", cat[0], ((decimal)cat[1]).ToString("C"));
   9: }

Note that when using EntitySQL, if you do projection and return custom shaped results as shown above then we HAVE to use DbDataRecord to iterate over the results (as shown above. However if the EntitySQL returns complete entities then we can return Entities using the context.CreateQuery<> method as shown below:

   1: string eSQL = "SELECT value c FROM EFRecipesEntities.Categories as c";
   2: var cats = context.CreateQuery<Category>(eSQL);
   3: foreach (var cat in cats)
   4: {
   5:     Console.WriteLine("{0}...{1}", cat.CategoryName, cat.CategoryId);
   6: }
===================================================================
Improving Performance:
Lazy loading is deferred loading done automatically. lazy loading is supported with EF version4 and is off by default. If you need to turn it on then use context.ContextOptions.LazyLoadingEnabled = true. With this option set to true, whenever you access a navigation property of a Entity it will loaded with the values from the DB by making a explicit second call to the DB to get the info. Thus to improve performance use Eager loading instead.

In LinqToSQL there is a similar property named DeferredLoadingEnabled which is enabled by default. if you need to disable it use dataContext.DeferredLoadingEnabled = false. Note that DefferredLoading is ignored if ObjectTrackingEnabled is set to false

To improve Performance consider PreCompiling the ObjectQuery using the CompiledQuery.Compile method if the query is going to be used multiple times in the app
Posted on Thursday, April 1, 2010 6:04 AM | Back to top


Comments on this post: Entity Framework version 1- Brief Synopsis and Tips – Part 1

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © Rohit Gupta | Powered by: GeeksWithBlogs.net