Tip 29. How to avoid lazy loading or load () reader issues

Tip 29. How to avoid lazy loading or load () reader issues

If you have the following code:

1 var results = from C in CTX. Customers  2                    where c.salesperson.emailaddress = = "..."  3                    Select C; 4 foreach (var customer in results)  5 {  6     Console.WriteLine (Customer. Name);  7     if (isinteresting (customer))  8     {  9         customer. Orders.load (); Ten         foreach (var order in customer. Orders)         {              Console.WriteLine ("\ T" + order). Value);     15}

This code will open 2 synchronized readers. An enumeration customers, and another enumeration of orders for the current customer. And only available if multiple Active resultsets (also known as Mars) is enabled. So if Mars is not enabled you are talking about an unpleasant exception.

Note : You may be wondering why I'm calling isinteresting (..) here. Because without this judgment, the code for this pattern is definitely not recommended. If you can avoid accessing the database in a loop like this, in other words, if you know beforehand that you need all of the customer's order, you should use include () to preload the order.

Enabling Mars is easy, just multiple the Active resultsets=true in the connection string; Can.

You don't usually need to do it yourself, because most of the connection strings are created by the EF designer and are set up for you. This is true in cases where the 3.5 database first and the 4.0 model are preferred.

But if you provide connectionstring, as in code only, you need to remember to turn on Mars.

Because this works for both 3.5 and 4.0, the chances are equal if it goes wrong.

However, the 4.0 error may be more subtle because of the new lazyloading feature (previously known as deferredloading).

In short, the main thrust of the question is to remember to open mars!

Tip 30. How to use the Custom database function (UDF)

Imagine you have a database function like the Distancebetween function in nerd dinner:

1 CREATE FUNCTION [dbo]. [Distancebetween] (2    @Lat1 as real, 3 @Long1 as Real,    4    @Lat2 as real, 5    @Long2 as Real) 6 RETURNS Real 7 as 8 BEGIN 9 ... Ten END

You want to use it in the Entity Framework

Declare this function

The first step is to open the edmx file in the XML editor and add a <Function> element under <Schema> in the <edmx:StorageModels> element.

This should be done after:

1 <function name= "Distancebetween"  2           iscomposable= "true"   3           schema= "dbo"   4           aggregate= " False "  5           builtin=" false "  6           returntype=" float "> 7    <parameter name=" Lat1 "type=" float "Mode=" in "/> 8    <parameter name=" Long1 "type=" float "mode=" in "/> 9    <parameter name=" Lat2 "type=" Float "mode=" in "/>10    <parameter name=" Long2 "type=" float "mode=" in "/>11 </Function>

Using Functions in ESQL

This function can now be called in ESQL:

1 SELECT VALUE (d) from Mymodel.dinners as D 2 WHERE storagenamespace.distancebetween (3       d.latitude,d.longitude,- 34,174) < 50

MyModel is the name of your EntityContainer (usually the same as ObjectContext),storagenamespace is the namespace of your storage model schema.

Using Functions in LINQ

Most people don't use esql, so you might be wondering how to use it in LINQ?

In 3.5sp1, the following is true:

1 var nearbydinners = 2 from      d in CTX. Dinners.where (3 "Storagenamespace.distancebetween (IT). Latitude, it. LONGITUDE,–34, 174) < 4) Select D;

Here we mix LINQ and ESQL with a query construction method that receives an ESQL fragment that invokes a database function in esql. Note that the code snippet is associated with the current item through the ' it ' keyword. If necessary, you can even associate to the parameter.

It's great.

But it would be better if there were no strings.

EF 4.0 the improvements in

In EF 4.0 You can write the following code instead:

1 var nearbydinners = 2 from     d in CTX. Dinners 3     where Distancebetween (D.latitude, d.longitude,–34,174) < 4     Select D;

This looks better. There are no strings like the above and compile-time checking is supported.

You need a way to make the above code work:

1 [Edmfunction ("Storagenamespace", "Distancebetween")]2 public double Distancebetween (3        double LAT1,  4        Double Long1,  5        double lat2,  6        double long2) 7 {8    throw new NotImplementedException ("You can only Call the This method as part of a LINQ expression "); 9}

You may wonder why this method throws an exception?

We never really need to execute this method directly. We just use it to write LINQ queries, which are translated into SQL instead of actually calling this method.

EF uses the Edmfuncation feature to know which database function needs to be called in place of this function.

It's cool.

Enjoy yourself.

Tip 31. How to combine L2o (LINQ to Objects) and L2E (LINQ to Entities) queries

Consider that you want to write a query such as the following:

1 var possiblebuyers= 2 from      p in CTX. People 3      where p.address.city = = "Sammamish" && Inmarketforahouse (P) 4      select P;

Theoretically, as long as the inmarketforahouse can be translated into SQL this code can be executed.

In EF4.0, you can do this by creating a CLR stub for the model or database function you want.

Suppose that there is no corresponding SQL.

Maybe this feature needs to use all those things that are not part of the database.

Now you have to "split" the query. For example, split the query into a base LINQ to Entities Query and a LINQ to Objects query that relies on L2E.

You might try a code like this:

1 var partialfilter = from P in CTX. People 2                     where p.address.city = = "Sammamish" 3                     select p;4 var possiblebuyers = from P in partiallyfilter 5                      whe Re Inmarketforahouse (P); 6                      Select P;

But this has little effect on the behavior of the code. IQueryable (CTX. People) will still be required to translate inmarketforahouse (..) into SQL.

You need to call the AsEnumerable () method, which effectively makes the query independent of two parts:

1 var possiblebuyers = from P in partiallyfilter.asenumerable () 2                      where Inmarketforahouse (p); 3                      select P;

AsEnumerable () ensures that LINQ to objects handles all subsequent requests. So the LINQ to Entities provider (for example, CTX. People) The user will not know the Inmarketforahouse () method.

Of course there are some warnings.

Although the final query may iterate only a small subset of records, the actual query sent to the database may return a large amount of data.

So you need to think about what's going to happen.

Ask yourself this question: how much data will I get from the database?

You may even think that there is no problem with "iterating" large amounts of data.

The problem is that by default you're not just iterating through the records. ObjectContext also identifies each entity, including those that are discarded in subsequent LINQ to objects, which is a considerable resource.

This particular problem can be "simple" using a notracking query to avoid.

But this leads to another series of problems, and you can't update the result set unless you attach them.

Anyway, hopefully next time you need to "split" the query you can know more about how to weigh the pros and cons.

Tip 32. How to create a database from SSDL – EF4.0 only

Recently we have released a CTP version of the extension EF4 Beta 1 containing the code only feature.

You can find more information about code only here.

If you look at code only, you'll see code similar to the following:

1//Create a builder and configure it 2 var builder = new contextbuilder<mycontext> (); 3 ... 4//Create a context 5 var mycontext = Builder. Create (sqlConnection); 6//Prepare the Context 7 if (!mycontext.databaseexists ()) 8    mycontext.createdatabase ();

CreateDatabase (), dropdatabase (), databaseexists () and createdatabasescripts () are extension methods that are published in the code only assembly.

This is an elegant thing: these extension methods are intersected with the remainder of code only.

You can use these extension methods in any ObjectContext, regardless of whether they were created by Code-only.

So you can increase the use of these methods in * any *objectcontext.

Imagine this scenario : someone else on your team checked in an EDMX as part of the project, but when you checked out you found there was no database script. Now you use Code-only to create a local database.

The methods that focus on database models, such as generating and executing DDL, are described in Objectcontext.metadataworkspace.

There are always some warnings accompanying these hints *:

1. Currently this works only on EF4 Beta1. When they are terminated we will release a new version of code only to work with the newer version of EF4.

2. CreateDatabase () does not know how to handle everything in the storage model. For example, if your edmx references a database view or stored procedure, Code only does not know how to generate an equivalent database object.

3. Currently this can only work with SQL Server. We have a plan to add the provider model to Code-only, but that is not yet implemented.

Despite these limitations, there is no doubt that CreateDatabase () and its companions will be useful.

Happy Coding!

* No warning, this is not a hint.

Tip 33. In EF Intermediate remove really how to work

Consider that you implement cascade deletions based on a foreign key relationship in the database.

As follows:

This delete rule table name when a category is deleted, all associated product is also deleted.

If you generate an EF model from a database, the model you get is not as different from the surface as it usually is:

But if you go deep into the CSDL part of XML, you'll see:

1 <association name= "Fk_products_categories" >  2        <end role= "Categories" type= " TipsModel.Store.Categories "multiplicity=" 1 ">  3               <ondelete action=" Cascade "/>  4        </end >  5        <end role= "Products" type= "TipsModel.Store.Products" multiplicity= "*"/>  6        < referentialconstraint>  7               <principal role= "Categories" >  8                     <propertyref name= "ID"/ >  9               </Principal>               <dependent role= "Products" > one                     <propertyref name= " CategoryID "/>               </Dependent>        </ReferentialConstraint> </Association>

Note the <OnDelete> element, which informs EF that when a category is deleted, also * will * delete the associated product.

I deliberately use * to * instead of * should *, because EF is not responsible for cascading deletions in the database.

EF is responsible for maintaining the correct ObjectContext after calling SaveChanges (). So EF common sense synchronizes objectcontext to the expected state after the database completes the expected cascade deletions.

As for the existence of this problem, if you open a tool like SQLProfiler, you will notice that when a primary element is deleted, EF triggers a delete request on the entity it knows (for example, those that are loaded into ObjectContext) that relies on the main element.

What essentially happens is that the Entity Framework thinks that deleting the primary element in the database will remove all dependencies on the main element in the database. So this creates a problem, what, an extra delete to request itself, causing the already loaded related object to be deleted by ObjectContext.

The key to note is that ef* does not actually retrieve all dependent entities in the database and perform the delete: it only deletes objects that already have dependencies in memory.

So here's the golden rule:

1. If you add a cascade delete rule to the model, you must have a corresponding delete rule in the database.
2. If for some reason you insist on breaking the rules (1), cascade Delete only works if you load all dependent objects into memory.
3. (2) is * not * recommended!!!

While we do our best to keep the ObjectContext in sync with the database, this effort will fail if you have multi-level deletions.

For example, if you have a relationship like this:

Category–> product–> Order

Deleting a category deletes all of the product and then deletes its order.

EF may, in rare cases, be unable to synchronize with the database when you delete a category.

For example, you have a loaded order that is associated to a category by a product that is not loaded, and when you delete the category, EF does not know that the order should be deleted.

This means that the order is left in the ObjectContext in the unchanged state, although it has been deleted in the database.

Everything is pre-set.

Tip 34. How to use updatable views in EF

Update : Thank you Zeeshan point out that by default the non-empty column of the entity returned by the view will eventually be the primary key.

Imagine a situation in which you have an updatable view in your database.

Next you decide to use this view in the Entity Framework, so you import this view further.

The resulting entity looks like this:

As you can see, each property has a "key" icon.

Because this entity is based on a single view, EF does not know which columns make up the primary key, so it assumes that each non-empty column is part of the primary key.

Fixed primary key

The first step is to change the primary key. In this example, the ID is the real primary key.

You can open the EDMX in the XML editor and change the EntityType so that it is associated with each attribute to the <KEY>, as follows:

Change to this:

One important thing to note is that you have to make this change in the EDMX <edmx:StorageModels> and <edmx:ConceptualModes> section at the same time , Because two models need to agree on a primary key definition.

Treat a view as a table

Now you can use the Entity Framework to query employees.

However, the Entity Framework does not allow you to update.

The general approach to this problem is to create a stored procedure and use them in a functional way.

But given that the view has the ability to update, the above scenario is obviously not ideal.

Fortunately, there is an alternative: the simple thing is that EF thinks this view is a table.

This will require you to change the definition of Storagemodel in EntitySet. In general, it looks like this at the beginning:

1 <entityset name= "Employees"   2            entitytype= "Tip34Model.Store.Employees"  3            store:type= "views"  4            store:schema= "dbo"  5            store:name= "Employees" >  6   <definingquery>select  7   [employees].[ ID] as [ID],  8   [employees].[ Firstname] As [Firstname],  9   [employees].[ Surname] As [Surname], ten   [employees].[ Email] as [email] one from   [dbo].[ Employees] as [Employees]   </DefiningQuery> </EntitySet>

In order for it to be treated as a table, replace it with the following:

1 <entityset name= "Employees"  2            entitytype= "Tip34Model.Store.Employees"  3            store:type= " Tables "4            schema=" dbo "/>

You can now perform any crud operations.

It's easy.

Tip 29. How to avoid lazy loading or load () reader issues