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.
- SELECT
- WHERE
- INNER JOIN
- LEFT/RIGHT OUTER JOIN
- FULL OUTER JOIN
- CROSS JOIN
- GROUP BY
- HAVING
- DISTINCT
- UNION
- ORDER BY
- CASE
- COALESCE
- Aggregation functions
Data model
We’ll use a simple data model that contains books and authors for our examples.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
<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> |
Entity Framework data context
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 |
<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"><</span><span class="typ">Author</span><span class="pun">></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"><</span><span class="typ">Book</span><span class="pun">></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"><</span><span class="typ">Article</span><span class="pun">></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"><</span><span class="typ">Author</span><span class="pun">>().</span><span class="typ">HasKey</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">AuthorId</span><span class="pun">);</span><span class="pln"> modelBuilder</span><span class="pun">.</span><span class="typ">Entity</span><span class="pun"><</span><span class="typ">Author</span><span class="pun">>()</span> <span class="pun">.</span><span class="typ">HasMany</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="pun">.</span><span class="typ">WithRequired</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="pln"> modelBuilder</span><span class="pun">.</span><span class="typ">Entity</span><span class="pun"><</span><span class="typ">Author</span><span class="pun">>()</span> <span class="pun">.</span><span class="typ">HasMany</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">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">=></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"><</span><span class="typ">Book</span><span class="pun">>().</span><span class="typ">HasKey</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">BookId</span><span class="pun">);</span><span class="pln"> modelBuilder</span><span class="pun">.</span><span class="typ">Entity</span><span class="pun"><</span><span class="typ">Book</span><span class="pun">>()</span> <span class="pun">.</span><span class="typ">HasRequired</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="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">=></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"><</span><span class="typ">Article</span><span class="pun">>().</span><span class="typ">HasKey</span><span class="pun">(</span><span class="pln">art </span><span class="pun">=></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"><</span><span class="typ">Article</span><span class="pun">>()</span> <span class="pun">.</span><span class="typ">HasRequired</span><span class="pun">(</span><span class="pln">art </span><span class="pun">=></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">=></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"><</span><span class="typ">Book</span><span class="pun">></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"><</span><span class="typ">Articles</span><span class="pun">></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> |
SELECT
See the data model and Entity Framework data context to understand the setup for the following examples.
1 2 |
<span class="pln">SELECT </span><span class="pun">*</span><span class="pln"> FROM books</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> |
1 2 |
<span class="pln">SELECT TOP </span><span class="lit">1</span> <span class="pun">*</span><span class="pln"> FROM books</span> |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
<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"><</span><span class="typ">Book</span><span class="pun">></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> |
1 2 |
<span class="pln">SELECT title FROM books</span> |
1 2 3 4 5 6 7 |
<span class="com">//fluent syntax</span> <span class="typ">IQueryable</span><span class="str"><string></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">=></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"><string></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> |
1 2 |
<span class="pln">SELECT title</span><span class="pun">,</span><span class="pln"> numPages FROM books</span> |
1 2 3 4 5 6 7 8 |
<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">=></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> |
Where
See the data model and Entity Framework data context to understand the setup for the following examples.
1 2 3 |
<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> |
1 2 3 4 5 6 7 8 |
<span class="com">//Fluent syntax</span> <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><span class="typ">Where</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">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"><</span><span class="typ">Book</span><span class="pun">></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> |
1 2 3 |
<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> |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
<span class="com">//fluent syntax with && operator</span> <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><span class="typ">Where</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">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">//fluent syntax and chaining where operations</span> <span class="typ">IQueryable</span><span class="pun"><</span><span class="typ">Book</span><span class="pun">></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">=></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">=></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 && operator</span> <span class="typ">IQueryable</span><span class="pun"><</span><span class="typ">Book</span><span class="pun">></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">&&</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"><</span><span class="typ">Book</span><span class="pun">></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> |
1 2 3 |
<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> |
1 2 3 4 5 6 7 8 9 |
<span class="com">//fluent syntax</span> <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><span class="typ">Where</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">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"><</span><span class="typ">Book</span><span class="pun">></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> |
1 2 3 4 5 6 7 8 9 10 11 12 13 |
<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"><</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><span class="typ">Where</span><span class="pun">(</span><span class="pln">b </span><span class="pun">=></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"><</span><span class="typ">Book</span><span class="pun">></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> |
1 2 3 |
<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> |
1 2 3 4 5 6 7 8 9 |
<span class="com">//fluent syntax</span> <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><span class="typ">Where</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">NumPages</span> <span class="pun">>=</span> <span class="lit">200</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">300</span><span class="pun">);</span> <span class="com">//query syntax</span> <span class="typ">IQueryable</span><span class="pun"><</span><span class="typ">Book</span><span class="pun">></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">>=</span> <span class="lit">200</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">300</span> <span class="kwd">select</span><span class="pln"> b</span><span class="pun">;</span> |
Inner join
See the data model and Entity Framework data context to understand the setup for the following examples.
1 2 3 4 |
<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> |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
<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">=></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">=></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">=></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">=></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> |
LEFT/RIGHT OUTER JOIN
See the data model and Entity Framework data context to understand the setup for the following examples.
1 2 3 4 |
<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> |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
<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">=></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">=></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">=></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">=></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> |
1 2 3 4 |
<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> |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
<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">=></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">=></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">=></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">=></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> |
FULL OUTER JOIN
See the data model and Entity Framework data context to understand the setup for the following examples.
1 2 3 4 |
<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> |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
<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> |
CROSS JOIN
See the data model and Entity Framework data context to understand the setup for the following examples.
1 2 3 |
<span class="pln">SELECT </span><span class="pun">*</span><span class="pln"> FROM books b CROSS JOIN authors a</span> |
1 2 3 4 5 6 7 8 9 |
<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">=></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> |
GROUP BY
See the data model and Entity Framework data context to understand the setup for the following examples.
1 2 3 |
<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> |
1 2 3 4 5 6 7 8 9 10 |
<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">=></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">=></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> |
1 2 3 |
<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> |
1 2 3 4 5 6 7 8 9 10 |
<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">=></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">=></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> |
HAVING
See the data model and Entity Framework data context to understand the setup for the following examples.
1 2 3 4 |
<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">></span> <span class="lit">10</span> |
1 2 3 4 5 6 7 8 9 10 11 12 |
<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">=></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">=></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">=></span><span class="pln"> g</span><span class="pun">.</span><span class="typ">Count</span> <span class="pun">></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">></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> |
DISTINCT
See the data model and Entity Framework data context to understand the setup for the following examples.
1 2 |
<span class="pln">SELECT DISTINCT </span><span class="pun">*</span><span class="pln"> FROM books</span> |
1 |
<span class="typ">IQueryable</span><span class="pun"><</span><span class="typ">Book</span><span class="pun">></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> |
UNION
See the data model and Entity Framework data context to understand the setup for the following examples.
1 2 3 4 5 |
<span class="pln">SELECT title FROM books UNION ALL SELECT title FROM articles</span> |
1 2 3 |
<span class="typ">IQueryable</span><span class="str"><string></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">=></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">=></span><span class="pln"> art</span><span class="pun">.</span><span class="typ">Title</span><span class="pun">));</span> |
1 2 3 4 5 |
<span class="pln">SELECT title FROM books UNION SELECT title FROM articles</span> |
1 2 3 |
<span class="typ">IQueryable</span><span class="str"><string></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">=></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">=></span><span class="pln"> art</span><span class="pun">.</span><span class="typ">Title</span><span class="pun">));</span> |
ORDER BY
See the data model and Entity Framework data context to understand the setup for the following examples.
1 2 3 |
<span class="pln">SELECT </span><span class="pun">*</span><span class="pln"> FROM books ORDER BY title</span> |
1 2 3 4 5 6 7 8 |
<span class="com">//fluent syntax</span> <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><span class="typ">OrderBy</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">Title</span><span class="pun">);</span> <span class="com">//query syntax</span> <span class="typ">IQueryable</span><span class="pun"><</span><span class="typ">Book</span><span class="pun">></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> |
1 2 3 |
<span class="pln">SELECT </span><span class="pun">*</span><span class="pln"> FROM books ORDER BY title DESC</span> |