如何过滤“包含"?实体框架中的实体? [英] How to filter "Include" entities in entity framework?
问题描述
实体:
public class Room
{
public Room()
{
this.Reservations = new HashSet<Reservation>();
}
public int Id { get; set; }
public decimal Rate { get; set; }
public int HotelId { get; set; }
public virtual Hotel Hotel { get; set; }
public virtual ICollection<Reservation> Reservations { get; set; }
}
public class Hotel
{
public Hotel()
{
this.Rooms = new HashSet<Room>();
}
public int Id { get; set; }
public string Name { get; set; }
public virtual ICollection<Room> Rooms { get; set; }
}
public class Reservation
{
public int Id { get; set; }
public DateTime StartDate { get; set; }
public DateTime EndDate { get; set; }
public string ContactName { get; set; }
public int RoomId { get; set; }
public virtual Room Room { get; set; }
}
public class ExecutiveSuite : Room
{
}
public class DataContext : DbContext
{
public DbSet<Hotel> Hotels { get; set; }
public DbSet<Reservation> Reservations { get; set; }
public DbSet<Room> Rooms { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<Room>()
.HasKey(r => r.Id)
.HasRequired(r => r.Hotel)
.WithMany(r => r.Rooms)
.HasForeignKey(r => r.HotelId);
modelBuilder.Entity<Hotel>()
.HasKey(h => h.Id);
modelBuilder.Entity<Room>()
.HasMany(r => r.Reservations)
.WithRequired(r => r.Room)
.HasForeignKey(r => r.RoomId);
}
}
客户端代码(控制台应用):
The client code(console app):
static void Main(string[] args)
{
// initialize and seed the database
using (var context = new DataContext())
{
var hotel = new Hotel { Name = "Grand Seasons Hotel" };
var r101 = new Room { Rate = 79.95M, Hotel = hotel };
var es201 = new ExecutiveSuite { Rate = 179.95M, Hotel = hotel };
var es301 = new ExecutiveSuite { Rate = 299.95M, Hotel = hotel };
var res1 = new Reservation
{
StartDate = DateTime.Parse("3/12/2010"),
EndDate = DateTime.Parse("3/14/2010"),
ContactName = "Roberta Jones",
Room = es301
};
var res2 = new Reservation
{
StartDate = DateTime.Parse("1/18/2010"),
EndDate = DateTime.Parse("1/28/2010"),
ContactName = "Bill Meyers",
Room = es301
};
var res3 = new Reservation
{
StartDate = DateTime.Parse("2/5/2010"),
EndDate = DateTime.Parse("2/6/2010"),
ContactName = "Robin Rosen",
Room = r101
};
es301.Reservations.Add(res1);
es301.Reservations.Add(res2);
r101.Reservations.Add(res3);
hotel.Rooms.Add(r101);
hotel.Rooms.Add(es201);
hotel.Rooms.Add(es301);
context.Hotels.Add(hotel);
context.SaveChanges();
}
using (var context = new DataContext())
{
context.Configuration.LazyLoadingEnabled = false;
// Assume we have an instance of hotel
var hotel = context.Hotels.First();
// Explicit loading with Load() provides opportunity to filter related data
// obtained from the Include() method
context.Entry(hotel)
.Collection(x => x.Rooms)
.Query()
.Include(y => y.Reservations)
.Where(y => y is ExecutiveSuite && y.Reservations.Any())
.Load();
Console.WriteLine("Executive Suites for {0} with reservations", hotel.Name);
foreach (var room in hotel.Rooms)
{
Console.WriteLine("
Executive Suite {0} is {1} per night", room.Id,
room.Rate.ToString("C"));
Console.WriteLine("Current reservations are:");
foreach (var res in room.Reservations.OrderBy(r => r.StartDate))
{
Console.WriteLine(" {0} thru {1} ({2})", res.StartDate.ToShortDateString(),
res.EndDate.ToShortDateString(), res.ContactName);
}
}
}
Console.WriteLine("Press <enter> to continue...");
Console.ReadLine();
}
using ( var context = new DataContext() )
{
//context.Configuration.LazyLoadingEnabled = false;
// Assume we have an instance of hotel
var hotel = context.Hotels.First();
var rooms = context.Rooms.Include( r => r.Reservations ).Where( r => r is ExecutiveSuite && r.Reservations.Any() ).Where( r => r.Hotel.Id == hotel.Id );
Console.WriteLine( "Executive Suites for {0} with reservations", hotel.Name );
foreach ( var room in hotel.Rooms )
{
Console.WriteLine( "
Executive Suite {0} is {1} per night", room.Id,
room.Rate.ToString( "C" ) );
Console.WriteLine( "Current reservations are:" );
foreach ( var res in room.Reservations.OrderBy( r => r.StartDate ) )
{
Console.WriteLine( " {0} thru {1} ({2})", res.StartDate.ToShortDateString(),
res.EndDate.ToShortDateString(), res.ContactName );
}
}
}
我尝试将其投影并放入匿名对象中:
I tried projecting and putting it in an anonymous object:
var hotel = context.Hotels.Select(h =>
new
{
Id = h.Id,
Name = h.Name,
Rooms = h.Rooms.Where(r => r.Reservations is ExecutiveSuite && r.Reservations.Any())
}).First();
但我得到一个例外:DbIsOfExpression 需要一个表达式参数，该表达式参数具有与类型参数兼容的多态结果类型."
but I get an exception: "DbIsOfExpression requires an expression argument with a polymorphic result type that is compatible with the type argument."
现在，如果您注意到，我以两种不同的方式实现它，首先是通过显式加载相关实体，其次是通过两个不同的查询，我的问题是，有没有一种方法可以加载我的对象图并过滤我包含"的实体，只需一次从数据库中访问?
Now, if you would notice, I implemented it in two different ways, first was by explicitly loading the related entities, second was by having two different queries, my question would be, is there a way I can load my object graph and filter the entities I "Include" with just a single trip from the database?
推荐答案
过滤包含实体有两种方式.
There are two ways to filter include Entity.
- 使用投影(参见@Eldho 答案)
- 使用第三方库
免责声明:我是项目的所有者Entity Framework Plus
EF+ Query IncludeFilter 允许轻松过滤包含的实体.
The EF+ Query IncludeFilter allows to easily filter included entities.
context.Entry(hotel)
.Collection(x => x.Rooms)
.Query()
.IncludeFilter(y => y.Reservations
.Where(z => z is ExecutiveSuite && z.Reservations.Any())
.Load();
在引擎盖下，图书馆确实做了一个投影.
Under the hood, the library does exactly a projection.
Wiki:EF+ 查询包含过滤器
编辑:回答子问题
你几乎做到了.房间已包含并过滤，但您未包含预订.
You almost did it. The rooms were included and filtered, but you didn't include the reservations.
var hotel = context.Hotels
// Include only executive suite with a reservation
.IncludeFilter(x => x.Rooms.Where(y => y is ExecutiveSuite && y.Reservations.Any()))
// Include only reservation from executive suite
.IncludeFilter(x => x.Rooms.Where(y => y is ExecutiveSuite).Select(z => z.Reservations))
.First();
回答评论
我们如何使用包含过滤器包含多级属性
How can we include multilevel properties with include filter
您可以通过指定每个路径(每个 IncludeFilter 一个)来包含多级
You can include multilevel by specifying each path (one per IncludeFilter)
所以 qry.Include("Rooms.Hotel") 变成:
qry.IncludeFilter(x => x.Rooms)
.IncludeFilter(x => x.Rooms.Select(y => y.Hotel))
回答评论
EF+ 是否支持 dotnet 5.0?
does EF+ have dotnet 5.0 support?
是的，它支持 dotnet 5.0 和 EF Core 5.0.但是，对于 IncludeFilter，您还应该直接在 EF Core 5 中查看已过滤的 include:https://www.learnentityframeworkcore5.com/whats-new-in-ef-core-5/filtered-included
Yes, it supports dotnet 5.0 and EF Core 5.0. However, for IncludeFilter, you should also look at the filtered include built-in directly in EF Core 5: https://www.learnentityframeworkcore5.com/whats-new-in-ef-core-5/filtered-included
这篇关于如何过滤“包含"?实体框架中的实体?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
