Scripting Archives - Wheres My Keyboard?

Jun

learn LINQ in 5 minutes

Tags: linq, linq cheatsheet, sql, visual studio

by tanush

Translating SQL to LINQ can prove difficult for new and experienced C# developers. This post contains common SQL queries written in LINQ. I hope it’ll serve as a reference when writing LINQ queries. I’ll use a MS SQL database and Entity Framework for my examples. However, these examples can be extracted to other ORMs and databases. Also, consider reading why LINQ beats SQL to learn how to think in LINQ terms rather than translating SQL to LINQ.

Data model

We’ll use a simple data model that contains books and authors for our examples.

<span class="pln">CREATE TABLE dbo</span><span class="pun">.</span><span class="pln">authors
</span><span class="pun">(</span><span class="pln">
    authorId INT NOT NULL IDENTITY</span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="lit">1</span><span class="pun">)</span><span class="pln"> CONSTRAINT PK_authors PRIMARY KEY</span><span class="pun">,</span><span class="pln">
	name NVARCHAR</span><span class="pun">(</span><span class="pln">MAX</span><span class="pun">)</span><span class="pln"> NOT NULL</span><span class="pun">,</span><span class="pln">
	birthday DATETIME NOT NULL
</span><span class="pun">)</span><span class="pln">

CREATE TABLE dbo</span><span class="pun">.</span><span class="pln">books
</span><span class="pun">(</span><span class="pln">
    bookId INT NOT NULL IDENTITY</span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="lit">1</span><span class="pun">)</span><span class="pln"> CONSTRAINT PK_books PRIMARY KEY</span><span class="pun">,</span><span class="pln">
	title NVARCHAR</span><span class="pun">(</span><span class="pln">MAX</span><span class="pun">)</span><span class="pln"> NOT NULL</span><span class="pun">,</span><span class="pln">
	numPages INT NOT NULL</span><span class="pun">,</span><span class="pln">
	genre NVARCHAR</span><span class="pun">(</span><span class="pln">MAX</span><span class="pun">)</span><span class="pln"> NOT NULL</span><span class="pun">,</span><span class="pln">
	authorId INT NOT NULL CONSTRAINT FK_books_authors 
                              FOREIGN KEY REFERENCES dbo</span><span class="pun">.</span><span class="pln">authors</span><span class="pun">(</span><span class="pln">authorId</span><span class="pun">)</span>
<span class="pun">)</span><span class="pln">

CREATE TABLE dbo</span><span class="pun">.</span><span class="pln">articles
</span><span class="pun">(</span><span class="pln">
	articleId INT NOT NULL IDENTITY</span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="lit">1</span><span class="pun">)</span><span class="pln"> CONSTRAINT PK_articles PRIMARY KEY</span><span class="pun">,</span><span class="pln">
	title NVARCHAR</span><span class="pun">(</span><span class="pln">MAX</span><span class="pun">)</span><span class="pln"> NOT NULL</span><span class="pun">,</span><span class="pln">
	numWords INT NOT NULL</span><span class="pun">,</span><span class="pln">
	authorId INT NOT NULL CONSTRAINT FK_articles_authors 
                              FOREIGN KEY REFERENCES dbo</span><span class="pun">.</span><span class="pln">authors</span><span class="pun">(</span><span class="pln">authorId</span><span class="pun">)</span>
<span class="pun">)</span>

CREATE TABLE dbo.authors

(

authorId INT NOT NULL IDENTITY(1,1) CONSTRAINT PK_authors PRIMARY KEY,

name NVARCHAR(MAX) NOT NULL,

birthday DATETIME NOT NULL

)

CREATE TABLE dbo.books

(

bookId INT NOT NULL IDENTITY(1,1) CONSTRAINT PK_books PRIMARY KEY,

title NVARCHAR(MAX) NOT NULL,

numPages INT NOT NULL,

genre NVARCHAR(MAX) NOT NULL,

authorId INT NOT NULL CONSTRAINT FK_books_authors

FOREIGN KEY REFERENCES dbo.authors(authorId)

)

CREATE TABLE dbo.articles

(

articleId INT NOT NULL IDENTITY(1,1) CONSTRAINT PK_articles PRIMARY KEY,

title NVARCHAR(MAX) NOT NULL,

numWords INT NOT NULL,

authorId INT NOT NULL CONSTRAINT FK_articles_authors

FOREIGN KEY REFERENCES dbo.authors(authorId)

)

Entity Framework data context

<span class="kwd">public</span> <span class="kwd">class</span> <span class="typ">DataContext</span> <span class="pun">:</span> <span class="typ">DbContext</span>
<span class="pun">{</span>
    <span class="kwd">private</span> <span class="kwd">const</span> <span class="kwd">string</span> <span class="typ">ConnectionString</span> <span class="pun">=</span> 
        <span class="pun">@</span><span class="str">"Server=MyServer;Database=MyDatabase;Trusted_Connection=True;"</span><span class="pun">;</span>
    <span class="kwd">public</span> <span class="typ">DataContext</span><span class="pun">()</span> <span class="pun">:</span> <span class="kwd">base</span><span class="pun">(</span><span class="typ">ConnectionString</span><span class="pun">)</span> <span class="pun">{</span> <span class="pun">}</span>

    <span class="kwd">public</span> <span class="typ">DbSet</span><span class="pun">&lt;</span><span class="typ">Author</span><span class="pun">&gt;</span> <span class="typ">Authors</span> <span class="pun">{</span> <span class="kwd">get</span><span class="pun">;</span> <span class="kwd">set</span><span class="pun">;</span> <span class="pun">}</span>
    <span class="kwd">public</span> <span class="typ">DbSet</span><span class="pun">&lt;</span><span class="typ">Book</span><span class="pun">&gt;</span> <span class="typ">Books</span> <span class="pun">{</span> <span class="kwd">get</span><span class="pun">;</span> <span class="kwd">set</span><span class="pun">;</span> <span class="pun">}</span>
    <span class="kwd">public</span> <span class="typ">DbSet</span><span class="pun">&lt;</span><span class="typ">Article</span><span class="pun">&gt;</span> <span class="typ">Articles</span> <span class="pun">{</span> <span class="kwd">get</span><span class="pun">;</span> <span class="kwd">set</span><span class="pun">;</span> <span class="pun">}</span>

    <span class="kwd">protected</span> <span class="kwd">override</span> <span class="kwd">void</span> <span class="typ">OnModelCreating</span><span class="pun">(</span><span class="typ">DbModelBuilder</span><span class="pln"> modelBuilder</span><span class="pun">)</span>
    <span class="pun">{</span><span class="pln">
        modelBuilder</span><span class="pun">.</span><span class="typ">Entity</span><span class="pun">&lt;</span><span class="typ">Author</span><span class="pun">&gt;().</span><span class="typ">HasKey</span><span class="pun">(</span><span class="pln">a </span><span class="pun">=&gt;</span><span class="pln"> a</span><span class="pun">.</span><span class="typ">AuthorId</span><span class="pun">);</span><span class="pln">
        modelBuilder</span><span class="pun">.</span><span class="typ">Entity</span><span class="pun">&lt;</span><span class="typ">Author</span><span class="pun">&gt;()</span>
            <span class="pun">.</span><span class="typ">HasMany</span><span class="pun">(</span><span class="pln">a </span><span class="pun">=&gt;</span><span class="pln"> a</span><span class="pun">.</span><span class="typ">Books</span><span class="pun">)</span>
            <span class="pun">.</span><span class="typ">WithRequired</span><span class="pun">(</span><span class="pln">b </span><span class="pun">=&gt;</span><span class="pln"> b</span><span class="pun">.</span><span class="typ">Author</span><span class="pun">);</span><span class="pln">
        modelBuilder</span><span class="pun">.</span><span class="typ">Entity</span><span class="pun">&lt;</span><span class="typ">Author</span><span class="pun">&gt;()</span>
            <span class="pun">.</span><span class="typ">HasMany</span><span class="pun">(</span><span class="pln">a </span><span class="pun">=&gt;</span><span class="pln"> a</span><span class="pun">.</span><span class="typ">Articles</span><span class="pun">)</span>
            <span class="pun">.</span><span class="typ">WithRequired</span><span class="pun">(</span><span class="pln">b </span><span class="pun">=&gt;</span><span class="pln"> b</span><span class="pun">.</span><span class="typ">Author</span><span class="pun">);</span><span class="pln">

        modelBuilder</span><span class="pun">.</span><span class="typ">Entity</span><span class="pun">&lt;</span><span class="typ">Book</span><span class="pun">&gt;().</span><span class="typ">HasKey</span><span class="pun">(</span><span class="pln">b </span><span class="pun">=&gt;</span><span class="pln"> b</span><span class="pun">.</span><span class="typ">BookId</span><span class="pun">);</span><span class="pln">
        modelBuilder</span><span class="pun">.</span><span class="typ">Entity</span><span class="pun">&lt;</span><span class="typ">Book</span><span class="pun">&gt;()</span>
            <span class="pun">.</span><span class="typ">HasRequired</span><span class="pun">(</span><span class="pln">b </span><span class="pun">=&gt;</span><span class="pln"> b</span><span class="pun">.</span><span class="typ">Author</span><span class="pun">)</span>
            <span class="pun">.</span><span class="typ">WithMany</span><span class="pun">()</span>
            <span class="pun">.</span><span class="typ">HasForeignKey</span><span class="pun">(</span><span class="pln">b </span><span class="pun">=&gt;</span><span class="pln"> b</span><span class="pun">.</span><span class="typ">AuthorId</span><span class="pun">);</span><span class="pln">

        modelBuilder</span><span class="pun">.</span><span class="typ">Entity</span><span class="pun">&lt;</span><span class="typ">Article</span><span class="pun">&gt;().</span><span class="typ">HasKey</span><span class="pun">(</span><span class="pln">art </span><span class="pun">=&gt;</span><span class="pln"> art</span><span class="pun">.</span><span class="typ">ArticleId</span><span class="pun">);</span><span class="pln">
        modelBuilder</span><span class="pun">.</span><span class="typ">Entity</span><span class="pun">&lt;</span><span class="typ">Article</span><span class="pun">&gt;()</span>
            <span class="pun">.</span><span class="typ">HasRequired</span><span class="pun">(</span><span class="pln">art </span><span class="pun">=&gt;</span><span class="pln"> art</span><span class="pun">.</span><span class="typ">Author</span><span class="pun">)</span>
            <span class="pun">.</span><span class="typ">WithMany</span><span class="pun">()</span>
            <span class="pun">.</span><span class="typ">HasForeignKey</span><span class="pun">(</span><span class="pln">art </span><span class="pun">=&gt;</span><span class="pln"> art</span><span class="pun">.</span><span class="typ">AuthorId</span><span class="pun">);</span>
    <span class="pun">}</span>
<span class="pun">}</span>

<span class="kwd">public</span> <span class="kwd">class</span> <span class="typ">Author</span>
<span class="pun">{</span>
    <span class="kwd">public</span> <span class="kwd">int</span> <span class="typ">AuthorId</span> <span class="pun">{</span> <span class="kwd">get</span><span class="pun">;</span> <span class="kwd">set</span><span class="pun">;</span> <span class="pun">}</span>
    <span class="kwd">public</span> <span class="kwd">string</span> <span class="typ">Name</span> <span class="pun">{</span> <span class="kwd">get</span><span class="pun">;</span> <span class="kwd">set</span><span class="pun">;</span> <span class="pun">}</span>
    <span class="kwd">public</span> <span class="typ">DateTime</span> <span class="typ">Birthday</span> <span class="pun">{</span> <span class="kwd">get</span><span class="pun">;</span> <span class="kwd">set</span><span class="pun">;</span> <span class="pun">}</span>
    <span class="kwd">public</span> <span class="typ">ICollection</span><span class="pun">&lt;</span><span class="typ">Book</span><span class="pun">&gt;</span> <span class="typ">Books</span> <span class="pun">{</span> <span class="kwd">get</span><span class="pun">;</span> <span class="kwd">set</span><span class="pun">;</span> <span class="pun">}</span>
    <span class="kwd">public</span> <span class="typ">ICollection</span><span class="pun">&lt;</span><span class="typ">Articles</span><span class="pun">&gt;</span> <span class="typ">Articles</span> <span class="pun">{</span> <span class="kwd">get</span><span class="pun">;</span> <span class="kwd">set</span><span class="pun">;</span> <span class="pun">}</span>
<span class="pun">}</span>

<span class="kwd">public</span> <span class="kwd">class</span> <span class="typ">Book</span>
<span class="pun">{</span>
    <span class="kwd">public</span> <span class="kwd">int</span> <span class="typ">BookId</span> <span class="pun">{</span> <span class="kwd">get</span><span class="pun">;</span> <span class="kwd">set</span><span class="pun">;</span> <span class="pun">}</span>
    <span class="kwd">public</span> <span class="kwd">string</span> <span class="typ">Title</span> <span class="pun">{</span> <span class="kwd">get</span><span class="pun">;</span> <span class="kwd">set</span><span class="pun">;</span> <span class="pun">}</span>
    <span class="kwd">public</span> <span class="kwd">int</span> <span class="typ">NumPages</span> <span class="pun">{</span> <span class="kwd">get</span><span class="pun">;</span> <span class="kwd">set</span><span class="pun">;</span> <span class="pun">}</span>
    <span class="kwd">public</span> <span class="kwd">string</span> <span class="typ">Genre</span> <span class="pun">{</span> <span class="kwd">get</span><span class="pun">;</span> <span class="kwd">set</span><span class="pun">;</span> <span class="pun">}</span>
    <span class="kwd">public</span> <span class="kwd">int</span> <span class="typ">AuthorId</span> <span class="pun">{</span> <span class="kwd">get</span><span class="pun">;</span> <span class="kwd">set</span><span class="pun">;</span> <span class="pun">}</span>
    <span class="kwd">public</span> <span class="typ">Author</span> <span class="typ">Author</span> <span class="pun">{</span> <span class="kwd">get</span><span class="pun">;</span> <span class="kwd">set</span><span class="pun">;</span> <span class="pun">}</span>
<span class="pun">}</span>

<span class="kwd">public</span> <span class="kwd">class</span> <span class="typ">Article</span>
<span class="pun">{</span>
    <span class="kwd">public</span> <span class="kwd">int</span> <span class="typ">ArticleId</span> <span class="pun">{</span> <span class="kwd">get</span><span class="pun">;</span> <span class="kwd">set</span><span class="pun">;</span> <span class="pun">}</span>
    <span class="kwd">public</span> <span class="kwd">string</span> <span class="typ">Title</span> <span class="pun">{</span> <span class="kwd">get</span><span class="pun">;</span> <span class="kwd">set</span><span class="pun">;</span> <span class="pun">}</span>
    <span class="kwd">public</span> <span class="kwd">int</span> <span class="typ">NumWords</span> <span class="pun">{</span> <span class="kwd">get</span><span class="pun">;</span> <span class="kwd">set</span><span class="pun">;</span> <span class="pun">}</span>
    <span class="kwd">public</span> <span class="kwd">int</span> <span class="typ">AuthorId</span> <span class="pun">{</span> <span class="kwd">get</span><span class="pun">;</span> <span class="kwd">set</span><span class="pun">;</span> <span class="pun">}</span>
    <span class="kwd">public</span> <span class="typ">Author</span> <span class="typ">Author</span> <span class="pun">{</span> <span class="kwd">get</span><span class="pun">;</span> <span class="kwd">set</span><span class="pun">;</span> <span class="pun">}</span>
<span class="pun">}</span>

<span class="com">//Construct and use a data context with the following</span>
<span class="com">//code.  I exclude this code for simplicity in the examples.</span>
<span class="kwd">using</span> <span class="pun">(</span><span class="typ">DataContext</span><span class="pln"> db </span><span class="pun">=</span> <span class="kwd">new</span> <span class="typ">DataContext</span><span class="pun">())</span>
<span class="pun">{</span>
    <span class="com">//queries go here</span>
<span class="pun">}</span>

public class DataContext : DbContext

{

private const string ConnectionString =

@"Server=MyServer;Database=MyDatabase;Trusted_Connection=True;";

public DataContext() : base(ConnectionString) { }

public DbSet<Author> Authors { get; set; }

public DbSet<Book> Books { get; set; }

public DbSet<Article> Articles { get; set; }

protected override void OnModelCreating(DbModelBuilder modelBuilder)

{

modelBuilder.Entity<Author>().HasKey(a => a.AuthorId);

modelBuilder.Entity<Author>()

.HasMany(a => a.Books)

.WithRequired(b => b.Author);

modelBuilder.Entity<Author>()

modelBuilder.Entity<Book>().HasKey(b => b.BookId);

modelBuilder.Entity<Book>()

.HasRequired(b => b.Author)

.WithMany()

.HasForeignKey(b => b.AuthorId);

modelBuilder.Entity<Article>().HasKey(art => art.ArticleId);

modelBuilder.Entity<Article>()

.HasRequired(art => art.Author)

.WithMany()

.HasForeignKey(art => art.AuthorId);

}

public class Author

{

public int AuthorId { get; set; }

public string Name { get; set; }

public DateTime Birthday { get; set; }

public ICollection<Book> Books { get; set; }

public ICollection<Articles> Articles { get; set; }

}

public class Book

{

public int BookId { get; set; }

public string Title { get; set; }

public int NumPages { get; set; }

public string Genre { get; set; }

public Author Author { get; set; }

}

public class Article

{

public int ArticleId { get; set; }

public int NumWords { get; set; }

}

//Construct and use a data context with the following

//code. I exclude this code for simplicity in the examples.

using (DataContext db = new DataContext())

{

//queries go here

}

SELECT

See the data model and Entity Framework data context to understand the setup for the following examples.

<span class="pln">SELECT </span><span class="pun">*</span><span class="pln">
FROM books</span>

1 2	<span class="pln">SELECT </span><span class="pun">*</span><span class="pln"> FROM books</span>

<span class="typ">IQueryable</span><span class="pun">&lt;</span><span class="typ">Book</span><span class="pun">&gt;</span><span class="pln"> books </span><span class="pun">=</span><span class="pln"> db</span><span class="pun">.</span><span class="typ">Books</span><span class="pun">;</span>

1	<span class="typ">IQueryable</span><span class="pun"><</span><span class="typ">Book</span><span class="pun">></span><span class="pln"> books </span><span class="pun">=</span><span class="pln"> db</span><span class="pun">.</span><span class="typ">Books</span><span class="pun">;</span>

TOP

<span class="pln">SELECT TOP </span><span class="lit">1</span> <span class="pun">*</span><span class="pln">
FROM books</span>

1 2	<span class="pln">SELECT TOP </span><span class="lit">1</span> <span class="pun">*</span><span class="pln"> FROM books</span>

<span class="com">//There are many ways to select the first row from a query.</span>
<span class="com">//Each does something slightly differently.</span>

<span class="com">//Select the first row.  Throw an exception if there are zero rows.</span>
<span class="typ">Book</span><span class="pln"> book </span><span class="pun">=</span><span class="pln"> db</span><span class="pun">.</span><span class="typ">Books</span><span class="pun">.</span><span class="typ">First</span><span class="pun">();</span>

<span class="com">//Select the first row.  Return the</span>
<span class="com">//default value if there are zero rows.</span>
<span class="typ">Book</span><span class="pln"> book </span><span class="pun">=</span><span class="pln"> db</span><span class="pun">.</span><span class="typ">Books</span><span class="pun">.</span><span class="typ">FirstOrDefault</span><span class="pun">();</span>

<span class="com">//Select the first row.  Throw an exception</span>
<span class="com">//if there is not exactly one row</span>
<span class="typ">Book</span><span class="pln"> book </span><span class="pun">=</span><span class="pln"> db</span><span class="pun">.</span><span class="typ">Books</span><span class="pun">.</span><span class="typ">Single</span><span class="pun">();</span>

<span class="com">//Select the first row.  Throw an excpetion</span>
<span class="com">//if there is more than one row.  Return the</span>
<span class="com">//default value if there are zero rows.</span>
<span class="typ">Book</span><span class="pln"> book </span><span class="pun">=</span><span class="pln"> db</span><span class="pun">.</span><span class="typ">Books</span><span class="pun">.</span><span class="typ">SingleOrDefault</span><span class="pun">();</span>

<span class="com">//Select the first row.  Don't throw exceptions</span>
<span class="com">//if there is not exactly one row.  Return a</span>
<span class="com">//queryable rather than actually pulling the value</span>
<span class="com">//from the database.  This allows us to add extra</span>
<span class="com">//query operations like Where or Select to the</span>
<span class="com">//returned value.</span>
<span class="typ">IQueryable</span><span class="pun">&lt;</span><span class="typ">Book</span><span class="pun">&gt;</span><span class="pln"> book </span><span class="pun">=</span><span class="pln"> db</span><span class="pun">.</span><span class="typ">Books</span><span class="pun">.</span><span class="typ">Take</span><span class="pun">(</span><span class="lit">1</span><span class="pun">);</span>

//There are many ways to select the first row from a query.

//Each does something slightly differently.

//Select the first row. Throw an exception if there are zero rows.

Book book = db.Books.First();

//Select the first row. Return the

//default value if there are zero rows.

//Select the first row. Throw an exception

//if there is not exactly one row

//Select the first row. Throw an excpetion

//if there is more than one row. Return the

//default value if there are zero rows.

//Select the first row. Don't throw exceptions

//if there is not exactly one row. Return a

//queryable rather than actually pulling the value

//from the database. This allows us to add extra

//query operations like Where or Select to the

//returned value.

IQueryable<Book> book = db.Books.Take(1);

single column

<span class="pln">SELECT title
FROM books</span>

1 2	<span class="pln">SELECT title FROM books</span>

<span class="com">//fluent syntax</span>
<span class="typ">IQueryable</span><span class="str">&lt;string&gt;</span><span class="pln"> titles </span><span class="pun">=</span><span class="pln"> db</span><span class="pun">.</span><span class="typ">Books</span><span class="pun">.</span><span class="typ">Select</span><span class="pun">(</span><span class="pln">b </span><span class="pun">=&gt;</span><span class="pln"> b</span><span class="pun">.</span><span class="typ">Title</span><span class="pun">);</span>

<span class="com">//query syntax</span>
<span class="typ">IQueryable</span><span class="str">&lt;string&gt;</span><span class="pln"> titles2 </span><span class="pun">=</span>
    <span class="kwd">from</span><span class="pln"> b </span><span class="kwd">in</span><span class="pln"> db</span><span class="pun">.</span><span class="typ">Books</span>
    <span class="kwd">select</span><span class="pln"> b</span><span class="pun">.</span><span class="typ">Title</span><span class="pun">;</span>

//fluent syntax

IQueryable<string> titles = db.Books.Select(b => b.Title);

//query syntax

IQueryable<string> titles2 =

from b in db.Books

select b.Title;

multiple columns

<span class="pln">SELECT title</span><span class="pun">,</span><span class="pln"> numPages
FROM books</span>

1 2	<span class="pln">SELECT title</span><span class="pun">,</span><span class="pln"> numPages FROM books</span>

<span class="com">//fluent syntax</span>
<span class="kwd">var</span><span class="pln"> titlesAndNumPages </span><span class="pun">=</span><span class="pln"> 
    db</span><span class="pun">.</span><span class="typ">Books</span><span class="pun">.</span><span class="typ">Select</span><span class="pun">(</span><span class="pln">b </span><span class="pun">=&gt;</span> <span class="kwd">new</span> <span class="pun">{</span><span class="pln"> b</span><span class="pun">.</span><span class="typ">Title</span><span class="pun">,</span><span class="pln"> b</span><span class="pun">.</span><span class="typ">NumPages</span> <span class="pun">});</span>

<span class="com">//query syntax</span>
<span class="kwd">var</span><span class="pln"> titlesAndNumPages2 </span><span class="pun">=</span>
    <span class="kwd">from</span><span class="pln"> b </span><span class="kwd">in</span><span class="pln"> db</span><span class="pun">.</span><span class="typ">Books</span>
    <span class="kwd">select</span> <span class="kwd">new</span> <span class="pun">{</span><span class="pln"> b</span><span class="pun">.</span><span class="typ">Title</span><span class="pun">,</span><span class="pln"> b</span><span class="pun">.</span><span class="typ">NumPages</span> <span class="pun">};</span>

//fluent syntax

var titlesAndNumPages =

db.Books.Select(b => new { b.Title, b.NumPages });

//query syntax

var titlesAndNumPages2 =

from b in db.Books

select new { b.Title, b.NumPages };

Where

See the data model and Entity Framework data context to understand the setup for the following examples.

single condition

<span class="pln">SELECT </span><span class="pun">*</span><span class="pln">
FROM books
WHERE title </span><span class="pun">=</span> <span class="str">'Catch 22'</span>

SELECT *

FROM books

WHERE title = 'Catch 22'

<span class="com">//Fluent syntax</span>
<span class="typ">IQueryable</span><span class="pun">&lt;</span><span class="typ">Book</span><span class="pun">&gt;</span><span class="pln"> books </span><span class="pun">=</span><span class="pln"> db</span><span class="pun">.</span><span class="typ">Books</span><span class="pun">.</span><span class="typ">Where</span><span class="pun">(</span><span class="pln">b </span><span class="pun">=&gt;</span><span class="pln"> b</span><span class="pun">.</span><span class="typ">Title</span> <span class="pun">==</span> <span class="str">"Catch 22"</span><span class="pun">);</span>

<span class="com">//Query syntax</span>
<span class="typ">IQueryable</span><span class="pun">&lt;</span><span class="typ">Book</span><span class="pun">&gt;</span><span class="pln"> books2 </span><span class="pun">=</span> 
    <span class="kwd">from</span><span class="pln"> b </span><span class="kwd">in</span><span class="pln"> db</span><span class="pun">.</span><span class="typ">Books</span>
    <span class="kwd">where</span><span class="pln"> b</span><span class="pun">.</span><span class="typ">Title</span> <span class="pun">==</span> <span class="str">"Catch 22"</span>
    <span class="kwd">select</span><span class="pln"> b</span><span class="pun">;</span>

//Fluent syntax

IQueryable<Book> books = db.Books.Where(b => b.Title == "Catch 22");

//Query syntax

IQueryable<Book> books2 =

from b in db.Books

where b.Title == "Catch 22"

select b;

AND

<span class="pln">SELECT </span><span class="pun">*</span><span class="pln">
FROM books
WHERE title </span><span class="pun">=</span> <span class="str">'Catch 22 '</span><span class="pln">AND numPages </span><span class="pun">=</span> <span class="lit">305</span>

SELECT *

FROM books

WHERE title = 'Catch 22 'AND numPages = 305

<span class="com">//fluent syntax with &amp;&amp; operator</span>
<span class="typ">IQueryable</span><span class="pun">&lt;</span><span class="typ">Book</span><span class="pun">&gt;</span><span class="pln"> books </span><span class="pun">=</span><span class="pln">
    db</span><span class="pun">.</span><span class="typ">Books</span><span class="pun">.</span><span class="typ">Where</span><span class="pun">(</span><span class="pln">b </span><span class="pun">=&gt;</span><span class="pln"> b</span><span class="pun">.</span><span class="typ">Title</span> <span class="pun">==</span> <span class="str">"Catch 22"</span> <span class="pun">&amp;&amp;</span><span class="pln"> b</span><span class="pun">.</span><span class="typ">NumPages</span> <span class="pun">==</span> <span class="lit">305</span><span class="pun">);</span>

<span class="com">//fluent syntax and chaining where operations</span>
<span class="typ">IQueryable</span><span class="pun">&lt;</span><span class="typ">Book</span><span class="pun">&gt;</span><span class="pln"> books2 </span><span class="pun">=</span><span class="pln">
    db</span><span class="pun">.</span><span class="typ">Books</span><span class="pun">.</span><span class="typ">Where</span><span class="pun">(</span><span class="pln">b </span><span class="pun">=&gt;</span><span class="pln"> b</span><span class="pun">.</span><span class="typ">Title</span> <span class="pun">==</span> <span class="str">"Catch 22"</span><span class="pun">)</span>
            <span class="pun">.</span><span class="typ">Where</span><span class="pun">(</span><span class="pln">b </span><span class="pun">=&gt;</span><span class="pln"> b</span><span class="pun">.</span><span class="typ">NumPages</span> <span class="pun">==</span> <span class="lit">305</span><span class="pun">);</span>

<span class="com">//query syntax with &amp;&amp; operator</span>
<span class="typ">IQueryable</span><span class="pun">&lt;</span><span class="typ">Book</span><span class="pun">&gt;</span><span class="pln"> books3 </span><span class="pun">=</span> 
    <span class="kwd">from</span><span class="pln"> b </span><span class="kwd">in</span><span class="pln"> db</span><span class="pun">.</span><span class="typ">Books</span>
    <span class="kwd">where</span><span class="pln"> b</span><span class="pun">.</span><span class="typ">Title</span> <span class="pun">==</span> <span class="str">"Catch 22"</span> <span class="pun">&amp;&amp;</span><span class="pln"> b</span><span class="pun">.</span><span class="typ">NumPages</span> <span class="pun">==</span> <span class="lit">305</span>
    <span class="kwd">select</span><span class="pln"> b</span><span class="pun">;</span>

<span class="com">//query syntax chaining where operations</span>
<span class="typ">IQueryable</span><span class="pun">&lt;</span><span class="typ">Book</span><span class="pun">&gt;</span><span class="pln"> books4 </span><span class="pun">=</span>
    <span class="kwd">from</span><span class="pln"> b </span><span class="kwd">in</span><span class="pln"> db</span><span class="pun">.</span><span class="typ">Books</span>
    <span class="kwd">where</span><span class="pln"> b</span><span class="pun">.</span><span class="typ">Title</span> <span class="pun">==</span> <span class="str">"Catch 22"</span>
    <span class="kwd">where</span><span class="pln"> b</span><span class="pun">.</span><span class="typ">NumPages</span> <span class="pun">==</span> <span class="lit">305</span>
    <span class="kwd">select</span><span class="pln"> b</span><span class="pun">;</span>

//fluent syntax with && operator

IQueryable<Book> books =

db.Books.Where(b => b.Title == "Catch 22" && b.NumPages == 305);

//fluent syntax and chaining where operations

IQueryable<Book> books2 =

.Where(b => b.NumPages == 305);

//query syntax with && operator

IQueryable<Book> books3 =

from b in db.Books

where b.Title == "Catch 22" && b.NumPages == 305

select b;

//query syntax chaining where operations

IQueryable<Book> books4 =

from b in db.Books

where b.Title == "Catch 22"

where b.NumPages == 305

select b;

<span class="pln">SELECT </span><span class="pun">*</span><span class="pln">
FROM books
WHERE title </span><span class="pun">=</span> <span class="str">'Catch 22'</span><span class="pln"> OR numPages </span><span class="pun">=</span> <span class="lit">305</span>

SELECT *

FROM books

WHERE title = 'Catch 22' OR numPages = 305

<span class="com">//fluent syntax</span>
<span class="typ">IQueryable</span><span class="pun">&lt;</span><span class="typ">Book</span><span class="pun">&gt;</span><span class="pln"> books </span><span class="pun">=</span><span class="pln">
    db</span><span class="pun">.</span><span class="typ">Books</span><span class="pun">.</span><span class="typ">Where</span><span class="pun">(</span><span class="pln">b </span><span class="pun">=&gt;</span><span class="pln"> b</span><span class="pun">.</span><span class="typ">Title</span> <span class="pun">==</span> <span class="str">"Catch 22"</span> <span class="pun">||</span><span class="pln"> b</span><span class="pun">.</span><span class="typ">NumPages</span> <span class="pun">==</span> <span class="lit">305</span><span class="pun">);</span>

<span class="com">//query syntax</span>
<span class="typ">IQueryable</span><span class="pun">&lt;</span><span class="typ">Book</span><span class="pun">&gt;</span><span class="pln"> books2 </span><span class="pun">=</span>
    <span class="kwd">from</span><span class="pln"> b </span><span class="kwd">in</span><span class="pln"> db</span><span class="pun">.</span><span class="typ">Books</span>
    <span class="kwd">where</span><span class="pln"> b</span><span class="pun">.</span><span class="typ">Title</span> <span class="pun">==</span> <span class="str">"Catch 22"</span> <span class="pun">||</span><span class="pln"> b</span><span class="pun">.</span><span class="typ">NumPages</span> <span class="pun">==</span> <span class="lit">305</span>
    <span class="kwd">select</span><span class="pln"> b</span><span class="pun">;</span>

//fluent syntax

IQueryable<Book> books =

db.Books.Where(b => b.Title == "Catch 22" || b.NumPages == 305);

//query syntax

IQueryable<Book> books2 =

from b in db.Books

where b.Title == "Catch 22" || b.NumPages == 305

select b;

<span class="pln">SELECT </span><span class="pun">*</span><span class="pln">
FROM books
WHERE title IN</span><span class="pun">(</span><span class="str">'Catch 22'</span><span class="pun">,</span> <span class="str">'The Giver'</span><span class="pun">)</span>

<span class="com">//fluent syntax</span>
<span class="typ">IQueryable</span><span class="pun">&lt;</span><span class="typ">Book</span><span class="pun">&gt;</span><span class="pln"> books </span><span class="pun">=</span><span class="pln">
    db</span><span class="pun">.</span><span class="typ">Books</span><span class="pun">.</span><span class="typ">Where</span><span class="pun">(</span><span class="pln">b </span><span class="pun">=&gt;</span> <span class="kwd">new</span><span class="pun">[]</span> <span class="pun">{</span> <span class="str">"Catch 22"</span><span class="pun">,</span> <span class="str">"The Giver"</span> <span class="pun">}.</span><span class="typ">Contains</span><span class="pun">(</span><span class="pln">b</span><span class="pun">.</span><span class="typ">Title</span><span class="pun">));</span>

<span class="com">//query syntax</span>
<span class="typ">IQueryable</span><span class="pun">&lt;</span><span class="typ">Book</span><span class="pun">&gt;</span><span class="pln"> books2 </span><span class="pun">=</span>
    <span class="kwd">from</span><span class="pln"> b </span><span class="kwd">in</span><span class="pln"> db</span><span class="pun">.</span><span class="typ">Books</span>
    <span class="kwd">where</span> <span class="kwd">new</span><span class="pun">[]</span> <span class="pun">{</span> <span class="str">"Catch 22"</span><span class="pun">,</span> <span class="str">"The Giver"</span> <span class="pun">}.</span><span class="typ">Contains</span><span class="pun">(</span><span class="pln">b</span><span class="pun">.</span><span class="typ">Title</span><span class="pun">)</span>
    <span class="kwd">select</span><span class="pln"> b</span><span class="pun">;</span>

SELECT *

FROM books

WHERE title IN('Catch 22', 'The Giver')

//fluent syntax

IQueryable<Book> books =

db.Books.Where(b => new[] { "Catch 22", "The Giver" }.Contains(b.Title));

//query syntax

IQueryable<Book> books2 =

from b in db.Books

where new[] { "Catch 22", "The Giver" }.Contains(b.Title)

select b;

BETWEEN

<span class="pln">SELECT </span><span class="pun">*</span><span class="pln">
FROM books
WHERE numPages BETWEEN </span><span class="lit">200</span><span class="pln"> AND </span><span class="lit">300</span>

SELECT *

FROM books

WHERE numPages BETWEEN 200 AND 300

<span class="com">//fluent syntax</span>
<span class="typ">IQueryable</span><span class="pun">&lt;</span><span class="typ">Book</span><span class="pun">&gt;</span><span class="pln"> books </span><span class="pun">=</span><span class="pln">
    db</span><span class="pun">.</span><span class="typ">Books</span><span class="pun">.</span><span class="typ">Where</span><span class="pun">(</span><span class="pln">b </span><span class="pun">=&gt;</span><span class="pln"> b</span><span class="pun">.</span><span class="typ">NumPages</span> <span class="pun">&gt;=</span> <span class="lit">200</span> <span class="pun">&amp;&amp;</span><span class="pln"> b</span><span class="pun">.</span><span class="typ">NumPages</span> <span class="pun">&lt;=</span> <span class="lit">300</span><span class="pun">);</span>

<span class="com">//query syntax</span>
<span class="typ">IQueryable</span><span class="pun">&lt;</span><span class="typ">Book</span><span class="pun">&gt;</span><span class="pln"> books2 </span><span class="pun">=</span>
    <span class="kwd">from</span><span class="pln"> b </span><span class="kwd">in</span><span class="pln"> db</span><span class="pun">.</span><span class="typ">Books</span>
    <span class="kwd">where</span><span class="pln"> b</span><span class="pun">.</span><span class="typ">NumPages</span> <span class="pun">&gt;=</span> <span class="lit">200</span> <span class="pun">&amp;&amp;</span><span class="pln"> b</span><span class="pun">.</span><span class="typ">NumPages</span> <span class="pun">&lt;=</span> <span class="lit">300</span>
    <span class="kwd">select</span><span class="pln"> b</span><span class="pun">;</span>

//fluent syntax

IQueryable<Book> books =

db.Books.Where(b => b.NumPages >= 200 && b.NumPages <= 300);

//query syntax

IQueryable<Book> books2 =

from b in db.Books

where b.NumPages >= 200 && b.NumPages <= 300

select b;

Inner join

See the data model and Entity Framework data context to understand the setup for the following examples.

<span class="pln">SELECT name
FROM books b
INNER JOIN authors a
    b</span><span class="pun">.</span><span class="pln">authorId </span><span class="pun">=</span><span class="pln"> a</span><span class="pun">.</span><span class="pln">authorId</span>

SELECT name

FROM books b

INNER JOIN authors a

b.authorId = a.authorId

<span class="com">//fluent syntax with association properties</span>
<span class="kwd">var</span><span class="pln"> booksAndTheirAuthors </span><span class="pun">=</span><span class="pln"> 
    db</span><span class="pun">.</span><span class="typ">Books</span><span class="pun">.</span><span class="typ">Select</span><span class="pun">(</span><span class="pln">b </span><span class="pun">=&gt;</span> <span class="kwd">new</span> <span class="pun">{</span> <span class="typ">Book</span> <span class="pun">=</span><span class="pln"> b</span><span class="pun">,</span><span class="pln"> b</span><span class="pun">.</span><span class="typ">Author</span> <span class="pun">});</span>

<span class="com">//fluent syntax without association properties</span>
<span class="kwd">var</span><span class="pln"> booksAndTheirAuthors2 </span><span class="pun">=</span><span class="pln">
    db</span><span class="pun">.</span><span class="typ">Books</span><span class="pun">.</span><span class="typ">Join</span><span class="pun">(</span><span class="pln">db</span><span class="pun">.</span><span class="typ">Authors</span><span class="pun">,</span><span class="pln">
                    a </span><span class="pun">=&gt;</span><span class="pln"> a</span><span class="pun">.</span><span class="typ">AuthorId</span><span class="pun">,</span><span class="pln">
                    b </span><span class="pun">=&gt;</span><span class="pln"> b</span><span class="pun">.</span><span class="typ">AuthorId</span><span class="pun">,</span>
                    <span class="pun">(</span><span class="pln">b</span><span class="pun">,</span><span class="pln"> a</span><span class="pun">)</span> <span class="pun">=&gt;</span> <span class="kwd">new</span> <span class="pun">{</span> <span class="typ">Book</span> <span class="pun">=</span><span class="pln"> b</span><span class="pun">,</span> <span class="typ">Author</span> <span class="pun">=</span><span class="pln"> a </span><span class="pun">});</span>

<span class="com">//query syntax with association properties</span>
<span class="kwd">var</span><span class="pln"> booksAndTheirAuthors3 </span><span class="pun">=</span>
    <span class="kwd">from</span><span class="pln"> b </span><span class="kwd">in</span><span class="pln"> db</span><span class="pun">.</span><span class="typ">Books</span>
    <span class="kwd">select</span> <span class="kwd">new</span> <span class="pun">{</span> <span class="typ">Book</span> <span class="pun">=</span><span class="pln"> b</span><span class="pun">,</span> <span class="typ">Author</span> <span class="pun">=</span><span class="pln"> b</span><span class="pun">.</span><span class="typ">Author</span> <span class="pun">};</span>

<span class="com">//query syntax without association properties</span>
<span class="kwd">var</span><span class="pln"> authorsAndTheirBooks2 </span><span class="pun">=</span>
    <span class="kwd">from</span><span class="pln"> b </span><span class="kwd">in</span><span class="pln"> db</span><span class="pun">.</span><span class="typ">Books</span><span class="pln">
    join a </span><span class="kwd">in</span><span class="pln"> db</span><span class="pun">.</span><span class="typ">Authors</span><span class="pln">
        on b</span><span class="pun">.</span><span class="typ">AuthorId</span><span class="pln"> equals a</span><span class="pun">.</span><span class="typ">AuthorId</span>
    <span class="kwd">select</span> <span class="kwd">new</span> <span class="pun">{</span> <span class="typ">Book</span> <span class="pun">=</span><span class="pln"> b</span><span class="pun">,</span> <span class="typ">Author</span> <span class="pun">=</span><span class="pln"> a </span><span class="pun">};</span>

//fluent syntax with association properties

var booksAndTheirAuthors =

db.Books.Select(b => new { Book = b, b.Author });

//fluent syntax without association properties

var booksAndTheirAuthors2 =

db.Books.Join(db.Authors,

a => a.AuthorId,

b => b.AuthorId,

(b, a) => new { Book = b, Author = a });

//query syntax with association properties

var booksAndTheirAuthors3 =

from b in db.Books

select new { Book = b, Author = b.Author };

//query syntax without association properties

var authorsAndTheirBooks2 =

from b in db.Books

join a in db.Authors

on b.AuthorId equals a.AuthorId

LEFT/RIGHT OUTER JOIN

See the data model and Entity Framework data context to understand the setup for the following examples.

LEFT

<span class="pln">SELECT </span><span class="pun">*</span><span class="pln">
FROM authors a
LEFT OUTER JOIN books b
    a</span><span class="pun">.</span><span class="pln">authorId </span><span class="pun">=</span><span class="pln"> b</span><span class="pun">.</span><span class="pln">authorId</span>

SELECT *

FROM authors a

LEFT OUTER JOIN books b

a.authorId = b.authorId

<span class="com">//fluent syntax with association properties</span>
<span class="kwd">var</span><span class="pln"> authorsAndTheirBooks </span><span class="pun">=</span><span class="pln">
    db</span><span class="pun">.</span><span class="typ">Authors</span><span class="pun">.</span><span class="typ">Select</span><span class="pun">(</span><span class="pln">a </span><span class="pun">=&gt;</span> <span class="kwd">new</span> <span class="pun">{</span> <span class="typ">Author</span> <span class="pun">=</span><span class="pln"> a</span><span class="pun">,</span><span class="pln"> a</span><span class="pun">.</span><span class="typ">Books</span> <span class="pun">});</span>

<span class="com">//fluent syntax without association properties</span>
<span class="kwd">var</span><span class="pln"> authorsAndTheirBooks2 </span><span class="pun">=</span><span class="pln">
    db</span><span class="pun">.</span><span class="typ">Authors</span><span class="pun">.</span><span class="typ">GroupJoin</span><span class="pun">(</span><span class="pln">db</span><span class="pun">.</span><span class="typ">Books</span><span class="pun">,</span><span class="pln">
                            a </span><span class="pun">=&gt;</span><span class="pln"> a</span><span class="pun">.</span><span class="typ">AuthorId</span><span class="pun">,</span><span class="pln">
                            b </span><span class="pun">=&gt;</span><span class="pln"> b</span><span class="pun">.</span><span class="typ">AuthorId</span><span class="pun">,</span>
                            <span class="pun">(</span><span class="pln">a</span><span class="pun">,</span><span class="pln"> b</span><span class="pun">)</span> <span class="pun">=&gt;</span> <span class="kwd">new</span> <span class="pun">{</span> <span class="typ">Author</span> <span class="pun">=</span><span class="pln"> a</span><span class="pun">,</span> <span class="typ">Books</span> <span class="pun">=</span><span class="pln"> b </span><span class="pun">});</span>

<span class="com">//query syntax with association properties</span>
<span class="kwd">var</span><span class="pln"> authorsAndTheirBook4 </span><span class="pun">=</span>
    <span class="kwd">from</span><span class="pln"> a </span><span class="kwd">in</span><span class="pln"> db</span><span class="pun">.</span><span class="typ">Authors</span>
    <span class="kwd">select</span> <span class="kwd">new</span> <span class="pun">{</span> <span class="typ">Author</span> <span class="pun">=</span><span class="pln"> a</span><span class="pun">,</span><span class="pln"> a</span><span class="pun">.</span><span class="typ">Books</span> <span class="pun">};</span>

<span class="com">//query syntax without association properties</span>
<span class="kwd">var</span><span class="pln"> authorsAndTheirBooks3 </span><span class="pun">=</span>
    <span class="kwd">from</span><span class="pln"> a </span><span class="kwd">in</span><span class="pln"> db</span><span class="pun">.</span><span class="typ">Authors</span><span class="pln">
    join b </span><span class="kwd">in</span><span class="pln"> db</span><span class="pun">.</span><span class="typ">Books</span><span class="pln">
        on a</span><span class="pun">.</span><span class="typ">AuthorId</span><span class="pln"> equals b</span><span class="pun">.</span><span class="typ">AuthorId</span> <span class="kwd">into</span><span class="pln"> g
    </span><span class="kwd">select</span><span class="pln"> g</span><span class="pun">.</span><span class="typ">DefaultIfEmpty</span><span class="pun">();</span>

//fluent syntax with association properties

var authorsAndTheirBooks =

db.Authors.Select(a => new { Author = a, a.Books });

//fluent syntax without association properties

var authorsAndTheirBooks2 =

db.Authors.GroupJoin(db.Books,

a => a.AuthorId,

b => b.AuthorId,

(a, b) => new { Author = a, Books = b });

//query syntax with association properties

var authorsAndTheirBook4 =

from a in db.Authors

select new { Author = a, a.Books };

//query syntax without association properties

var authorsAndTheirBooks3 =

from a in db.Authors

join b in db.Books

on a.AuthorId equals b.AuthorId into g

select g.DefaultIfEmpty();

RIGHT

<span class="pln">SELECT </span><span class="pun">*</span><span class="pln">
FROM books b
RIGHT OUTER JOIN authors a
    b</span><span class="pun">.</span><span class="pln">authorId </span><span class="pun">=</span><span class="pln"> a</span><span class="pun">.</span><span class="pln">authorId</span>

SELECT *

FROM books b

RIGHT OUTER JOIN authors a

b.authorId = a.authorId

<span class="com">//fluent syntax with association properties</span>
<span class="kwd">var</span><span class="pln"> authorsAndTheirBooks </span><span class="pun">=</span><span class="pln">
    db</span><span class="pun">.</span><span class="typ">Authors</span><span class="pun">.</span><span class="typ">Select</span><span class="pun">(</span><span class="pln">a </span><span class="pun">=&gt;</span> <span class="kwd">new</span> <span class="pun">{</span> <span class="typ">Author</span> <span class="pun">=</span><span class="pln"> a</span><span class="pun">,</span><span class="pln"> a</span><span class="pun">.</span><span class="typ">Books</span> <span class="pun">});</span>

<span class="com">//fluent syntax without association properties</span>
<span class="kwd">var</span><span class="pln"> authorsAndTheirBooks2 </span><span class="pun">=</span><span class="pln">
    db</span><span class="pun">.</span><span class="typ">Authors</span><span class="pun">.</span><span class="typ">GroupJoin</span><span class="pun">(</span><span class="pln">db</span><span class="pun">.</span><span class="typ">Books</span><span class="pun">,</span><span class="pln">
                            a </span><span class="pun">=&gt;</span><span class="pln"> a</span><span class="pun">.</span><span class="typ">AuthorId</span><span class="pun">,</span><span class="pln">
                            b </span><span class="pun">=&gt;</span><span class="pln"> b</span><span class="pun">.</span><span class="typ">AuthorId</span><span class="pun">,</span>
                            <span class="pun">(</span><span class="pln">a</span><span class="pun">,</span><span class="pln"> b</span><span class="pun">)</span> <span class="pun">=&gt;</span> <span class="kwd">new</span> <span class="pun">{</span> <span class="typ">Author</span> <span class="pun">=</span><span class="pln"> a</span><span class="pun">,</span> <span class="typ">Books</span> <span class="pun">=</span><span class="pln"> b </span><span class="pun">});</span>

<span class="com">//query syntax with association properties</span>
<span class="kwd">var</span><span class="pln"> authorsAndTheirBook4 </span><span class="pun">=</span>
    <span class="kwd">from</span><span class="pln"> a </span><span class="kwd">in</span><span class="pln"> db</span><span class="pun">.</span><span class="typ">Authors</span>
    <span class="kwd">select</span> <span class="kwd">new</span> <span class="pun">{</span> <span class="typ">Author</span> <span class="pun">=</span><span class="pln"> a</span><span class="pun">,</span><span class="pln"> a</span><span class="pun">.</span><span class="typ">Books</span> <span class="pun">};</span>

<span class="com">//query syntax without association properties</span>
<span class="kwd">var</span><span class="pln"> authorsAndTheirBooks3 </span><span class="pun">=</span>
    <span class="kwd">from</span><span class="pln"> a </span><span class="kwd">in</span><span class="pln"> db</span><span class="pun">.</span><span class="typ">Authors</span><span class="pln">
    join b </span><span class="kwd">in</span><span class="pln"> db</span><span class="pun">.</span><span class="typ">Books</span><span class="pln">
        on a</span><span class="pun">.</span><span class="typ">AuthorId</span><span class="pln"> equals b</span><span class="pun">.</span><span class="typ">AuthorId</span> <span class="kwd">into</span><span class="pln"> g
    </span><span class="kwd">select</span><span class="pln"> g</span><span class="pun">.</span><span class="typ">DefaultIfEmpty</span><span class="pun">();</span>

//fluent syntax with association properties

var authorsAndTheirBooks =

//fluent syntax without association properties

var authorsAndTheirBooks2 =

a => a.AuthorId,

b => b.AuthorId,

//query syntax with association properties

var authorsAndTheirBook4 =

from a in db.Authors

//query syntax without association properties

var authorsAndTheirBooks3 =

from a in db.Authors

join b in db.Books

select g.DefaultIfEmpty();

FULL OUTER JOIN

See the data model and Entity Framework data context to understand the setup for the following examples.

<span class="pln">SELECT b</span><span class="pun">.</span><span class="pln">title</span><span class="pun">,</span><span class="pln"> a</span><span class="pun">.</span><span class="pln">name
FROM books b
FULL OUTER JOIN authors a
    ON b</span><span class="pun">.</span><span class="pln">authorId </span><span class="pun">=</span><span class="pln"> a</span><span class="pun">.</span><span class="pln">authorId</span>

SELECT b.title, a.name

FROM books b

FULL OUTER JOIN authors a

ON b.authorId = a.authorId

<span class="kwd">var</span><span class="pln"> leftOuterJoin </span><span class="pun">=</span>
    <span class="kwd">from</span><span class="pln"> a </span><span class="kwd">in</span><span class="pln"> db</span><span class="pun">.</span><span class="typ">Authors</span><span class="pln">
    join b </span><span class="kwd">in</span><span class="pln"> db</span><span class="pun">.</span><span class="typ">Books</span><span class="pln">
        on a</span><span class="pun">.</span><span class="typ">AuthorId</span><span class="pln"> equals b</span><span class="pun">.</span><span class="typ">AuthorId</span> <span class="kwd">into</span><span class="pln"> g
    </span><span class="kwd">from</span><span class="pln"> left </span><span class="kwd">in</span><span class="pln"> g</span><span class="pun">.</span><span class="typ">DefaultIfEmpty</span><span class="pun">()</span>
    <span class="kwd">select</span> <span class="kwd">new</span> 
    <span class="pun">{</span>
        <span class="typ">Title</span> <span class="pun">=</span><span class="pln"> left</span><span class="pun">.</span><span class="typ">Title</span><span class="pun">,</span>
        <span class="typ">Name</span> <span class="pun">=</span><span class="pln"> a</span><span class="pun">.</span><span class="typ">Name</span>
    <span class="pun">};</span>

<span class="kwd">var</span><span class="pln"> rightOuterJoin </span><span class="pun">=</span>
    <span class="kwd">from</span><span class="pln"> b </span><span class="kwd">in</span><span class="pln"> db</span><span class="pun">.</span><span class="typ">Books</span><span class="pln">
    join a </span><span class="kwd">in</span><span class="pln"> db</span><span class="pun">.</span><span class="typ">Authors</span><span class="pln">
        on b</span><span class="pun">.</span><span class="typ">AuthorId</span><span class="pln"> equals a</span><span class="pun">.</span><span class="typ">AuthorId</span> <span class="kwd">into</span><span class="pln"> g
    </span><span class="kwd">from</span><span class="pln"> right </span><span class="kwd">in</span><span class="pln"> g</span><span class="pun">.</span><span class="typ">DefaultIfEmpty</span><span class="pun">()</span>
    <span class="kwd">select</span> <span class="kwd">new</span>
    <span class="pun">{</span>
        <span class="typ">Title</span> <span class="pun">=</span><span class="pln"> b</span><span class="pun">.</span><span class="typ">Title</span><span class="pun">,</span>
        <span class="typ">Name</span> <span class="pun">=</span><span class="pln"> right</span><span class="pun">.</span><span class="typ">Name</span>
    <span class="pun">};</span>


<span class="kwd">var</span><span class="pln"> fullOuterJoin </span><span class="pun">=</span><span class="pln"> leftOuterJoin</span><span class="pun">.</span><span class="typ">Concat</span><span class="pun">(</span><span class="pln">rightOuterJoin</span><span class="pun">);</span>

var leftOuterJoin =

from a in db.Authors

join b in db.Books

from left in g.DefaultIfEmpty()

select new

{

Title = left.Title,

Name = a.Name

};

var rightOuterJoin =

from b in db.Books

join a in db.Authors

on b.AuthorId equals a.AuthorId into g

from right in g.DefaultIfEmpty()

select new

{

Title = b.Title,

Name = right.Name

};

var fullOuterJoin = leftOuterJoin.Concat(rightOuterJoin);

CROSS JOIN

See the data model and Entity Framework data context to understand the setup for the following examples.

<span class="pln">SELECT </span><span class="pun">*</span><span class="pln">
FROM books b
CROSS JOIN authors a</span>

SELECT *

FROM books b

CROSS JOIN authors a

<span class="com">//fluent syntax</span>
<span class="kwd">var</span><span class="pln"> booksAndAuthors </span><span class="pun">=</span><span class="pln">
    db</span><span class="pun">.</span><span class="typ">Books</span><span class="pun">.</span><span class="typ">SelectMany</span><span class="pun">(</span><span class="pln">b </span><span class="pun">=&gt;</span> <span class="kwd">new</span> <span class="pun">[]</span> <span class="pun">{</span> <span class="kwd">new</span> <span class="pun">{</span> <span class="typ">Book</span> <span class="pun">=</span><span class="pln"> b</span><span class="pun">,</span><span class="pln"> b</span><span class="pun">.</span><span class="typ">Author</span> <span class="pun">}</span> <span class="pun">});</span>

<span class="com">//query syntax</span>
<span class="kwd">var</span><span class="pln"> booksAndAuthors2 </span><span class="pun">=</span>
    <span class="kwd">from</span><span class="pln"> b </span><span class="kwd">in</span><span class="pln"> db</span><span class="pun">.</span><span class="typ">Books</span>
    <span class="kwd">from</span><span class="pln"> a </span><span class="kwd">in</span><span class="pln"> db</span><span class="pun">.</span><span class="typ">Authors</span>
    <span class="kwd">select</span> <span class="kwd">new</span> <span class="pun">{</span> <span class="typ">Book</span> <span class="pun">=</span><span class="pln"> b</span><span class="pun">,</span> <span class="typ">Author</span> <span class="pun">=</span><span class="pln"> a </span><span class="pun">};</span>

//fluent syntax

var booksAndAuthors =

db.Books.SelectMany(b => new [] { new { Book = b, b.Author } });

//query syntax

var booksAndAuthors2 =

from b in db.Books

from a in db.Authors

GROUP BY

See the data model and Entity Framework data context to understand the setup for the following examples.

single column

<span class="pln">SELECT authorId</span><span class="pun">,</span><span class="pln"> COUNT</span><span class="pun">(*)</span><span class="pln"> AS count
FROM books
GROUP BY authorId</span>

SELECT authorId, COUNT(*) AS count

FROM books

GROUP BY authorId

<span class="com">//fluent syntax</span>
<span class="kwd">var</span><span class="pln"> numBooksPerAuthor </span><span class="pun">=</span><span class="pln"> 
    db</span><span class="pun">.</span><span class="typ">Books</span><span class="pun">.</span><span class="typ">GroupBy</span><span class="pun">(</span><span class="pln">b </span><span class="pun">=&gt;</span><span class="pln"> b</span><span class="pun">.</span><span class="typ">AuthorId</span><span class="pun">)</span>
            <span class="pun">.</span><span class="typ">Select</span><span class="pun">(</span><span class="pln">g </span><span class="pun">=&gt;</span> <span class="kwd">new</span> <span class="pun">{</span> <span class="typ">AuthorId</span> <span class="pun">=</span><span class="pln"> g</span><span class="pun">.</span><span class="typ">Key</span><span class="pun">,</span> <span class="typ">Count</span> <span class="pun">=</span><span class="pln"> g</span><span class="pun">.</span><span class="typ">Count</span><span class="pun">()</span> <span class="pun">});</span>

<span class="com">//query syntax</span>
<span class="kwd">var</span><span class="pln"> numBooksPerAuthor2 </span><span class="pun">=</span> 
    <span class="kwd">from</span><span class="pln"> b </span><span class="kwd">in</span><span class="pln"> db</span><span class="pun">.</span><span class="typ">Books</span>
    <span class="kwd">group</span><span class="pln"> b </span><span class="kwd">by</span><span class="pln"> b</span><span class="pun">.</span><span class="typ">AuthorId</span> <span class="kwd">into</span><span class="pln"> g
    </span><span class="kwd">select</span> <span class="kwd">new</span> <span class="pun">{</span> <span class="typ">AuthorId</span> <span class="pun">=</span><span class="pln"> g</span><span class="pun">.</span><span class="typ">Key</span><span class="pun">,</span> <span class="typ">Count</span> <span class="pun">=</span><span class="pln"> g</span><span class="pun">.</span><span class="typ">Count</span><span class="pun">()</span> <span class="pun">};</span>

//fluent syntax

var numBooksPerAuthor =

.Select(g => new { AuthorId = g.Key, Count = g.Count() });

//query syntax

var numBooksPerAuthor2 =

from b in db.Books

group b by b.AuthorId into g

select new { AuthorId = g.Key, Count = g.Count() };

multiple columns

<span class="pln">SELECT authorId</span><span class="pun">,</span><span class="pln"> genre</span><span class="pun">,</span><span class="pln"> COUNT</span><span class="pun">(*)</span><span class="pln"> AS count
FROM books
GROUP BY authorId</span><span class="pun">,</span><span class="pln"> genre</span>

SELECT authorId, genre, COUNT(*) AS count

FROM books

GROUP BY authorId, genre

<span class="com">//fluent syntax</span>
<span class="kwd">var</span><span class="pln"> numBooksPerAuthorGenre </span><span class="pun">=</span><span class="pln">
    db</span><span class="pun">.</span><span class="typ">Books</span><span class="pun">.</span><span class="typ">GroupBy</span><span class="pun">(</span><span class="pln">b </span><span class="pun">=&gt;</span> <span class="kwd">new</span> <span class="pun">{</span><span class="pln"> b</span><span class="pun">.</span><span class="typ">AuthorId</span><span class="pun">,</span><span class="pln"> b</span><span class="pun">.</span><span class="typ">Genre</span> <span class="pun">})</span>
            <span class="pun">.</span><span class="typ">Select</span><span class="pun">(</span><span class="pln">g </span><span class="pun">=&gt;</span> <span class="kwd">new</span> <span class="pun">{</span><span class="pln"> g</span><span class="pun">.</span><span class="typ">Key</span><span class="pun">.</span><span class="typ">AuthorId</span><span class="pun">,</span><span class="pln"> g</span><span class="pun">.</span><span class="typ">Key</span><span class="pun">.</span><span class="typ">Genre</span><span class="pun">,</span> <span class="typ">Count</span> <span class="pun">=</span><span class="pln"> g</span><span class="pun">.</span><span class="typ">Count</span><span class="pun">()</span> <span class="pun">});</span>

<span class="com">//query syntax</span>
<span class="kwd">var</span><span class="pln"> numBooksPerAuthorGenre2 </span><span class="pun">=</span>
    <span class="kwd">from</span><span class="pln"> b </span><span class="kwd">in</span><span class="pln"> db</span><span class="pun">.</span><span class="typ">Books</span>
    <span class="kwd">group</span><span class="pln"> b </span><span class="kwd">by</span> <span class="kwd">new</span> <span class="pun">{</span><span class="pln"> b</span><span class="pun">.</span><span class="typ">AuthorId</span><span class="pun">,</span><span class="pln"> b</span><span class="pun">.</span><span class="typ">Genre</span> <span class="pun">}</span> <span class="kwd">into</span><span class="pln"> g
    </span><span class="kwd">select</span> <span class="kwd">new</span> <span class="pun">{</span><span class="pln"> g</span><span class="pun">.</span><span class="typ">Key</span><span class="pun">.</span><span class="typ">AuthorId</span><span class="pun">,</span><span class="pln"> g</span><span class="pun">.</span><span class="typ">Key</span><span class="pun">.</span><span class="typ">Genre</span><span class="pun">,</span> <span class="typ">Count</span> <span class="pun">=</span><span class="pln"> g</span><span class="pun">.</span><span class="typ">Count</span><span class="pun">()</span> <span class="pun">};</span>

//fluent syntax

var numBooksPerAuthorGenre =

db.Books.GroupBy(b => new { b.AuthorId, b.Genre })

.Select(g => new { g.Key.AuthorId, g.Key.Genre, Count = g.Count() });

//query syntax

var numBooksPerAuthorGenre2 =

from b in db.Books

group b by new { b.AuthorId, b.Genre } into g

select new { g.Key.AuthorId, g.Key.Genre, Count = g.Count() };

HAVING

See the data model and Entity Framework data context to understand the setup for the following examples.

<span class="pln">SELECT authorId</span><span class="pun">,</span><span class="pln"> COUNT</span><span class="pun">(*)</span><span class="pln"> AS count
FROM books
GROUP BY authorId
HAVING COUNT</span><span class="pun">(*)</span> <span class="pun">&gt;</span> <span class="lit">10</span>

SELECT authorId, COUNT(*) AS count

FROM books

GROUP BY authorId

HAVING COUNT(*) > 10

<span class="com">//fluent syntax</span>
<span class="kwd">var</span><span class="pln"> authorsWithManyBooks </span><span class="pun">=</span><span class="pln">
    db</span><span class="pun">.</span><span class="typ">Books</span><span class="pun">.</span><span class="typ">GroupBy</span><span class="pun">(</span><span class="pln">b </span><span class="pun">=&gt;</span><span class="pln"> b</span><span class="pun">.</span><span class="typ">AuthorId</span><span class="pun">)</span>
            <span class="pun">.</span><span class="typ">Select</span><span class="pun">(</span><span class="pln">g </span><span class="pun">=&gt;</span> <span class="kwd">new</span> <span class="pun">{</span> <span class="typ">AuthorId</span> <span class="pun">=</span><span class="pln"> g</span><span class="pun">.</span><span class="typ">Key</span><span class="pun">,</span> <span class="typ">Count</span> <span class="pun">=</span><span class="pln"> g</span><span class="pun">.</span><span class="typ">Count</span><span class="pun">()</span> <span class="pun">})</span>
            <span class="pun">.</span><span class="typ">Where</span><span class="pun">(</span><span class="pln">g </span><span class="pun">=&gt;</span><span class="pln"> g</span><span class="pun">.</span><span class="typ">Count</span> <span class="pun">&gt;</span> <span class="lit">10</span><span class="pun">);</span>

<span class="com">//query syntax</span>
<span class="kwd">var</span><span class="pln"> authorsWithManyBook2 </span><span class="pun">=</span>
    <span class="kwd">from</span><span class="pln"> b </span><span class="kwd">in</span><span class="pln"> db</span><span class="pun">.</span><span class="typ">Books</span>
    <span class="kwd">group</span><span class="pln"> b </span><span class="kwd">by</span><span class="pln"> b</span><span class="pun">.</span><span class="typ">AuthorId</span> <span class="kwd">into</span><span class="pln"> g
    </span><span class="kwd">where</span><span class="pln"> g</span><span class="pun">.</span><span class="typ">Count</span><span class="pun">()</span> <span class="pun">&gt;</span> <span class="lit">10</span>
    <span class="kwd">select</span> <span class="kwd">new</span> <span class="pun">{</span> <span class="typ">AuthorId</span> <span class="pun">=</span><span class="pln"> g</span><span class="pun">.</span><span class="typ">Key</span><span class="pun">,</span> <span class="typ">Count</span> <span class="pun">=</span><span class="pln"> g</span><span class="pun">.</span><span class="typ">Count</span><span class="pun">()</span> <span class="pun">};</span>

//fluent syntax

var authorsWithManyBooks =

.Where(g => g.Count > 10);

//query syntax

var authorsWithManyBook2 =

from b in db.Books

where g.Count() > 10

select new { AuthorId = g.Key, Count = g.Count() };

DISTINCT

See the data model and Entity Framework data context to understand the setup for the following examples.

<span class="pln">SELECT DISTINCT </span><span class="pun">*</span><span class="pln">
FROM books</span>

1 2	<span class="pln">SELECT DISTINCT </span><span class="pun">*</span><span class="pln"> FROM books</span>

<span class="typ">IQueryable</span><span class="pun">&lt;</span><span class="typ">Book</span><span class="pun">&gt;</span><span class="pln"> authors </span><span class="pun">=</span><span class="pln"> db</span><span class="pun">.</span><span class="typ">Books</span><span class="pun">.</span><span class="typ">Distinct</span><span class="pun">();</span>

IQueryable<Book> authors = db.Books.Distinct();

UNION

See the data model and Entity Framework data context to understand the setup for the following examples.

UNION ALL

<span class="pln">SELECT title
FROM books
UNION ALL
SELECT title
FROM articles</span>

SELECT title

FROM books

UNION ALL

SELECT title

FROM articles

<span class="typ">IQueryable</span><span class="str">&lt;string&gt;</span><span class="pln"> titles </span><span class="pun">=</span><span class="pln">
    db</span><span class="pun">.</span><span class="typ">Books</span><span class="pun">.</span><span class="typ">Select</span><span class="pun">(</span><span class="pln">b </span><span class="pun">=&gt;</span><span class="pln"> b</span><span class="pun">.</span><span class="typ">Title</span><span class="pun">)</span>
            <span class="pun">.</span><span class="typ">Concat</span><span class="pun">(</span><span class="pln">db</span><span class="pun">.</span><span class="typ">Articles</span><span class="pun">.</span><span class="typ">Select</span><span class="pun">(</span><span class="pln">art </span><span class="pun">=&gt;</span><span class="pln"> art</span><span class="pun">.</span><span class="typ">Title</span><span class="pun">));</span>

IQueryable<string> titles =

.Concat(db.Articles.Select(art => art.Title));

UNION

<span class="pln">SELECT title
FROM books
UNION
SELECT title
FROM articles</span>

SELECT title

FROM books

UNION

SELECT title

FROM articles

<span class="typ">IQueryable</span><span class="str">&lt;string&gt;</span><span class="pln"> titles </span><span class="pun">=</span><span class="pln">
    db</span><span class="pun">.</span><span class="typ">Books</span><span class="pun">.</span><span class="typ">Select</span><span class="pun">(</span><span class="pln">b </span><span class="pun">=&gt;</span><span class="pln"> b</span><span class="pun">.</span><span class="typ">Title</span><span class="pun">)</span>
            <span class="pun">.</span><span class="typ">Union</span><span class="pun">(</span><span class="pln">db</span><span class="pun">.</span><span class="typ">Articles</span><span class="pun">.</span><span class="typ">Select</span><span class="pun">(</span><span class="pln">art </span><span class="pun">=&gt;</span><span class="pln"> art</span><span class="pun">.</span><span class="typ">Title</span><span class="pun">));</span>

IQueryable<string> titles =

.Union(db.Articles.Select(art => art.Title));

ORDER BY

See the data model and Entity Framework data context to understand the setup for the following examples.

single column

<span class="pln">SELECT </span><span class="pun">*</span><span class="pln">
FROM books
ORDER BY title</span>

SELECT *

FROM books

ORDER BY title

<span class="com">//fluent syntax</span>
<span class="typ">IQueryable</span><span class="pun">&lt;</span><span class="typ">Book</span><span class="pun">&gt;</span><span class="pln"> books </span><span class="pun">=</span><span class="pln"> db</span><span class="pun">.</span><span class="typ">Books</span><span class="pun">.</span><span class="typ">OrderBy</span><span class="pun">(</span><span class="pln">b </span><span class="pun">=&gt;</span><span class="pln"> b</span><span class="pun">.</span><span class="typ">Title</span><span class="pun">);</span>

<span class="com">//query syntax</span>
<span class="typ">IQueryable</span><span class="pun">&lt;</span><span class="typ">Book</span><span class="pun">&gt;</span><span class="pln"> books2 </span><span class="pun">=</span>
    <span class="kwd">from</span><span class="pln"> b </span><span class="kwd">in</span><span class="pln"> db</span><span class="pun">.</span><span class="typ">Books</span>
    <span class="kwd">orderby</span><span class="pln"> b</span><span class="pun">.</span><span class="typ">Title</span>
    <span class="kwd">select</span><span class="pln"> b</span><span class="pun">;</span>

//fluent syntax

IQueryable<Book> books = db.Books.OrderBy(b => b.Title);

//query syntax

IQueryable<Book> books2 =

from b in db.Books

orderby b.Title

select b;

DESC

<span class="pln">SELECT </span><span class="pun">*</span><span class="pln">
FROM books
ORDER BY title DESC</span>

SELECT *

FROM books

ORDER BY title DESC

All posts in Scripting

learn LINQ in 5 minutes

Data model

Entity Framework data context

SELECT

Where

Inner join

LEFT/RIGHT OUTER JOIN

FULL OUTER JOIN

CROSS JOIN

GROUP BY

HAVING

DISTINCT

UNION

ORDER BY