Loading presentation...

Present Remotely

Send the link below via email or IM


Present to your audience

Start remote presentation

  • Invited audience members will follow you as you navigate and present
  • People invited to a presentation do not need a Prezi account
  • This link expires 10 minutes after you close the presentation
  • A maximum of 30 users can follow your presentation
  • Learn more about this feature in our knowledge base article

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.


Make your likes visible on Facebook?

Connect your Facebook account to Prezi and let your likes appear on your timeline.
You can change this under Settings & Account at any time.

No, thanks

Entity Framework Code First: Advanced Querying with LINQ

Advanced querying using Entity Framework Code First and LINQ

Ian Lovell

on 16 November 2012

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of Entity Framework Code First: Advanced Querying with LINQ

Entity Framework Advanced Querying with LINQ Introduced in C# 3.0
Allow you to add methods to an existing type without creating a new derived type, recompiling, or otherwise modifying the original type. Extension Methods - a reminder public class StringExtensions
public static int ToInt32(this string val)
return Convert.ToInt32(val);

var myInt = "5".ToInt32();
var myInt = StringExtensions.ToInt32("5"); A delegate is a type that references a method
Similar to a function pointer in C++, they allow methods to be passed as parameters Delegates public delegate int PerformCalculation(int x, int y);

public int MyMethod(int x, int y)
return x + y;

PerformCalculation foo = MyMethod;
Console.WriteLine(foo(2, 2));

public void AnotherMethod(PerformCalculation foo)
var x = ...
vay y = ...
var result = foo(x, y);
} Anonymous Methods Prior to C# 2.0, the only way to declare a delgate was to use named methods
C# 2.0 introduced anonymous methods PerformCalculation foo = delegate (int x, int y)
{ return x + y; };

foo(2, 2); Lambdas Introduced in C# 3.0
An inline anonymous delegate instance
There are expression lambdas and statement lambdas
Expression lambdas have an expression on the right hand side: (input parameters) => expression

PerformCalculation foo = (x, y) => x + y; The parentheses are optional only if the lambda has one input parameter, and the compiler can infer the parameter type, otherwise they are required.
Two or more input parameters are separated by commas enclosed in parenthesis
Sometimes it is difficult or impossible for the compiler to infer the input types. You can specify the types explicitly.
Specify zero input parameters with empty parenthesis Expression Lambdas (x, y) => x == y;
(int x, string s) => s.Length > x;
() => SomeMethod();
(x) => x++;
x => x++; Similar to an expression lambda, but contains one or more statements
The body of a statement lambda can consist of any number of statements; however, in practice there are typically as few as possible
Statements placed between braces { } Statement Lambdas (input parameters) => { statement; };

foo = (x, y) =>
var result = x + y;
return result;
}; The .NET framework provides two useful generic delegate types that can be used instead of defining your own
Action is a delegate that can take 0-16 input parameters and returns nothing
Func is a delegate that can take 0-16 input parameters and returns a value Action and Func delegate void Add(int x, int y);
Add foo = (x, y) => { };

Action<int, int> foo = (x, y) => { };

Func<string, int> foo = s => s.Length; Expression lambdas are special in that they can be used to both represent a delegate instance and an expression tree
Expression trees represent code in a tree-like data structure where each node is an expression, for example a method call or a binary operation such as x < y
You can compile and run code represented by expression trees, or you can inspect and modify the expression tree at runtime
You cannot use a statement lambda in an expression tree
When a lambda expression is assigned to a variable of type Expression, the compiler emits code to build an expression tree that represents the lambda expression Expression Trees Expression<Func<int, bool>> expr = num => num < 5;

ParameterExpression param = (ParameterExpression)exprTree.Parameters[0];
BinaryExpression operation = (BinaryExpression)exprTree.Body;
ParameterExpression left = (ParameterExpression)operation.Left;
ConstantExpression right = (ConstantExpression)operation.Right;

Console.WriteLine("Decomposed expression: {0} => {1} {2} {3}",
param.Name, left.Name, operation.NodeType, right.Value);

Func<int, bool> foo = expr.Compile();

Console.WriteLine(foo(5)); Expression Tree example There are two important interfaces used extensively in LINQ - IEnumerable<T> and IQueryable<T>
IQueryable<T> inherits from IEnumerable<T>
The difference is essentially whether your query is composable or not.
IQueryable<T> allows us to work with expression trees
IEnumerable<T> is strictly in memory operations
Both are lazy by default The two worlds of LINQ public IQueryable<Customer> GetAllQueryable()
return context.Customers;

public IEnumerable<Customer> GetAllEnumerable()
return context.Customers;
} IQueryable<T> vs IEnumerable<T> var customers = repostiory.GetAllQueryable().OrderBy(c => c.Name).Take(10); IQueryable<T> IQueryable operators are implemented by Queryable which is a static class in the .NET framework
All of the standard query operators are extension methods
On Queryable, the extension methods extend IQueryable<T>
Queryable extension methods take an Expression
Therefore, the compiler is converting your expression lambda into an expression (Expression), not a delegate instance (e.g. Func/Action) public static IOrderedQueryable<TSource> OrderBy<TSource, TKey>(this IQueryable<TSource> source, Expression<Func<TSource, TKey>> keySelector) LINQ provider has a holistic view of a query
The query is built up as an expression tree that the LINQ provider can interrogate to translate the entire query into a suitable format (e.g. SQL)
The generated queries are therefore more efficient as the query can be performed in its entirety
In the case of LINQ to Entities this means that ordering, sorting, filtering and projection can be performed in SQL Server rather than on the client IQueryable<T> Queries are performed in process on in memory objects
In comparison, any part of the query composed on an IEnumerable<T> will be performed on the client, in the sequence that the query is composed (i.e. the order of the standard query operators)
The IEnumerable<T> operators are implemented as extension methods by the Enumerable static class in the .NET framework
All of these operators take delegate instances (i.e. Func/Action, not Expression)
In this case, the compiler is converting your expression lambdas into delegate instances, not expression trees IEumerable<T> var customers = repository.GetAllEnumerable().OrderBy(c => c.Name).Take(10); Here, all customers are returned from SQL Server, and the ordering and filtering are performed on the client.
After the GetAllEnumerable returns, you are actually using LINQ to Objects, not the LINQ to Entities provider What does a query produce? Laziness When you write this query, you aren't actually materializing the objects that the query returns, you are simply building a data structure that describes the query
Even for IEnumerable<T>, nothing is going to happen until you either attempt to iterator over the query, or use one of the standard query operators that forces the query to be executed string[] strs = new [] { "Hello", "world" };
var query = strs.Where(s => s.Length == 5); Laziness can be both good and bad
In LINQ, it is known as deferred execution
It can be good in that we may have conditional logic that means the query may never run. In this case, even though we've built a query, the database will never be hit. This (along with the object materialization) is the expensive part.
The problem with deferred execution is that each time we iterate, we execute a new query.
Deferred execution doesn't fail fast Deferred Execution Stopping Deferred Execution Use one of the "greedy" operators
ToList(), ToArray(), ToDictionary()
Produce a concrete type
Sum(), First(), Single(), Count()
All of these operators have to produce a concrete result so the query is executed immediately
These do not return IQueryable<T> or IEnumerable<T>, but actual concrete results (a List, an object, an int etc)
Note also that with non-greedy operators, some are streaming, and some consume everything once execution begins var employees = context.Employees.Where(e => e.Age > 20).ToList();
foreach (var e in employees)

foreach (var e in employees)
} Can be used with query syntax to create temporary variables in a query
Generally used to make queries more readable let keyword var query = from e in repository.GetAll()
where e.Name.ToLower() == "ian"
select e.Name.ToLower();

var query = from e in repository.GetAll()
let lname = e.Name.ToLower()
where lname == "ian"
select lname;
Can be used with query syntax
Is used to continue a query after a projection
The original range variable goes out of scope
Can be used to simplify a query by breaking it down, but the common use of into is with grouping into keyword var employees =
from employee in repository.GetAll()
where employee.Name.StartsWith("P")
select employee
into pEmployee
where pEmployee.Name.Length < 5
select pEmployee; Transforms a sequence into a sequence of groups
The group operator will end a query, you can use into in query syntax to continue the query
Unlike SQL, it returns a hierarchical data structure Grouping var groupedEmployees =
from employee in repository.GetAll()
group employee by employee.Name[0] into letterGroup
orderby letterGroup.Key ascending
select letterGroup;

foreach (var group in groupedEmployees)
foreach (var employee in group)
} Grouping with a Composite Key var groupedEmployees =
from employee in repository.GetAll()
group employee by new
FirstLetter = employee.Name[0]

foreach (var group in groupEmployees)
Console.WriteLine("{0} {1}", group.Key.DepartmentID, group.Key.FirstLetter);
foreach (var employee in group)
} var groupedEmployees =
from employee in repository.GetAll()
group employee
by new { employee.DepartmentID, FirstLetter = employee.Name[0] }
into gEmployee
where gEmployee.Count() > 1
select new
DepartmentID = gEmployee.Key.DepartmentID,
FirstLetter = gEmployee.Key.FirstLetter,
Count = gEmployee.Count(),
Employees = gEmployee
}; Grouping and Projecting We can do anything in fluent syntax that we can do in query syntax (and more) Grouping and Projection with fluent syntax var groupedEmployees =
.GroupBy(e => new { e.DepartmentID, FirstLetter = e.Name[0]})
.Where(g => g.Count() > 1)
.Select(g => new {
Count = g.Count()
}); Nested queries are used in scenarios similar to nested SELECT commands in T-SQL
Beware of how many times the inner query executes when using IEnumerable<T> Nested Queries var engineeringEmployees =
from employee in employeeRepository.GetAll()
where employee.DepartmentID ==
(from department in departmentRepository.GetAll()
where department.Name == "Engineering"
select department).First().ID
select employee; Correlated Subqueries One place where nested queries are useful is with correlated subqueries
This is where the outer range variable appears inside the nested query
Performance issues still possible
Joins are often preferable var employees =
from employee in employeeRepository.GetAll()
select new
Name = employee.Name,
Department = (from d in departmentRep.GetAll()
where department.ID == employee.DepartmentID
select department).First().Name
} Work very similarly to joins in SQL
They connect two collections of data
Connects an outer and inner sequence
Uses equal keyword, not an == expression
Inner sequence is loaded into a keyed collection
Much faster than subquery for in-memory sequence
Equivalent to INNER JOIN in SQL
Only returns the intersection of two sequences
Produces a flat sequence
Only equijoins are supported
Joins var employees =
from employee in employeeRep.GetAll()
join department in departmentRep.GetAll()
on employee.DepartmentID equals department.ID
select new { employee.Name, Department = department.Name }; You rarely have to use joins explicitly with LINQ to Entities, because Entity Framework already knows about your entity relationships from your mappings
Instead, you use the navigation properties to get related data, and Entity Framework takes care of performing the joins for you, and materialising the results
Joins would be used if you are trying to correlate data between two tables that have no relationship established between them
You may also use a join if you don't have any navigation properties on your types, just the primary/foreign keys Joins with Entity Framework Group Joins
Joins return flat result sets, but group joins return hierarchical result sets
Occurs when into appears after a join
Outputs groups of sequences and preserves hierarchy
Equivalent to a LEFT OUTER JOIN in SQL
The order of the collections is important, as it is a left join, the outer collection items are always included, and the into range variable becomes an IEnumerable<T> of the inner colleciton item
After into, you still have access to the outer range variable, but not the inner range variable var employeesByDepartment =
from department in departmentRep.GetAll()
join employee in employeeRep.GetAll()
on department.ID equals employee.DepartmentID
into eg
select new { Name = department.Name, Employees = eg };

foreach (var department in employeesByDepartment)
foreach (var employee in department.Empoyees) { ... }
} Can use many join keywords in same query
E.g. join Customer to Orders to OrderItems
Possible to join with composite keys
Same strategy as composite grouping - need to construct a key object
A join is an inner join
A group join is a left join
To perform a cross join (every entity instance matched with every entity instance in the other table) skip the join keyword: Join Hints var query =
from employee in employeeRep.GetAll()
from department in departmentRep.GetAll()
select new { EmployeeName = employee.Name, DepName = department.Name }; Distinct - remove duplicate values
Except - returns the differences of two sequences
Intersect - returns the intersection of two sequences
Union - returns unique elements from both sequences
Concat - returns all elements from both sequences
Set Operations int[] twos = { 2, 2, 4, 6, 8, 10 };
int[] threes = { 3, 6, 9, 12, 15 };

// 6
var intersection = twos.Intersect(threes);

// 2, 4, 8, 10
var except = twos.Except(threes);

// 2, 4, 6, 8, 10, 3, 9, 12, 15
var union = twos.Union(threes);

// 2, 2, 4, 6, 8, 10, 3, 9, 12, 15
var concat = twos.Concat(threes); Operators that test equality use the default IEqualityComparer<T>
Operators will accept a custom comparer
Anonymous types are special, in that their equality is determined by a value comparison of all of their public properties
I.e. if all of their property values are the same, then the two anonymous type instances are considered equal, even though they are reference types
Determining Equality Select - Projects values in a sequence based on a transformation function
SelectMany - Flattens and projects across multiple sequences Projection Operators // IEnumerable<IEnumerable<Order>>
var orders = context.Customers.Select(c => c.Orders);

// IEnumerable<Order>
var orders = context.Customers.SelectMany(c => c.Orders);

Full transcript