LINQ to Entity - Object Services API

Scope -

 

• Object Services API
1. Querying                                              
2. Using Query Operators                          
3. Updating, Inserting, and Deleting

 

• Compiled Queries

 

 

Object Services API

 

-          The nucleus of EF is its layer of abstraction that is divided into conceptual, mapping, and logical layers making up the EDM.

-          EF utilizes two APIs, Object Services and the Entity Client, for working with the EDM.

 

-          The Entity Client API is a lower-level .NET data provider, which provides a mechanism for interacting directly with your Conceptual Model by using the new query language, ESQL.

-          The Object Services API is principally built on top of the entity client provider and provides the functionality to query in ESQL along with LINQ to Entities (that is, object queries).

-          Object Services supports two execution models against the EDM: LINQ to Entities and ESQL.

 

Here we’ll cover LINQ to Entities in EF.

 

1. Querying                                              
2. Using Query Operators                          
3. Updating, Inserting, and Deleting

 

1). Querying – Using Object Service API

 

• Canonical Query Trees
• ObjectQuery class
• Loading associated Objects
• GetObjectByKey method
• TryGetObjectByKey method

 

 

Canonical Query Trees

 

• Below in the left pane - The C# code is formed like a standard LINQ query and easy to understand.
• Howerver clearly, the SQL generated from EF is more complex than what you would typically write in this situation.
• The reason for this is that EF (and really, ADO.NET under the covers) is using an algorithm for creating Canonical Query Trees (CQTs).
• The CQTs generated by EF are Highly Optimized to run at peak efficiency.
• To create a provider neutral mechanism for communicating with any data store, and to be able to ensure performance in both simple and complex situations, CQTs are the means to the end.

 

 

using(AdventureWorksEntities aw = new AdventureWorksEntities()) {    ObjectQuery<EmployeeDepartmentHistory> edh = aw.EmployeeDepartmentHistory;    IQueryable<EmployeeDepartmentHistory> departmentHistories = from dep in edh                                                                                                   select dep;    foreach (var edh in departmentHistories)    {      Debug.Print(edh.EmployeeID.ToString());    } }

-- SQL Generated from EF SELECT 1 AS [C1], [Extent1].[EmployeeID] AS [EmployeeID], [Extent1].[DepartmentID] AS [DepartmentID], [Extent1].[ShiftID] AS [ShiftID], [Extent1].[StartDate] AS [StartDate], [Extent1].[EndDate] AS [EndDate], [Extent1].[ModifiedDate] AS [ModifiedDate] FROM [HumanResources].[EmployeeDepartmentHistory] AS [Extent1]

 

Object Query Class

 

The another important facet regarding the query is the ObjectQuery class - the type that is returned from the aw.EmployeeDepartmentHistory call.

 

·         The ObjectQuery class is a generic class that is part of System.Data.Objects.

·         The purpose of the ObjectQuery class is to return collections of typed entities.

·         Because ObjectQuery is associated with your ObjectContext, it has the metadata and connection information needed to translate your LINQ query into T-SQL.

 

Load association objects:

 

You have two options to enable your query to load association objects.

 

• The first option, to specify a Query Path, is used to predefine the object graph returned by your query. This provides a mechanism for retrieving all your objects in a single SQL query.
• The second option, using Explicit Loads, creates multiple SQL queries and round-trips to the database to populate your entities.

 

Query Path –

 

• Here you work using ‘Include’ keyword.

 

using(AdventureWorksEntities aw = new AdventureWorksEntities()) {    ObjectQuery<EmployeeDepartmentHistory> edh = aw.EmployeeDepartmentHistory. Include("Employee");    IQueryable<EmployeeDepartmentHistory> departmentHistories = from dep in edh                                                                                                   select dep;    foreach (var edh in departmentHistories)    {      Debug.Print(edh.Employee.BirthDate.ToString());    } }

using(AdventureWorksEntities aw = new AdventureWorksEntities()) {    ObjectQuery<EmployeeDepartmentHistory> edh = aw.EmployeeDepartmentHistory. Include("Employee. Contact")");    IQueryable<EmployeeDepartmentHistory> departmentHistories = from dep in edh                                                                                                   select dep;    foreach (var edh in departmentHistories)    {      Debug.Print(edh.Employee. Contact. EmailAddress.ToString());    } }

 

 

Explicit Loads –

 

 

using(AdventureWorksEntities aw = new AdventureWorksEntities()) {    ObjectQuery<Employee> employees =aw.Employee;    foreach (Employee emp in employees)    {             emp.EmployeeDepartmentHistory.Load();             Debug.Print(emp.EmployeeDepartmentHistory.First().DepartmentID.ToString());    } }

 

 

GetObjectByKey method

 

-          This technique can be used to retrieve an entity from a persisted store without creating and executing an object query.

-          The GetObjectByKey method is part of the ObjectContext class and returns an object representation of your entity based on the EntityKey that you supply.

 

-          Below the code retrieve an Employee entity by the EmployeeID key.

-          Here first we are creating a new KeyValuePair class with EmployeeID set to the integer 21.

-          Next, we created a new EntityKey, and then pass this EntityKey into the ObjectContext.

 

-          Notice - ObjectContext is not in a using block as you have seen in other examples.

 

-          Reason - By making your ObjectContext shared, you can Increase Performance because the object graph and data is cached in the ObjectContext. For example, the query below will be executed only a single time if you are using a shared ObjectContext, whereas it will be executed every time if you are disposing of the ObjectContext (the using block). This probably seems like a great idea; caching is great. However, the ObjectContext is not thread-safe, and as your working set grows and the database changes, issues can occur.

 

 

//C# Example   AdventureWorksEntities aw = new AdventureWorksEntities(); IEnumerable<KeyValuePair<string, object>> entityKeyValues = new KeyValuePair<string, object>[] {new KeyValuePair<string, object>("EmployeeID", 21) };   EntityKey ek = new EntityKey("AdventureWorksEntities.Employee", entityKeyValues); Employee myEmp = (Employee)aw.GetObjectByKey(ek); Debug.Print(myEmp.BirthDate.ToString());

 

 

TryGetObjectByKey method

 

-          This is similar to the GetObjectByKey method - used to retrieve an object from the persistence layer by an EntityKey.

-          The difference with this method is that it accepts a reference object as a parameter, and returns a Boolean indicating whether it was successful or failed in the retrieval.

 

//C# Example   AdventureWorksEntities aw = new AdventureWorksEntities(); IEnumerable<KeyValuePair<string, object>> entityKeyValues = new KeyValuePair<string, object>[] {new KeyValuePair<string, object>("EmployeeID", 21) };   EntityKey ek = new EntityKey("AdventureWorksEntities.Employee", entityKeyValues); Employee myEmp = (Employee)aw.GetObjectByKey(ek); Debug.Print(myEmp.BirthDate.ToString());

//C# Example   AdventureWorksEntities aw = new AdventureWorksEntities(); IEnumerable<KeyValuePair<string, object>> entityKeyValues = new KeyValuePair<string, object>[] {new KeyValuePair<string, object>("EmployeeID", 999999) };   EntityKey ek = new EntityKey("AdventureWorksEntities.Employee", entityKeyValues); object entity; bool isGood = aw.TryGetObjectByKey(ek,out entity);   if (isGood) {    Employee emp = (Employee)entity;    Debug.Print(emp.BirthDate.ToString()); }

 

 

2). Using Query Operators

 

When working with LINQ, you have two options when querying any store:

 

-          Query Expression Types and

-          Method-Based Expression Types

 

If both syntactical structures generate the same SQL code, which one is better?”

The fact is that it comes down to personal preference. However, the query-based expression syntax is more readable and easier to understand because it is similar to SQL. Otherwise there is fundamentally no difference between the two when it comes to the conversion from C# to SQL.

 

// Query Based   List<Employee> emp = (from r in aw.Employee                                     where r.SickLeaveHours > 40                                     select r).ToList();	// Method Based   List<Employee> emp2 = aw.Employee.Where(s => s.SickLeaveHours > 40).ToList();

 

LINQ and LINQ to Entities provide you with a robust set of below.

 

-          Standard Query Operators –

-          Set methods

-          Join Methods

-          Singleton Methods

 

 

Standard Query Operators

 

-          Operators that come with LINQ include functionality for Filtering, Projection, Sorting, Aggregation, Paging, & Grouping

 

Projection	-          Projection with the use of the select keyword. -          As projection is functionally the transformation of a result set into a different form, the select and select many operators are the methods for accomplishing this.
Filtering	-          Filtering is done with the where clause.
Sorting	-          Filtering is done with OrderBy, OrderByDescending, Reverse, ThenBy, and ThenByDescending methods. -          Furthermore, along with query expressions for ordering, you can also use the LINQ C# keywords orderby, descending, and ascending within your query.
Aggregation
Paging
Grouping	-          Grouping, which is the function of grouping data based on some criteria, can be accomplished by using the GroupBy method.

 

e.g.

 

// OrderBy and GroupBy in LINQ to Entities   List<Employee> emp = (from r in aw.Employee                                     orderby r.HireDate                                     select r).ToList();   List<IGrouping<DateTime,Employee>> emp2 = (from r in aw.Employee                                                             select r).GroupBy(s=>s.HireDate).ToList();

 

 

Set Methods

 

-          The purpose of the set methods is to refine the result set based on the existence (or nonexistence) of elements in the same or another set.

-          Examples of these methods include All, Any, Concat, Contains, DefaultIfEmpty, Distinct, EqualAll, Except, Intersect, and Union.

 

e.g.

 

var firstQuery = from s in aw.Contact                         where s.EmailAddress.StartsWith("a")                         select new                         {                         s.FirstName,                         s.MiddleName,                         s.LastName                         }; var secondQuery = from t in aw.Contact                         where t.Title.StartsWith("M")                         select new                         {                         t.FirstName,                         t.MiddleName,                         t.LastName                         };   firstQuery = firstQuery.Intersect(secondQuery); foreach (var u in firstQuery) {             Debug.Print(u.LastName); }

 

 

Join Methods

 

-          There are two operators that can be used for joining: the Join and GroupJoin methods.

-          As you might  expect, the purpose of the Join and GroupJoin methods is to create an association between two objects in an ad hoc manner.

-          Join methods are used when you don’t have a defined foreign-key relationship or a navigation property, but need an association query.

 

e.g.

 

var q =             (from c in aw.Contact             join emp in aw.Employee             on c.Title equals emp.Title into totEmpCont             select new { c, totEmpCont }).ToList();

 

 

Singleton Methods

 

-          These methods are broken into two categories: Aggregate methods and Element methods.

-          The aggregate methods, are used to compute a single value from a result set. These methods are the Min, Max, Average (and so forth) methods that can be used with LINQ expressions.

-          The Element methods are used to retrieve a specific element from a result set sequence. These methods are the First, Last, Single, Skip,Take (and so forth) methods, which are natively part of LINQ.

 

e.g.

 

DateTime q = (from g in aw.Employee                      select g.BirthDate).Max(); Debug.Print(q.ToString());
Employee x = (from emp in aw.Employee                      where emp.MaritalStatus == "M"                      select emp).First();

 

 

 

 

3). Updating, Inserting, and Deleting

 

Update

 

//Update an Object in EF   Employee x = (from emp in aw.Employee where emp.EmployeeID == 1 select emp).First(); x.MaritalStatus = "S"; aw.SaveChanges();	--SQL Generated from EF   exec sp_executesql N'update [HumanResources].[Employee] set [MaritalStatus] = @0 where ([EmployeeID] = @1) ',N'@0 nvarchar(1),@1 int',@0=N'S',@1=1
//C# Example   IQueryable<Employee> emp = from s in aw.Employee.Include("Contact") where s.BirthDate <= new DateTime(1989, 11, 23) && s.Gender == "F" && s.MaritalStatus == "S" select s;   foreach (Employee x in emp) {   x.Contact.EmailPromotion = 1;   x.CurrentFlag = true; } aw.SaveChanges();	--SQL Generated from EF   Two SQL update statements are executed. The first is to update the Employee table, and the second is to update the Contact table.   exec sp_executesql N'update [HumanResources].[Employee] set [MaritalStatus] = @0 where ([EmployeeID] = @1) ',N'@0 nvarchar(1),@1 int',@0=N'S',@1=1   exec sp_executesql N'update [Person].[Contact] set [EmailPromotion] = @0 where ([ContactID] = @1) ',N'@0 int,@1 int',@0=1,@1=1001

 

 

Insert

 

Note – When we add an entity (e.g Dinners) in EF, then it automaticaly generates one method ‘AddTo<EntityName>’ (Here - AddToDinners) method. This method can then be used to insert a record in an entity.

 

public void AddToDinners(Dinners dinners)

{

    base.AddObject("Dinners", dinners);

}

 

//C# Example   Department dept = new Department(); dept.GroupName = "MyGroup1"; dept.Name = "MyName"; dept.ModifiedDate = DateTime.Now; aw.AddToDepartment(dept); int changes = aw.SaveChanges();

--SQL Generated from EF   exec sp_executesql N'insert [HumanResources].[Department] ([Name], [GroupName], [ModifiedDate]) values (@0, @1, @2) select [DepartmentID] from [HumanResources].[Department] where @@ROWCOUNT > 0 and [DepartmentID] = scope_identity()', N'@0 nvarchar(6),@1 nvarchar(8),@2 datetime',@0=N'MyName',@1=N'MyGroup1',@2=''2007-12-17 18:32:56:500''

 

 

Delete

 

//C# Example   Department dept = aw.Department.Where(s => s.GroupName == "MyGroup1").First(); aw.DeleteObject(dept); int changes = aw.SaveChanges();

--SQL Generated from EF   SELECT TOP (1) [Extent1].[DepartmentID] AS [DepartmentID], [Extent1].[Name] AS [Name], [Extent1].[GroupName] AS [GroupName], [Extent1].[ModifiedDate] AS [ModifiedDate] FROM [HumanResources].[Department] AS [Extent1] WHERE N'MyGroup1' = [Extent1].[GroupName] exec sp_executesql N'delete [HumanResources].[Department] where ([DepartmentID] = @0)',N'@0 smallint',@0=17

 

 

Compiled Query –

 

-          Compiled queries offer opportunity to optimize query performance.

-          In many applications you might have code that repeatedly executes the same query, possibly with different argument values.

-          By default, LINQ to SQL parses the language expression each time to build the corresponding SQL statement, regardless of whether that expression has been seen previously.

-          Compiled queries allow LINQ to SQL to Avoid Reparsing The Expression And Regenerating the SQL statement for each repeated query.

 

 

The below code snippet shows an example of a simple compiled query, executed twice with varying parameters.

 

	DataContext db = new DataContext(); var customers = CompiledQuery.Compile((DataContext context, string filterCountry )  =>                          from c in db.Customers              where c.Orders.Count > 5              select c; foreach(var row in customers(db, "USA"))  {    Console.WriteLine(row); } foreach(var row in customers(db, "Spain")) {       Console.WriteLine(row); }

 

 

Hope this helps.

 

 

Thanks & Regards,

Arun Manglick || Senior Tech Lead