The Include path expression must refer to a navigation property defined on the type EF Using relation Tables

Question

I am working with multiple tables like master and child table. One master has many child record. I just want to fetch child record on conditional based. When i try to get record it gives me following error.

The Include path expression must refer to a navigation property defined on the type

Here is my code

 var _vatId = (int)Enumerations.Enumerations.Vat.Standard;
            var master = _db.PurchaseOrderMaster.Include(x => x.tbl_PurchaseOrderDetails.Where(a => a.VatId == _vatId)).FirstOrDefault(x => x.Id == PurchaseOrderId);

Why i am doing this because if master has 1000 child records and i just need 100 then i must use where clause to fetch 100 records rather than 1000 records.

Answer 1

Are you sure you want to use Include instead of Select?

One of the slower parts of a database query is the transport of the selected data from the DBMS to your local process. Hence it is wise to select only the data that you actually plan to use.

If PurchaseOrder 4 has 1000 PurchaseOrderDetails, then every PurchaseOrderDetail will have a foreign key PurchaseOrderId with a value 4. It would be a waste to transport this same value 4 a 1001 times, while 1 time would be sufficient.

// fetch (several properties of) purchase orders with their details
var result = dbContext.PurchaseOrders
    .Where(purchaseOrder => ...)          // only if you don't want all Purchase Orders
    .Select(purchaseOrder => new
    {
         // select only the properties that you plan to use
         Id = purchaseOrder.Id,
         Name = purchaseOrder.Name,
         ...

         Details = purchaseOrder.PurchaseOrderDetails
             // only if you don't want all Details of this purchase order:
             .Where(detail => ...)        
             .Select(detail => new
             {
                  // again: select only the properties you plan to use
                  Id = detail.Id,
                  ...

                  // not needed: you know the value:
                  // PurchaseOrderId = detail.PurchaseOrderId,
             })
             .ToList(),
    });

In entity framework always use Select to query data and select only the properties you actually plan to use. Only use Include if you intend to update the fetched data