Using QueryView to filter collection

3. Eager and Lazy Loading entities and Navigation properties Navigation properties

3.4 Relationship Span

4.1.2 Using QueryView to filter collection

Console.WriteLine("{0}\t {1}\t {2}\t {3}",

cus.CustomerID, cus.CompanyName, cus.UserName == null ?

"No UserName" : cus.UserName, cus.HasAccount);

Console.WriteLine();

}

The above code queries for two customers, one which has a customer account and other one do not. Screen shot below confirms the result of the query for HANAR which does not have an account.

Note: Since queryviews are readonly, there is no support for insert, update and delete of an entity by entity framework. You are required to create stored

procedures for each entity and associations need to be saved to the database. The mapping of the stored procedures also has to be done manually by editing edmx file because in the current version of the designer does not support mapping stored procedures to entities that use queryviews.

4.1.2 Using QueryView to filter collection

Problem: You have customers and Orders defined on the database. Recently the application was retrieving all the orders for a given customer but new

business rules states that application should only retrieve orders for customers that were placed after 1998. Any orders place before 1998 year should not be fetched. In addition any orders that are marked as deleted should not be retrieved. You are told to implement this logic across the entire application.

Solution: Although we could write a query that would filter the orders to the requirement stated above. However that would require fixing the orders query at numerous places and may lead to bugs. Instead we can modify the Orders ObjectQuery exposed on the Objectcontext so that anytime Orders

entitycollection is accessed; the filter is already applied on it. The best way to do is to remove the entityset mapping which is mapped to a table. Instead Orders EntitySet should be mapped to a custom query that filters the orders to remove deleted orders and orders place before 1998. If there are entities

associated with Orders, they will also have to rewritten to use QueryView even though their implementation does not change. This is a requirement imposed by the entity framework because it cannot confirm the graph consistency and provide validation on part of the model that uses default mapping with the rest which uses QueryView.

Discussion: The diagram below shows how customer and orders are related to each other.

To apply filter on Orders we need to define a QueryView which will filter the orders. Since Orders are related to customer, we also need to define

QueryView for Customers and the association between customer and orders.

When we import the tables shown above using Update Model Wizard into

EDM, we get default mappings of entities to tables. Screenshot below show how the entity data model looks like.

Since we are going to be using custom mapping we need to remove the Table mapping for both Customer and Order from mapping window. Since

QueryView is not supported by the designer, we will modify the EntitySet mapping for Customers Orders and the association between customer and Orders. Example below shows the mapping for Customers entityset.

select value

QueryWithFilterModel.Customer(c.CustomerID,c.ContactName) from

QueryWithFilterModelStoreContainer.Customers as c

</QueryView>

</EntitySetMapping>

Since customers are related to Orders, we are forced to provide mapping for customers. The esql query above retrieves customers from the store model and sets the value for Customer entity. QueryWithFilterModel is the name of the conceptual model namespace and QueryWithFilterModelStoreContainer is the StorageEntityContainer name.

The QueryView for Orders EntitySet looks like this

select value

QueryWithFilterModel.Order(o.OrderID,o.OrderDate)

from

QueryWithFilterModelStoreContainer.Orders as o

where !o.IsDeleted and o.OrderDate >=

cast('1998/1/1' as Edm.DateTime)

</QueryView>

</EntitySetMapping>

On the above model, I am querying the store model for only orders that are not deleted and which have an orderdate greater than the date specified in the query. Notice to cast my string as date, I am using cast operator available on esql and specifying the type to be Edm.DateTime.

Next QueryView we need to define is the association between Customers and Orders which is define on the conceptual model like this

<End Role="Customers"

Type="QueryWithFilterModel.Customers" Multiplicity="1" />

<End Role="Orders" Type="QueryWithFilterModel.Orders"

Multiplicity="*" />

</Association>

The association requires the first parameter to be customer entity key followed by Orders entity key. Example below shows the query required create the association defined above between customer and Orders.

On the above associationSetMapping, I am retrieving Orders from my storage model and then using createref method exposed on esql I am retrieving Customer entity key followed by Orders entity key. Using those keys I populate the association defined on the conceptual model with

customer and order entity keys. It is important to explain some of prefixes uses with entities. Table below shows the prefixes used with the entity and how are they mapped with entity framework.

StorageEntityContainer QueryWithFilterModelStoreContainer CdmEntityContainer QueryViewWithFilter

concpetualModel Namespace QueryWithFilterModel

To confirm that on fetching orders for a customer, we do not retrieve all the orders, we can write a query that returns all the orders for a customer. In the example below I am using a regular sql query to get the count of the orders placed by SAVEA customer and then using linq, I am querying my

conceptual model which applies my QueryView to only retrieve orders that meet our query criteria. Screen shot below shows our result.

var db = new QueryViewWithFilter.QueryViewWithFilter();

var cmd = db.CreateStoreCommand("SELECT COUNT(*) FROM ORDERS WHERE CUSTOMERID = 'SAVEA'");

cmd.Connection.Open();

var totalorders =

Convert.ToInt32(cmd.ExecuteScalar());

cmd.Connection.Close();

var cus = db.Customers.Include("Orders").First(c =>

c.CustomerID == "SAVEA");

Console.WriteLine("Actual Orders " + totalorders);

Console.WriteLine("Filtered Orders " + cus.Orders.Count());

4.1.3 QueryView to map Many to Many Relationship with PlayLoad

Problem: You have defined three tables in the database called Clubs,

Members and Membership. A club can have many members and member can be part of many clubs. To map clubs to members, we have created a link table Membership which defines the many to Many association. The database diagram is shown below.

In addition to the primary key field from Clubs and Members, Membership table also contains an additional column MemberShipType that defines what kind of membership the member holds. Membership type could be of two types; Gold and Platinum. You want to expose the database relationship as Many to Many association between Club and Members. There should be two types of associations between Clubs and Members. First association should expose a navigation property GoldMembers entity collection and second association should expose a navigation property PlatiniumMembers.

GoldMembers should only retrieve GoldMembers from the membership table and PlatiniumMembers should only retrieve Members who have platinum membership with the club. In addition you want to be able to insert and update and delete different types of members.

Solution: When we import the Many to Many relationships with payload column like MembershipType, entity framework does not get rid of the link table. To access the members for a given club, you have to traverse

Membership table to access the members. What we need is two direct associations between Clubs and Members. First association GoldMembers will return GoldMembers of the club and PlatiniumMembers association would return PlatiniumMembers of the club. To map a single Many to Many relationship with payload as two Many to Many relationship is not directly supported by the entity framework or the designer. To achieve this

relationship we have to first remove the Membership link entity and create two associations between clubs and members using QueryView. First

association would return results from link table member where membership type is Gold and second association would return rows from Membership table where membership Type is Platinium. When we use QueryView with associations, we cannot use build in support to insert, update and delete Members and clubs. We also need to specify QueryView for both Clubs and Members entity. The final EDM is shown below.

Solution: To map our relational model to entity model shown above, we will use the import wizard to get head start which will help us generate SSDL, CSDL and MSL. Screen shot below shows the model as it looks like when we use the import wizard to import Clubs, Membership and Members table.

We will first delete Membership entity because we want to have a direct Many to Many association between club and Member. To create association returning GoldMembers, right click on Club entity and select Association.

On the screen shot below, we have selected Members with a multiplicity of Many and Clubs with multiplicity of Many. We have given the association Name Gold and use GoldMembers navigation property to access

GoldMembers for the club.

Next we need to create an association for Platinum members for the club.

We use the similar step to create PlatiniumMembers association between Club and Members entity. On the screen shot below we have set Many multiplicity for Club and Members and use PlatiniumMembers navigation property to access Platinum members for the club.

To map our two associations, we will use QueryView to extract appropriate data from Membership link table. Using QueryView requires that all entities to also use queryview and cannot use the default mapping from the entity framework. Therefore we also need to remove the Club and Membership association created by the import wizard by selecting the entity, choose the mapping window and delete the mapping.

The QueryView for member queries the store model and map the results to member entity. After defining the QueryView, we do not get any support for insert, update and delete for member entity. Therefore we need to create stored procedures and map the stored procedure’s parameter to properties on the entity. The msl below shows mapping required for member entity.

MSL

select value

QueryViewWithManyToMany.Member(m.MemberId,m.Name)

from

To define the stored procedure called inside MSL, we can either import the stored procedure from database using the designer or declare the store

procedure right inside the command text property of function declaration inside the ssdl. SSDL shown below uses the second option by defining the code for the stored procedure inside the CommandText property. This is for demonstration purpose or if u do not have permissions to create database objects on the database. If you have the appropriate permissions, it would be preferable to create stored procedure, this way you would get compilation for the stored procedure and benefit the compile time check offered by the

database engine. SSDL below shows function declaration need to insert, update and delete member entity.

<Function Name="InsertMember" IsComposable="false"

Schema="dbo">

insert into QueryView.Members(Name) values

<Function Name="UpdateMember" IsComposable="false"

Schema="dbo">

update QueryView.Members set Name = @Name

</CommandText>

<Function Name="DeleteMember" IsComposable="false"

Schema="dbo">

delete QueryView.Members where MemberId =

@MemberId

</CommandText>

<Parameter Name="MemberId" Type="int" Mode="In"

</Function>

For insert stored procedure, MemberId is returned by using scope identity which returns the id of the last record inserted. The memberid returned is mapped to MemberId on member entity using ResultBinding. Similary

defining QueryView for Club and mapping the stored procedure on the ssdl to properties defined on Club entity is very similar to Member entity. For

completeness MSL and SSDL below shows the mapping required.

MSL

<Function Name="InsertClub" IsComposable="false"

Schema="dbo">

<Function Name="UpdateClub" IsComposable="false"

Schema="dbo">

update QueryView.Clubs set ClubName =

@ClubName

</CommandText>

<Parameter Name="ClubName" Type="varchar"

Mode="In" />

</Function>

<Function Name="DeleteClub" IsComposable="false"

Schema="dbo">

delete QueryView.Clubs where ClubId =

@ClubId

</CommandText>

</Function>

To configure the two many to many associations defined on the conceptual model, we need to use QueryView to only retrieve data that is applicable for the association. For instance for ClubMembers we need to only retrieve relationship records where MemberShipType is G for Gold members.

Similarly for Platinium Members, only records with MembershipType of P are returned. MSL below shows the queryview required for GoldMember association since Gold and Platinum are similar in behavior.

<AssociationSetMapping TypeName="QueryViewWithManyToMany.Gold"

<DeleteFunction

On the above association mapping, I am specifying the Name gold which is defined on my conceptual model along with the TypeName for the

association. The query view for Gold members queries the Membership table defined on the stored model filtering the records where MembershipType is G. The Gold association on the conceptual model requires two entity keys.

The first entity key is the club and second is the member entity key. To obtain the entity key, we are using createref function available on esql that uses row function to return the entity key when passed the primary key of the table.

The store procedure mapping grabs the entity key from both ends of the association. From the club end of the association, I am retrieving ClubId and mapping it to ClubId parameter on the stored procedure. For Members end of the association, I am retrieving the MemberId and mapping it to MemberId parameter on the stored procedure. The stored procedure mapping defined for the Gold association is as follow.

<Function Name="InsertGoldMembership" IsComposable="false"

<Parameter Name="MemberId" Type="int" Mode="In"

delete QueryView.Membership where ClubId =

@ClubId and MemberId = @MemberId

</CommandText>

</Function>

For inserting GoldMembership, I am passing G as the third parameter to indicate that Membership inserted is of type Gold. We cannot reuse the same stored procedure for PlatiniumMembership because the last parameter to the insert would be P instead of G. However Deleting Gold or Platinum

membership is not any different so we can map the same delete stored procedure for both mappings.

After completing the mapping, we should have two entity collections exposed on a Club, GoldMembers and Platinium members. And we can add members to both collection and it would be inserted with appropriate

MemberType in the Membership table. In the code below, I am creating a football club and assigning two Gold club members and one Platinium members and saving the club to the database. To make sure that clubs and members got inserted correctly, using the second datacontext, I am retrieving the Club and eagerly loading both Platinum and Gold members collection by using Include. To confirm both collections have the appropriate members, I am printing the results to output window. Screen shot below shows the output on the console window.

var db = new QueryViewMM();

var club = new Club

.Include("PlatiniumMembers").Include("GoldMembers")

.First(c => c.ClubName ==

"Football Club");

Console.WriteLine("Club Name " + footballclub.ClubName);

Console.WriteLine("Gold Club Members");

foreach (var member in footballclub.GoldMembers) {

Console.WriteLine(member.Name);

}

Console.WriteLine("Platinium Members");

foreach (var member in footballclub.PlatiniumMembers) {

Console.WriteLine(member.Name);

}

4.2 DefiningQuery

Introduction

DefingQuery is a query view that is defined on the store model. When a view is imported from the database using Entity model wizard, EF creates a

definingQuery that does a select on the view. If you import a table that does

not have a primary key, EF will create a DefiningQuery that does a select on the table. In additional it will infer that all columns in the table participate in primary key and will mark all columns as keys on the store model. Similarly on the conceptual side, it will also make all properties as being entity keys.

This behavior is very confusing because if you do not know that EF

framework has mapped your table as a DefiningQuery, you will be clueless as to why your inserts, updates and deletes are failing on the conceptual entity.

This brings us to a discussion of how EF handles crud operations on entities that are mapped using DefiningQuery. Since DefiningQuery is only a read-only view of data written in store specific syntax, EF does not have any understanding of how the entity is stored in the database. To save an entity that is mapped using DefiningQuery, we have to declare stored procedures on the store model and then map the stored procedures to insert, update and delete operations on the conceptual entity.

DefiningQueries allows you to use native sql syntax to create any complex projection and expose the projection as a view that an entity on the conceptual model can be mapped to. If there are modeling scenarios that you cannot accomplish using Ef because of the way the data is stored in the database, you can transform the data using DefiningQueries and project it in a way that is friendly with EF modeling scenarios. DefiningQueries does not support parameters so a sql written inside of DefingQuery section cannot contain parameters that you specify a value for at runtime.

Entity framework also allows you to define a complex view using QueryView that uses esql. Unlike DefiningQueries, QuerViews are declared inside the mapping section and queries the store model to fetch its data. It is a preferred approach of defining complex queries. However if you cannot represent a projection using QueryView because it does not support all the esql operators, you should use DefiningQuery as the last resort. Some common use of

DefiningQueries would be to use constructs that cannot be mapped directly either using esql or Linq. For instance to create recursive queries sql server provides Common table Expression which does not have any translation to either esql or Linq. To use recursive queries, we can use Common Table Expression inside of DefiningQuery and rest of conceptual model can simply use the view without knowing the underlying details of the store model. In

version 1 release of EF, there is no support for spatial data type. To overcome these limitations, we can create a DefiningQuery which brings spatial data type as image and then inside of the partial class we can transform the data into a Geographical data type which will allow us to perform domain specific activities like plotting a point on map. Basically DefiningQuery provides unlimited capabilities to exploit the data agnostic features which EF cannot leverage.

Another great use of DefiningQueries is to declare a complicated sql join inline. For maintainability perspective, it is good to define a view in the

In document Entity Framework Learning Guide (Page 139-157)