How to query IQueryable with Include - ThenInclude?

Question

In .NET Core 2.2 I'm stuck with filtering IQueryable built as:

_context.Ports.Include(p => p.VesselsPorts)
              .ThenInclude(p => p.Arrival)
              .Include(p => p.VesselsPorts)
              .ThenInclude(p => p.Departure)
              .OrderBy(p => p.PortLocode);

in many-to-many relation. And the entity models are such as:

public class PortModel
{
        [Key]
        public string PortLocode { get; set; }
        public double? MaxKnownLOA { get; set; }
        public double? MaxKnownBreadth { get; set; }
        public double? MaxKnownDraught { get; set; }
        public virtual ICollection<VesselPort> VesselsPorts { get; set; }
}

public class VesselPort
{
        public int IMO { get; set; }
        public string PortLocode { get; set; }
        public DateTime? Departure { get; set; }
        public DateTime? Arrival { get; set; }
        public VesselModel VesselModel { get; set; }
        public PortModel PortModel { get; set; }
}

Based on this this SO answer I managed to create LINQ like that:

_context.Ports.Include(p => p.VesselsPorts).ThenInclude(p => p.Arrival).OrderBy(p => p.PortLocode)
.Select(
                    p => new PortModel
                    {
                        PortLocode = p.PortLocode,
                        MaxKnownBreadth = p.MaxKnownBreadth,
                        MaxKnownDraught = p.MaxKnownDraught,
                        MaxKnownLOA = p.MaxKnownLOA,
                        VesselsPorts = p.VesselsPorts.Select(vp => vp.Arrival > DateTime.UtcNow.AddDays(-1)) as ICollection<VesselPort>
                    }).AsQueryable();

BUT what I need is to find all port records, where: VesselsPorts.Arrival > DateTime.UtcNow.AddDays(-1) quantity is greater than int x = 5 value (for the example). And I have no clue how to do it :/

Answer 1

Thanks to @GertArnold comment, I ended up with query:

ports = ports.Where(p => p.VesselsPorts.Where(vp => vp.Arrival > DateTime.UtcNow.AddDays(-1)).Count() > x);

Answer 2

When using entity framework people tend to use Include instead of Select to save them some typing. It is seldom wise to do so.

The DbContext holds a ChangeTracker. Every complete row from any table that you fetch during the lifetime of the DbContext is stored in the ChangeTracker, as well as a clone. You get a reference to the copy. (or maybe a reference to the original). If you change properties of the data you got, they are changed in the copy that is in the ChangeTracker. During SaveChanges, the original is compared to the copy, to see if the data must be saved.

So if you are fetching quite a lot of data, and use include, then every fetched items is cloned. This might slow down your queries considerably.

Apart from this cloning, you will probably fetch more properties than you actually plan to use. Database management systems are extremely optimized in combining tables, and searching rows within tables. One of the slower parts is the transfer of the selected data to your local process.

For example, if you have a database with Schools and Students, with the obvious one to many-relation, then every Student will have a foreign key to the School he attends.

So if you ask for School [10] with his 2000 Students, then every Student will have a foreign key value of [10]. If you use Include, then you will be transferring this same value 10 over 2000 times. What a waste of processing power!

In entity framework, when querying data, always use Select to select the properties, and Select only the properties that you actually plan to use. Only use Include if you plan to change the fetched items.

Certainly don't use Include to save you some typing!

Requirement: Give me the Ports with their Vessels

var portsWithTheirVessels = dbContext.Ports
.Where(port => ...)       // if you don't want all Ports

.Select(port => new
{
    // only select the properties that you want:
    PortLocode = port.PortLoCode,
    MaxKnownLOA = port.MaxKnownLOA,
    MaxKnownBreadth = prot.MaxKnownBreadth,
    MaxKnownDraught = ports.MaxKnownDraught,

    // The Vessels in this port:
    Vessels = port.VesselsPort.Select(vessel => new
    {
       // again: only the properties that you plan to use
       IMO = vessel.IMO,
       ...

       // do not select the foreign key, you already know the value!
       // PortLocode = vessle.PortLocode,
    })
    .ToList(),
});

Entity framework knows your one-to-many relation, and knows that if you use the virtual ICollection that it should do a (Group-)Join.

Some people prefer to do the Group-Join themselves, or they use a version of entity framework that does not support using the ICollection.

var portsWithTheirVessels = dbContext.Ports.GroupJoin(dbContext.VesselPorts,

port => port.PortLocode,             // from every Port take the primary key
vessel => vessel.PortLocode,         // from every Vessel take the foreign key to Port

// parameter resultSelector: take every Port with its zero or more Vessels to make one new
(port, vesselsInThisPort) => new
{
    PortLocode = port.PortLoCode,
    ...

    Vessels = vesselsInThisPort.Select(vessel => new
    {
        ...
    })
    .ToList(),
});

Alternative:

var portsWithTheirVessels = dbContext.Ports.Select(port => new
{
    PortLocode = port.PortLoCode,
    ...

    Vessels = dbContext.VesselPorts.Where(vessel => vessel.PortLocode == port.PortLocode)
        .Select(vessel => new 
        {
            ...
        }
        .ToList(),

});

Entity framework will translate this also to a GroupJoin.