如何筛选“包含”在实体框架的实体？如何筛选“包含”在实体框架的实体？(How to filter “

实体：

    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);

        }
    }

客户端代码（控制台应用程序）：

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("\nExecutive 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("\t{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( "\nExecutive 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( "\t{0} thru {1} ({2})", res.StartDate.ToShortDateString(),
                                res.EndDate.ToShortDateString(), res.ContactName );
           }
        }
     }

我试图突出，并且把它在一个匿名对象：

       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需要用与类型参数兼容的多态的结果类型的表达式的说法。”

现在，如果你会发现，我实现了这两种不同的方式，第一种是通过明确的加载相关实体，二是通过具有两个不同的查询，我的问题是，有没有办法，我可以载入我的对象图和过滤实体I“包括”从数据库只是一个单一的行程？

Answer 1:

有两种方法来过滤包括实体。

• 使用投影（见@Eldho答案）
• 使用第三方库

免责声明 ：我是项目的主人实体框架加

该EF +查询IncludeFilter可以轻松地过滤包括实体。

context.Entry(hotel)
       .Collection(x => x.Rooms)
       .Query()
       .IncludeFilter(y => y.Reservations
                            .Where(z => z is ExecutiveSuite && z.Reservations.Any())
       .Load();

引擎盖下，图书馆不正是一个投影。

维基： EF +查询包含过滤器

编辑 ：回答subquestion

你几乎做到了。 酒店的客房都包括在内，并过滤，但不包括保留意见。

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();

Answer 2:

需要注意的是目前还无法过滤相关实体被载入其中。 包括将始终在所有相关实体带来参考MSDN

申请此功能在这里

为了过滤子集合你可以尝试select该模型或匿名的投影。

var anonymousProjection = dbContext.CustomerEntity
                                 .Where(c => ! c.IsDeleted)
                                 .Select(x=> new 
                                  {
                                       customers = x,
                                       orders = x.Orders.Where(h=>h.IsDeleted)
                                  }).ToList();

类似的答案

Answer 3:

我想带来一个全新的视角来此的。 尽管这不会解决这个问题，它可以帮助你。 使用AutoMapper，你可以把它们在目标对象之前筛选集合。 我安装我的方式解决，一切都在映射的DTO任何行动之前，所以我使用AutoMapper为那些包括过滤器。 奇迹般有效...

文章来源: How to filter “Include” entities in entity framework?