DotVVM框架中跨两张表的LINQ查询实现求助

嘿，刚上手C#和DotVVM就碰到多表LINQ查询的问题太正常了！我来结合你的Autors表给你拆解几种最常用的写法——首先咱们得补个关联表的例子，毕竟跨表得有两张关联的表嘛，就拿最常见的Books表来举例，它的创建语句大概是这样的：

CREATE TABLE [dbo].[Books] (
    [ID] INT IDENTITY (1, 1) NOT NULL,
    [Title] NVARCHAR (MAX) NULL,
    [AuthorID] INT NULL,
    [PublishDate] DATE NULL,
    FOREIGN KEY ([AuthorID]) REFERENCES [dbo].[Autors]([ID])
)

如果你的项目用了Entity Framework Core（DotVVM通常和它搭配），应该已经在实体类里配置了导航属性吧？比如Autors类里加个ICollection<Book> Books，Book类里加个Autor Autor。这种情况下查询简直小菜一碟：

比如要查所有作者和他们的全部书籍：

var authorsWithBooks = dbContext.Autors
    .Include(a => a.Books) // 加载关联的书籍数据
    .ToList();

要是想筛选特定条件，比如找写过带"C#"字样书籍的作者：

var authorsWithCSharpBooks = dbContext.Autors
    .Include(a => a.Books)
    .Where(a => a.Books.Any(b => b.Title.Contains("C#")))
    .ToList();

如果没配置导航属性，或者需要更灵活的关联逻辑，就用手动Join：

内连接（只返回两边都有匹配的记录） #

比如要把作者和对应的书籍配对，只显示有书籍的作者：

// 查询表达式写法
var authorBookPairs = from a in dbContext.Autors
                      join b in dbContext.Books on a.ID equals b.AuthorID
                      select new 
                      {
                          AuthorFullName = $"{a.Jmeno} {a.Prijmeni}",
                          BookTitle = b.Title,
                          PublishDate = b.PublishDate
                      };

// 方法链写法
var authorBookPairs = dbContext.Autors
    .Join(
        dbContext.Books,
        author => author.ID,
        book => book.AuthorID,
        (author, book) => new 
        {
            AuthorFullName = $"{author.Jmeno} {author.Prijmeni}",
            BookTitle = book.Title,
            PublishDate = book.PublishDate
        }
    )
    .ToList();

左连接（返回所有作者，哪怕没有书籍） #

要是想包含那些还没写过书的作者，就得用GroupJoin配合DefaultIfEmpty：

var allAuthorsWithBooks = from a in dbContext.Autors
                          join b in dbContext.Books on a.ID equals b.AuthorID into authorBooksGroup
                          from book in authorBooksGroup.DefaultIfEmpty()
                          select new 
                          {
                              AuthorID = a.ID,
                              AuthorFullName = $"{a.Jmeno} {a.Prijmeni}",
                              BookTitle = book?.Title ?? "暂无书籍"
                          };

比如要找所有捷克籍（假设Narodnost值为"Česká"）作者写的书籍，不用返回整张表的内容：

// 用导航属性筛选
var czechAuthoredBooks = dbContext.Books
    .Where(b => b.Autor.Narodnost == "Česká")
    .Select(b => new 
    {
        BookTitle = b.Title,
        PublishDate = b.PublishDate,
        AuthorName = $"{b.Autor.Jmeno} {b.Autor.Prijmeni}"
    })
    .ToList();

// 用Join筛选
var czechAuthoredBooks = from b in dbContext.Books
                         join a in dbContext.Autors on b.AuthorID equals a.ID
                         where a.Narodnost == "Česká"
                         select new 
                         {
                             BookTitle = b.Title,
                             PublishDate = b.PublishDate,
                             AuthorName = $"{a.Jmeno} {a.Prijmeni}"
                         };

内容的提问来源于stack exchange，提问作者Vojtěch Giesl