LINQ to Entities

.LINQ to Entities

LINQ to Entities

Web page by Kevin Harris of Homer IL

Please contact Kevin Harris of Homer IL concerning this web site

"Instances of the generic ObjectQuery class, which implements the generic IQueryable interface, serve as the data source for LINQ to Entities queries."


This page focuses on LINQ to Entities, the portion of LINQ which enables developers to write queries against the Entity Framework conceptual model. For a general discussion of LINQ see my LINQ page on the Microsoft Developer Site. For example LINQ coding, see 101 LINQ Samples on MSDN.

LINQ Interfaces

LINQ can query data sources that implement either the IEnumerable or IQuerable generic interfaces. LINQ to Entities queries only works with IQuerable interfaces.

  • IEnumerable - designed for working with in-memory collections such as List, Dictionary, etc. Supports LINQ to Object and LINQ to XML.

  • IQuerable - designed for working with out-of-memory data sources such as databases and web services. IQuerable is derived from IEnumerable and provides additional functionality to work with unknown data types. Supports LINQ to SQL and LINQ to Entities.


Lazy Loading vs Eager Loading

LINQ supports supports lazy loading of data by default, i.e. delays loading the data until the point where it is needed. Several LINQ statements can be used in the query which will force immediate evaluation. Some of those statements are: FirstOrDefault(), ToList(), ToArray(), Single(), and any of the aggregate functions (Sum, Max, Min, Average, Count, etc.).

LINQ to Entities can be composed of Method-Based Query Syntax, Query Expression Syntax, or a combination of the two. Below are examples of both Method Syntax and Query Syntax.

.Bills and Period Table in Entity Data Model


Bill and Period Table in Entity Data Model

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
        // Method Syntax
        public string GetBillMethodSyntax()
        {
            CathySite.DAL.Models.bill theBill;
            using (var context = new CathySiteEntities())
            {
                theBill = context.bills
                                 .Include("period")
                                 .Where(b => b.ID == 1)
                                 .FirstOrDefault();
            }
            return theBill.Payee + " " + theBill.period.Name;
        }


Method Syntax

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
        // Query Syntax
        public string GetBillQuerySyntax()
        {
            CathySite.DAL.Models.bill theBill;
            using (var context = new CathySiteEntities())
            {
                theBill = (from b in context.bills.Include("period")
                           where b.ID == 1
                           select b)
                           .FirstOrDefault();
            }
            return theBill.Payee + " " + theBill.period.Name;
        }


Query Syntax


Query Extension Methods

Extension methods are a special kind of static method which allows the addition of methods to existing types without having to create a new derived type or modify the original type. The most common extension methods are the LINQ standard query operators that add query functionality to IEnumerable. Extension methods for LINQ queries can be created by developers, as shown in the following code which creates an extension record to find a data object by its ID value.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
using CathySite.DAL.Models;
using System.Linq;

namespace CathySite.DAL.Queries
{
    public static class BillQueries
    {
        public static bill FindById (this IQueryable<bill> bills, int id)
        {
            return bills.Single(b => b.ID == id);
        }

    }
}


Query Extension Method in DAL Project

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
        // Call Query Extension Method
        public string FindTheBill()
        {
            CathySite.DAL.Models.bill theBill;
            using (var context = new CathySiteEntities())
            {
                theBill = context.bills.FindById(1);
            }
            return theBill.Payee + " " + theBill.Amount + " " + theBill.Date.ToShortDateString();
        }


Using Query Extension Method


Example LINQ Code

The follow example is used to authenticate the user. The navigation properties are used to check the permission level of the user (u.role.permission.Level > 0).

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
                using (var context = new CathySiteEntities())
                {
                    int UserID = context.users
                        .Where(u => u.User_Name == model.UserName
                            && u.Password == model.Password
                            && u.role.permission.Level > 0)
                        .Select(u => u.User_ID)
                        .FirstOrDefault();
                    if (UserID > 0 )
                    {
                        FormsAuthentication.SetAuthCookie(model.UserName, false);
                        return RedirectToAction("Index", "Admin");
                    }
                    else
                    {
                        ModelState.AddModelError("", "The User Name or Password is incorrect.");
                    }
                }


Authentication Example using Navigation Properties