10-tip 37-41 of Entity Framework skill Series

 

Tip 37. How to include by conditions (Conditional Include)

Problem

A few days ago someone asked on stackoverflow how to include by conditions.

They intend to query some entities (for example, movies) and want to pre-load a related project (for example, reviews), but only those reviews that match some conditions (for example, review. stars = 5 ).

Unfortunately, the pre-loading of EF does not fully support this, for example, for objectquery <movie>. Include (...) Method, include, or load all or not load anything.

Solution

However, there is also a work und.

The following is an example of how to make the solution "come true:

Public class movie
{
Public int ID {Get; set ;}
Public string name {Get; set ;}
Public String genre {Get; set ;}
Public list <Review> reviews {Get; set ;}
}
Public class review
{
Public int ID {Get; set ;}
Public int stars {Get; set ;}
Public String summary {Get; set ;}
Public movie {Get; set ;}
Public user {Get; set ;}
}

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

You can do this:

VaR dbquery =
From movie in CTX. Movies
Where movie. Genre = "Horror"
Select New {
Movie,
Reviews = from review in movie. Reviews
Where review. Stars = 5
Select Review
};

VaR movies = dbquery
. Asenumerable ()
. Select (M => M. Movie );

The question is how does this work?

The first query creates a new anonymous instance, which contains each horror movie and its 5-star comments.

Because the asenumerable () method is called, the second query uses LINQ to objects to run in memory. Only the movie is retrieved from objects with anonymous packaging.

What's interesting is that each movie contains its 5-star comments that have been loaded.

So this sectionCode:

Foreach (VAR movie in movies)
{
Foreach (VAR review in movie. Reviews)
Assert (review. rating = 5 );
}

Will pass smoothly!

This works because EF implements something called relationship fix-up.

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

And because we load both the Comments List of movie and a filtered former, after both enter objectcontext, EF will ensure that they are automatically linked, which means that in movie. matching comments will appear in the reviews set.

For example, the condition contains.

There are some different synthesis methods on this topic:

Execute two independent queries: one query movie and one query reviews, and then let the association combination complete the rest of the work.

Execute a select multiple types query as shown in this figure.

Sort Association-see Tip 1

Once you understand how a link combination works, you can make full use of it.

Enjoy.

Tip 38.How to Use Code only in the Data Service Framework (. NET data service, development code Astoria)

Generally, the method for creating an ADO. NET data service (also known as Astoria) is to create a class inherited from dataservice <t>.

Public class bloggingservice: dataservice <bloggingentities>

If you want to use Entity Framework at the underlying layer, the T type you provide must inherit from objectcontext.

This can work well most of the time, but it is not allowed when codeonly is used, and the above is the reason.

A bloggingentities instance is built within the dataservice Framework and Its metadataworkspace model is obtained.

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 knows nothing about this.

Well...

Thank God this is a very simple work und. You only need to rewrite the createdatasource () method of dataservie <t> like this:

Protected override bloggingservice createdatasource ()
{
// Code-only code goes here:
VaR contextbuilder = getconfiguredcontextbuilder ();
VaR connection = getsqlconnection ();
Return contextbuilder. Create (connection );
}

As you can see, this is quite simple.

Key Points

Because of the performance, it is important to avoid the overhead of contextbuilder every time you reconfigure contextbuilder. Therefore, the getconfiguredcontextbuilder () method should be created and configured only once, and the builder will be cached for subsequent calls.

Warning

This prompt only applies to. Net 4.0 beta2 and later versions.

Code-only works on. NET 4.0 and is used as a separate download (when writing this article ). ADO. net Data Service (also known as Astoria) * uses *. net 4.0, but not in beta1, so codeonly cannot be used in Astoria for the time being. 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 prompt, you still need a short period of time in ancient times.

Tip 39. How to Set overlapping associations-Ef 4.0 only

Scenario:

In EF 4, there is a foreign key Association (FK relationship), which first appeared in. Net 4.0 beta2. Therefore, there may be a model like this:

Public class division
{
Public int divisionid {Get; Set} // primary key
Public string name {Get; set ;}
Public Virtual list <lawyer> lawyers {Get; set ;}
Public Virtual list <unit> units {Get; set ;}
}
Public class lawyer
{
Public int lawyerid {Get; set;} // primary key
Public int divisionid {Get; set;} // primary key + FK to Division
Public string name {Get; set ;}
Public Virtual Division {Get; set ;}
Public Virtual list <unit> units {Get; set ;}
}
Public class productteam
{
Public 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 {Get; set ;}
Public Virtual lawyer {Get; set ;}
}

Note:LawyerThere isLawyeridAndDivisionidComposite primary key.

When you start to operate the productteam class, interesting things will appear. This class also existsLawyerAndDivisionTwo references and necessary FK attributes.

If you perform the following operations:

VaR team = (from T in CTX. productteams
Where T. Lawyer. Name = "Fred Bloggs"
Select T). firstordefault ();
Team. Lawyer = NULL;
CTX. savechanges ();

What has this actually done?

Indicates whether to emptyTeam. lawyeridAndTeam. divisionidOr onlyTeam. lawyeridWhat about it?

From the perspective of the relationship, clearing any FK attribute is enough to disconnect the association.

Well...

It is difficult to get what the user wants correctly. Therefore, EF uses a consistent rule that you can depend on, rather than introducing some wonderful rules based on naming conventions:

When you set a reference relationship to null, EF clears all empty-type FK attributes that support association, regardless of whether the FK is involved in other associations.

Problem:

In this case, EF is cleared.DivisionidAndLawyeridBecause they all supportLawyerNavigation properties.

This means thatLawyerIf this parameter is set to null, * or * is set to null.Division.

But do you really want to do that?

Maybe, maybe not.

Solution:

If you just wantLawyerLeave it empty. You have two options:

Change the modelDivisionidThis FK changes to a non-empty type, so EF can onlyLawyeridIf this parameter is left blank, the division association is completely retained.

However, a solution to change the model is not always appropriate. What if Division needs to be empty?

A better choice is to directly associate the data using the FK attribute:

VaR team = (from T in CTX. productteams
Where T. Lawyer. Name = "Fred Bloggs"
Select T). firstordefault ();
Team. lawyerid = NULL;
CTX. savechanges ();

As expected, divisionid and division are retained without being affected.

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

Problem:

Imagine you have these entities:

Public class product
{
Public int ID {Get; set ;}
Public string name {Get; set ;}
Public Virtual category {Get; set ;}
}

Public class category
{
Public int ID {Get; set ;}
Public string name {Get; set ;}
Public Virtual list <product> Products {Get; set ;}
}

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

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

VaR displaydata = from product in CTX. Products. Include ("category ")
Select product;

However, you have uploaded unnecessary things and bound the UI to the conceptual model to produce tight coupling, so this is not a good note.

You may try another query like this:

VaR displaydata = from product in CTX. Products
Select New {
Id = product. ID,
Name = product. Name,
Categoryname = product. Category. Name
};

However, you will soon find that you cannot pass anonymous objects to another method, at least not using this unfriendly method.

Solution:

Most people think that only the entity and anonymous types can be queried by using LINQ to entities.

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

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

Public class productview
{
Public int ID {Get; set ;}
Public string name {Get; set ;}
Public String categoryname {Get; set ;}
}

Then write as follows:

VaR displaydata = from product in CTX. Products
Select New productview {
Id = product. ID,
Name = product. Name,
Categoryname = product. Category. Name
};

In this way, it is no problem to pass this object to the view.

I just discovered this method myself. I always assumed that this would fail.

This is a good surprise.

Tip 41. How to perform T-SQL directly on the database

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

Entity Framework also has a backdoor...

. Net 3.5 SP1

In. Net 3.5 SP1, you can useObjectcontextObtain the connection to the underlying data.

Call objectcontext. Connection to return an idbconnection object, but this is not the one we need. This is an entityconnection. Entityconnection has a storeconnection attribute that can return the desired object:

VaR entityconn = CTX. connection as entityconnection;
VaR dbconn = entityconn. storeconnection as sqlconnection;

Once you have this connection, you can freely execute a query or command in ADO. Net mode:

Dbconn. open ();
VaR cmd = new sqlcommand ("select * from products", dbconn );
Using (VAR reader = cmd. executereader ())
{
While (reader. Read ())
{
Console. writeline ("product: ID: {0} Name: {1} categoryid: {2 }",
Reader [0]. tostring (),
Reader [1]. tostring (),
Reader [2]. tostring ()
);
}
}
Dbconn. Close ();

Is it easy?

. Net 4.0

It is even better in. Net 4.0. Two new methods are directly addedOjbectcontext.

Executestorecommand (...) is used to execute commands

Executestorequery <t> (...) is used to execute a query.

Use executestorequery <t> (..)

If you useExecutestorequery <t> (..), EF will create and fill t instances for you. So you can write:

Foreach (VAR product in CTX. executestorequery <product> (SQL ))
{
Console. writeline ("product: ID: {0} Name: {1} categoryid: {2 }",
Product. ID,
Product. Name,
Product. categoryid
);
}

To use this code, the name of the column returned by the query must match the attribute name 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:

Public class productview
{
Public int ID {Get; set ;}
Public string name {Get; set ;}
Public String categoryname {Get; set ;}
}

To query the instances of this class, you only need to write the SQL statement that returns the ID, name, and categoryname columns.

For example:

String SQL = @ "select P. ID, P. Name, C. Name as categoryname
From products P
Join categories C
On P. categoryid = C. ID ";

Foreach (var pv in CTX. executestorequery <productview> (SQL ))
{
Console. writeline ("{0} {1} {2 }",
PV. ID,
PV. Name,
PV. categoryname
);
}

Of course, this example is only for demonstration purposes. In general, the query will be more complex, for example, some work that the native of LINQ to entities cannot handle.

For this special example, you can easily use the standard LINQ to entities Code. For more information, see Tip 40. You will know how to do this.

Edit the entity returned by excutestorequery <t> (...)

If the created classes are indeed an entity and you want to edit them, you need to provide more information:

VaR producttoedit = CTX. executestorequery <product> (SQL,
"Products ",
Mergeoption. preservechanges
). Single ();
Producttoedit. categoryid = 6;
CTX. savechanges ();

The second parameter is the name of the entityset to which the product belongs. The third parameter tells EF how to combine these entity with copies that may already exist in objectcontext.

If you have done this correctly, the changes made to producttoedit will be submitted back to the database when savechanges () is called.

Use executestorecommand ():

This is very simple. You can execute some commands, such as a batch update method.ProgramAny data returned internally is representative of the number of affected rows.

/// 10% inflation day!
CTX. executestorecommand (
"Update products set price = price * 1.1"
);

This is that simple.

Enjoy.