(translation) The ten-tip 37-41 of the Entity Framework Skill series

Tip 37. How to include conditionally (Conditional include)

Problem

A few days ago someone on the StackOverflow asked how to carry on the conditional inclusion.

They are going to query some entities (say movies) and want to preload a related project (say, reviews), but only those reviews that match some criteria (such as review.stars==5).

Unfortunately, the pre-loading of EF has no complete support for this, such as for Objectquery<movie> The Include (...) method, include either loading all or not loading anything.

Solution Solutions

But there is also a workaround.

Here is an example scenario that makes this solution "come true":

1 public class Movie  2 {  3 public     int ID {get;set;}  4 public     string Name {get;set;}  5 public     string Genre {get;set;}  6 Public     list<review> reviews {get;set;}  7}  8  9 public class Review {one public     int ID {get;set;} n     int Stars {get;set;}  Public     string Summary {get;set;} n     movie movie {Get;set;} public     user user {get;set;} 17}

Imagine you want to retrieve all the "horror" movies and all their 5-star reviews.

You can do this:

1 var dbquery =  2 from    movie in CTX. Movies  3    where movie. Genre = = "Horror"  4    Select new {  5       movie,   6       reviews = from review in movie. Reviews  7                 where review. Stars = = 5  8                 Select review  9    };10 var movies = Dbquery    . AsEnumerable ()    . Select (M = M.movie);

Now the question is, how does this work?

The first query creates a new instance of an anonymous type that contains each horror movie and its 5-star comment.

Because the AsEnumerable () method is called, the second query runs in memory using LINQ to Objects and only takes the movie out of an object that has an anonymous type wrapper.

And interestingly, each movie contains its 5-star reviews that have already been loaded.

So this piece of code:

1 foreach (var movie in movies) 2 {3     foreach (Var review in movie). Reviews) 4         Assert (review. Rating = = 5); 5}

Will pass!

This can work because EF implements something called Association combinations (relationship fix-up).

Relationship fix-up ensures that related objects are automatically linked when the second entity enters ObjectContext.

And since we both loaded the movie and a filtered list of the former comments, both of which go into ObjectContext, EF ensures that they are automatically linked, which means that a matching comment will appear in the Movie.reviews collection.

For example, the condition contains.

There are a number of different compositing methods on this topic:

Execute two separate queries: One query movie, one query reviews, and then the associated combination to complete the rest of the work.

Perform a query as shown here to select multiple types.

Sort associations – see Tip 1

Once you understand how associative composition works, you can really make the most of it.

Enjoy.

tips. How to use code only in the Data Services Framework (. NET Data Service, Development code Astoria)

A common way to create a service that is an ADO data service (also known as Astoria) is to create a class that inherits from Dataservice<t>.

Public class Bloggingservice:dataservice<bloggingentities>

If you are using the entity Framework at the bottom, the type of t you provide must inherit from ObjectContext.

Most of the time this works well, but it's not possible to use codeonly, which is why.

A bloggingentities instance is built within the DataService framework, and the model is obtained from its metadataworkspace.

The problem is that if you use Code-only to configure the model, the only way to construct bloggingentities is through Code-only Contextbuilder, and Astoria is ignorant of it.

Well...

Thank goodness there's a very simple workaround, you just need to rewrite Dataservie<t> 's CreateDataSource () method like this:

1 protected override Bloggingservice CreateDataSource () 2 {3     //code-only Code goes here:4     var contextbuilder = G Etconfiguredcontextbuilder (); 5     var connection = Getsqlconnection (); 6     return contextbuilder.create (connection); 7}

As you can see, it's quite simple.

Points

For performance reasons, it is important to avoid the cost of each reconfiguration of Contextbuilder, so the Getconfiguredcontextbuilder () method should only create and configure the builder once, and cache this builder for the next call.

Warning

This hint applies only to. NET 4.0 Beta2 and later.

Code-only works only with. NET 4.0 and acts as a separate download (when this article is written). Ado. NET Data Service (also known as Astoria) * will be released as part of. NET 4.0, but not in Beta1, so you can't use codeonly in Astoria temporarily, you have to wait for Astoria to appear in. NET 4.0 BETA2, You may also wait for another release of Code-only.

This means that before you can try this hint, you still need a short period of time in ancient times.

Tip 39. How to set overlapping associations – EF 4.0 only

Scene:

With the Foreign Key Association (FK relationship) in EF 4, the first time it appears in. NET 4.0 BETA2, it is possible to have a model like this:

1 public Class Division  2 {  3 public    int Divisionid {get;set}//Primary Key  4 public    string Name {get ; set;}  5 public    Virtual list<lawyer> lawyers {Get;set;}  6 Public    Virtual list<unit> Units {get;set;}  7}  8 public class Lawyer  9 {ten public    int Lawyerid {get;set;}//Primary Key one public    int Divisioni D {get;set;}//Primary Key + FK to Division    n string Name {get;set;} public    Virtual Division Divisio  n {get;set;} public    virtual list<unit> Units {get;set;}-public class Productteam     int ProductID {get;set;}//Primary Key public     int? Divisionid {get;set;}//FK to Division & Lawyer-public     int? Lawyerid {get;set;}//FK to Lawyer, public     string Name {get;set;}-Public     Virtual Division Division {Get;se t;} * Public     Virtual Lawyer Lawyer {get;set;} 24}

Note that Lawyer has a composite primary key that is composed of Lawyerid and Divisionid .

Interesting things happen when you start manipulating the Productteam class, where there are both Lawyer and division two references and the necessary FK attributes.

If you do the following:

1 var team = (from-T in CTX. Productteams 2                   where t.lawyer.name = = "Fred Bloggs" 3                   select T). FirstOrDefault (); 4 team. Lawyer = null; 5 CTX. SaveChanges ();

What the hell did this do?

Does it mean emptying the team. Lawyerid and team. Divisionid or just a team. Where's Lawyerid?

From a relational perspective, emptying any FK attribute is sufficient to break the association.

Well...

It is difficult to get the user's desire correctly, so EF uses a consistent rule that you can rely on, rather than introducing some fantastic rules based on naming conventions:

When a user sets a reference relationship to NULL, EF empties all the FK attributes of the nullable type that support the association, regardless of whether the FK participates in another association.

Problem:

So in this case EF empties divisionid and Lawyerid, because they all support Lawyer navigation Properties.

This means that the Lawyer will be empty at the same time * will also be empty division.

But do you really want to do that?

May be, maybe not.

Solution:

If you only want to empty the Lawyer , you have two options:

The change model will Divisionid this FK into a non-nullable type, so that EF can only empty the Lawyerid so that the Association to Division is preserved intact.

But a solution that changes the model is not always appropriate, and if division really needs to be empty?

A better option is to manipulate the association directly via the FK property:

1 var team = (from-T in CTX. Productteams 2                   where t.lawyer.name = = "Fred Bloggs" 3                   select T). FirstOrDefault (); 4 team. Lawyerid = null; 5 CTX. SaveChanges ();

As expected, Divisionid and division will remain unaffected.

Tip 40. How to get the presentation layer model through L2E

Problem:

Imagine you have these entities:

1 public class Product  2 {  3 public     int ID {get; set;}  4 public     string Name {get; set;}  5 Public     Virtual category category {get; set;}  6} 7 public class Category  8 {  9 public     int ID {get, set;} public     string Name {get; set;} 11
   public Virtual list<product> Products {get; set;} 12}

But in your UI, you want to display the product ID, the product name, and the category name of the product.

You might try to pass this query to the presentation layer.

1 var displaydata = from product in CTX. Products.include ("Category") 2                   Select Product;

But it's not a good idea to pass on something you don't need, and to bind the UI to a conceptual model to create a tight coupling.

You might try one of these queries again:

1 var displaydata = from product in CTX. Products 2                   Select new {3                      ID = product.id,  4                      Name = product. Name,  5                      CategoryName = product. Category.name  6                   };

But you will soon find that you can not pass an anonymous type object to another method, at least without using this unfriendly method.

Solution:

Most people think that LINQ to entities can only query for entity and anonymous types.

But in fact it can be queried to get any object of a non-generic type with a default constructor.

In short, this means that you can create a view class like this:

1 public class ProductView 2 {3 public     int ID {get; set;} 4 public     string Name {get; set;} 5 public     str ing CategoryName {get; set;} 6}

Then write this:

1 var displaydata = from product in CTX. Products 2                   Select New ProductView {3                      ID = product.id,  4                      Name = product. Name,  5                      CategoryName = product. Category.name  6                   };

There is no problem in passing this object to the view.

I have just discovered this method myself, and I have always assumed this would fail.

So it's a good surprise.

Tip 41. How to execute T-SQL directly against a database

Sometimes you will find that you need to execute a query or command that is not supported by the Entity Framework. In fact, this problem is common to most ORM, which is why most of these ORM leave a backdoor behind the database.

The Entity framework also has a backdoor ...

. NET 3.5 SP1

In. NET 3.5 SP1, you can get a connection to the underlying data through ObjectContext .

Call Objectcontext.connection Returns a IDbConnection object, but this is not the one we need, this is a entityconnection. And EntityConnection has a storeconnection property that can return the object we need:

1 var entityconn = ctx. Connection as EntityConnection; 2 var dbconn = entityconn.storeconnection as SqlConnection;

Once you have this connection, you are free to execute a query or command in the usual way of ADO:

1 Dbconn.open ();  2 var cmd = new SqlCommand ("SELECT * FROM Products", dbconn);  3 using (var reader = cmd. ExecuteReader ())  4 {  5 while     (reader. Read ())  6     {  7         Console.WriteLine ("Product:id:{0} Name:{1} categoryid:{2}",  8             reader[0]. ToString (),  9             reader[1]. ToString (), ten             reader[2]. ToString () one         );     () dbconn.close ();

It's easy, huh?

. NET 4.0

Even better in. NET 4.0. There are 2 new methods that are added directly to the Ojbectcontext .

Executestorecommand (..) for executing commands

executestorequery<t> (..) for executing queries

Using executestorequery<t> (..)

If you use executestorequery<t> (..), EF will create an instance of T for you and populate it. So you can write this:

1 foreach (var product in CTX. executestorequery<product> (SQL)) 2 {3         Console.WriteLine ("Product:id:{0} Name:{1} categoryid:{2}", 4             Product. Id, 5             product. Name, 6             product. CategoryId  7         ); 8}

For this piece of code to work, the column name returned by the query must match the name of the property in the class, and the class must have a default constructor. But the class does not even need to be an entity.

So if you have a class like this:

1 public class ProductView 2 {3 public     int ID {get; set;} 4 public     string Name {get; set;} 5 public     Strin G CategoryName {get; set;} 6}

To query for an instance of this class, you simply write the SQL that returns the Id,name and CategoryName columns.

For example, like this:

1 string SQL = @ "Select P.id, P.name, c.name as CategoryName  2 from Products                P  3                JOIN Categories C  4
   on P.categoryid = c.id ";  5 foreach (var pv in CTX. Executestorequery<productview> (SQL))  6 {  7     Console.WriteLine ("{0} {1} {2}",   8                        PV. ID,   9                        PV. Name, ten                        PV. CategoryName     ); 12}

Of course, this example is for illustrative purposes only, and in general the query is more complex, such as some work that LINQ to entities cannot handle.

For this particular example, you can easily use the standard LINQ to Entities code to complete, see tip 40 you will know how to do it.

Edit The entity returned by Excutestorequery<t> (..)

If you create a class that is really an entity, and you want to edit them, you need to provide more information:

1 var producttoedit = ctx. Executestorequery<product> (SQL, 2       "Products", 3        mergeoption.preservechanges 4). Single (); 5 Producttoedit.categoryid = 6; 6 CTX. SaveChanges ();

The second parameter is the name of the EntitySet that the product belongs to, and the third parameter tells EF how to merge the entity with a copy that might already exist in ObjectContext.

If you do this correctly, when you call SaveChanges (), the changes made to Producttoedit are committed back to the database.

Using Executestorecommand ():

This is very simple, you execute some commands, such as a batch update method, you will get any data returned within the provider, representative of the number of rows affected.

1//10% inflation day! 2 CTX. Executestorecommand (3   "UPDATE products SET Price = Price * 1.1" 4);

It's that simple.

Enjoy.

(translation) The ten-tip 37-41

of the Entity Framework Skill series