Repository navigation
Joins
Joins use LINQ query syntax. There are no navigation properties, so you write the join explicitly each time.
Returns only rows that have a match in both tables.
var results = await (
from book in db.Table<Book>()
join author in db.Table<Author>() on book.AuthorId equals author.Id
select new { book.Title, author.Name, book.Price }
).ToListAsync();You can also use the fluent Join method:
var results = await db.Table<Book>()
.Join(
db.Table<Author>(),
book => book.AuthorId,
author => author.Id,
(book, author) => new { book.Title, author.Name, book.Price }
)
.ToListAsync();Returns all rows from the left table and matching rows from the right. Where there is no match, the right side values will be null.
var results = await (
from author in db.Table<Author>()
join book in db.Table<Book>() on author.Id equals book.AuthorId into bookGroup
from book in bookGroup.DefaultIfEmpty()
select new { author.Name, book.Title }
).ToListAsync();In this group-join form, the second from must be directly after the join.
You can keep the group and aggregate it instead of flattening it. Count and LongCount translate directly, and their projected values can be aggregated later.
var counts = await db.Table<Author>()
.GroupJoin(
db.Table<Book>(),
author => author.Id,
book => book.AuthorId,
(author, books) => new { author.Name, BookCount = books.Count() })
.ToListAsync();Returns matched rows plus the unmatched rows from both sides. Use the built-in FullJoin on .NET 11 or later, or the framework's FullOuterJoin on .NET 8–10. The result selector gets the outer row (null when only an inner row matched) and the inner row (null when only an outer row matched). Requires SQLite 3.39 or newer.
var results = await db.Table<Author>()
.FullJoin(
db.Table<Book>(),
author => author.Id,
book => book.AuthorId,
(author, book) => new { Author = author == null ? null : author.Name, Book = book == null ? null : book.Title })
.ToListAsync();.NET 10 and later support Queryable.LeftJoin and Queryable.RightJoin with a result selector. .NET 11 adds Queryable.FullJoin and selector-free Join, LeftJoin, RightJoin and FullJoin overloads returning (Outer, Inner) tuples:
var pairs = await db.Table<Author>()
.FullJoin(db.Table<Book>(), author => author.Id, book => book.AuthorId)
.ToListAsync();FullJoin and RightJoin require SQLite 3.39 or newer.
The selector-free .NET 11 GroupJoin returns IGrouping<TOuter, TInner>, whose Key is the outer row. Follow it directly with SelectMany, optionally using DefaultIfEmpty for a left join:
var rows = await db.Table<Author>()
.GroupJoin(db.Table<Book>(), author => author.Id, book => book.AuthorId)
.SelectMany(group => group.DefaultIfEmpty(), (group, book) => new
{
Author = group.Key.Name,
Title = book == null ? null : book.Title
})
.ToListAsync();Materializing unflattened join groups is not supported. For group counts, use the result-selector form shown above.
Returns every combination of rows from both tables.
var results = await (
from author in db.Table<Author>()
from book in db.Table<Book>()
select new { author.Name, book.Title }
).ToListAsync();A filtered table can be used as the second from source. A normal filtered source becomes an inner join. Adding DefaultIfEmpty() makes it a left join and returns null for a missing row.
var matches = await (
from author in db.Table<Author>()
from book in db.Table<Book>()
.Where(book => book.AuthorId == author.Id && book.Price > 0)
.DefaultIfEmpty()
select new { author.Name, BookTitle = book == null ? null : book.Title }
).ToListAsync();The correlated filter must be translatable to SQL.
Chain as many joins as you need:
var results = await (
from book in db.Table<Book>()
join author in db.Table<Author>() on book.AuthorId equals author.Id
join publisher in db.Table<Publisher>() on book.PublisherId equals publisher.Id
select new { book.Title, author.Name, publisher.CompanyName }
).ToListAsync();You can also mix inner and left joins:
var results = await (
from book in db.Table<Book>()
join author in db.Table<Author>() on book.AuthorId equals author.Id
join review in db.Table<Review>() on book.Id equals review.BookId into reviewGroup
from review in reviewGroup.DefaultIfEmpty()
select new { book.Title, author.Name, review.Rating }
).ToListAsync();Add a where clause after the join:
var results = await (
from book in db.Table<Book>()
join author in db.Table<Author>() on book.AuthorId equals author.Id
where author.Country == "USA" && book.Price < 30
select new { book.Title, author.Name }
).ToListAsync();