我有两个表。书名和作者。一个图书标题可以包含多个图书作者:
BookTitles:
- Book_Id
- BookName
BookAuthors:
- Author_Id
- AuthorName
- Book_Id
在第二个表中,Book_Id是一个外键。我使用的是实体框架,数据库优先。以下是BookTitles表的模型(自动创建):
public partial class BookTitles
{
public BookTitles()
{ this.BookAuthors = new HashSet<BookAuthors>(); }
public int Book_Id { get; set; }
public string BookName { get; set; }
public virtual ICollection<BookAuthors> BookAuthors { get; set; }
}
最后一个属性显示BookTitles类型的对象将包含从第二个表中检索到的BookAuthors对象列表。
问题:我如何检索BookTitles记录,这些记录将包含,例如,类型为BookAuthors的对象,该对象将具有BookAuthors.AuthorName=="AAA"?
我试过了,但没用:
using (var db = new BooksContext())
{
var books =
db.BookTitles.Where(x => x.BookAuthors.Contains(new BookAuthors() {AuthorName = "AAA"})).ToList();
}
如何使用Lambda表达式进行查询,以及如何使用LINQ to Entities进行查询?
使用Any
而不是Contains
:
var books =
db.BookTitles.Where(x => x.BookAuthors.Any(y => y.AuthorName == "AAA")).ToList();
或者,如果BookAuthor
包含指向BookTitles
的导航属性,则应该能够执行以下操作:
var books =
db.BookAuthors.FirstOrDefault(x => x.AuthorName == "AAA").BookTitles.ToList();
尝试以下操作:
using (var db = new BooksContext())
{
var books = from title in db.bookTitles
from author in title.BookAuthors
where author.AuthorName == "AAA"
select title;
}